CtrlK
BlogDocsLog inGet started
Tessl Logo

tessl/npm-drizzle-kit

CLI migration tool for Drizzle ORM that automatically generates SQL migrations, pushes schema changes, introspects databases, and provides visual database management through Drizzle Studio.

Overview
Eval results
Files

postgresql-api.mddocs/

PostgreSQL Programmatic API

Programmatic API for PostgreSQL database operations including snapshot generation, migration creation, schema pushing, and Drizzle Studio server management.

Capabilities

Generate PostgreSQL Snapshot

Generates a PostgreSQL schema snapshot from TypeScript schema imports.

/**
 * Generate PostgreSQL snapshot from schema imports
 * @param imports - Record of schema module exports
 * @param prevId - Previous snapshot ID (optional)
 * @param schemaFilters - PostgreSQL schemas to include (optional, default: ['public'])
 * @param casing - Serialization casing (optional)
 * @returns PostgreSQL schema snapshot
 */
function generateDrizzleJson(
  imports: Record<string, unknown>,
  prevId?: string,
  schemaFilters?: string[],
  casing?: 'camelCase' | 'snake_case'
): PgSchemaKit;

Usage Example:

import { generateDrizzleJson } from 'drizzle-kit/api';
import * as schema from './schema';

// Generate snapshot
const snapshot = generateDrizzleJson(schema);

// With previous ID (for migration chain)
const snapshot2 = generateDrizzleJson(schema, snapshot.id);

// With schema filters
const publicOnly = generateDrizzleJson(schema, undefined, ['public']);

// With casing
const snakeCase = generateDrizzleJson(schema, undefined, undefined, 'snake_case');

Generate PostgreSQL Migration

Generates SQL migration statements by comparing two PostgreSQL snapshots.

/**
 * Generate SQL migration from two PostgreSQL snapshots
 * @param prev - Previous snapshot
 * @param cur - Current snapshot
 * @returns Array of SQL statements
 */
function generateMigration(
  prev: DrizzleSnapshotJSON,
  cur: DrizzleSnapshotJSON
): Promise<string[]>;

Usage Example:

import { generateDrizzleJson, generateMigration } from 'drizzle-kit/api';
import * as oldSchema from './old-schema';
import * as newSchema from './new-schema';

// Generate snapshots
const prevSnapshot = generateDrizzleJson(oldSchema);
const curSnapshot = generateDrizzleJson(newSchema);

// Generate migration SQL
const sqlStatements = await generateMigration(prevSnapshot, curSnapshot);

// Execute or save migration
console.log(sqlStatements.join('\n'));

Push PostgreSQL Schema

Pushes schema changes directly to a PostgreSQL database.

/**
 * Push schema changes to PostgreSQL database
 * @param imports - Record of schema module exports
 * @param drizzleInstance - Drizzle PostgreSQL database instance
 * @param schemaFilters - PostgreSQL schemas to include (optional)
 * @param tablesFilter - Table name filters (optional)
 * @param extensionsFilters - Extension filters (optional)
 * @returns Push result with warnings and apply function
 */
function pushSchema(
  imports: Record<string, unknown>,
  drizzleInstance: PgDatabase<any>,
  schemaFilters?: string[],
  tablesFilter?: string[],
  extensionsFilters?: 'postgis'[]
): Promise<PushResult>;

interface PushResult {
  hasDataLoss: boolean;
  warnings: string[];
  statementsToExecute: string[];
  apply: () => Promise<void>;
}

Usage Example:

import { pushSchema } from 'drizzle-kit/api';
import { drizzle } from 'drizzle-orm/node-postgres';
import { Pool } from 'pg';
import * as schema from './schema';

// Create Drizzle instance
const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
});
const db = drizzle(pool);

// Push schema
const result = await pushSchema(schema, db);

// Check for warnings
if (result.hasDataLoss) {
  console.warn('Warning: Data loss may occur!');
  result.warnings.forEach(w => console.warn(w));
}

// Review statements
console.log('Statements to execute:');
result.statementsToExecute.forEach(s => console.log(s));

// Apply changes
if (confirm('Apply changes?')) {
  await result.apply();
  console.log('Schema pushed successfully');
}

// With filters
const filteredResult = await pushSchema(
  schema,
  db,
  ['public', 'auth'],           // Schema filters
  ['users_*', '!temp_*'],       // Table filters
  ['postgis']                    // Extension filters
);

Start PostgreSQL Studio Server

Starts Drizzle Studio server for PostgreSQL database.

/**
 * Start Drizzle Studio server for PostgreSQL
 * @param imports - Record of schema module exports
 * @param credentials - PostgreSQL credentials or PGlite client
 * @param options - Server options (optional)
 */
