Migrations

The most important thing about Drizzle ORM is that you can use it as a source of truth for database schema.
DrizzleKit - is a CLI companion for DrizzleORM, it lets generate SQL statements for schema creation and alternations or apply changes directly to the database.

See detailed docs for extended examples and walk-throughs.

Quick start

Declare your schema:

./src/schema.ts

import { index, integer, mysqlTable, bigint, varchar } from 'drizzle-orm/mysql-core';
 
export const users = mysqlTable('users', {
  id: bigint('id', { mode: 'number' }).primaryKey().autoincrement(),
  fullName: varchar('full_name', { length: 256 }),
}, (users) => ({
  nameIdx: index('name_idx').on(users.fullName),
}));
 
export const authOtps = mysqlTable('auth_otp', {
  id: bigint('id', { mode: 'number' }).primaryKey().autoincrement(),
  phone: varchar('phone', { length: 256 }),
  userId: int('user_id').references(() => users.id),
});

Run CLI migration command:

$ pnpm drizzle-kit generate:mysql --schema=./src/schema.ts

And it will generate a migration SQL file:

CREATE TABLE `users` (
 `id` bigint primary key auto_increment,
 `full_name` varchar(256)
);
 
 
CREATE TABLE `auth_otp` (
 `id` bigint primary key auto_increment,
 `phone` varchar(256),
 `user_id` int
);
 
 
ALTER TABLE auth_otp ADD CONSTRAINT auth_otp_user_id_users_id_fk FOREIGN KEY (`user_id`) REFERENCES users(`id`) ;
CREATE INDEX name_idx ON users (`full_name`);

We've designed Drizzle ORM to be an opt-in solution at any point of your development flow.
You can opt-in to run generated migrations with Drizzle, or run it elsewhere wherever its suitable for you to run them with Drizzle:

import { drizzle } from 'drizzle-orm/mysql2';
import { migrate } from 'drizzle-orm/mysql2/migrator';
import mysql from 'mysql2/promise';
 
// create the connection
const poolConnection = mysql.createPool({
  host: 'localhost',
  user: 'root',
  database: 'test',
  multipleStatements: true,
});
 
const db = drizzle(poolConnection);
 
// this will automatically run needed migrations on the database
await migrate(db, { migrationsFolder: './drizzle' });

Batch Custom types