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

mysql-api.mddocs/

MySQL Programmatic API

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

Capabilities

Generate MySQL Snapshot

Generates a MySQL schema snapshot from TypeScript schema imports.

/**
 * Generate MySQL snapshot from schema imports
 * @param imports - Record of schema module exports
 * @param prevId - Previous snapshot ID (optional)
 * @param casing - Serialization casing (optional)
 * @returns MySQL schema snapshot
 */
async function generateMySQLDrizzleJson(
  imports: Record<string, unknown>,
  prevId?: string,
  casing?: 'camelCase' | 'snake_case'
): Promise<MySQLSchemaKit>;

Usage Example:

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

// Generate snapshot
const snapshot = await generateMySQLDrizzleJson(schema);

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

// With snake_case naming
const snakeCase = await generateMySQLDrizzleJson(schema, undefined, 'snake_case');

Generate MySQL Migration

Generates SQL migration statements by comparing two MySQL snapshots.

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

Usage Example:

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

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

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

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

Push MySQL Schema

Pushes schema changes directly to a MySQL database.

/**
 * Push schema changes to MySQL database
 * @param imports - Record of schema module exports
 * @param drizzleInstance - Drizzle MySQL database instance
 * @param databaseName - Name of the database
 * @returns Push result with warnings and apply function
 */
function pushMySQLSchema(
  imports: Record<string, unknown>,
  drizzleInstance: MySql2Database<any>,
  databaseName: string
): Promise<PushResult>;

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

Usage Example:

import { pushMySQLSchema } from 'drizzle-kit/api';
import { drizzle } from 'drizzle-orm/mysql2';
import mysql from 'mysql2/promise';
import * as schema from './schema';

// Create MySQL connection
const connection = await mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: 'password',
  database: 'mydb',
});

// Create Drizzle instance
const db = drizzle(connection);

// Push schema
const result = await pushMySQLSchema(schema, db, 'mydb');

// 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');
}

// Clean up
await connection.end();

Start MySQL Studio Server

Starts Drizzle Studio server for MySQL database.

/**
 * Start Drizzle Studio server for MySQL
 * @param imports - Record of schema module exports
 * @param credentials - MySQL connection credentials
 * @param options - Server options (optional)
 */
function startStudioMySQLServer(
  imports: Record<string, unknown>,
  credentials: MysqlCredentials,
  options?: {
    host?: string;
    port?: number;
    casing?: 'camelCase' | 'snake_case';
  }
): Promise<void>;

Usage Example:

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

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

// Start with connection parameters
const customServer = await startStudioMySQLServer(
  schema,
  {
    host: 'localhost',
    port: 3306,
    user: 'root',
    password: 'password',
    database: 'mydb',
  },
  {
    host: '0.0.0.0',
    port: 4983,
    casing: 'camelCase',
  }
);

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

// With SSL
const sslServer = await startStudioMySQLServer(
  schema,
  {
    host: 'remote-host',
    database: 'mydb',
    ssl: {
      ca: '/path/to/ca-cert.pem',
      rejectUnauthorized: true,
    },
  }
);

Types

DrizzleMySQLSnapshotJSON

MySQL schema snapshot type (alias for MySQLSchemaKit).

type DrizzleMySQLSnapshotJSON = MySQLSchemaKit;

interface MySQLSchemaKit {
  id: string;
  prevId: string;
  version: string;
  dialect: 'mysql';
  tables: Record<string, Table>;
  views: Record<string, View>;
  _meta: {
    tables: Record<string, string>;
    columns: Record<string, string>;
  };
}

Credentials

MySQL connection credentials with multiple variants.

type MysqlCredentials =
  // Standard connection
  | {
      host: string;
      port?: number;              // Default: 3306
      user?: string;
      password?: string;
      database: string;
      ssl?: string | SslOptions;
    }
  // Connection URL
  | {
      url: string;
    };

interface SslOptions {
  pfx?: string;                   // PFX/PKCS12 encoded private key and certificate
  key?: string;                   // Private key in PEM format
  passphrase?: string;            // Passphrase for private key
  cert?: string;                  // Certificate chain in PEM format
  ca?: string | string[];         // CA certificates in PEM format
  crl?: string | string[];        // Certificate revocation lists
  ciphers?: string;               // Cipher suite specification
  rejectUnauthorized?: boolean;   // Verify server certificate (default: true)
}

Schema Components

interface Table {
  name: 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;
  autoincrement: boolean;
  default?: string;
  onUpdate?: string;
  generated?: {
    as: string;
    type: 'stored' | 'virtual';
  };
}

