Database

Repositories & Queries

Introduction#

You read and write data with Kysely's query builder, typed by your app's tables. A repository is an optional class for one table that keeps its queries in one place: rows go in, rows come out, and nothing happens that you did not call. There is no lazy loading and no saving behind your back.

import { Repository } from '@marmeon/database';

export class NoteRepository extends Repository<'notes'> {
  static table = 'notes' as const;

  latestOf(userId: number) {
    return this.query().selectAll().where('user_id', '=', userId).orderBy('updated_at', 'desc').limit(10).execute();
  }
}

A controller asks for NoteRepository in its constructor and calls latestOf(), find() or insert(). Every name in a query is checked against the table's type, and the result is typed by what the query selects.

Table types#

The types of your tables come from your migrations, never from the state of a database. marmeon db:types runs every migration on a scratch database of the app's driver, SQLite in memory or a temporary Postgres database, reads the tables back and writes bootstrap/database.d.ts. The file is committed, so a type check needs no database. On Postgres the command creates and drops that temporary database, so its role needs the right to create databases.

marmeon migrate and migrate:fresh write the file for you when migrations ran, in development only. In production, staging and tests the committed file is final, and the app's directory may not even be writable. --no-types skips it in development too. In CI, marmeon db:types --check fails when a migration changed and the file was not written again:

bootstrap/database.d.ts is out of date — run: marmeon db:types

The file fills the Tables interface, and Relations with the relations of the foreign keys. Views are not in it: db:types reads tables only, on SQLite and Postgres. Three helpers turn a table into the row types you write code with:

import type { NewRow, Row, RowUpdate } from '@marmeon/database';

export type Note = Row<'notes'>; // a row as read
export type NewNote = NewRow<'notes'>; // what an insert takes
export type NoteChanges = RowUpdate<'notes'>; // what an update takes

Some columns have types of their own:

A JSON column takes a string on purpose: Postgres would store an array that is not stringified as one of its own arrays. db:types warns about a column type it cannot map, which is then typed as unknown, and about a table that no module's migration created.

Writing queries#

Database is the default connection, typed by Tables. Inject it and use Kysely's builder:

import { Database, sql } from '@marmeon/database';

export class NoteSearch {
  readonly #db: Database;

  constructor(db: Database) {
    this.#db = db;
  }

  search(userId: number, term: string) {
    const pattern = `%${term.toLowerCase().replace(/[\\%_]/g, (c) => `\\${c}`)}%`;
    return this.#db
      .selectFrom('notes')
      .select(['id', 'title'])
      .where('user_id', '=', userId)
      .where(sql<boolean>`lower(title) like ${pattern} escape '\\'`)
      .orderBy('title')
      .orderBy('id')
      .execute();
  }
}

sql is Kysely's template, so your app needs no import of Kysely itself. A value in the template, such as ${pattern}, is sent as a parameter and never becomes part of the SQL text. sql.raw() is the exception: it puts a string into the SQL as it is, so never give it input. Kysely, Transaction, Selectable, Insertable and Updateable are exported as types too.

Kysely's own documentation covers the builder: joins, subqueries, onConflict, returning and the rest. What Marmeon adds is on this page.

Repositories#

Defining a repository#

A repository extends Repository<'table'> and names its table again as a value, static table = 'notes' as const, because types do not exist when the app runs. Without it, building the repository throws: NoteRepository needs its table as a runtime value: static table = '…' as const;. Other statics change its behaviour:

Timestamps and soft deletes follow the table's migration. When it calls timestamps(), update() sets updated_at. When it calls softDeletes(), delete() sets deleted_at, and every read leaves deleted rows out. The migrations page explains the marks.

A repository is an ordinary class, built by the container. It holds nothing but its connection, so register it once in your module's provider: container.singleton(NoteRepository). Its constructor takes Connections and nothing else, which is what lets using(trx) rebuild it for another transaction. Give other dependencies to the service that uses the repository.

Reading#

