How Drizzle Schema Migrations Work Across Database Providers in Agent Native

Agent Native uses automatic dialect detection and SQL adaptation to run identical migration definitions across PostgreSQL, Turso/libSQL, pglite, and SQLite without requiring provider-specific code changes.

The BuilderIO/agent-native repository implements a unified migration system that handles Drizzle schema migrations across multiple database providers. By analyzing the DATABASE_URL scheme and applying dialect-specific transformations, the framework ensures consistent schema evolution whether you are using Neon, Supabase, Turso, embedded pglite, or local SQLite.

Detecting Database Providers with createDrizzleConfig

The migration system begins by detecting the active database provider through the createDrizzleConfig function in packages/core/src/db/drizzle-config.ts. This helper inspects the DATABASE_URL environment variable to determine the appropriate Drizzle dialect and driver configuration.

The detection logic examines URL schemes to identify providers:

• Postgres: postgres:// or postgresql:// → dialect: "postgresql"
• Turso / libSQL: libsql:// → dialect: "turso"
• pglite: pglite: → dialect: "postgresql" with driver: "pglite"
• SQLite: file: or omitted → dialect: "sqlite"

// packages/core/src/db/drizzle-config.ts
export function createDrizzleConfig(
  opts: CreateDrizzleConfigOptions = {},
): Config {
  const { schema = "./server/db/schema.ts", out = "./server/db/migrations" } = opts;

  const envUrl = process.env.DATABASE_URL ?? "";
  const scheme = envUrl.toLowerCase();
  const isPostgres = scheme.startsWith("postgres://") || scheme.startsWith("postgresql://");
  const isPglite    = scheme.startsWith("pglite:");
  const isTurso     = scheme.startsWith("libsql://");

  return defineConfig({
    schema,
    out,
    dialect:
      isPostgres || isPglite ? "postgresql" : isTurso ? "turso" : "sqlite",
    ...(isPglite ? { driver: "pglite" as const } : {}),
    // Additional credentials configuration...
  });
}

This centralized detection allows a single npm run db:migrate command to work across all providers without modifying the codebase.

Running Migrations with runMigrations

The runMigrations function in packages/core/src/db/migrations.ts executes migrations while handling provider-specific quirks. It accepts an array of migration objects containing version numbers and SQL statements, plus a table option for book-keeping to avoid version clashes between different templates.

// packages/core/src/db/migrations.ts
export const migrations = [
  { 
    version: 1, 
    sql: `CREATE TABLE users (id TEXT PRIMARY KEY, name TEXT)` 
  },
  {
    version: 2,
    sql: {
      postgres: `ALTER TABLE users ADD COLUMN created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP`,
      sqlite:   `ALTER TABLE users ADD COLUMN created_at INTEGER`,
    },
  },
];

Key implementation details include:

• Book-keeping tables: Each template uses distinct table names (e.g., slides_migrations, plan_migrations) to prevent version conflicts
• Connection management: For Postgres, direct-endpoint connections are opened only when pending migrations exist, avoiding idle connections that bypass PgBouncer
• Concurrency safety: A reference-counted singleton (_migrationExecPromise) ensures concurrent runners share a single connection

Provider-Specific SQL Adaptations

Before execution, raw SQL statements undergo dialect-specific transformations to ensure compatibility across providers.

Postgres adaptations (lines 58-62 in migrations.ts):

• Converts SQLite-specific syntax like datetime('now') to CURRENT_TIMESTAMP

SQLite adaptations (lines 66-71 in migrations.ts):

• Strips ADD COLUMN IF NOT EXISTS clauses
• Swallows duplicate-column errors to emulate idempotent semantics via the isDuplicateColumnError helper (lines 88-92)

Migrations can define provider-specific SQL using an object syntax:

{
  version: 3,
  sql: {
    postgres: `CREATE INDEX CONCURRENTLY idx_users_email ON users(email)`,
    sqlite:   `CREATE INDEX idx_users_email ON users(email)`,
  }
}

When a string is provided instead of an object, the same SQL runs on every dialect after passing through the appropriate adapter function.

Safety Guards Against Accidental Schema Changes

The framework implements runtime guards to prevent destructive operations against production databases. In drizzle-config.ts (lines 19-25), the isNeonUrl and isDrizzlePushInvocation checks abort any drizzle-kit push attempt against a Neon database.

This protection is reinforced by a CI-side script at scripts/guard-no-drizzle-push.mjs that prevents automated pipelines from executing schema pushes on Neon. These guards ensure that only the framework's additive migration runner modifies production schemas, preventing accidental table drops.

Summary

• Dialect detection happens automatically via createDrizzleConfig by parsing the DATABASE_URL scheme
• SQL adaptation transforms statements before execution to handle differences between Postgres and SQLite syntax
• Connection optimization opens direct Postgres connections only when migrations are pending, respecting PgBouncer pooling
• Safety guards block drizzle-kit push against Neon databases to prevent accidental schema destruction
• Concurrent execution is handled through a reference-counted singleton that ensures thread-safe migration runs

Frequently Asked Questions

How does Agent Native detect which database provider is being used?

The createDrizzleConfig function in packages/core/src/db/drizzle-config.ts examines the DATABASE_URL environment variable and identifies the provider by its URL scheme. Postgres URLs starting with postgres:// or postgresql:// trigger the PostgreSQL dialect, libsql:// indicates Turso, pglite: selects the embedded pglite driver, and file: or missing schemes default to SQLite.

What happens if I run drizzle-kit push against a Neon database?

The system will abort the operation with an error. Both runtime checks in drizzle-config.ts (lines 42-48) and the CI script at scripts/guard-no-drizzle-push.mjs explicitly prevent drizzle-kit push against Neon databases. This protects production data from accidental schema drops that would occur if framework tables were missing.

How are concurrent migrations handled?

A reference-counted singleton (_migrationExecPromise in packages/core/src/db/migrations.ts) ensures that concurrent migration requests share a single execution context. This prevents race conditions when multiple server instances attempt to run migrations simultaneously during startup.

Can I use provider-specific SQL syntax in migrations?

Yes. Migrations accept either a string (run on all providers after adaptation) or an object with postgres and sqlite keys containing dialect-specific SQL. This allows you to use Postgres-specific features like CREATE INDEX CONCURRENTLY while providing equivalent SQLite alternatives.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}