@maykonpaulo/maestro-provider-sql

📖 Guia completo — criar o admin de qualquer sistema: está no README do pacote @maykonpaulo/maestro-server no npm → https://www.npmjs.com/package/@maykonpaulo/maestro-server

A relational-family provider for @maykonpaulo/maestro-core — introspects a real SQL database, operates real data at runtime, and creates a missing table when your declared entity does not exist yet.

Per ADR 0007 — Universal Provider Strategy, this package groups the whole relational family — SQLite, PostgreSQL, MySQL, MSSQL — under one dialect-selected provider, instead of one package per exact database product. It supersedes @maykonpaulo/maestro-provider-sqlite.

A returned provider implements three of the core's contracts at once, whichever dialect you pick:

• IntrospectionProvider — discovers tables, columns, primary keys, foreign keys and (for sqlite/postgres/mysql) indices.
• DatasourceProvider — real list/findById/create/update/delete/count against the live database (filters, sort, search and pagination translated to SQL).
• SchemaWriteProvider — ensureEntity creates the table when it does not exist yet (idempotent; never alters or drops existing structure).

What this is not

• Not a full ORM or query engine. Filtering/sorting/search/pagination cover the shapes @maykonpaulo/maestro-core's DatasourceProvider contract needs — not arbitrary SQL.
• Not a schema migration tool. ensureEntity only creates what is missing; altering or dropping existing columns is out of scope (see ADR 0007).
• Not a connection pool or credential manager. It never reads .env or a connection string implicitly — the database location is always an explicit option you pass in.

Installation

npm install @maykonpaulo/maestro-provider-sql @maykonpaulo/maestro-core
# plus the driver for the dialect(s) you actually use:
npm install pg          # postgres
npm install mysql2      # mysql
npm install mssql       # mssql
# sqlite needs nothing extra — it uses the bundled sql.js (WASM), see below

@maykonpaulo/maestro-core is a peer dependency. pg/mysql2/mssql are optional peer dependencies, loaded dynamically only for the dialect you request — installing this package never forces any of them onto you.

Usage

import { runIntrospectionProvider } from '@maykonpaulo/maestro-core';
import { createSqlProvider } from '@maykonpaulo/maestro-provider-sql';

const provider = createSqlProvider({ dialect: 'sqlite', databasePath: './app.db' });
// or: createSqlProvider({ dialect: 'postgres', host: 'localhost', database: 'app', user: 'app', password: '...' })
// or: createSqlProvider({ dialect: 'mysql', host: 'localhost', database: 'app', user: 'app', password: '...' })
// or: createSqlProvider({ dialect: 'mssql', server: 'localhost', database: 'app', options: { trustServerCertificate: true } })

// Introspection — validated/normalized via the core
const { result } = await runIntrospectionProvider(provider);

// Real CRUD against the same database
const created = await provider.create({ table: 'users', primaryKey: 'id', data: { name: 'Ada' } });
const page = await provider.list({ table: 'users', primaryKey: 'id', pagination: { strategy: 'page', page: 1, pageSize: 20 } });

// Create the table if it isn't there yet
await provider.ensureEntity({
  entity: { table: 'tags', fields: [{ name: 'id', nativeType: 'TEXT', type: 'uuid', nullable: false, isPrimaryKey: true }] },
});

Configuration per dialect

export type SqliteDialectOptions =
  | { dialect: 'sqlite'; databasePath: string }    // path to a local .db file, loaded once, flushed back after every write
  | { dialect: 'sqlite'; database: SqlJsDatabase }; // an already-open sql.js instance — caller owns persistence

export interface PostgresDialectOptions extends pg.PoolConfig {
  dialect: 'postgres';
  schema?: string; // defaults to 'public'
}

export interface MysqlDialectOptions extends mysql2.PoolOptions {
  dialect: 'mysql';
  uri?: string; // alternative to discrete host/user/database/...
}

export interface MssqlDialectOptions extends Omit<mssql.config, 'server'> {
  dialect: 'mssql';
  connectionString?: string; // alternative to discrete server/database/...
  schema?: string;           // defaults to 'dbo'
}

postgres/mysql/mssql pool/connection options pass through to their driver's own config type as-is (minus dialect/schema) — this package adds no translation layer on top of what pg/mysql2/mssql already accept. The pool/connection is opened once per createSqlProvider(...) call and reused for the life of that instance.

Closing the connection

postgres/mysql/mssql hold a real network connection pool for as long as the provider instance is alive. Call await provider.close() when you're done with it (before the process exits, or in a test's teardown) so open sockets don't linger. sqlite implements close() as a no-op — sql.js has no live external connection to release.

Why sql.js instead of a native SQLite binding

