Using Drizzle ORM with Agent-Native Portable Schema Patterns

Agent-Native provides a portable database layer that lets you write a single Drizzle ORM schema compatible with SQLite, Postgres, and Neon by using the createDrizzleConfig helper and enforcing additive-only migrations.

The BuilderIO/agent-native repository ships a type-safe persistence layer designed to eliminate database drift between client and server environments. By using Drizzle ORM with Agent-Native portable schema patterns, you can define tables once and run them across any Drizzle-supported database engine without dialect-specific modifications.

How Portable Schemas Work in Agent-Native

Agent-Native achieves database portability through a shared configuration abstraction that automatically wires your schema to the underlying runtime. The core implementation lives in packages/core/db/drizzle-config.ts, where the createDrizzleConfig helper detects the active database connection and instantiates Drizzle with the correct dialect settings.

Each template (such as Slides, Plan, or Videos) imports this helper in its own drizzle.config.ts file. For example, templates/slides/drizzle.config.ts registers the template's schema while ensuring the resulting db instance remains compatible with both SQLite for local development and Postgres for production deployments.

Key Constraints for Portability

To maintain cross-database compatibility, Agent-Native follows three strict rules:

• Use generic column types – Stick to text, integer, and timestamp instead of dialect-specific types like SERIAL or JSONB.
• Abstract CRUD operations – Rely on Drizzle's query builder (select, insert, update, delete) rather than raw SQL that might vary between SQLite and Postgres.
• Additive migrations only – Schema changes must use CREATE TABLE IF NOT EXISTS, ADD COLUMN, or CREATE INDEX IF NOT EXISTS without destructive DROP or RENAME statements.

Defining Database Tables for Cross-Platform Compatibility

Define your tables using Drizzle's SQLite-compatible helpers, which Agent-Native uses as the baseline for maximum portability across dialects.

// src/db/schema.ts – portable across SQLite & Postgres
import { sqliteTable, text, integer, timestamp } from "drizzle-orm";

export const notes = sqliteTable("notes", {
  id: integer("id").primaryKey({ autoIncrement: true }),
  title: text("title").notNull(),
  body: text("body"),
  createdAt: timestamp("created_at").defaultNow(),
});

While sqliteTable is used here, Drizzle's type system treats these definitions as portable. If you were building a Postgres-only project, you could substitute pgTable, but Agent-Native deliberately standardizes on the SQLite variant to ensure the schema works across all supported engines.

Wiring Schemas with createDrizzleConfig

The createDrizzleConfig function bridges your schema definition with the runtime-provided database connection. Each template implements this in its drizzle.config.ts file.

// templates/slides/drizzle.config.ts
import { createDrizzleConfig } from "@agent-native/core/db/drizzle-config";

export default createDrizzleConfig({
  // The schema is imported from the shared location
  schema: import.meta.glob("./db/schema.ts", { eager: true }) as any,
});

This configuration accomplishes three critical tasks:

1. Detects the runtime database via the Nitro db helper.
2. Instantiates Drizzle with the supplied schema and correct dialect driver.
3. Exports the prepared db object for use in actions and API routes via import { db } from "@agent-native/core".

Executing Type-Safe Queries in Actions

Once configured, you can import the db object and your schema types into any server action to perform fully type-safe database operations.

// templates/slides/actions/save-note.ts
import { eq } from "drizzle-orm";
import { db } from "@agent-native/core";
import { notes } from "@/db/schema";

export default defineAction({
  schema: z.object({ title: z.string(), body: z.string() }),
  run: async ({ title, body }) => {
    await db.insert(notes).values({ title, body });
  },
});

The Drizzle query builder automatically translates these fluent API calls (db.insert, db.select, db.update) into the correct SQL for the underlying engine, whether SQLite or Postgres.

Enforcing Additive Migrations

Agent-Native protects schema portability through a guard script that prevents destructive changes. The file scripts/guard-no-drizzle-push.mjs enforces an additive-only migration policy, ensuring that changes like DROP COLUMN or RENAME TABLE never reach production.

When you need to evolve the schema, use only additive operations:

// scripts/migrate-add-column.ts
import { db } from "@agent-native/core";
import { notes } from "@/db/schema";

await db
  .alterTable(notes)
  .addColumn("updatedAt", timestamp("updated_at"))
  .run();

This approach guarantees compatibility with SQLite's ALTER TABLE limitations while remaining valid Postgres syntax, keeping local and production databases in sync.

Summary

• Single schema definition – Write tables once in packages/core/db/drizzle-config.ts using portable types that work across SQLite and Postgres.
• Automatic dialect detection – The createDrizzleConfig helper handles engine-specific instantiation without manual configuration.
• Shared type safety – Client helpers, server routes, and background jobs all import the same schema types from @agent-native/core.
• Protected migrations – The scripts/guard-no-drizzle-push.mjs guard ensures only additive changes are deployed, preventing breaking schema drift.

Frequently Asked Questions

What makes Agent-Native schemas "portable" across database engines?

Agent-Native schemas are portable because they avoid dialect-specific SQL features and use Drizzle's abstraction layer. By defining tables with sqliteTable and generic types like text and integer, and by restricting migrations to additive-only operations, the same schema file works on SQLite for local development and Postgres or Neon in production without modification.

Can I use Postgres-specific features like JSONB with Agent-Native?

No, using Postgres-specific features like JSONB, SERIAL, or RETURNING * would break portability. Agent-Native intentionally restricts schemas to common column types that map cleanly to both SQLite and Postgres. If you need JSON-like storage, use text columns and serialize data, or wait for Drizzle to provide portable JSON abstractions.

How does the additive migration guard prevent breaking changes?

The guard script located at scripts/guard-no-drizzle-push.mjs runs in CI to block any migration containing destructive SQL keywords like DROP, RENAME, or DELETE. This enforcement ensures that alterTable operations only add columns or indexes, which are supported safely by both SQLite and Postgres, preventing schema drift between environments.

Where is the Drizzle configuration defined in Agent-Native templates?

Each template defines its Drizzle configuration in a drizzle.config.ts file at the template root (e.g., templates/slides/drizzle.config.ts). This file imports createDrizzleConfig from @agent-native/core/db/drizzle-config and passes the template's schema definition, establishing the connection between your portable tables and the runtime database.

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