Switch engines

Engine migration

Most of AskDB is engine-agnostic. The dialect is the part that knows your SQL flavor — change the dialect id, 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 SQL checks: must start with SELECT or WITH, single statement (with a SQL Server caveat), no comments, no write or DDL keywords, no locking reads, no calls to known side-effecting functions, and the heuristic tenant and sensitive-column checks. Per engine, only the detail changes: SQL is split into tokens the way that engine reads strings, quoted identifiers, and comments, and each engine adds its own entries to the keyword denylist, the function denylist, or both. The locking reads rejected follow each engine’s syntax too, such as LOCK IN SHARE MODE on MySQL and WITH (UPDLOCK) on SQL Server. See Safety boundaries.
• The privacy model. The model sees schema, not rows, regardless of engine.

What changes

• The engine package you install.
• The dialect. A direct ask() call passes it; the CLI and @askdb/client infer it from the schema artifact (see step 3).
• The dialect’s prompt brief, which tells the model the engine’s SQL (ILIKE and ::type on Postgres, TOP on SQL Server), and the read-only check’s rules for the engine (extra forbidden keywords and blocked functions).
• How the prompt ask() sends quotes names. A schema, table or column whose name is one of the engine’s reserved words is listed quoted the engine’s way, so the model copies it quoted: billing."order" on Postgres, billing.`order` on MySQL and MariaDB, billing.[order] on SQL Server, "order" on SQLite. On Postgres, which folds unquoted names to lowercase, a name with capitals is quoted too, so a Prisma table Post is listed as public."Post". Where tables are listed with their schema, the prompt also tells the model that when it quotes a qualified name, it quotes each part separately ("billing"."order", not "billing.order").
• 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	Engine package	dialect value	Suggested driver
PostgreSQL	@askdb/postgres	"postgres"	pg
MySQL	@askdb/mysql	"mysql"	mysql2
MariaDB	@askdb/mysql	"mariadb"	mysql2
SQLite	@askdb/sqlite	"sqlite"	better-sqlite3
SQL Server	@askdb/sqlserver	"sqlserver"	mssql

Step by step

1. Install the new engine package

PostgreSQL

npm

npm install @askdb/postgres ai

pnpm

pnpm add @askdb/postgres ai

yarn

yarn add @askdb/postgres ai

If you plan to run live introspection, also add the driver:

npm

npm install pg

pnpm

pnpm add pg

yarn

yarn add pg

MySQL

npm

npm install @askdb/mysql ai

pnpm

pnpm add @askdb/mysql ai

yarn

yarn add @askdb/mysql ai

If you plan to run live introspection, also add the driver:

npm

npm install mysql2

pnpm

pnpm add mysql2

yarn

yarn add mysql2

SQLite

npm

npm install @askdb/sqlite ai

pnpm

pnpm add @askdb/sqlite ai

yarn

yarn add @askdb/sqlite ai

If you plan to run live introspection, also add the driver:

npm

npm install better-sqlite3

pnpm

pnpm add better-sqlite3

yarn

yarn add better-sqlite3

SQL Server

npm

npm install @askdb/sqlserver ai

pnpm

pnpm add @askdb/sqlserver ai

yarn

yarn add @askdb/sqlserver ai

If you plan to run live introspection, also add the driver:

npm

npm install mssql

pnpm

pnpm add mssql

yarn

yarn add mssql

You can keep the old engine package installed during a transition. Each engine package is self-contained. Database drivers are optional peers used only by live introspection or by your own execution layer.

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:

PostgreSQL

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

MySQL

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

For MySQL and MariaDB, the URL’s database is the one introspected, and the prompt ask() sends lists its tables unqualified (orders). Stable IDs lists the namespace each engine writes. The URL must name that database: if the path has no database (mysql://user:pass@host:3306) and you don’t list databases, introspection throws an error instead of writing an empty schema. To introspect several databases at once (MySQL’s “schemas”), list them. Each database becomes its own namespace (sales.orders), listed qualified in the prompt, and foreign keys between listed databases are kept:

askdb.config.ts

import { env } from "@askdb/config";

export default {
  introspection: {
    provider: "mysql",
    providerConfig: {
      mysql: { databaseUrl: env("DATABASE_URL") },
    },
    schemas: ["app", "sales", "analytics"],
  },
  // ...
};

A foreign key into a database that wasn’t introspected has no target in the artifact, so it is dropped with a cross_database_fk warning that names the constraint and the referenced database. Without a database list that is every foreign key into another database; with one, it is every foreign key into a database missing from the list.

SQLite

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

askdb.config.ts

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

Then run:

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

SQLite has no schemas, so its tables appear under the public namespace (table ids like table:public.orders), and the prompt ask() sends lists them unqualified (orders).

SQL Server

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

SQL Server accepts three connection-string formats, and a server with a self-signed certificate needs a TLS option: see Connect to SQL Server.

On Postgres and SQL Server, introspection.schemas lists schemas (the default is every non-system schema). On MySQL and MariaDB it lists databases, as above. SQLite has a single namespace and ignores it. askdb introspect --schemas app,sales does the same for one run and overrides the configured list.

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.

For one-off live introspection without installing askdb in the project, include the driver in the same ephemeral command:

pnpm dlx -p askdb -p mysql2 askdb introspect --engine mysql --url "$MYSQL_URL"
npx -p askdb -p mssql askdb introspect --engine sqlserver --url "$SQLSERVER_URL"

If the logical schema is identical and you are only swapping the engine 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/client use it to infer the dialect, so those entry points need no update. A dialect set in askdb.config.ts takes precedence over the artifact’s engine: remove it, or change it, when you switch.

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

When using @askdb/client with no dialect in the config, the dialect resolves from the schema artifact’s recorded provider, the same inference the CLI uses. No code change is needed after a dialect switch if the new artifact declares the correct provider.

To force a specific dialect (for example, if the artifact’s provider doesn’t match your target engine), pass it as a per-call override:

// askdb initialized at startup with createAskDb() — see Embed in a Node app.
const { sql } = await askdb.ask(question, {
  dialect: "mysql",      // overrides auto-resolution
});

All six dialect specs (postgres, cockroachdb, mysql, mariadb, sqlite, sqlserver) ship inside @askdb/core. 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.

PostgreSQL

import { Pool } from "pg";

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

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

MySQL

import mysql from "mysql2/promise";

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

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

SQLite

import Database from "better-sqlite3";

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

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

SQL Server

import mssql from "mssql";

// An ADO.NET string: mssql doesn't read mssql:// or sqlserver:// URLs.
const pool = await mssql.connect(process.env.SQLSERVER_URL);

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

The driver reads fewer connection-string formats than AskDB does. Connect your own pool shows a config object that works in development and production.

On dialect parity

Postgres is the reference dialect — features ship there first and the other engines follow. For most analytical questions, the engines this guide covers 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 querying two schema artifacts against different dialects in the same process. With @askdb/client, pass the schema and dialect as per-call overrides:

// askdb initialized at startup with createAskDb() — see Embed in a Node app.

const fromOrders = await askdb.ask(question, {
  schema: { path: "./orders.schema" },
  dialect: "postgres",
});

const fromAnalytics = await askdb.ask(question, {
  schema: { path: "./analytics.schema" },
  dialect: "mysql",
});

Read next

Modes and dialectsThe conceptual model behind dialects.Embed in a Node appThe base integration that the engine swap modifies.Package referenceThe full per-engine package surface.