drizzle-orm 0.45.2: Compile SQL template ASTs, dialect parameter placeholders, relational filter operators, placeholder bindings, and schema reflections in drizzle-orm@0.45.2 across PostgreSQL, SQLite, and MySQL dialects

# drizzle-orm Query AST Compilation and Dialect Parameterization

## Prior Findings
A query for `drizzle-orm` returned existing samples covering `casing: "snake_case"` proxy argument dispatch (`sha256:17faf4155357c9de9c0915953f971d73c724b4521f091bae8eba314358067c37`), SQL identifier quote escaping (`sha256:57a6e36e26613c9d14c388a78043dcbcf145549d1919e796bc8c418b19542a17`), and SQLite column modes / timestamp defaults (`sha256:4c3faa300151186bca927d4a5bf3914977042ccc06f18dec2e68dc19d90d699a`).

This sample proves a distinct core behavior: the compilation of the `sql` template AST, dialect-specific parameter placeholders (`$n` vs `?`), expression operator rendering, placeholder filling, table column introspection, and CRUD query building in pinned release `drizzle-orm@0.45.2`.

## Pinned Version
`drizzle-orm@0.45.2`

## Observed Behavior
- **Dialect Parameterization**: `PgDialect.sqlToQuery()` converts query parameters into 1-based indexed placeholders (`$1, $2, ...`) and emits `{ sql, params, typings }`. In contrast, `SQLiteSyncDialect.sqlToQuery()` and `MySqlDialect.sqlToQuery()` convert parameters to positional `?` placeholders while maintaining the exact parameter array order.
- **SQL AST Composition**: `sql` template literals compose nested column references, table identifiers, `sql.join` sequences, `sql.raw` unescaped fragments, and `sql.empty()` tokens into unified ASTs without premature string concatenation.
- **Placeholder Binding**: `placeholder(name)` creates an unresolved parameter token. `fillPlaceholders(params, values)` replaces named placeholders with runtime values and throws an explicit error (`No value for placeholder "<name>" was provided`) when an expected key is missing.
- **Expression Operators**: Core operators (`eq`, `ne`, `gt`, `gte`, `lt`, `lte`, `and`, `or`, `not`, `inArray`, `notInArray`, `isNull`, `isNotNull`, `between`, `notBetween`, `like`, `notLike`, `ilike`, `notIlike`) compile to standard dialect SQL expressions. `not(op)` prefixes expressions with `not` without redundant enclosing parentheses, and `isNull`/`isNotNull` do not allocate parameter slots.
- **Schema Reflection and Table Aliasing**: `getTableColumns(table)` returns column instances reflecting table schema constraints (`primaryKey`, `notNull`, `hasDefault`). `aliasedTable(table, alias)` constructs table references whose columns automatically qualify queries under the specified alias.
- **CRUD Compilation**: `PgDialect` and `SQLiteSyncDialect` builders (`buildDeleteQuery`, `buildUpdateQuery`) generate dialect-specific parameterized SQL strings with bound parameters.

{"case":{"caseId":"case:sha256:1e67dd3e84cda6eed6f58fb610e00b3297ed404e32e2e7aa0a17b915592ac9db","contract":["PgDialect compiles sql template tag expressions into PostgreSQL numbered placeholders ($1, $2), while SQLiteSyncDialect and MySqlDialect compile positional placeholders (?) with matching parameter bindings.","assert PgDialect compiles sql template tag expressions into 1-based numbered placeholders $1, $2 with extracted parameter values","assert SQLiteSyncDialect and MySqlDialect compile sql template tag expressions into positional question-mark placeholders with extracted parameter values","assert escapeName and escapeString properly escape table identifiers and string literals across dialects","assert sql.raw, sql.identifier, sql.join, and sql.empty compose raw fragments, quoted identifiers, and empty tokens into SQL ASTs","assert placeholder and fillPlaceholders bind parameter objects or throw an error when a declared placeholder key is missing","assert relational comparison and logical filter operators (eq, ne, gt, gte, lt, lte, and, or, not, inArray, notInArray, isNull, isNotNull, between, notBetween, like, notLike, ilike, notIlike) generate correct SQL syntax and parameter lists","assert getTableColumns and getTableName reflect schema column properties while aliasedTable properly scopes column identifiers in compiled queries","assert PgDialect and SQLiteSyncDialect buildDeleteQuery and buildUpdateQuery generate dialect-specific parameterized queries"],"goal":"Compile SQL template ASTs, dialect parameter placeholders, relational filter operators, placeholder bindings, and schema reflections in drizzle-orm@0.45.2 across PostgreSQL, SQLite, and MySQL dialects","kind":"HOW","packages":["pkg:npm/drizzle-orm@0.45.2"],"schemaVersion":1,"symbols":["sql","eq","and","or","inArray","placeholder","fillPlaceholders","getTableColumns","aliasedTable","PgDialect","SQLiteSyncDialect","MySqlDialect"]},"contractCommand":["node","test/contract.mjs"],"environment":{"arch":"x64","ecosystem":"npm","executionContext":"node","language":"node","os":"linux","packageManager":"npm","runtime":"node","schemaVersion":1},"license":"MIT-0","packages":["pkg:npm/drizzle-orm@0.45.2"],"schemaVersion":1,"symbols":["sql","eq","and","or","inArray","placeholder","fillPlaceholders","getTableColumns","aliasedTable","PgDialect","SQLiteSyncDialect","MySqlDialect"],"verifierAdapter":"node-typescript@1"}

