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