Add support for composite primary keys in Astro DB

[ThomasGysemans]

Summary

In SQL it is possible to create a primary key that is a composite of several columns:

CREATE TABLE ArticleThemes (
  articleId INTEGER,
  theme TEXT,
  FOREIGN KEY (articleId) REFERENCES Article(id),
  FOREIGN KEY (theme) REFERENCES Theme(theme),
  PRIMARY KEY (articleId, theme)
);

I'd like to be able to do the same in Astro DB.

Background & Motivation

It's quite common to reach such situation where a ManyToMany relationship leads to composite primary key in SQL. I wish it would be possible for Astro DB to make the creation of databases look more like plain SQL.

Goals

• Easier ManyToMany relationship implementation in Astro DB like SQL does.
• Allow for easier implementation of the unique rule across multiple columns.

Example

I've encountered such situation where I have blog posts that all have 1 or more themes attached to them (in a table named "Article"). The themes are stored in another table named "Theme" and since it's a ManyToMany relationship my practice in SQL taught me that I should create a third table for linking the two that I named "ArticleThemes".

Here is what I'd write in plain 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
);

I'm a beginner in Astro and from what I have read from the documentation I'd implement it like this:

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

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

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

Unfortunately this isn't possible as primaryKey can only be applied to a single column. The code above produces an error that I think should not happen. I think it makes sense that if primaryKey is set to true to multiple columns then the SQL creation script should combine them.

As of now (v4.7.0 of Astro and v0.10.6 of AstroDB) I have no choice but to define ArticleThemes like this:

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

What's unfortunate is that I have no efficient way to make sure that the combination of articleId and theme is always unique across the whole table, whereas I could if I was writing the SQL script directly.

EDIT

Something I didn't know about when creating this discussion... it is actually possible to make sure the combination of articleId and theme are always unique with an index:

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

As explained here

However I maintain this discussion as I think a composite primary key would be a good thing to have in Astro DB.

[florian-lefebvre]

Astro DB has been deprecated