Every one of them leaves soft-deleted rows out and never returns a secret column. A finder cannot name a secret column at all: pluck('password') is a compile error, and refused when the query runs. A key of findBy() or firstWhere() that is not a column of the table, or is a secret one, is refused before the query, also when it got past the types. findBy() and firstWhere() find a NULL with null. A value that is undefined, such as a field a request did not send, is refused, because a condition without a value would match any row. Which row is "first" is up to the database unless the column is unique; order a query() yourself when it matters.

findOrFail(id, { with: ['author', 'tags'] }) loads the relations into the same query, as with() does, and the row's type has them: WithRelations<Tables, 'notes', 'author' | 'tags'> names it. chunk() reads the rows by id, each batch after the last one it read, one query per batch. Rows removed during the loop do not make it skip others. Rows added meanwhile come at the end or, when another transaction commits them later with a lower id, not at all. Its callback narrows what it reads:

for await (const users of this.#users.chunk(500, (query) => query.where('email_verified_at', 'is', null).with('profile_links'))) {
  for (const user of users) await this.#reminders.send(user, user.profile_links);
}

The callback may add conditions and relations. It must keep id among the columns, and an orderBy() in it is refused: the batches go by id.

query().withTrashed() keeps the soft-deleted rows, and so does a relation that calls it in its callback. The relations section shows both.

this.query() and this.db are there for your own methods. this.db is the whole connection, for statements on another table or an insertInto(…).onConflict(…).

Writing#

The values are exact: a field the table does not have is a compile error, also when the values come from a variable. With soft deletes, update() leaves a deleted row alone.

tryInsert() and tryUpdate() decide "already taken" by the unique index, so two requests at the same moment cannot both pass. They run in a transaction of their own, or in a savepoint inside an open one: on Postgres a failed statement breaks the transaction around it, and the savepoint keeps that transaction usable. They return a Result: narrow it over ok, then look at error.kind. Returned from a transaction's callback, the err rolls that transaction back like any other, as the transactions page explains. The starter kit registers a user this way:

import type { PasswordHash } from '@marmeon/auth';
import type { Result } from '@marmeon/core';
import { Repository } from '@marmeon/database';
import type { User } from './users.ts';

export class UserRepository extends Repository<'users'> {
  static table = 'users' as const;

  register(values: { name: string; email: string; password: PasswordHash; locale: string }): Promise<Result<User, { kind: 'duplicate' }>> {
    return this.tryInsert(values);
  }
}

To answer a form with "taken" at its field instead, wrap the write in failOnDuplicate(). The validation page shows it.

Route binding#

Every repository can bind a route parameter: notes.bind('note', NoteRepository) gives the controller the row, or answers 404. notes.bind('note', NoteRepository, { with: ['tags'] }) loads relations with it, in the same query. routeKey, routeKeyPattern, bindings scoped to a parent and relations are on the routing page.

Relations#

The foreign keys of your migrations relate your tables: profile_links.user_id gives profile_links a user, and users its profile_links. The migrations page shows how the relations are named. with() loads one into the same query, as JSON the database builds:

import { Database } from '@marmeon/database';

export class ProfileDirectory {
  readonly #db: Database<'user-profile'>;

  constructor(db: Database<'user-profile'>) {
    this.#db = db;
  }

  profile(id: number) {
    return this.#db
      .selectFrom('users')
      .selectAll('users')
      .with('login_activity', { as: 'activity' }, (activity) => activity.select('last_login_at'))
      .with('profile_links', { as: 'links' }, (links) => links.select(['id', 'user_id', 'label', 'url']).orderBy('id'))
      .where('users.id', '=', id)
      .executeTakeFirst();
  }
}

The result is the user's row with activity: { last_login_at } | null and links: { id, user_id, label, url }[]. It is one query: there is no lazy loading, so there are no N+1 queries to fall into, and the query log shows one statement.

Loading a relation#

