Description

The building blocks of the first-party SQL stores that Nest's packages ship (PostgresWorkflowStore in @nestjs/workflows/postgres, and more to come, on PostgreSQL and MySQL): executors that run a store's SQL through the client the application already has (node-postgres or mysql2, Drizzle, TypeORM, Prisma, Kysely) and join its transactions, a store's versioned migrations with their SQL and its command line, the readiness a store awaits before its statements, and helpers for writing them.

Applications don't install or import it: a package depends on it, and its /postgres and /mysql subpaths re-export the executors and their types.

Installation

For a package that ships a store, as a regular dependency (never a peer, so the kit's patch releases dedupe across an application's packages):

$ npm i --save @nestjs/store-kit

Entries

• @nestjs/store-kit: what every dialect shares: the executor types (SqlExecutor, SqlTransaction, SqlTransactionOptions, SqlIsolationLevel, SqlExecuteResult, SqlDialect), isNotATransactionError() (an executor's refusal of an object that isn't a transaction, by its code ERR_SQL_NOT_A_TRANSACTION), the readers of a column's text (toText/toInt/toBool/toJson) and runStoreCli(), a package's command. SqlExecutor<'postgres'> and SqlExecutor<'mysql'> are each dialect's executors (what the executors return, and what a store's options take): the root's SqlExecutor alone is either, and /postgres's and /mysql's SqlExecutor (with their StoreOptions) are their dialect's.
• @nestjs/store-kit/postgres: the executors (fromPg, fromDrizzle, fromTypeOrm, fromPrisma, fromKysely, and isNotATransactionError()), StoreSchema (migrations, their SQL, a store's options and readiness), the statement helpers (SqlParams, columns, toText/toInt/toBool/toJson, quoteSchema, advisoryLock) and assertReadCommittedTransaction().
• @nestjs/store-kit/mysql: the same for MySQL 8.4 LTS and 9.x (and MariaDB, for a store that opts in: see below): the executors (fromMysql2, fromDrizzle, fromTypeOrm, fromPrisma, fromKysely, and isNotATransactionError()), StoreSchema (a store's tables in the connection's database as <schema>_<table>, migrations applied statement by statement under GET_LOCK() and resumed where a failed run stopped), and the statement helpers (SqlParams, columns, toText/toInt/toBool/toJson, quoteIdentifier, quoteTable, keyColumn, lockKeys, ensureLockRows, lockRowId, retryOnDeadlock, mysqlErrorCode).
• @nestjs/store-kit/testing: sqlExecutorContract(), for an executor of another client.

A SqlTransaction carries the client's own transaction object as its native (the node-postgres client or mysql2 connection in its transaction, Drizzle's tx, TypeORM's EntityManager, Prisma's transaction client, Kysely's Transaction): the object wrapTransaction() takes, and gives back (wrapTransaction(x).native === x). So a store that opens a transaction (executor.transaction()) can hand it to the application's code, whose writes through the client's own API land in that transaction. Every executor of the kit sets it; it's optional in SqlTransaction, and sqlExecutorContract() checks it where an executor sets it. On PostgreSQL, which ends a transaction at its first failed statement, work that caught the error of a statement it sent through native is rejected, nothing committed, as it is for one sent through query(): fromPg() reads its COMMIT's reply, at no cost, and the other executors send one statement at the end of work that read native.

MariaDB

A MySQL store runs on MariaDB once its StoreSchema says so (mariadb: true); without it, the store refuses MariaDB at startup. The store's readiness then reports the server it found (readiness.server: 'mysql' or 'mariadb'), for a statement that has to differ between the two. fromTypeOrm() takes a DataSource of type 'mariadb', and the command line reaches MariaDB through a mysql:// URL.

• Versions. The stores' key columns use utf8mb4_0900_bin, which MariaDB has from 11.4.5 and compares as MySQL does (binary, NO PAD) from 11.4.7 and 11.8.2: before that, it pads, and abc and abc are the same key. The store checks the collation itself at startup, and refuses a MariaDB where it's missing or pads. Tables created on 11.4.5 or 11.8.0-11.8.1 keep the padding collation after an upgrade: create them again on a fixed version.
• The application's REPEATABLE READ transactions. With innodb_snapshot_isolation on (MariaDB's default since 11.6.2), a REPEATABLE READ transaction's locking read, or insert, of a row another transaction committed after the transaction's snapshot fails with 1020 (ER_CHECKREAD), where MySQL reads the latest committed row, or reports a duplicate key as 1062. A store's insert-if-absent in the application's transaction then fails instead of finding the row, and its lockKeys() can fail the same way. Run the application's transactions at READ COMMITTED, turn innodb_snapshot_isolation off, or run a transaction that fails with 1020 again, as one that fails with 1213. A store's own transactions are READ COMMITTED, where it never arises.
• JSON. MariaDB nests a JSON document at most 32 levels deep (MySQL: 100): a deeper one fails a store's write with 4040. It can also cut a JSON function's result short, into invalid JSON and without a warning, where it materializes the result, as in a derived table. JSON_EXTRACT() is cut short before 11.4.11, 11.8.7 and 12.3.2 (MDEV-38873: SELECT d.doc FROM (SELECT JSON_EXTRACT('[{"id":"a"}]', '$') AS doc) AS d reads [{"id": "a"}). The functions that edit a document (JSON_SET(), JSON_INSERT(), JSON_REPLACE(), JSON_ARRAY_APPEND(), JSON_REMOVE()) are cut short on every version so far (MDEV-40641), and SqlParams.json() goes through JSON_SET() for a document with numbers other than safe integers. So on any version, bind a document's text where it goes through a derived table (SqlParams.text()).
• sqlExecutorContract() reads performance_schema.events_transactions_current for a transaction's identity and isolation level. On MariaDB, start the server with --performance-schema=ON --performance-schema-consumer-events-transactions-current=ON --performance-schema-instrument='transaction=ON'.

A store in brief

export const outboxSchema = new StoreSchema({
  packageName: '@nestjs/outbox',
  storeName: 'PostgresOutboxStore',
  command: 'nest-outbox',
  defaultSchema: 'nest_outbox',
  migrations: [initialMigration], // { version: 1, name: 'initial', up: (s) => [`CREATE TABLE ${s}.messages (...)`] }
  createError: (message, details) => new OutboxSchemaError(message, details),
});

export class PostgresOutboxStore implements OutboxStore, OnModuleInit {
  static migrationSql(options?: MigrationSqlOptions): string {
    return outboxSchema.sql(options);
  }

  static readonly schemaVersion = outboxSchema.latest;

  private readonly executor: SqlExecutor;
  private readonly readiness: StoreReadiness;

  constructor(options: PostgresOutboxStoreOptions, storage?: OutboxStorage) {
    const resolved = outboxSchema.resolveOptions(options); // { executor, schema, migrate }, checked
    this.executor = resolved.executor;
    this.readiness = outboxSchema.readiness({ ...resolved, logger: new Logger('OutboxModule') });
    storage?.registerSource({ messages: this, inbox: this });
  }

  onModuleInit(): Promise<void> {
    return this.readiness.ready(); // migrates (or checks) the schema before the workers start
  }

  migrate(): Promise<number[]> {
    return this.readiness.migrate();
  }
}

The package's bin (nest-outbox), which users run as npx @nestjs/outbox migrate|status|sql:

#!/usr/bin/env node
import { runStoreCli } from '@nestjs/store-kit';
import { outboxSchema } from './outbox.schema.js';

process.exitCode = await runStoreCli([outboxSchema], process.argv.slice(2));

Tests

npm run test:e2e runs the suite on PGlite, and on PostgreSQL through every executor: SQL_TEST_PG_URL (docker compose up -d starts one on port 55432), else a throwaway cluster from local PostgreSQL binaries, else those tests are skipped with the reason. The MySQL tests (a vitest project of their own, two files at a time, after the PostgreSQL ones) run on SQL_TEST_MYSQL_URL (mysql://root:<password>@127.0.0.1:3306), MySQL or MariaDB (started with the performance_schema flags above), else they are skipped with the reason. Test databases are named skit_<host>_... on both servers and swept when their process is gone.

Support

Nest is an MIT-licensed open source project. It can grow thanks to the sponsors and support by the amazing backers. If you'd like to join them, please read more here.

Stay in touch

• Author - Kamil Myśliwiec
• Website - https://nestjs.com
• Twitter - @nestframework

License

Nest is MIT licensed.