function startStudioPostgresServer(
  imports: Record<string, unknown>,
  credentials: PostgresCredentials | { driver: 'pglite'; client: PGlite },
  options?: {
    host?: string;
    port?: number;
    casing?: 'camelCase' | 'snake_case';
  }
): Promise<void>;

Usage Example:

import { startStudioPostgresServer } from 'drizzle-kit/api';
import * as schema from './schema';

// Start Studio with connection URL
await startStudioPostgresServer(
  schema,
  {
    url: process.env.DATABASE_URL,
  }
);

// Start with custom options
await startStudioPostgresServer(
  schema,
  {
    host: 'localhost',
    port: 5432,
    user: 'postgres',
    password: 'password',
    database: 'mydb',
  },
  {
    host: '0.0.0.0',
    port: 4983,
    casing: 'camelCase',
  }
);

console.log('Studio started at http://localhost:4983');

// With PGlite
import { PGlite } from '@electric-sql/pglite';

const pglite = new PGlite('./my-database');
const pgliteServer = await startStudioPostgresServer(
  schema,
  {
    driver: 'pglite',
    client: pglite,
  }
);

Upgrade PostgreSQL Snapshot

Upgrades a PostgreSQL snapshot to the latest format version.

/**
 * Upgrade PostgreSQL snapshot to latest version
 * @param snapshot - Old format snapshot
 * @returns Upgraded snapshot
 */
function upPgSnapshot(snapshot: any): PgSchemaKit;

Usage Example:

import { upPgSnapshot } from 'drizzle-kit/api';
import { readFileSync, writeFileSync } from 'fs';

// Read old snapshot
const oldSnapshot = JSON.parse(readFileSync('./old-snapshot.json', 'utf8'));

// Upgrade to latest format
const upgradedSnapshot = upPgSnapshot(oldSnapshot);

// Save upgraded snapshot
writeFileSync('./snapshot.json', JSON.stringify(upgradedSnapshot, null, 2));

Types

DrizzleSnapshotJSON

PostgreSQL schema snapshot type (alias for PgSchemaKit).

type DrizzleSnapshotJSON = PgSchemaKit;

interface PgSchemaKit {
  id: string;
  prevId: string;
  version: string;
  dialect: 'postgresql';
  tables: Record<string, Table>;
  enums: Record<string, Enum>;
  schemas: Record<string, Schema>;
  sequences: Record<string, Sequence>;
  roles: Record<string, Role>;
  policies: Record<string, Policy>;
  views: Record<string, View>;
  _meta: {
    schemas: Record<string, string>;
    tables: Record<string, string>;
    columns: Record<string, string>;
  };
}

Credentials

PostgreSQL connection credentials with multiple variants.

type PostgresCredentials =
  // Standard connection
  | {
      host: string;
      port?: number;
      user?: string;
      password?: string;
      database: string;
      ssl?: boolean | 'require' | 'allow' | 'prefer' | 'verify-full' | ConnectionOptions;
    }
  // Connection URL
  | {
      url: string;
    }
  // AWS RDS Data API
  | {
      driver: 'aws-data-api';
      database: string;
      secretArn: string;
      resourceArn: string;
    }
  // PGlite
  | {
      driver: 'pglite';
      url: string;
    };

Schema Components

interface Table {
  name: string;
  schema: string;
  columns: Record<string, Column>;
  indexes: Record<string, Index>;
  foreignKeys: Record<string, ForeignKey>;
  primaryKeys: Record<string, PrimaryKey>;
  uniqueConstraints: Record<string, UniqueConstraint>;
  checkConstraints: Record<string, CheckConstraint>;
}

interface Column {
  name: string;
  type: string;
  primaryKey: boolean;
  notNull: boolean;
  default?: string;
  typeSchema?: string;
  identity?: string;
  generated?: {
    as: string;
    type: 'stored' | 'virtual';
  };
}

interface Index {
  name: string;
  columns: string[];
  isUnique: boolean;
  concurrently: boolean;
  method: 'btree' | 'hash' | 'gist' | 'gin' | 'brin' | 'spgist';
  where?: string;
  with?: Record<string, any>;
}

interface ForeignKey {
  name: string;
  tableFrom: string;
  tableTo: string;
  schemaTo?: string;
  columnsFrom: string[];
  columnsTo: string[];
  onDelete?: 'cascade' | 'set null' | 'set default' | 'restrict' | 'no action';
  onUpdate?: 'cascade' | 'set null' | 'set default' | 'restrict' | 'no action';
}

interface Enum {
  name: string;
  schema: string;
  values: string[];
}

interface Sequence {
  name: string;
  schema: string;
  startWith?: string;
  increment?: string;
  minValue?: string;
  maxValue?: string;
  cache?: string;
  cycle?: boolean;
}

