@maykonpaulo/maestro-provider-sqlite

An IntrospectionProvider implementation for @maykonpaulo/maestro-core that reads the schema of a local SQLite database — tables, columns, primary keys, foreign keys and indices — and reports it back as a validated, normalized IntrospectionResult.

This is the first official provider built on top of the core's provider contracts (IntrospectionProvider/IntrospectionContext/runIntrospectionProvider), per ADR 0005 — Provider Adapter Strategy. It follows the conventions defined there: it depends on the core as a peer dependency, keeps its SQLite dependency entirely to itself, and never reimplements validation or normalization.

What this is not

• Not a driver, ORM, or query engine. It only reads schema metadata (sqlite_master, PRAGMA table_info/foreign_key_list/index_list/index_info) — it never executes an application query and never writes to the database.
• Not wired into the CLI. maestro introspect (in @maykonpaulo/maestro-cli) remains offline/local and does not yet resolve this or any other provider automatically. That mechanism is a separate, future decision (see ADR 0005, DA-04).
• 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-sqlite @maykonpaulo/maestro-core

@maykonpaulo/maestro-core is a peer dependency — install the version your project already uses.

Usage

import { runIntrospectionProvider } from '@maykonpaulo/maestro-core';
import { SqliteIntrospectionProvider } from '@maykonpaulo/maestro-provider-sqlite';

const provider = new SqliteIntrospectionProvider({ databasePath: './app.db' });
const { provider: name, result } = await runIntrospectionProvider(provider);

// name === 'sqlite'
// result is already validated and normalized (entities sorted by table, relations by id)

A createSqliteIntrospectionProvider(options) factory function is also exported, if you prefer not to use new.

Configuration

Exactly one of the two options below must be provided:

export type SqliteIntrospectionProviderOptions =
  | { databasePath: string }                 // path to a local .db file, read once
  | { database: SqlJsDatabase };             // an already-open sql.js database instance

database exists mainly for tests and for callers who already manage a sql.js Database instance (for example, an in-memory database populated in the same process) — every new SQL.Database() call creates an independent in-memory database, so a shared instance must be passed explicitly rather than reopened by path.

Why sql.js instead of a native SQLite binding

The provider 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. The tradeoff is that all operations are synchronous only after an async one-time WASM module initialization, and file I/O reads the whole database into memory rather than streaming — both are irrelevant for a schema-introspection use case, which needs neither high write throughput nor huge multi-GB databases.

What is introspected

• Tables — every table in sqlite_master, excluding SQLite's own internal tables (sqlite_*, e.g. sqlite_sequence).
• Columns — name, declared (native) type, inferred FieldType (via the core's inferFieldType), nullability, primary key, foreign key, unique/indexed flags, parsed maxLength (from a declared size like VARCHAR(255)), default value, and the core's derived flags (isTimestamp, candidateForSearch, candidateForSoftDelete) — reusing the same heuristics the core already exposes for any provider to use.
• Primary keys — represented via isPrimaryKey on each participating column. A single-column INTEGER PRIMARY KEY (SQLite's rowid alias) is corrected to nullable: false, since SQLite's own PRAGMA table_info reports notnull: 0 for it even though such a column can never hold NULL.
• Foreign keys — mapped to relations, with a deterministic id of the form <table>.<column>-><targetTable>.<targetColumn> and confidence: 'definite' (derived from an actual FOREIGN KEY constraint, not a naming heuristic). The relation type is one-to-one when the local column is a primary key or carries a unique constraint, many-to-one otherwise. many-to-many is never inferred — SQLite's schema alone cannot distinguish a join table from any other table.
• Indices — non-unique and unique indices you create explicitly (CREATE INDEX/CREATE UNIQUE INDEX) are mapped into each entity's indices, preserving column order. The synthetic sqlite_autoindex_* entries SQLite creates to back a PRIMARY KEY/UNIQUE column constraint are not duplicated into indices — that information is already represented via isPrimaryKey/isUnique on the field.

Known limitations

• Composite (multi-column) primary keys: each participating column is marked isPrimaryKey: true, but IntrospectionResult has no way to express that they form a single composite key group.
• Composite (multi-column) foreign keys: skipped entirely — the current RelationIntrospectionSchema only supports a single { table, field } pair per side. Single-column foreign keys are unaffected.
• Views: not introspected as entities in this version.
• BLOB columns: map to the core's 'string' fallback FieldType, since there is no dedicated binary field type in the current contract.

None of these are core limitations — they reflect the current shape of IntrospectionResult, which this provider does not alter (per ADR 0005: extending the core's contract to fit a single provider is explicitly out of scope).

Testing

Tests run entirely in-process against sql.js in-memory databases — no Docker, no external service, no network access. See tests/sqlite-introspection-provider.test.ts.