{
  "name": "npm-drizzle-orm-version-depth",
  "version": "1.0.0",
  "lockfileVersion": 3,
  "requires": true,
  "packages": {
    "": {
      "name": "npm-drizzle-orm-version-depth",
      "version": "1.0.0",
      "license": "MIT-0",
      "dependencies": {
        "drizzle-orm": "0.45.2"
      }
    },
    "node_modules/drizzle-orm": {
      "version": "0.45.2",
      "resolved": "https://registry.npmjs.org/drizzle-orm/-/drizzle-orm-0.45.2.tgz",
      "integrity": "sha512-kY0BSaTNYWnoDMVoyY8uxmyHjpJW1geOmBMdSSicKo9CIIWkSxMIj2rkeSR51b8KAPB7m+qysjuHme5nKP+E5Q==",
      "license": "Apache-2.0",
      "peerDependencies": {
        "@aws-sdk/client-rds-data": ">=3",
        "@cloudflare/workers-types": ">=4",
        "@electric-sql/pglite": ">=0.2.0",
        "@libsql/client": ">=0.10.0",
        "@libsql/client-wasm": ">=0.10.0",
        "@neondatabase/serverless": ">=0.10.0",
        "@op-engineering/op-sqlite": ">=2",
        "@opentelemetry/api": "^1.4.1",
        "@planetscale/database": ">=1.13",
        "@prisma/client": "*",
        "@tidbcloud/serverless": "*",
        "@types/better-sqlite3": "*",
        "@types/pg": "*",
        "@types/sql.js": "*",
        "@upstash/redis": ">=1.34.7",
        "@vercel/postgres": ">=0.8.0",
        "@xata.io/client": "*",
        "better-sqlite3": ">=7",
        "bun-types": "*",
        "expo-sqlite": ">=14.0.0",
        "gel": ">=2",
        "knex": "*",
        "kysely": "*",
        "mysql2": ">=2",
        "pg": ">=8",
        "postgres": ">=3",
        "sql.js": ">=1",
        "sqlite3": ">=5"
      },
      "peerDependenciesMeta": {
        "@aws-sdk/client-rds-data": {
          "optional": true
        },
        "@cloudflare/workers-types": {
          "optional": true
        },
        "@electric-sql/pglite": {
          "optional": true
        },
        "@libsql/client": {
          "optional": true
        },
        "@libsql/client-wasm": {
          "optional": true
        },
        "@neondatabase/serverless": {
          "optional": true
        },
        "@op-engineering/op-sqlite": {
          "optional": true
        },
        "@opentelemetry/api": {
          "optional": true
        },
        "@planetscale/database": {
          "optional": true
        },
        "@prisma/client": {
          "optional": true
        },
        "@tidbcloud/serverless": {
          "optional": true
        },
        "@types/better-sqlite3": {
          "optional": true
        },
        "@types/pg": {
          "optional": true
        },
        "@types/sql.js": {
          "optional": true
        },
        "@upstash/redis": {
          "optional": true
        },
        "@vercel/postgres": {
          "optional": true
        },
        "@xata.io/client": {
          "optional": true
        },
        "better-sqlite3": {
          "optional": true
        },
        "bun-types": {
          "optional": true
        },
        "expo-sqlite": {
          "optional": true
        },
        "gel": {
          "optional": true
        },
        "knex": {
          "optional": true
        },
        "kysely": {
          "optional": true
        },
        "mysql2": {
          "optional": true
        },
        "pg": {
          "optional": true
        },
        "postgres": {
          "optional": true
        },
        "prisma": {
          "optional": true
        },
        "sql.js": {
          "optional": true
        },
        "sqlite3": {
          "optional": true
        }
      }
    }
  }
}

