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!
Everything in 1 file:
📦 <project root>
└ 📂 src
└ 📂 db
└ 📜 schema.tsScattered across multiple files:
📦 <project root>
└ 📂 src
└ 📂 db
└ 📂 schema
├ 📜 users.ts
├ 📜 countries.ts
├ 📜 cities.ts
├ 📜 products.ts
├ 📜 clients.ts
├ 📜 enums.ts
└ 📜 etc.tsIn 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.
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.
