Switch engines

Engine migration

Most of AskDB is engine-agnostic. The dialect adapter is the part that knows your SQL flavor — swap it, and the same schema-author workflow and the same ask() call work against a different engine.

What stays the same

The schema artifact format. Same schema.json, same tables/*.md, same enrichment workflow.
The ask() API. Same options, same return shape.
The validator semantics: read-only, single-statement, no system schemas, tenant filter enforcement.
The privacy model. The model sees schema, not rows, regardless of engine.

What changes

The engine package you install.
The dialect value you pass to ask().
Engine-specific SQL features the validator allows (LIMIT vs TOP, parameter syntax, JSON path syntax).
The driver your app uses to execute (pg, mysql2, better-sqlite3, mssql).
How the CLI introspects the database (connection-string format and catalog queries differ per engine).

The matrix

Engine	Adapter package	`dialect` value	Suggested driver
PostgreSQL	`@askdb/postgres`	`"postgres"`	`pg`
MySQL	`@askdb/mysql`	`"mysql"`	`mysql2`
SQLite	`@askdb/sqlite`	`"sqlite"`	`better-sqlite3`
SQL Server	`@askdb/sqlserver`	`"sqlserver"`	`mssql`

Step by step

1. Install the new adapter

npm install @askdb/postgres

npm install @askdb/mysql

npm install @askdb/sqlite

npm install @askdb/sqlserver

You can keep the old adapter installed during a transition. Each adapter is self-contained.

2. Introspect the new database (if the schema differs)

If you are pointing at a different database instance — one that may have a different set of tables or columns — run introspection against it to regenerate the physical layer of the schema artifact:

npx askdb introspect \
  --engine postgres \
  --url "postgresql://user:pass@host/dbname" \
  --out my-app.schema \
  --schema-id my-app

npx askdb introspect \
  --engine mysql \
  --url "mysql://user:pass@host/dbname" \
  --out my-app.schema \
  --schema-id my-app

For SQLite, set the file path via introspection.sqliteFile in askdb.config.ts:

export default {
  introspection: {
    provider: "sqlite",
    sqliteFile: "./my-database.db",
  },
  // ...
};

Then run:

npx askdb introspect --out my-app.schema --schema-id my-app

npx askdb introspect \
  --engine sqlserver \
  --url "sqlserver://user:pass@host/dbname" \
  --out my-app.schema \
  --schema-id my-app

The connection string format follows each engine’s convention. The engine itself comes from --engine (or introspection.provider in askdb.config.ts) — the CLI does not infer it from the URL. The artifact records which engine produced it, and askdb ask infers the dialect from the artifact.

If the logical schema is identical and you are only swapping the adapter package, the existing artifact is already valid — skip this step and go straight to step 3.

3. Update the `dialect` value in `ask()` calls

The schema artifact records which engine it was introspected from. The CLI (askdb ask) and askdb.config.ts use that information to infer the dialect automatically — no manual update needed for those entry points.

This holds for Prisma-introspected artifacts too: the connector reads the datasource.provider from your .prisma file (postgresql → postgres, mysql, sqlite, sqlserver) and records it, so askdb ask still knows the dialect without a live database.

When calling ask() programmatically, dialect is an explicit parameter. Update it to match the new engine:

const { sql } = await ask({
  question,
  schema,
  dialect: "mysql",      // was "postgres"
  model,
});

All four dialect specs ship inside @askdb/core — ask({ dialect: "mysql" }) works without installing anything else. The engine packages (@askdb/mysql, @askdb/postgres, …) supply the introspection connector for that engine (and re-export its dialect constant for convenience); you need them to introspect, not to generate.

4. Update your driver

Swap the driver library for the one that matches the new engine. The execution layer is yours — AskDB doesn’t open the connection — so this is a straight library swap.

import { Pool } from "pg";

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  statement_timeout: 5000,
  max: 10,
});

const { rows } = await pool.query(sql);

import mysql from "mysql2/promise";

const pool = mysql.createPool({
  uri: process.env.MYSQL_URL,
  connectionLimit: 10,
});

const [rows] = await pool.execute(sql);

import Database from "better-sqlite3";

const db = new Database(process.env.SQLITE_FILE ?? "./my-database.db");

const rows = db.prepare(sql).all();

import mssql from "mssql";

const pool = await mssql.connect(process.env.SQLSERVER_URL);

const result = await pool.request().query(sql);
const rows = result.recordset;

On dialect parity

Postgres is the reference dialect — features ship there first and the other engines follow. For most analytical questions, the four dialects generate equivalent SQL; for engine-specific features (JSON operators, window function syntax, regex variations), behavior follows what each engine supports natively.

If you hit a dialect difference that surprises you, that’s a bug — file it on the project issues.

Running multiple engines side by side

Nothing stops you from loading two schema artifacts and calling ask() against different dialects in the same process. Useful for multi-database apps:

const ordersSchema = await loadSchema("./orders.schema");
const analyticsSchema = await loadSchema("./analytics.schema");

const fromOrders = await ask({
  question, schema: ordersSchema, dialect: "postgres", model,
});

const fromAnalytics = await ask({
  question, schema: analyticsSchema, dialect: "mysql", model,
});