{
  "name": "npm-drizzle-orm-version-depth",
  "version": "1.0.0",
  "description": "Proves SQL AST compilation, dialect parameterization, filter expressions, and schema reflection in drizzle-orm@0.45.2",
  "type": "module",
  "scripts": {
    "test": "node test/contract.mjs"
  },
  "license": "MIT-0",
  "dependencies": {
    "drizzle-orm": "0.45.2"
  }
}

import assert from 'node:assert';
import {
  sql,
  eq,
  ne,
  gt,
  gte,
  lt,
  lte,
  and,
  or,
  not,
  inArray,
  notInArray,
  isNull,
  isNotNull,
  like,
  notLike,
  ilike,
  notIlike,
  between,
  notBetween,
  placeholder,
  fillPlaceholders,
  param,
  getTableColumns,
  getTableName,
  aliasedTable,
} from 'drizzle-orm';
import {
  pgTable,
  serial,
  text as pgText,
  integer as pgInt,
  boolean as pgBool,
  varchar as pgVarchar,
} from 'drizzle-orm/pg-core';
import { PgDialect } from 'drizzle-orm/pg-core/dialect';
import {
  sqliteTable,
  integer as sqliteInt,
  text as sqliteText,
} from 'drizzle-orm/sqlite-core';
import { SQLiteSyncDialect } from 'drizzle-orm/sqlite-core/dialect';
import {
  mysqlTable,
  int as mysqlInt,
  varchar as mysqlVarchar,
} from 'drizzle-orm/mysql-core';
import { MySqlDialect } from 'drizzle-orm/mysql-core/dialect';

const pgUsers = pgTable('users', {
  id: serial('id').primaryKey(),
  name: pgText('name').notNull(),
  email: pgVarchar('email', { length: 255 }),
  age: pgInt('age'),
  isActive: pgBool('is_active').default(true),
});

const sqliteUsers = sqliteTable('users', {
  id: sqliteInt('id').primaryKey(),
  name: sqliteText('name').notNull(),
  email: sqliteText('email'),
  age: sqliteInt('age'),
  isActive: sqliteInt('is_active').default(1),
});

const mysqlUsers = mysqlTable('users', {
  id: mysqlInt('id').primaryKey(),
  name: mysqlVarchar('name', { length: 100 }).notNull(),
  email: mysqlVarchar('email', { length: 255 }),
  age: mysqlInt('age'),
});

const pgDialect = new PgDialect();
const sqliteDialect = new SQLiteSyncDialect();
const mysqlDialect = new MySqlDialect();

// 1. Dialect parameter placeholder compilation ($1, $2 vs ?)
{
  const pgChunk = sql`select ${pgUsers.id}, ${pgUsers.name} from ${pgUsers} where ${and(eq(pgUsers.name, 'Alice'), inArray(pgUsers.age, [20, 30]))}`;
  const pgResult = pgDialect.sqlToQuery(pgChunk);
  assert.strictEqual(
    pgResult.sql,
    'select "users"."id", "users"."name" from "users" where ("users"."name" = $1 and "users"."age" in ($2, $3))'
  );
  assert.deepStrictEqual(pgResult.params, ['Alice', 20, 30]);

  const sqliteChunk = sql`select ${sqliteUsers.id}, ${sqliteUsers.name} from ${sqliteUsers} where ${and(eq(sqliteUsers.name, 'Alice'), inArray(sqliteUsers.age, [20, 30]))}`;
  const sqliteResult = sqliteDialect.sqlToQuery(sqliteChunk);
  assert.strictEqual(
    sqliteResult.sql,
    'select "users"."id", "users"."name" from "users" where ("users"."name" = ? and "users"."age" in (?, ?))'
  );
  assert.deepStrictEqual(sqliteResult.params, ['Alice', 20, 30]);

  const mysqlChunk = sql`select ${mysqlUsers.id}, ${mysqlUsers.name} from ${mysqlUsers} where ${and(eq(mysqlUsers.name, 'Alice'), inArray(mysqlUsers.age, [20, 30]))}`;
  const mysqlResult = mysqlDialect.sqlToQuery(mysqlChunk);
  assert.strictEqual(
    mysqlResult.sql,
    'select `users`.`id`, `users`.`name` from `users` where (`users`.`name` = ? and `users`.`age` in (?, ?))'
  );
  assert.deepStrictEqual(mysqlResult.params, ['Alice', 20, 30]);
}