interface Schema {
  name: string;
}

interface View {
  name: string;
  schema: string;
  definition: string;
  materialized: boolean;
  with?: Record<string, any>;
  tablespace?: string;
  using?: string;
  withNoData?: boolean;
}

interface Policy {
  name: string;
  schema: string;
  on: string;
  as?: 'permissive' | 'restrictive';
  for?: 'all' | 'select' | 'insert' | 'update' | 'delete';
  to?: string[];
  using?: string;
  withCheck?: string;
}

interface Role {
  name: string;
  createDb?: boolean;
  createRole?: boolean;
  inherit?: boolean;
}

Supported Features

PostgreSQL API supports all PostgreSQL features:

  • Tables: All column types, constraints, defaults
  • Enums: PostgreSQL enum types
  • Schemas: Database schema namespaces
  • Sequences: Serial, identity, custom sequences
  • Views: Regular and materialized views
  • Indexes: All index types (btree, hash, gist, gin, brin, spgist)
  • Partial Indexes: Indexes with WHERE clauses
  • Expression Indexes: Indexes on expressions
  • Constraints: Foreign keys, primary keys, unique, check
  • Row-Level Security: Policies
  • Roles: Database roles (experimental)
  • Extensions: PostGIS table filtering

Common Patterns

Custom Migration Tool

import { generateDrizzleJson, generateMigration } from 'drizzle-kit/api';
import { writeFileSync } from 'fs';
import * as schema from './schema';

async function createMigration(name: string) {
  // Load previous snapshot
  const prevSnapshot = JSON.parse(
    readFileSync('./snapshots/latest.json', 'utf8')
  );

  // Generate current snapshot
  const curSnapshot = generateDrizzleJson(schema);

  // Generate migration
  const sql = await generateMigration(prevSnapshot, curSnapshot);

  // Save migration file
  const timestamp = Date.now();
  writeFileSync(
    `./migrations/${timestamp}_${name}.sql`,
    sql.join('\n')
  );

  // Update latest snapshot
  writeFileSync(
    './snapshots/latest.json',
    JSON.stringify(curSnapshot, null, 2)
  );

  console.log(`Migration created: ${timestamp}_${name}.sql`);
}

Schema Validation

import { generateDrizzleJson, pushSchema } from 'drizzle-kit/api';
import { drizzle } from 'drizzle-orm/node-postgres';
import * as schema from './schema';

async function validateSchema(db: PgDatabase<any>) {
  // Push schema in dry-run mode
  const result = await pushSchema(schema, db);

  // Check if schema is in sync
  if (result.statementsToExecute.length === 0) {
    console.log('✓ Schema is in sync');
    return true;
  }

  // Report differences
  console.log('✗ Schema is out of sync');
  console.log('Required changes:');
  result.statementsToExecute.forEach(stmt => console.log(`  ${stmt}`));

  return false;
}

Multi-Schema Management

import { generateDrizzleJson } from 'drizzle-kit/api';
import * as schema from './schema';

// Generate snapshot for specific schemas
const publicSchema = generateDrizzleJson(schema, undefined, ['public']);
const authSchema = generateDrizzleJson(schema, undefined, ['auth']);
const allSchemas = generateDrizzleJson(schema, undefined, ['public', 'auth', 'custom']);

AWS RDS Data API Integration

import { pushSchema, startStudioPostgresServer } from 'drizzle-kit/api';
import { drizzle } from 'drizzle-orm/aws-data-api/pg';
import { RDSDataClient } from '@aws-sdk/client-rds-data';
import * as schema from './schema';

// Create RDS Data API client
const rdsClient = new RDSDataClient({ region: 'us-east-1' });

const db = drizzle(rdsClient, {
  database: process.env.DATABASE_NAME!,
  secretArn: process.env.SECRET_ARN!,
  resourceArn: process.env.RESOURCE_ARN!,
});

// Push schema using Data API
const result = await pushSchema(schema, db);
await result.apply();

PGlite Integration

import { pushSchema, startStudioPostgresServer } from 'drizzle-kit/api';
import { PGlite } from '@electric-sql/pglite';
import { drizzle } from 'drizzle-orm/pglite';
import * as schema from './schema';

// Create PGlite instance
const client = new PGlite('./my-database');
const db = drizzle(client);

// Push schema to embedded database
const result = await pushSchema(schema, db);
await result.apply();

// Start Studio with PGlite
const server = await startStudioPostgresServer(
  schema,
  { driver: 'pglite', client }
);

Install with Tessl CLI

npx tessl i tessl/npm-drizzle-kit

docs

cli-commands.md

configuration.md

index.md

mysql-api.md

postgresql-api.md

singlestore-api.md

sqlite-api.md

tile.json