with(name) loads every column of the related table except its secret columns. A belongsTo or a hasOne gives one row or null, a hasMany or a belongsToMany a list, empty when there is none. The names come from bootstrap/database.d.ts, so a wrong one is a compile error that lists the right ones:

Argument of type '"profile_link"' is not assignable to parameter of type '"login_activity" | "profile_links"'.

with() is a method of every select of the app's connection: a repository's query(), Database, a transaction, a subquery. In a query that joins tables, it offers the relations of each table, except a name two of them share. Relations are offered on a table's own name, selectFrom('users'), not on an alias such as selectFrom('users as u'). A module's Database<'user-profile'> offers only relations whose tables the module sees, its own and the tables other modules expose: the related table, and for a belongsToMany its pivot too. The same holds for withCount() and whereHas().

Narrowing and nesting#

The callback gets the related table's query. Select, filter, order and limit it like any query, and load its relations with with() again, to any depth. Its scope is the related table only: refer to the outer row through sql or eb.ref(), as in links.where(sql<boolean>`profile_links.label <> users.name`). When it selects columns of that table, the relation holds those columns. A column under another name ('label as title') does not count: it comes on top of every column but the secret ones, until the callback names one column of the table as it is. When it selects none, because it only orders, filters or nests, the relation keeps every column but the secret ones. selectAll() means the same:

this.#db
  .selectFrom('users')
  .select(['users.id', 'users.name'])
  .with('profile_links', (links) => links.where('label', '!=', '').orderBy('id').limit(3).with('user', (user) => user.select('name')));

{ as } gives a relation another key, with('profile_links', { as: 'links' }), with or without a callback after it.

A belongsTo is typed without null only when its foreign key is NOT NULL and the related table has no soft deletes. Otherwise the related row may be missing, and the type says so. A hasOne may always be missing.

Relations without a foreign key#

withRelation(name, definition, callback?) loads rows no foreign key describes, or a relation in a shape of its own: the rows of table whose foreignKey holds the outer row's localKey, which is id unless you name another column of the query's tables (the first table that has it). With one: true it loads the first row the callback's order gives, or null:

this.#db
  .selectFrom('users')
  .select(['users.id', 'users.name'])
  .withRelation('newest_link', { table: 'profile_links', foreignKey: 'user_id', one: true }, (links) => links.orderBy('id', 'desc'));

Its columns, secret columns and soft-deleted rows follow the rules of with().

Counting related rows#

withCount(name) counts the rows of a hasMany or a belongsToMany in the same query and adds the number under <name>_count. { as } names it otherwise, and a callback narrows what is counted, with the related table in scope as in with():

this.#db
  .selectFrom('users')
  .select(['users.id', 'users.name'])
  .withCount('profile_links')
  .withCount('profile_links', { as: 'secure_links' }, (links) => links.where('url', 'like', 'https://%'));

Each row gets profile_links_count: number and secure_links: number, a number on SQLite and on Postgres alike, 0 when there is none. The callback filters what is counted: groupBy(), having(), limit(), offset() and a union() or another set operation in it are refused, because they would change the count. A name the query selects already is refused too, since one row cannot hold two values under it. A belongsTo or a hasOne has one row or none, so there is nothing to count: withCount() on one is a compile error that lists the relations it can count, and whereHas() asks the question instead:

Argument of type '"login_activity"' is not assignable to parameter of type '"profile_links"'.

A data table shows and sorts by such a count like a column.

Filtering by relations#

whereHas(name, callback?) keeps the rows that have a related row, and whereDoesntHave(name, callback?) the rows that have none. Both become exists (…) in the same query. With a callback, only related rows its conditions match count:

this.#db
  .selectFrom('users')
  .select(['users.id', 'users.name'])
  .whereHas('profile_links', (links) => links.where('url', 'like', 'https://%'))
  .whereDoesntHave('login_activity');

Any relation works, a belongsToMany through its pivot too. The callbacks nest: whereHas() inside a whereHas(), a with() or a withCount() callback filters that level. What the callback selects does not matter: only whether a row is there. limit(), offset() and a set operation in it are refused, because they would change the answer. So is having() without groupBy(), which makes every related row one group and which databases answer differently.

