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, serial, varchar } from 'drizzle-orm/mysql-core';
 
export const users = mysqlTable('users', {
  id: serial('id').primaryKey(),
  fullName: varchar('full_name', { length: 256 }),
}, (users) => ({
  nameIdx: index('name_idx').on(users.fullName),
}));
 
export const authOtps = mysqlTable('auth_otp', {
  id: serial('id').primaryKey(),
  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` int PRIMARY KEY,
 `full_name` varchar(256)
);
 
 
CREATE TABLE `auth_otp` (
 `id` serial PRIMARY KEY,
 `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' });

Performance Custom types