interface Index {
  name: string;
  columns: string[];
  isUnique: boolean;
  using?: 'btree' | 'hash';
}

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

interface PrimaryKey {
  name: string;
  columns: string[];
}

interface UniqueConstraint {
  name: string;
  columns: string[];
}

interface CheckConstraint {
  name: string;
  value: string;
}

interface View {
  name: string;
  definition: string;
  algorithm?: 'undefined' | 'merge' | 'temptable';
  sqlSecurity?: 'definer' | 'invoker';
  withCheckOption?: 'cascaded' | 'local';
}

Supported Features

MySQL API supports:

  • Tables: All MySQL column types, auto_increment
  • Indexes: B-tree and hash indexes
  • Constraints: Foreign keys, primary keys, unique, check constraints
  • Views: Regular views with algorithm options
  • Generated Columns: Virtual and stored generated columns
  • ON UPDATE: Automatic timestamp updates

Note: MySQL does not support:

  • Enums (use VARCHAR with CHECK constraint)
  • Schemas/namespaces (single database)
  • Sequences (use AUTO_INCREMENT)
  • Materialized views

Common Patterns

Custom Migration Tool

import {
  generateMySQLDrizzleJson,
  generateMySQLMigration,
} from 'drizzle-kit/api';
import { writeFileSync, readFileSync } 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 = generateMySQLDrizzleJson(schema);

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

  // Save migration file with breakpoints
  const timestamp = Date.now();
  const migrationContent = sql.join('\n--> statement-breakpoint\n');
  writeFileSync(
    `./migrations/${timestamp}_${name}.sql`,
    migrationContent
  );

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

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

Schema Validation

import { pushMySQLSchema } from 'drizzle-kit/api';
import { drizzle } from 'drizzle-orm/mysql2';
import mysql from 'mysql2/promise';
import * as schema from './schema';

async function validateSchema() {
  const connection = await mysql.createConnection({
    host: process.env.DB_HOST,
    user: process.env.DB_USER,
    password: process.env.DB_PASSWORD,
    database: process.env.DB_NAME,
  });

  const db = drizzle(connection);

  // Push schema in dry-run mode
  const result = await pushMySQLSchema(schema, db, process.env.DB_NAME!);

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

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

  await connection.end();
  return false;
}

Connection Pool Integration

import { pushMySQLSchema } from 'drizzle-kit/api';
import { drizzle } from 'drizzle-orm/mysql2';
import mysql from 'mysql2/promise';
import * as schema from './schema';

// Create connection pool
const pool = mysql.createPool({
  host: 'localhost',
  user: 'root',
  password: 'password',
  database: 'mydb',
  connectionLimit: 10,
});

const db = drizzle(pool);

// Push schema using pool
const result = await pushMySQLSchema(schema, db, 'mydb');

if (!result.hasDataLoss) {
  await result.apply();
}

// Pool remains open for application use

SSL/TLS Connection

import { startStudioMySQLServer } from 'drizzle-kit/api';
import { readFileSync } from 'fs';
import * as schema from './schema';

const server = await startStudioMySQLServer(
  schema,
  {
    host: 'remote-host',
    port: 3306,
    user: 'dbuser',
    password: process.env.DB_PASSWORD,
    database: 'production',
    ssl: {
      ca: readFileSync('/path/to/ca-cert.pem', 'utf8'),
      cert: readFileSync('/path/to/client-cert.pem', 'utf8'),
      key: readFileSync('/path/to/client-key.pem', 'utf8'),
      rejectUnauthorized: true,
    },
  }
);

PlanetScale Integration

import { pushMySQLSchema } from 'drizzle-kit/api';
import { drizzle } from 'drizzle-orm/planetscale-serverless';
import { Client } from '@planetscale/database';
import * as schema from './schema';

// Create PlanetScale client
const client = new Client({
  url: process.env.DATABASE_URL,
});

const db = drizzle(client);

// Push schema (note: PlanetScale may have limitations)
const result = await pushMySQLSchema(schema, db, 'main');

if (result.statementsToExecute.length > 0) {
  console.log('Schema changes detected');
  // Note: PlanetScale uses branching, consider using migrations instead
}

Migration Breakpoints

MySQL requires statement breakpoints because it doesn't support transactional DDL:

import { generateMySQLMigration } from 'drizzle-kit/api';

// Generate migration
const statements = await generateMySQLMigration(prev, cur);

// Add breakpoints for sequential execution
const migration = statements.join('\n--> statement-breakpoint\n');

// Each statement will be executed separately
// This ensures partial success if a statement fails

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