What is TypeScript SQL-like Drizzle ORM?

Drizzle ORM (11.5k ⭐) is a TypeScript ORM for SQL databases designed with maximum type safety in mind. It comes with a drizzle-kit CLI companion for automatic SQL migrations generation. Drizzle ORM is meant to be a library, not a framework. It stays as an opt-in solution all the time at any levels.

Drizzle ORM follows the SQL-like syntax whenever possible, and is strongly typed ground up. It supports full type safety, smart automated migrations generation, best in class fully typed joins, fully typed partial and non-partial selects of any complexity, and zero dependencies.

Drizzle ORM supports various databases, such as PostgreSQL, MySQL, SQLite, Cloudflare D1, libSQL, Turso, PlanetScale, Neon, Vercel Postgres and Supabase. It also integrates with Zod schema generation.

Drizzle ORM is praised by developers for its simplicity, flexibility and performance. If you know SQL, you know Drizzle ORM!

No ORM learning curve

Drizzle ORM is designed to be easy to use for anyone who knows SQL. You don’t have to learn a new syntax or API to work with Drizzle ORM. You can just write SQL-like queries and get type-safe results.

import { drizzle } from 'drizzle-orm/postgres-js';
import postgres from 'postgres';
import { users, posts } from './schema';

const client = postgres(connectionString);
const db = drizzle(client);

const userPosts = await db
  .select()
  .from(users)
  .innerJoin(posts, users.id.eq(posts.userId));

SQL-like syntax for table definitions and queries

SQL-like syntax for table definitions and queries is another feature of Drizzle ORM that makes it easy to use for anyone who knows SQL. You can write your table definitions and queries using a function-based syntax that resembles SQL statements, such as:

// Table definition
export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  fullName: text('full_name'),
  phone: varchar('phone', { length: 256 }),
});

// Query
const allUsers = await db.select().from(users);

Drizzle ORM will translate your syntax into valid SQL statements and execute them on the database. You can also use SQL operators, functions, aggregations, and other features with Drizzle ORM syntax. For example:

// Query with where clause
const usersWithPhone = await db
  .select()
  .from(users)
  .where(users.phone.isNotNull());

// Query with join and aggregation
const userPostsCount = await db
  .select(users.id, users.fullName, count(posts.id).as('postsCount'))
  .from(users)
  .innerJoin(posts, users.id.eq(posts.userId))
  .groupBy(users.id, users.fullName);

Smart automated migrations generation

This is a feature of Drizzle ORM that allows you to use your schema definitions as a source of truth for your database schema. You can use the drizzle-kit CLI companion to generate SQL statements for schema creation and alterations, or apply changes directly to the database.

Run the CLI migration command with the --schema option, such as:

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

This will generate a migration SQL file in your specified folder, such as:

CREATE TABLE "users" (
  "id" serial PRIMARY KEY,
  "full_name" text,
  "phone" varchar(256)
);

You can either run the generated SQL file manually or use Drizzle ORM to apply the migrations, such as:

import { drizzle } from 'drizzle-orm/postgres-js';
import { migrate } from 'drizzle-orm/postgres-js/migrator';
import postgres from 'postgres';

const client = postgres(connectionString);
const db = drizzle(client);

await migrate(db, { migrationsFolder: './drizzle' });

Zod schema generation

Zod (23.9k ⭐) is a TypeScript-first schema declaration and validation library that allows you to create and parse schemas for your data. You can use Zod with Drizzle ORM to generate Zod schemas from Drizzle ORM schemas, using the drizzle-zod plugin.

import { pgEnum, pgTable, serial, text, timestamp } from 'drizzle-orm/pg-core';
import { createInsertSchema, createSelectSchema } from 'drizzle-zod';
import { z } from 'zod';

const users = pgTable('users', {
  id: serial('id').primaryKey(),
  name: text('name').notNull(),
  email: text('email').notNull(),
  role: text('role', { enum: ['admin', 'user'] }).notNull(),
  createdAt: timestamp('created_at').notNull().defaultNow(),
});

// Schema for inserting a user - can be used to validate API requests
const insertUserSchema = createInsertSchema(users);

// Schema for selecting a user - can be used to validate API responses
const selectUserSchema = createSelectSchema(users);

// Overriding the fields
const insertUserSchema = createInsertSchema(users, {
  role: z.string(),
});

// Refining the fields - useful if you want to change the fields before they become nullable/optional in the final schema
const insertUserSchema = createInsertSchema(users, {
  id: (schema) => schema.id.positive(),
  email: (schema) => schema.email.email(),
  role: z.string(),
});

// Usage

const user = insertUserSchema.parse({
  name: 'John Doe',
  email: 'johndoe@test.com',
  role: 'admin',
});

// Zod schema type is also inferred from the table schema, so you have full type safety
const requestSchema = insertUserSchema.pick({ name: true, email: true });

Best in class fully typed joins

Drizzle ORM allows you to write complex queries with multiple joins and get type-safe results. You can use Drizzle ORM syntax to join tables using different types of joins, such as inner join, left join, right join, and full join. You can also specify the join conditions using SQL operators, such as equal, not equal, greater than, less than, etc.

For example, to join two tables named users and posts, you can write:

import { drizzle } from 'drizzle-orm/postgres-js';
import postgres from 'postgres';
import { users, posts } from './schema';

const client = postgres(connectionString);
const db = drizzle(client);

const userPosts = await db
  .select()
  .from(users)
  .innerJoin(posts, users.id.eq(posts.userId));

Drizzle ORM will translate your syntax into a valid SQL statement and execute it on the database. You will get a type-safe result that contains the fields from both tables. You can also use other methods to modify your query, such as where, orderBy, limit, offset, etc.

Auto-inferring of TypeScript types

Drizzle ORM allows you to get different types for your data depending on the context of your query. You can use Drizzle ORM syntax to select or insert data into your tables, and Drizzle ORM will infer the types of your data based on the table schema and the query options.

For example, to select data from a table named users, you can write:

import { drizzle } from 'drizzle-orm/postgres-js';
import postgres from 'postgres';
import { users } from './schema';

const client = postgres(connectionString);
const db = drizzle(client);

const allUsers = await db.select().from(users);

Drizzle ORM will infer the type of allUsers as an array of objects that contain all the fields from the users table. You can also use other methods to modify your query, such as where, orderBy, limit, offset, etc.

To insert data into a table named users, you can write:

import { drizzle } from 'drizzle-orm/postgres-js';
import postgres from 'postgres';
import { users } from './schema';

const client = postgres(connectionString);
const db = drizzle(client);

const newUser = await db.insert(users).values({
  name: 'Jane Doe',
  email: 'janedoe@test.com',
  role: 'user',
});

Drizzle ORM will infer the type of newUser as an object that contains only the fields that are inserted into the users table. You can also use other methods to modify your query, such as returning, onConflict, etc.