schema: renderColumn ignores column.references — generated CREATE TABLE has no inline REFERENCES clause (SQLite has no other option)

Problem

When a column is declared with .references('table.col').onDelete('cascade'), the resulting column.references metadata is stored on the column definition but never emitted to SQL during CREATE TABLE rendering. The framework that wraps bqb has no other way to express a foreign key for SQLite (since SQLite doesn't support ALTER TABLE ADD CONSTRAINT), so SQLite databases end up with zero foreign keys — even though the call site clearly asked for them.

For MySQL and PostgreSQL the situation is less acute because bqb's addForeignKey() does emit a working ALTER TABLE … ADD CONSTRAINT, so the FK lands as a separate migration. But for SQLite that path is a dead-end: the SQL would be rejected at execution time, so consumers strip the file from disk. The only viable SQLite path is inline REFERENCES, which bqb currently doesn't emit.

Verified against bun-query-builder@0.1.23 (dist build)

dist/src/index.js:

SQLite renderColumn — lines ~16894-16912:

renderColumn(column) {
  const typeSql = this.getColumnType(column);
  const parts = [this.quoteIdentifier(column.name), typeSql];
  if (column.isPrimaryKey) {
    parts.push("PRIMARY KEY");
    const autoIncrement = this.getAutoIncrementClause(column);
    if (autoIncrement) parts.push(autoIncrement);
  }
  if (!column.isNullable && !column.isPrimaryKey) parts.push("not null");
  const defaultValue = this.getDefaultValue(column);
  if (defaultValue) parts.push(defaultValue);
  return parts.join(" ");
}

column.references is read nowhere.

MySQL renderColumn (lines ~17056-17070) and Postgres renderColumn (lines ~17211-17230) have the exact same shape — none of them check column.references either.

addForeignKey — lines ~16835, ~17005, ~17161 does correctly produce ALTER TABLE … ADD CONSTRAINT … FOREIGN KEY for all three dialects. But SQLite can't execute that:

ALTER TABLE "judge_reviews" ADD CONSTRAINT "judge_reviews_judge_id_fk"
  FOREIGN KEY ("judge_id") REFERENCES "judges"("id") ON DELETE CASCADE;
-- SQLite error: near "CONSTRAINT": syntax error

So SQLite consumers can either accept "no FKs ever" or filter the file out of their migrations pipeline before execution.

Repro

import { defineDriver } from 'bun-query-builder'

const driver = getDialectDriver('sqlite')
const sql = driver.createTable({
  table: 'judge_reviews',
  columns: [
    { name: 'id', type: 'integer', isPrimaryKey: true, isUnique: false, isNullable: false, hasDefault: false },
    { name: 'title', type: 'text', isPrimaryKey: false, isUnique: false, isNullable: true, hasDefault: false },
    {
      name: 'judge_id',
      type: 'integer',
      isPrimaryKey: false, isUnique: false, isNullable: true, hasDefault: false,
      references: { table: 'judges', column: 'id', onDelete: 'cascade' },
    },
  ],
})

console.log(sql)

Actual output:

CREATE TABLE IF NOT EXISTS "judge_reviews" (
  "id" INTEGER PRIMARY KEY AUTOINCREMENT,
  "title" TEXT,
  "judge_id" INTEGER
);

Expected output (SQLite syntax):

CREATE TABLE IF NOT EXISTS "judge_reviews" (
  "id" INTEGER PRIMARY KEY AUTOINCREMENT,
  "title" TEXT,
  "judge_id" INTEGER REFERENCES "judges"("id") ON DELETE CASCADE
);

Real-world impact

Downstream framework that uses bqb (stacksjs/stacks) declares relationships like:

// app/Models/JudgeReview.ts
belongsTo: ['Judge', 'User']

and walks the metadata down to column.references correctly. The bench-review reference app has 97 migration files on disk, zero of which carry any FK statement, because:

• SQLite path: bqb's renderColumn drops the inline REFERENCES (this bug)
• The addForeignKey() separate-migration path that bqb does support gets stripped by the framework before execution (since SQLite would reject it)

Net result: an entire production app with a SQLite store and zero referential integrity, with no warning to the user.

Suggested fix

In all three renderColumn implementations, append the inline FK after the defaultValue check:

renderColumn(column) {
  // ...existing parts (name, type, PRIMARY KEY, NOT NULL, DEFAULT)...

  if (column.references) {
    const { table: refTable, column: refColumn, onDelete, onUpdate } = column.references
    parts.push(`REFERENCES ${this.quoteIdentifier(refTable)}(${this.quoteIdentifier(refColumn)})`)
    if (onDelete) parts.push(`ON DELETE ${onDelete.toUpperCase()}`)
    if (onUpdate) parts.push(`ON UPDATE ${onUpdate.toUpperCase()}`)
  }

  return parts.join(" ")
}

This is the most portable fix — inline REFERENCES works on SQLite, MySQL, and Postgres. (For MySQL, only InnoDB enforces it; for SQLite, only with PRAGMA foreign_keys = ON.)

Open design question — separate or both?

Once renderColumn emits the inline REFERENCES, the existing addForeignKey() (which emits a separate ALTER TABLE ADD CONSTRAINT migration) becomes:

• Redundant on SQLite — the inline form is the only one that works, so the consumer should not also generate a separate ALTER.
• Duplicative on MySQL/Postgres — both forms work, but emitting both creates two constraints (with different names) for the same FK.

Recommended: have addForeignKey() skip generation for columns whose references metadata is already set on a CREATE TABLE codegen pass. Alternatively, expose a supportsAlterTableAddConstraint capability flag on the driver so the consumer (Stacks framework) can decide which path to take. Either solution would let the Stacks framework drop its destructive preprocessing pass.

Suggested PRAGMA documentation (SQLite-specific)

SQLite enforces FK constraints only when PRAGMA foreign_keys = ON is set on the connection — and even with the constraint declared inline, violations silently pass without that pragma. Worth a README/docs note alongside the fix so callers don't get a false sense of safety.

Acceptance

• Repro test above produces the expected output for sqlite, mysql, and postgres dialects.
• Existing addForeignKey() behavior is preserved (or explicitly skipped when the column already carries inline references — design call).
• At least one integration test asserts that a column declared with .references('users.id').onDelete('cascade') appears in the generated CREATE TABLE SQL.
• README example for .references() shows the inline-FK output, plus the PRAGMA foreign_keys = ON requirement for SQLite.

Downstream

This is the foundational fix; once it lands, stacksjs/stacks can:

• Stop deleting FK migration files from disk (see stacksjs/stacks#1915).
• Drop the separate createMysqlForeignKeyMigrations / createPostgresForeignKeyMigrations files in favor of inline FKs on CREATE TABLE, which works on all three drivers (and removes a class of partial-apply bugs where the table lands but the FK migration is skipped).

Cross-ref: stacksjs/stacks#1915.