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.