drizzle-kit up CLI examples
Examples of drizzle-kit up CLI usage: npx drizzle-kit up --dialect=cockroach and npx drizzle-kit up --dialect=cockroach --out=./migrations-folder.
drizzle-kit/migrations
92 notes in this subject, read out of this brain and free to use. This is page 1 of 2.
Custom SQL migration file format and example
Custom migration files are SQL files (e.g., 20242409135510_seed-users.sql) containing manual SQL statements. Example: INSERT statements to seed data like `INSERT INTO "users" ("name") VALUES('Dan');`
Custom migrations directory structure
Custom migration files are stored in the drizzle directory alongside auto-generated migrations. The directory naming follows the pattern of timestamp followed by the migration name (e.g., 20242409135510_seed-users).
Option 6: Codebase first with drizzle-kit export and Atlas
In the codebase first approach using drizzle-kit export and Atlas, your TypeScript Drizzle schema is the source of truth. You use the drizzle-kit export command to read your Drizzle schema, generate the SQL representation, and output it to the console. You can then apply these SQL statements to the database via Atlas or other external SQL migration tools.
Option 4: Codebase first with runtime migrations
In the codebase first approach with runtime migrations, you use drizzle-kit generate to create SQL migration files, then apply them to the database during runtime of your application using the migrate function. This approach is widely used for monolithic applications that apply database migrations during zero downtime deployment and rollback DDL changes if something fails. It is also used in serverless deployments with migrations running in a custom resource once during deployment process.
Runtime migration example with CockroachDB
To apply migrations at runtime with CockroachDB, import drizzle and the migrate function, initialize a database connection, and call the migrate function. Example: import { drizzle } from "drizzle-orm/cockroach"; import { migrate } from 'drizzle-orm/cockroach/migrator'; const db = drizzle(process.env.DATABASE_URL); await migrate(db);
generate creates migration files but does not apply them
The generate command creates SQL migration files with additional information needed for drizzle-kit or other migration tools. After generation, these migrations are not automatically applied to the database and must be applied in a separate step.
generate command detects all index property changes
Unlike push, the generate command will detect and trigger migration generation for any changes to any property in drizzle indexes API, with no limitations on which properties can be modified.
Durable Objects migration configuration in wrangler.toml
Configure migrations for Durable Objects in wrangler.toml using the [[migrations]] section. Specify a tag (e.g., 'v1') and list the Durable Object classes using new_sqlite_classes parameter. Also add a [[rules]] section with type 'Text' and globs '**/*.sql' to allow importing migration files.
Run migrations in Cloudflare Durable Objects constructor
Use ctx.blockConcurrencyWhile() to ensure all migrations complete before accepting queries in the Durable Object constructor. This prevents queries from running before the schema is ready. Import migrate from 'drizzle-orm/durable-sqlite/migrator' and call migrate(this.db, migrations).
Durable Object concurrency blocking for migrations
Use ctx.blockConcurrencyWhile(async () => { await this._migrate(); }) in the DurableObject constructor to ensure all migrations complete before the Durable Object accepts any incoming requests or queries. This prevents queries from executing against an unmigrated database.
Importing migrations from generated files in Durable Objects
Import migrations as a default import: 'import migrations from '../drizzle/migrations''. This requires the wrangler.toml to have [[rules]] configuration with type 'Text' and globs pattern '**/*.sql' with fallthrough true to allow importing generated SQL migration files.
Running migrations with Durable Objects
Generate migrations with 'npx drizzle-kit generate'. Migrations can only be applied from Cloudflare Workers, not externally. Import the migrate function from 'drizzle-orm/durable-sqlite/migrator' and import migrations from '../drizzle/migrations'. Call migrate(db, migrations) within a Durable Object method. Use ctx.blockConcurrencyWhile() in the constructor to ensure migrations complete before accepting queries, otherwise call migrate() in functions that access the database.
Updating schema with new columns and applying migrations
After adding a new column to a schema definition in schema.ts, migrations can be generated and applied to the database using drizzle-kit commands. New columns will appear as null in existing rows if data was not backfilled.
TiDB setup step 10 apply changes optional
The tenth step is to apply changes to the TiDB database, which is optional.
Apply migrations using useMigrations hook
To apply migrations at runtime in an Expo app with OP-SQLite, import `useMigrations` from 'drizzle-orm/op-sqlite/migrator' and pass the db instance and migrations object. The hook returns an object with `success` and `error` properties to track migration status.
Generate migrations for OP-SQLite
For OP-SQLite with Expo, generate migrations by running the command `npx drizzle-kit generate`. Migrations are then applied at runtime using the `migrate()` function from drizzle-orm.
Drizzle Kit commands supported with D1 HTTP
When using the d1-http driver with Drizzle Kit, the following commands are supported: migrate, push, introspect, and studio.
Gel migration commands
After defining the Gel schema, create and apply migrations using: gel migration create (to generate a migration file) and gel migration apply (to apply migrations to the database).
Drizzle migrations web and mobile environments status
The section on Drizzle migrations in web and mobile environments will be updated in the next release. For Expo SQLite, OP SQLite, and React Native migrations, users should refer to the Get Started guide.
drizzle-kit check --ignore-conflicts flag
The --ignore-conflicts flag can be used with drizzle-kit check to skip commutativity checks and bypass conflict detection. This flag should only be used in rare cases, and if you need it, it may indicate a bug in drizzle-kit that should be reported.
drizzle-kit check with CLI dialect option
Run 'npx drizzle-kit check --dialect=mssql' to specify the dialect directly as a CLI option without a configuration file.
drizzle-kit export example with mssql schema
Example schema export: given schema.ts with mssqlTable('users', {id: int().primaryKey().identity(), email: text().notNull(), name: text()}), running npx drizzle-kit export outputs: CREATE TABLE [users] ([id] int IDENTITY(1, 1), [email] text NOT NULL, [name] text, CONSTRAINT [users_pkey] PRIMARY KEY([id]));
drizzle-kit export CLI options
drizzle-kit export has these CLI-only options: --sql (generating SQL representation of Drizzle Schema, default is true). Additional required configuration options available via CLI: --dialect (required, one of mssql/mysql/postgresql/etc), --schema (required, path to typescript schema file or folder), --config (optional, configuration file path, default is drizzle.config.ts).
drizzle-kit generate with custom config example
Example configuration for creating a custom migration: create drizzle.config.ts with dialect set to 'mssql', schema pointing to './src/schema.ts', and out set to './migrations'. Then run: npx drizzle-kit generate --config=./configs/drizzle.config.ts --name=seed-users --custom
drizzle-kit migrate complete workflow example
Complete workflow: (1) Define schema in src/schema.ts using drizzle-orm/mssql-core with mssqlTable to define tables. (2) Create drizzle.config.ts with dialect 'mssql', schema path, and database credentials. (3) Run 'npx drizzle-kit generate --name=init' to generate migration files in the migrations folder. (4) Run 'npx drizzle-kit migrate' to apply the generated migrations to the database.
drizzle-kit stores applied migrations in __drizzle_migrations table
When migrations are run, drizzle-kit stores records of successfully applied migrations in a table named __drizzle_migrations in the drizzle schema by default. Both the table name and schema are customizable.
drizzle-kit up with CLI dialect option
To run drizzle-kit up via CLI, execute: npx drizzle-kit up --dialect=mssql
JavaScript and TypeScript migrations coming soon
The ability to run custom JavaScript and TypeScript migration and seeding scripts is planned for a future release of Drizzle Kit. Users can follow the GitHub discussion at https://github.com/drizzle-team/drizzle-orm/discussions/2832 for updates.
Custom migration file structure
Custom migration files are created in the drizzle directory with a timestamp prefix and underscore-separated name (for example, 20242409135510_seed-users.sql). The SQL file can contain any valid SQL statements such as INSERT, UPDATE, DELETE, or other DDL operations.
drizzle-kit export command
The drizzle-kit export command is used to convert a TypeScript schema into raw SQL DDL and print it out.
Drizzle migrations fundamentals and approaches
SQL databases require strict schemas. Drizzle supports multiple production-grade migration approaches: database first (schema is source of truth in database, pulled to codebase) and codebase first (schema is source of truth in code, applied to database). The drizzle-kit CLI provides commands for migrate, generate, push, pull, and export to support all approaches.
Example: Generated SQL migration file for MSSQL
The generated migration.sql file contains CREATE TABLE with MSSQL-specific syntax. Example: CREATE TABLE [users] ( [id] int IDENTITY(1, 1), [name] varchar(255), [email] varchar(255), CONSTRAINT [users_pkey] PRIMARY KEY([id]), CONSTRAINT [users_email_key] UNIQUE([email]) );
SQL export with drizzle-kit export
Use drizzle-kit export to output the SQL representation of a Drizzle schema to the console. This approach reads the Drizzle schema and generates SQL statements that can be applied via Atlas or other external SQL migration tools.
Example: Runtime migration with migrate function
To apply migrations at runtime: import { drizzle } from "drizzle-orm/node-mssql"; import { migrate } from 'drizzle-orm/node-mssql/migrator'; const db = drizzle(process.env.DATABASE_URL); await migrate(db);
Generated SQL migrations with external tools
Use drizzle-kit generate to create SQL migration files, then apply them via external migration tools like Bytebase, Liquibase, Atlas, or directly to the database. This approach keeps migration generation within Drizzle while delegating application to external systems.
Runtime migrations with generated SQL files
Use drizzle-kit generate to create SQL migration files, then apply them at runtime using the migrate function from drizzle-orm. Call migrate(db) in your application code to read migration.sql files, fetch migration history from database, pick previously unapplied migrations, and apply them. This approach is used for monolithic applications with zero downtime deployments and serverless deployments with custom resources.
Drizzle Kit migrate command
Add 'migrate' script to package.json that runs 'drizzle-kit migrate'. This command runs the generated SQL migration files against the database.
Drizzle Kit generate command
Add 'generate' script to package.json that runs 'drizzle-kit generate'. This command creates SQL migration files in the out directory specified in drizzle.config.ts. Migration files are placed in timestamped subdirectories (e.g., '20242409125510_pale_mister_fear/migration.sql').
drizzle-kit check basic usage with config file
To run drizzle-kit check with a config file, specify the dialect in drizzle.config.ts and run 'npx drizzle-kit check'. Example config: export default defineConfig({ dialect: 'singlestore' });
drizzle-kit check requires dialect and connection credentials
The drizzle-kit check command requires you to specify both dialect and database connection credentials. You can provide them either via a drizzle.config.ts config file or via CLI options.
drizzle-kit check with CLI options
You can run drizzle-kit check without a config file by passing options directly via command line: npx drizzle-kit check --dialect=singlestore
drizzle-kit generate with CLI options
You can run drizzle-kit generate with inline CLI options: npx drizzle-kit generate --dialect=singlestore --schema=./src/schema.ts
drizzle-kit generate with config file
Example of using drizzle-kit generate with a config file: create a drizzle.config.ts with dialect set to 'singlestore' and schema pointing to './src/schema.ts', then run 'npx drizzle-kit generate'.
drizzle-kit generate CLI-only options reference
The drizzle-kit generate command has the following CLI-only options: custom (generate empty SQL for custom migration), name (generate migration with custom name), ignore-conflicts (skip commutativity conflict checks, default is false).
drizzle-kit generate configuration options
The drizzle-kit generate command accepts the following configuration options: dialect (required, database dialect), schema (required, path to typescript schema file(s) or folder(s)), out (migrations output folder, default './drizzle'), config (configuration file path, default 'drizzle.config.ts'), breakpoints (SQL statements breakpoints, default 'true').
drizzle-kit generate migration output format
Generated migrations are saved in a folder with a timestamp name (e.g., 20242409125510_migration_name) containing two files: migration.sql with the generated SQL statements and snapshot.json with a JSON representation of the schema.
drizzle-kit generate applying migrations
Generated migrations can be applied using drizzle-kit migrate, using drizzle-orm's migrate() function, using external migration tools like bytebase, or by running migrations directly on the database.
drizzle-kit migrate --ignore-conflicts flag
The `--ignore-conflicts` flag is available starting from `drizzle-orm@1.0.0-beta.16`. It allows the `migrate` command to skip commutativity checks and bypass conflicts. Use `npx drizzle-kit migrate --ignore-conflicts`. This flag should only be used if commutativity checks are producing false positives, and such cases should be reported as bugs.
drizzle-kit migrate with CLI options
To use `drizzle-kit migrate` with CLI options without a config file, pass `--dialect` and `--url` flags: `npx drizzle-kit migrate --dialect=singlestore --url=mysql://user:password@host:3306/dbname`
SingleStore migrations require single client connection
For the built-in migrate function with DDL migrations, use a single client connection rather than a pool connection. A pool connection can be used for querying purposes based on business demands.
Custom migration file structure
When using `drizzle-kit generate --custom --name=seed-users`, a new migration directory is created with a timestamp-based name (e.g., 20242409135510_seed-users). Inside this directory is a SQL file where custom migrations can be written. For example, a migration named seed-users would create a file at ./drizzle/20242409135510_seed-users.sql containing the custom SQL statements.
drizzle-kit migrate command
drizzle-kit migrate lets you apply generated SQL migration files to your database.
drizzle-kit up command
drizzle-kit up is used to upgrade snapshots of previously generated migrations.
drizzle-kit check command
drizzle-kit check will walk through all generated migrations and check for any race conditions (collisions) of generated migrations.
Migration option 5: Codebase first with generated SQL files and external tools
When you want database schema in your TypeScript codebase, want Drizzle to generate SQL migration files, but want to apply them yourself or via external migration tools, use drizzle-kit generate. Your TypeScript Drizzle schema is the source of truth. You can apply the generated SQL files directly to the database or use external tools like Bytebase, Liquibase, or Atlas.
drizzle-kit CLI commands
drizzle-kit is a CLI app for managing migrations with Drizzle. The main commands are: drizzle-kit migrate, drizzle-kit generate, drizzle-kit push, and drizzle-kit pull. It allows you to push your schema, generate SQL migration files, or pull the schema from the database.
Migration option 4: Codebase first with generated SQL files and runtime migration
When you want database schema in your TypeScript codebase, want Drizzle to generate SQL migration files, and want to apply them during runtime of your application, use drizzle-kit generate to create SQL migration files and then import and call the migrate function from drizzle-orm. This approach is used for monolithic applications with zero-downtime deployments and serverless deployments with migrations running in custom resources.
Runtime migration with drizzle-orm/singlestore/migrator
To apply migrations during runtime, import the migrate function from drizzle-orm/singlestore/migrator, create a database connection, and call await migrate(db). This reads migration.sql files in the migrations folder, fetches migration history from the database, picks previously unapplied migrations, and applies them.
Encore automatically applies Drizzle migrations
Migrations are automatically applied when you run your Encore application with 'encore run'. You do not need to run 'drizzle-kit migrate' manually.