Astro DB: there is no way of creating a primary key composed of several columns

[ThomasGysemans]

Astro Info

Astro                    v4.7.0
Node                     v22.0.0
System                   macOS (arm64)
Package Manager          unknown
Output                   server
Adapter                  @astrojs/vercel/serverless
Integrations             @astrojs/tailwind
                         astro-icon
                         astro:db
                         @astrojs/db/file-url
                         @astrojs/svelte

If this issue only occurs in one browser, which browser is a problem?

No response

Describe the Bug

Hi, I'm trying to create a very simple database using Astro DB. I have a table for blog posts, a table for all the themes of my project and another table for associating articles to several themes. It's a ManyToMany relationship.

Here is how I'd do it in SQL:

CREATE TABLE Article (
  id INTEGER PRIMARY KEY,
  title TEXT,
  description TEXT,
  content TEXT
);

CREATE TABLE Theme (
  theme TEXT PRIMARY KEY
);

CREATE TABLE ArticleThemes (
  articleId INTEGER,
  theme TEXT,
  FOREIGN KEY (articleId) REFERENCES Article(id),
  FOREIGN KEY (theme) REFERENCES Theme(theme),
  PRIMARY KEY (articleId, theme) -- THE PROBLEM IS HERE
);

In Astro DB there is no way to create a primary key that is composed of several columns. As a consequence, I have no way to make sure that the combination (articleId, theme) will always be unique.

Here is what I've come up with so far (in /db/config.ts):

import { column, defineDb, defineTable } from 'astro:db';

const Article = defineTable({
    columns: {
        id: column.number({ primaryKey: true }),
        title: column.text(),
        description: column.text(),
        content: column.text(),
    }
});

const Theme = defineTable({
    columns: {
        theme: column.text({ primaryKey: true }),
    }
});

const ArticleThemes = defineTable({
    columns: {
        articleId: column.number({ references: () => Article.columns.id }),
        theme: column.text({ references: () => Theme.columns.theme }),
    }
});

// https://astro.build/db/config
export default defineDb({
    tables: {
        Article,
        ArticleThemes,
        Theme,
    }
});

How am I supposed to create a proper ManyToMany relationship like this one? I have noticed that a default primary key is created (named _id) if none is provided in the schema (I've noticed it by doing a npx astro db shell --query "select * from articleThemes"), which is not very practical since I'd like a composite.

What's the expected result?

I wish I could do this:

const ArticleThemes = defineTable({
    columns: {
        articleId: column.number({ primaryKey: true, references: () => Article.columns.id }),
        theme: column.text({ primaryKey: true, references: () => Theme.columns.theme }),
    },
});

which is not possible as of now since primaryKey can only be applied to a single column. Therefore the code above generates an error.

Link to Minimal Reproducible Example

https://stackblitz.com/github/ThomasGysemans/astrodb-blog-demo

Participation

• [X] I am willing to submit a pull request for this issue.

[jacobdalamb]

Are you trying to create a composite key?

If so you can use indexes, in your case, you can modify the ArticleThemes table definition like this:

const ArticleThemes = defineTable({
    columns: {
        articleId: column.number({ references: () => Article.columns.id }),
        theme: column.text({ references: () => Theme.columns.theme }),
    },
    indexes: [{ on: ["articleId", "theme"], unique: true }],
});

[ThomasGysemans]

That is exactly what I'm trying to do. I thought it would make sense to create a composite primary key to make sure such combination was always unique throughout the whole table. I didn't think about a unique index. Thank you, this solves my issue.

However, I still wonder if creating a composite primary key would be possible one day (as I suggested here https://github.com/withastro/roadmap/discussions/910)