Get started

Overview PostgreSQL MySQL SQLite

Manage schema

Overview

Column types Indexes & Constraints Sequences Migrations Views Schemas Extensions

Access your data

Query Select Insert Update Delete Filters Joins Magic sql`` operator

Performance

Queries Serverless

Advanced

Set Operations Generated Columns Transactions Batch Dynamic query building Read Replicas Custom types Goodies

Extensions

Prisma ESLint Plugin drizzle-zod drizzle-typebox drizzle-valibot drizzle-graphql

Become a Sponsor

Twitter

Discord

SQL schema declaration

You can declare your SQL schema directly in TypeScript either in a single schema.ts file, or you can spread them around — whichever you prefer, all the freedom!

1 File

Separate Files

Separate Folders

Everything in 1 file:

📦 <project root>
└ 📂 src
└ 📂 db
   └ 📜 schema.ts

Scattered across multiple files:

📦 <project root>
└ 📂 src
└ 📂 db
   └ 📂 schema
      ├ 📜 users.ts
      ├ 📜 countries.ts
      ├ 📜 cities.ts
      ├ 📜 products.ts
      ├ 📜 clients.ts
      ├ 📜 enums.ts
      └ 📜 etc.ts

In separate/domain-specific folders:

📦 <project root>
└ 📂 src
 ├ 📂 get-user
 │  ├ 📜 user.ts
 │  └ 📜 handler.ts
 ├ 📂 get-city
 │  ├ 📜 city.ts
 │  └ 📜 handler.ts    
 ├  ...

You can declare tables, indexes and constraints, foreign keys and enums.

⚠️

Pay attention to the mandatory export keyword, if you’re using drizzle-kit SQL migrations generator.

PostgreSQL

MySQL

SQLite

import { integer, pgEnum, pgTable, serial, uniqueIndex, varchar } from 'drizzle-orm/pg-core';

// declaring enum in database
export const popularityEnum = pgEnum('popularity', ['unknown', 'known', 'popular']);

export const countries = pgTable('countries', {
  id: serial('id').primaryKey(),
  name: varchar('name', { length: 256 }),
}, (countries) => {
  return {
    nameIndex: uniqueIndex('name_idx').on(countries.name),
  }
});

export const cities = pgTable('cities', {
  id: serial('id').primaryKey(),
  name: varchar('name', { length: 256 }),
  countryId: integer('country_id').references(() => countries.id),
  popularity: popularityEnum('popularity'),
});

Database and table explicit entity types:

import { pgTable, serial, text, varchar } from 'drizzle-orm/pg-core';
import { drizzle } from 'drizzle-orm/node-postgres';

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

export type User = typeof users.$inferSelect; // return type when queried
export type NewUser = typeof users.$inferInsert; // insert type
...

const db = drizzle(...);

const result: User[] = await db.select().from(users);

export async function insertUser(user: NewUser): Promise<User[]> {
  return db.insert(users).values(user).returning();
}

Check out all supported PostgreSQL column types here.

import { int, mysqlEnum, mysqlTable, uniqueIndex, varchar, serial } from 'drizzle-orm/mysql-core';

// declaring enum in database
export const countries = mysqlTable('countries', {
  id: serial("id").primaryKey(),
  name: varchar('name', { length: 256 }),
}, (countries) => ({
  nameIndex: uniqueIndex('name_idx').on(countries.name),
}));

export const cities = mysqlTable('cities', {
  id: serial("id").primaryKey(),
  name: varchar('name', { length: 256 }),
  countryId: int('country_id').references(() => countries.id),
  popularity: mysqlEnum('popularity', ['unknown', 'known', 'popular']),
});

Database and table explicit entity types:

import { MySqlRawQueryResult, mysqlTable, serial, text, varchar } from 'drizzle-orm/mysql-core';
import mysql from 'mysql2/promise';
import { drizzle } from 'drizzle-orm/mysql2';

export const users = mysqlTable('users', {
  id: serial("id").primaryKey(),
  fullName: text('full_name'),
  phone: varchar('phone', { length: 256 }),
});

export type User = typeof users.$inferSelect; // return type when queried
export type NewUser = typeof users.$inferInsert; // insert type
...

// init mysql2 Pool or Client
const poolConnection = mysql.createPool({
    host:'localhost', 
    user: 'root',
    database: 'test'
});

export const db = drizzle(poolConnection);

const result: User[] = await db.select().from(users);

async function insertUser(user: NewUser): Promise<MySqlRawQueryResult> {
  return db.insert(users).values(user);
}

Check out all supported MySQL column types here.

import { sqliteTable, text, integer, uniqueIndex } from 'drizzle-orm/sqlite-core';

export const countries = sqliteTable('countries', {
    id: integer('id').primaryKey(),
    name: text('name'),
  }, (countries) => ({
    nameIdx: uniqueIndex('nameIdx').on(countries.name),
  })
);

export const cities = sqliteTable('cities', {
  id: integer('id').primaryKey(),
  name: text('name'),
  countryId: integer('country_id').references(() => countries.id),
})

Database and table explicit entity types

import { text, integer, sqliteTable } from 'drizzle-orm/sqlite-core';

export const users = sqliteTable('users', {
  id: integer('id').primaryKey(),
  fullName: text('full_name'),
  phone: text('phone'),
})

export type User = typeof users.$inferSelect // return type when queried
export type InsertUser = typeof users.$inferInsert // insert type
...
import { drizzle } from 'drizzle-orm/better-sqlite3';
import Database from 'better-sqlite3';

const sqlite = new Database('sqlite.db');
const db = drizzle(sqlite);

const result: User[] = db.select().from(users).all();

const insertUser = (user: InsertUser) => {
  return db.insert(users).values(user).run()
}

Check out all supported SQLite column types here.

PostgreSQL column types

Become a Gold Sponsor