@nage-api/data-sql

The SQL driver for @nage-api/data (PLAN.md §14.1), built on Sequelize.

Install one driver, never two: the engine is chosen when the workspace is scaffolded, so no dead branch of the other stack is compiled in. Today this is the only driver there is — @nage-api/data-mongo (§8) has not been written.

sequelize is a peer dependency, so the dialect package is yours to choose: pg, mysql2, mariadb or sqlite3 alongside it.

import { Module } from '@nestjs/common';
import { resolveTlsOptions } from '@nage-api/core';
import { NageSqlModule } from '@nage-api/data-sql';
import type { NageConfig } from '@nage-api/contracts';
import { Sequelize } from 'sequelize';

import type { Env } from './config/env.schema.js';

/** Both come from the application: validated env, and the config it built. */
declare const env: Env;
declare const config: NageConfig;

const sequelize = new Sequelize(env.DATABASE_URL, {
  // `verify-full` unless the config says otherwise; the legacy pair of
  // `rejectUnauthorized: false` literals is what this replaces (§12).
  dialectOptions: { ssl: resolveTlsOptions(config.database?.ssl) },
  logging: false,
});

@Module({ imports: [NageSqlModule.forRoot({ sequelize })] })
export class AppModule {}

A model declares the framework's audit columns and leaves Sequelize's own bookkeeping off, because the driver stamps those columns itself:

import { defineQueryPolicy } from '@nage-api/data';
import { SqlRepository } from '@nage-api/data-sql';
import { DataTypes, type Model, type ModelStatic, type Sequelize } from 'sequelize';

interface Product {
  readonly id: number;
  name: string;
  price: number;
}

/** The row type the driver expects: attributes stay opaque to it. */
type Row = Model<Record<string, unknown>, Record<string, unknown>>;

declare const sequelize: Sequelize;

const ProductModel: ModelStatic<Row> = sequelize.define<Row>(
  'Product',
  {
    id: { type: DataTypes.INTEGER, primaryKey: true, autoIncrement: true },
    name: { type: DataTypes.STRING, allowNull: false },
    price: { type: DataTypes.INTEGER, allowNull: false },
    // Stamped by `@nage-api/data`, so they are ordinary columns here.
    created_at: DataTypes.STRING,
    updated_at: DataTypes.STRING,
    created_by: DataTypes.INTEGER,
    updated_by: DataTypes.INTEGER,
    deleted_at: DataTypes.STRING,
    deleted_by: DataTypes.INTEGER,
  },
  // `timestamps: false` and no `paranoid`: two mechanisms writing the same
  // columns disagree about their format and their meaning.
  { tableName: 'products', timestamps: false, freezeTableName: true },
);

const productPolicy = defineQueryPolicy<Product>({
  filterable: ['name', 'price'],
  sortable: ['name', 'price'],
  selectable: ['id', 'name', 'price'],
  searchable: ['name'],
});

const products = new SqlRepository<Product>(ProductModel, { policy: productPolicy });

What it provides

| Export | Purpose | | --------------------- | ----------------------------------------------------------------- | | SqlRepository | RepositoryPort over a Sequelize model | | SqlUnitOfWork | UnitOfWork over a managed Sequelize transaction | | SqlMigrationStore | The migrations ledger, plus a best-effort lock | | SqlDataSourceHealth | authenticate() probe for /health/ready | | translateWhere | The DSL → Op.* translation | | NageSqlModule | Publishes the unit of work and health probe against core's tokens |

Behaviour worth knowing

A client-supplied key never reaches Sequelize. translateWhere maps each DSL operator to an Op.* symbol and throws on anything else, so the emitted clause contains only symbols this package owns. That is the second line of defence; parseQuery in @nage-api/data is the first.

Audit and soft delete are the framework's, not Sequelize's. The driver uses the shared stamp* helpers rather than Sequelize's timestamps/paranoid, so the columns and semantics are identical across drivers — which is what lets one conformance suite cover both.

Transactions are the database's. SqlUnitOfWork.run uses a managed transaction: it commits on success, rolls back on failure, and re-raises the original error — the same observable contract the in-memory driver gives.

Migrations replace auto-sync. SqlMigrationStore records each applied migration in nage_migrations. Its lock is a row in that same table rather than an advisory lock — SQLite has none — so it is best effort: it stops a second process starting, and a process that dies mid-migration leaves the row behind for an operator to delete. Migrating as one deploy step, not on every instance boot, is what the design assumes. nage db migrate is the intended entry point and is not implemented yet (see @nage-api/cli's "Not yet implemented"), so today the runner is called from a script of your own.

Testing

The driver runs the shared conformance suite from @nage-api/data against SQLite in-process — real SQL generation, real transactions, no container required, and the same suite the in-memory driver runs, which is what makes parity checkable.

Against Postgres and MySQL it is untested. @nage-api/testing ships the ContainerHarness the matrix needs and pins the images, but no suite here uses it and the adapter has never run against Docker, so dialect-specific behaviour — ILIKE, the LIKE escape character, findAndCountAll with a join — rests on Sequelize rather than on a test in this repository.

Not yet implemented

• The module does not open the connection. forRoot takes an already-built Sequelize; there is no forRootAsync and nothing reads database.pool, so pool sizing, acquireTimeoutMs and connect-retry backoff are the application's to arrange (§14.2). It does close the pool: onApplicationShutdown calls sequelize.close(), and installShutdown runs that after the HTTP drain, so nothing is still querying when it happens.
• Named scopes. Query.scope reaches the driver and is ignored.
• Optimistic locking. descriptor.versioned is accepted and unused.
• bulkUpdate and bulkDelete are row-by-row. Each matching row is fetched and written individually so that the audit stamps and soft-delete semantics match the other driver exactly; a wide bulk write is N statements, not one.
• populate is thin. It becomes a Sequelize include by association name. Only a belongsTo case is tested, and the driver does not set distinct, so eager-loading a to-many association can inflate pagination.count.

docs/packages/data-sql.md is the longer guide: every option and its default, the model definition this driver expects, and the mistakes worth knowing about.