Writing through relations#

A repository's relation(id, name) writes through a relation of one row. For a belongsToMany, it manages the rows of the pivot that link the two tables:

await this.relation(noteId, 'tags').sync([1, 4, 7]); // { attached: [7], detached: [2] }
await this.relation(noteId, 'tags').attach(9); // [9]
await this.relation(noteId, 'tags').detach(4); // 1

sync(ids) leaves the note linked to exactly these tags: it attaches the missing ones, detaches the others and leaves the rest alone, and says which it attached and detached. attach() takes one id or a list, skips the ones linked already, also by a request at the same moment, and returns those it linked. detach() returns how many links went: without an argument, every one; with an empty list, none. Ids that may be undefined, such as a field a request did not send, are a compile error and refused when the call runs.

A link to a row that is missing or soft-deleted is refused before anything is written, and the error names the ids:

relation('tags').attach() would link the notes row 3 to the tags row 9, which is missing or soft-deleted — nothing was written. Check the ids first: the rule exists() leaves soft-deleted rows out. See docs/queries.md#writing-through-relations.

Check ids that come from a request with exists() in its rules. A link to a row that was soft-deleted later stays until you detach it.

For a hasMany or a hasOne, create(values) and createMany(values) insert related rows with the foreign key set:

const link = await users.relation(user.id, 'profile_links').create({ label: 'Blog', url: 'https://example.com' });

The values are those of an insert without the foreign key, which the relation sets: naming it is a compile error. The rows come back as stored, without their secret columns, and timestamps() in the related table's migration fills in created_at and updated_at. A hasOne's unique key refuses a second row.

Each call runs in a transaction of its own, or in a savepoint inside an open one, so a refused sync() changes nothing. The row whose relation you write must be there: a missing or soft-deleted one answers 404 (RecordNotFoundError). A belongsTo has no relation(): update its foreign key instead. A pivot with soft deletes is refused, and nested writes, such as creating a row with its relations, do not exist: write each relation with a call of its own.

RelationWriter<Tables, 'notes', 'tags'> names what relation() returns: a LinkWriter for a belongsToMany, a RelatedWriter for a hasMany or a hasOne. WritableRelationName<Tables, 'notes'> names the relations it takes.

Soft-deleted rows#

A relation never loads the rows a table's soft deletes mark as deleted, and withCount(), whereHas() and whereDoesntHave() never count them, through a pivot neither. Call withTrashed() in the callback to keep them for that relation. On a repository's query(), withTrashed() keeps them in the query's own rows:

// In a NoteRepository, when notes and their attachments both have soft deletes:
this.query().withTrashed().selectAll().with('attachments', (attachments) => attachments.withTrashed());

withTrashed() works on the level it is called on: the relations below it still leave deleted rows out unless their callbacks call it too. On a plain db.selectFrom(), which filters nothing, it has nothing to keep. A table you join yourself, inside a callback or anywhere else, is your query: soft-deleted rows are not filtered there.

Relations and secret columns#

A secret column never comes back through a relation. It is not among the columns with() loads, and naming one inside a relation is refused when the query runs: by name, with secret(eb, …), sql.ref() or sql.id(), under an alias, from a table the callback joins, from the outer query or a subquery or common table expression of it, or deeper down. So is a whole row of a source that holds one, such as eb.table('users') in a function.

That goes for a subquery in a relation's where() too, as in any query, although its value stays in the database. Filter on a key instead: where('user_id', 'in', (eb) => eb.selectFrom('personal_access_tokens').select('user_id').where(…)), or with a join.

with('personal_access_tokens') selects personal_access_tokens.token_hash, a secret column — a relation never loads one. Read it where it is needed, in a query of its own with secret(eb, …). See docs/queries.md#relations-and-secret-columns.