// 2. Identifier escaping
{
  assert.strictEqual(pgDialect.escapeName('simple'), '"simple"');
  assert.strictEqual(pgDialect.escapeName('user "table"'), '"user ""table"""');
  assert.strictEqual(sqliteDialect.escapeName('user "table"'), '"user ""table"""');
  assert.strictEqual(mysqlDialect.escapeName('user `table`'), '`user ``table```');
}

// 3. String literal escaping
{
  assert.strictEqual(pgDialect.escapeString("O'Reilly"), "'O''Reilly'");
  assert.strictEqual(sqliteDialect.escapeString("O'Reilly"), "'O''Reilly'");
  assert.strictEqual(mysqlDialect.escapeString("O'Reilly"), "'O''Reilly'");
}

// 4. SQL composition primitives: raw, identifier, join, empty
{
  const joined = sql`select ${sql.join([sql.identifier('id'), sql.identifier('name')], sql`, `)} from ${sql.identifier('accounts')}`;
  assert.strictEqual(pgDialect.sqlToQuery(joined).sql, 'select "id", "name" from "accounts"');
  assert.strictEqual(mysqlDialect.sqlToQuery(joined).sql, 'select `id`, `name` from `accounts`');

  const rawChunk = sql`select * from ${sql.raw('legacy_table')} where status = ${sql.raw("'ACTIVE'")}`;
  assert.strictEqual(pgDialect.sqlToQuery(rawChunk).sql, "select * from legacy_table where status = 'ACTIVE'");
  assert.deepStrictEqual(pgDialect.sqlToQuery(rawChunk).params, []);

  const emptyChunk = sql`prefix ${sql.empty()}suffix`;
  assert.strictEqual(pgDialect.sqlToQuery(emptyChunk).sql, 'prefix suffix');
}

// 5. Placeholder declaration and parameter value binding
{
  const placeholderChunk = sql`select * from users where id = ${placeholder('userId')} and age > ${placeholder('minAge')}`;
  const compiledPlaceholder = pgDialect.sqlToQuery(placeholderChunk);
  assert.strictEqual(compiledPlaceholder.sql, 'select * from users where id = $1 and age > $2');
  assert.strictEqual(compiledPlaceholder.params[0].name, 'userId');
  assert.strictEqual(compiledPlaceholder.params[1].name, 'minAge');

  const filledParams = fillPlaceholders(compiledPlaceholder.params, { userId: 42, minAge: 18 });
  assert.deepStrictEqual(filledParams, [42, 18]);

  assert.throws(
    () => fillPlaceholders(compiledPlaceholder.params, { userId: 42 }),
    /No value for placeholder "minAge" was provided/
  );
}

