Foreign keys topic

This document aims to demonstrate how to use foreign keys when writing queries in package:typed_sql.

Examples throughout this document assume a database schema defined as follows:

abstract final class Bookstore extends Schema {
  Table<Author> get authors;
  Table<Book> get books;
}

@PrimaryKey(['authorId'])
abstract final class Author extends Row {
  @AutoIncrement()
  int get authorId;

  @Unique()
  String get name;
}

@PrimaryKey(['bookId'])
abstract final class Book extends Row {
  @AutoIncrement()
  int get bookId;

  String? get title;

  @References(table: 'authors', field: 'authorId', name: 'author', as: 'books')
  int get authorId;

  @DefaultValue(0)
  int get stock;
}

And that the database is loaded with the following examples:

final initialAuthors = [
  (name: 'Easter Bunny',),
  (name: 'Bucks Bunny',),
];

final initialBooks = [
  // By Easter Bunny
  (title: 'Are Bunnies Unhealthy?', authorId: 1, stock: 10),
  (title: 'Cooking with Chocolate Eggs', authorId: 1, stock: 0),
  (title: 'Hiding Eggs for dummies', authorId: 1, stock: 12),
  // By Bucks Bunny
  (title: 'Vegetarian Dining', authorId: 2, stock: 42),
  (title: 'Vegan Dining', authorId: 2, stock: 3),
];

Declaring foreign keys

In the schema above Book.authorId is declared as a foreign key using the @References annotation. The table and field parameters are required, these point to the table and field that is being referenced. The name and as properties are optional, if present they will be used to generate convenient subquery properties when building queries.

@PrimaryKey(['bookId'])
abstract final class Book extends Row {
  @AutoIncrement()
  int get bookId;

  String? get title;

  @References(
    // This fields references "authorId" from "authors" table
    table: 'authors',
    field: 'authorId',

    // The reference is _named_ "author", this gives rise to a
    // Expr<Book>.author property when building queries.
    name: 'author', // optional

    // This is referenced _as_ "books", this gives rise to a
    // Expr<Author>.books property when building queries.
    as: 'books', // optional
  )
  int get authorId;

  @DefaultValue(0)
  int get stock;
}

Note

At this point package:typed_sql does not support composite foreign keys. There is probably nothing that fundamentally makes it impossible to introduce support for composite foreign keys in the future. But in most cases, you should probably avoid such constructs when possible.

Following references in a query (using reference name)

With the @References annotation in place, package:typed_sql will use the name: 'author' parameter to generate an extension method Expr<Book>.author. The author extension method will return a subquery looking up the row in the authors table referenced by Book.authorId.

The following example shows, how we can use the Expr<Book>.author to lookup a book and its author in a single query.

final bookAndAuthor = await db.books
    .byKey(1)
    .select((book) => (
          book,
          book.author, // <-- use the 'author' subquery property
        ))
    .fetch(); // return Future<(Book, Author)?>

if (bookAndAuthor == null) {
  throw Exception('Book not found');
}
final (book, author) = bookAndAuthor;
check(book.title).equals('Are Bunnies Unhealthy?');
check(author.name).equals('Easter Bunny');

We can also use the Expr<Book>.author property in .where, and we can access properties on the Expr<Author> returned by Expr<Book>.author, as demonstrated in the following example:

final titleAndAuthor = await db.books
    .where((book) => book.author.name.equals(toExpr('Easter Bunny')))
    .select((book) => (
          book.title,
          book.author.name,
        ))
    .fetch();

check(titleAndAuthor).unorderedEquals([
  // title, author
  ('Are Bunnies Unhealthy?', 'Easter Bunny'),
  ('Cooking with Chocolate Eggs', 'Easter Bunny'),
  ('Hiding Eggs for dummies', 'Easter Bunny'),
]);

Had the Book.authorId been nullable, then Expr<Book>.author would have returned Expr<Author?>. But you can still access properties on Expr<Author?>, however, you'll find that all properties are nullable.

It's also worth noting that if we had omitted the name: 'author' parameter from the @References annotation, then the Expr<Book>.author extension method would not have been generated. So you can decide whether or not you want the these helper methods.

While subqueries are simple to use, and outright fantastic when writing point queries, as they make it easy to return a row along with any referenced rows in a single query. Subqueries can also lead to poor query performance, in which case joins might be a better option.

Warning

When accessing properties on a subquery like Expr<Book>.author.name it is important to remember that accessing two properties leads to two subqueries. It's possible that the query optimizer will coalesce them, but this might depend on your database.

Following referenced-by in a query (using reference as)

The @Reference annotation also has an optional as: books parameter. This will be used by package:typed_sql to generate an extension method Expr<Author>.books. The books extension method will return a SubQuery<(Expr<Book>,)> matching all the rows in the books table that reference the given row in the authors table.

We can't directly return a SubQuery in a.select projection, but we can use .first or an aggregate function on the SubQuery in a .select projection, or .where filter. The following example shows how lookup authorId = 1 along with the count of books referencing said row the authors table.

final authorAndCount = await db.authors
    .byKey(1)
    .select((author) => (
          author,
          author.books.count(),
        ))
    .fetch();

if (authorAndCount == null) {
  throw Exception('Author not found');
}
final (author, count) = authorAndCount;
check(author.name).equals('Easter Bunny');
check(count).equals(3);

This is particularly convenient for point queries as demonstrated above. But you can also use it for queries returning multiple rows, as in the example below.

final authorAndCount = await db.authors
    .select((author) => (
          author.name,
          author.books.count(),
        ))
    .fetch();

check(authorAndCount).unorderedEquals([
  // name, count
  ('Easter Bunny', 3),
  ('Bucks Bunny', 2),
]);

While it's possible to count authors for each book using a subquery, as in the example above. It may be easier for the query optimizer to optimize this query, if we used .join and .groupBy, see Aggregate functions for more details.

Composite foreign keys

As illustrated in the examples above the @References annotation is used on a field to define the field as a foreign key. This works great until you need to define a composite foreign key. If a foreign key consists of multiple fields, then we can't use the @References annotation. For this we need to use the @ForeignKey annotation on the row class.

The following example illustrates how to define a composite foreign key using the @ForeignKey annotation.

abstract final class BlogDatabase extends Schema {
  Table<Post> get posts;
  Table<Comment> get comments;
}

@PrimaryKey(['author', 'slug'])
abstract final class Post extends Row {
  String get author;
  String get slug;

  String get content;
}

@PrimaryKey(['commentId'])
@ForeignKey(
  ['author', 'postSlug'],
  table: 'posts',
  fields: ['author', 'slug'],
  name: 'post',
  as: 'comments',
)
abstract final class Comment extends Row {
  int get commentId;

  // composite foreign key
  String get author;
  String get postSlug;

  String get comment;
}

The @ForeignKey annotation works the same as the @References annotation, except that it is possible to include multiple fields in the foreign key. Similar to @References the optional name and as attributes give rise to extension methods on expressions for Expr<Comment> and Expr<Post> respectively.

The @ForeignKey annotation can be used to define foreign keys consistent of a single field, ie. foreign keys are that not composite foreign keys. When used in this manner the @ForeignKey annotation behaves the same as the @References annotation, the only difference being where the annotation is attached.

Tip

Prefer using the @References annotation when defining a foreign key consistent of a single field.

Classes

ForeignKey

Annotation for declaring a composite foreign key.

References

Annotation for fields that references fields from another table.