The sqlite dialect uses sql.js (SQLite compiled to WebAssembly) rather than a native addon such as better-sqlite3. This was a deliberate choice made after hitting the exact risk this avoids: attempting to install a native binding in this development environment failed because no prebuilt binary was available for the local Node/OS combination and no Python toolchain was present for a source build (node-gyp requires Python to compile native addons). sql.js has zero native compilation step — it is pure JS + a .wasm file — so it installs and runs identically across platforms, Node versions, and CI runners without any build toolchain. Opened once per createSqlProvider(...) call: reads never touch disk again after the initial load, and mutating calls (create/update/delete/ensureEntity) flush the whole database back to databasePath immediately after the in-memory change. There is no live connection or lock — this is a local, single-process assumption; concurrent external writers to the same file are not observed and can be overwritten. The tradeoff is that every operation loads the whole database into memory rather than streaming — fine for a local admin-scale database, not for a multi-GB one.

What is introspected

• sqlite — every table in sqlite_master (excluding SQLite's own internal tables), columns (name, declared type, inferred FieldType, nullability, PK/FK/unique/indexed flags, parsed maxLength, default value, and the core's derived flags), foreign keys as relations (one-to-one when the local column is a PK/unique, many-to-one otherwise), and non-synthetic indices.
• postgres — via information_schema (tables/columns/keys) plus pg_catalog (indices, since Postgres has no information_schema view for them). Same field/relation shape as sqlite.
• mysql — via information_schema (COLUMNS/KEY_COLUMN_USAGE, which already carries the referenced table/column for foreign keys directly — no extra join needed) and information_schema.STATISTICS for indices.
• mssql — via INFORMATION_SCHEMA (tables/columns/keys/foreign keys). Indices are not introspected for this dialect (see Known limitations) — sys.indexes/sys.index_columns are deferred.

inferFieldType (from the core) maps each dialect's native type name to a FieldType — the same heuristic across all four, so e.g. Postgres's character varying and MySQL's varchar and MSSQL's nvarchar all land on comparable FieldTypes.

Known limitations

• Composite (multi-column) primary/foreign keys: not representable by the current IntrospectionResult contract, in any dialect; each participating column of a composite PK is marked isPrimaryKey: true individually, and composite FKs are skipped entirely.
• Views: not introspected as entities, in any dialect.
• mssql indices: not introspected in this version (see above) — postgres/mysql/sqlite all introspect indices, mssql does not yet.
• BLOB/binary columns: map to the core's 'string' fallback FieldType.
• ensureEntity: creates columns and single-column PRIMARY KEY/NOT NULL/UNIQUE constraints only, in every dialect — foreign keys, composite keys and indices declared on the entity are not applied by table creation. TEXT/NVARCHAR(MAX)-class columns cannot carry a UNIQUE constraint in MySQL/MSSQL without an explicit key length, a driver-level restriction this provider does not work around.
• mssql UNIQUEIDENTIFIER casing: SQL Server always returns a UNIQUEIDENTIFIER as uppercase hex, regardless of the case it was written with. This provider lower-cases any string field shaped like a UUID in every row mssql returns, so an id round-trips identically through create() → findById()/list() — the one case-normalization this package applies, scoped to the one dialect that actually needs it.

None of these are core limitations — they reflect the current shape of IntrospectionResult/SchemaWriteProvider, which this provider does not alter.

Testing

sqlite tests run entirely in-process against sql.js in-memory databases — no Docker, no external service, no network access (tests/sqlite-introspection.test.ts, tests/sqlite-datasource.test.ts, tests/sqlite-schema-write.test.ts).

postgres/mysql/mssql each have two layers of tests:

• Pure query-building and connection-option validation, without any server (tests/postgres-provider.test.ts, tests/mysql-provider.test.ts, tests/mssql-provider.test.ts).
• Full integration tests against a real, ephemeral server, started per test run via testcontainers (tests/postgres-integration.test.ts, tests/mysql-integration.test.ts, tests/mssql-integration.test.ts) — a real postgres:16-alpine/mysql:8/mcr.microsoft.com/mssql/server:2022-latest Docker container, exercising ensureEntity (table creation, idempotency), real CRUD (create/find/update/delete/list/count with filters, sort, search and pagination) and introspect() against the table it just created. These require Docker; if it isn't available, run just the pure-unit test files above.

tests/createSqlProvider.test.ts covers dialect dispatch.

Turnkey admin (point at your database, get a UI)

This provider plugs straight into the Maestro turnkey layer: install @maykonpaulo/maestro-server and @maykonpaulo/maestro-admin, point them at your database and get a full governed admin (list/CRUD/RBAC/audit + a metadata-driven UI) with no per-entity code.

import { createSqlProvider } from '@maykonpaulo/maestro-provider-sql';
import { createMaestroServer, createIntrospectedEngine } from '@maykonpaulo/maestro-server';

const provider = createSqlProvider({ dialect: 'postgres', host: 'localhost', database: 'app', user: 'app', password: '...' });
const engine = await createIntrospectedEngine({ provider, access: 'full' });
await createMaestroServer({ engine }).listen(3000);

See the Turnkey admin guide.