// 6. Filter operators AST rendering
{
  const testOps = [
    { op: eq(pgUsers.age, 25), expectedSql: '"users"."age" = $1', expectedParams: [25] },
    { op: ne(pgUsers.age, 25), expectedSql: '"users"."age" <> $1', expectedParams: [25] },
    { op: gt(pgUsers.age, 18), expectedSql: '"users"."age" > $1', expectedParams: [18] },
    { op: gte(pgUsers.age, 18), expectedSql: '"users"."age" >= $1', expectedParams: [18] },
    { op: lt(pgUsers.age, 65), expectedSql: '"users"."age" < $1', expectedParams: [65] },
    { op: lte(pgUsers.age, 65), expectedSql: '"users"."age" <= $1', expectedParams: [65] },
    { op: isNull(pgUsers.age), expectedSql: '"users"."age" is null', expectedParams: [] },
    { op: isNotNull(pgUsers.age), expectedSql: '"users"."age" is not null', expectedParams: [] },
    { op: between(pgUsers.age, 18, 30), expectedSql: '"users"."age" between $1 and $2', expectedParams: [18, 30] },
    { op: notBetween(pgUsers.age, 18, 30), expectedSql: '"users"."age" not between $1 and $2', expectedParams: [18, 30] },
    { op: like(pgUsers.name, 'A%'), expectedSql: '"users"."name" like $1', expectedParams: ['A%'] },
    { op: notLike(pgUsers.name, 'A%'), expectedSql: '"users"."name" not like $1', expectedParams: ['A%'] },
    { op: ilike(pgUsers.name, 'a%'), expectedSql: '"users"."name" ilike $1', expectedParams: ['a%'] },
    { op: notIlike(pgUsers.name, 'a%'), expectedSql: '"users"."name" not ilike $1', expectedParams: ['a%'] },
    { op: not(eq(pgUsers.age, 25)), expectedSql: 'not "users"."age" = $1', expectedParams: [25] },
    { op: notInArray(pgUsers.age, [1, 2]), expectedSql: '"users"."age" not in ($1, $2)', expectedParams: [1, 2] },
  ];

  for (const { op, expectedSql, expectedParams } of testOps) {
    const q = sql`where ${op}`;
    const compiled = pgDialect.sqlToQuery(q);
    assert.strictEqual(compiled.sql, `where ${expectedSql}`);
    assert.deepStrictEqual(compiled.params, expectedParams);
  }
}

// 7. Schema Reflection and Table Aliasing
{
  assert.strictEqual(getTableName(pgUsers), 'users');
  const cols = getTableColumns(pgUsers);
  assert.deepStrictEqual(Object.keys(cols), ['id', 'name', 'email', 'age', 'isActive']);
  assert.strictEqual(cols.id.name, 'id');
  assert.strictEqual(cols.id.primary, true);
  assert.strictEqual(cols.name.notNull, true);
  assert.strictEqual(cols.isActive.hasDefault, true);
  assert.strictEqual(cols.email.name, 'email');

  const uAlias = aliasedTable(pgUsers, 'u');
  const aliasQuery = sql`select ${uAlias.id}, ${uAlias.name} from ${pgUsers} as ${uAlias} where ${eq(uAlias.id, 42)}`;
  const compiledAlias = pgDialect.sqlToQuery(aliasQuery);
  assert.strictEqual(
    compiledAlias.sql,
    'select "u"."id", "u"."name" from "users" as "u" where "u"."id" = $1'
  );
  assert.deepStrictEqual(compiledAlias.params, [42]);
}

// 8. Dialect query builders for CRUD statements
{
  const pgDelete = pgDialect.buildDeleteQuery({
    table: pgUsers,
    where: eq(pgUsers.id, 1),
  });
  const compiledPgDelete = pgDialect.sqlToQuery(pgDelete);
  assert.strictEqual(
    compiledPgDelete.sql,
    'delete from "users" where "users"."id" = $1'
  );
  assert.deepStrictEqual(compiledPgDelete.params, [1]);

  const pgUpdate = pgDialect.buildUpdateQuery({
    table: pgUsers,
    set: { age: param(31, pgUsers.age) },
    where: eq(pgUsers.id, 1),
  });
  const compiledPgUpdate = pgDialect.sqlToQuery(pgUpdate);
  assert.strictEqual(
    compiledPgUpdate.sql,
    'update "users" set "age" = $1 where "users"."id" = $2'
  );
  assert.deepStrictEqual(compiledPgUpdate.params, [31, 1]);

  const sqliteDelete = sqliteDialect.buildDeleteQuery({
    table: sqliteUsers,
    where: eq(sqliteUsers.id, 1),
  });
  const compiledSqliteDelete = sqliteDialect.sqlToQuery(sqliteDelete);
  assert.strictEqual(
    compiledSqliteDelete.sql,
    'delete from "users" where "users"."id" = ?'
  );
  assert.deepStrictEqual(compiledSqliteDelete.params, [1]);

  const sqliteUpdate = sqliteDialect.buildUpdateQuery({
    table: sqliteUsers,
    set: { age: param(31, sqliteUsers.age) },
    where: eq(sqliteUsers.id, 1),
  });
  const compiledSqliteUpdate = sqliteDialect.sqlToQuery(sqliteUpdate);
  assert.strictEqual(
    compiledSqliteUpdate.sql,
    'update "users" set "age" = ? where "users"."id" = ?'
  );
  assert.deepStrictEqual(compiledSqliteUpdate.params, [31, 1]);
}