A condition may compare a secret column, in the callback of with(), withCount() or whereHas() as in any where(): its value stays in the database. The answer does not: every row, count or yes the condition gives says something about the secret, so never let a request choose the column or send a like pattern for it. A comparison in SQL does not take constant time either, so do not look a secret up by its value. Look the row up by its key and compare the secret in code, as an API token is checked: by its id, then its secret in constant time.

The text of a raw sql`…` template is not checked: a column written into it is the query's own choice. Neither is a view: it is raw SQL of a migration, so a view over a table with secret columns returns what its own SQL selects.

The app's connection#

The app's connection resolves the relations from its database, where the migrations left the foreign keys and their marks. A query built on a connection without Marmeon's plugins, connections.infrastructure() or Kysely's withoutPlugins(), is refused before it is sent, and so is a repository's update() or delete() bound to one with using():

This query loads, counts or filters by relations, leaves out soft-deleted rows or touches a row's timestamps (with(), withCount(), whereHas(), withRelation(), withTrashed(), a repository's query(), update() or delete()), which only the app's connection resolves — it was not sent: it runs on the database connection "default" without Marmeon's plugins (infrastructure(), withoutPlugins()). Build it on connections.get() or Database. See docs/queries.md#relations.

Nested values come back with the same types as the outer row's: booleans, parsed JSON, decimals as strings and timestamps in UTC, on SQLite and Postgres alike.

Module boundaries#

Database sees every table. Database<'notes'> is the connection as the notes module sees it:

• its own tables, created by its migrations, for everything;
• the tables other modules expose, for reading and joins only.

A module exposes a table in its definition. The starter kit's auth module exposes users:

import { defineModule } from '@marmeon/core';
import routes from './routes.ts';

export default defineModule({
  name: 'auth',
  migrations: new URL('./migrations/', import.meta.url),
  exposes: ['users'],
  routes,
});

In the notes module, insertInto('users'), updateTable('users') and deleteFrom('users') are compile errors, and a table of another module that is not exposed does not exist at all. Writing to users goes through the auth module's UserRepository. The boundary is a type: at runtime both are the same connection, and raw SQL is not checked. A module that exposes a table another module's migration created, or that no migration created, stops db:types with an error that says so.

A repository sees every table: Repository<'profile_links'> is typed with all of Tables. To give it the module's view, pass ModuleTables as its second type argument. Its queries then see only the module's own tables and the exposed ones, with() offers only relations to those, and relation() writes only into the module's own:

import { Repository, type ExposedTables, type ModuleTables, type TableOwners, type Tables } from '@marmeon/database';

export class ProfileLinkRepository extends Repository<'profile_links', ModuleTables<Tables, TableOwners, ExposedTables, 'user-profile'>> {
  static table = 'profile_links' as const;

  async removeAllOf(userIds: readonly number[]): Promise<number> {
    if (userIds.length === 0) return 0;
    const result = await this.db.deleteFrom('profile_links').where('user_id', 'in', userIds).executeTakeFirst();
    return Number(result.numDeletedRows);
  }
}

The migrator records which module's migration created each table. That record is what the boundaries and the comments in bootstrap/database.d.ts are built on.

Errors#

Catch a unique violation inside a transaction only around a savepoint, as tryInsert() does. On Postgres the failed statement otherwise aborts the whole transaction, and every later query in it fails. Where "taken" is an outcome your code handles, prefer tryInsert() and tryUpdate(): their err of kind duplicate is a Result, not an exception.

Another connection#

A repository with static connection = 'reporting' works on that connection. Its tables still come from Tables, the types of your migrations. Code that queries a connection with tables of its own asks Connections for it: connections.get<ReportingTables>('reporting'). The database page shows how to define one.

Queries take turns#

A physical connection runs one query at a time, in the order the queries were sent. Outside a transaction, a Postgres pool spreads queries over its connections. A transaction holds one connection, and SQLite has only one, so there a Promise.all of queries, such as the props of a page, runs one query after the other. A query stream (.stream()) holds its connection until it ends: while it runs, send other queries to another connection.