Tables

Define tables in Drizzle ORM with type-safe schemas for PostgreSQL, MySQL, and SQLite.

Tables are the foundation of your database schema in Drizzle ORM. Each table is defined with columns and optional constraints like indexes, foreign keys, and checks.

Basic table definition

PostgreSQL

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

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  name: text('name').notNull(),
  email: varchar('email', { length: 255 }).notNull().unique(),
});

MySQL

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

export const users = mysqlTable('users', {
  id: serial('id').primaryKey(),
  name: text('name').notNull(),
  email: varchar('email', { length: 255 }).notNull().unique(),
});

SQLite

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

export const users = sqliteTable('users', {
  id: integer('id').primaryKey({ autoIncrement: true }),
  name: text('name').notNull(),
  email: text('email').notNull().unique(),
});

Table with constraints

Add indexes, foreign keys, and other constraints using the third parameter:

PostgreSQL

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

export const posts = pgTable('posts', {
  id: serial('id').primaryKey(),
  title: varchar('title', { length: 255 }).notNull(),
  content: text('content'),
  authorId: integer('author_id').notNull(),
  createdAt: timestamp('created_at').defaultNow(),
}, (table) => [
  index('title_idx').on(table.title),
  index('author_created_idx').on(table.authorId, table.createdAt),
]);

MySQL

import { mysqlTable, serial, varchar, text, int, timestamp, index } from 'drizzle-orm/mysql-core';

export const posts = mysqlTable('posts', {
  id: serial('id').primaryKey(),
  title: varchar('title', { length: 255 }).notNull(),
  content: text('content'),
  authorId: int('author_id').notNull(),
  createdAt: timestamp('created_at').defaultNow(),
}, (table) => [
  index('title_idx').on(table.title),
  index('author_created_idx').on(table.authorId, table.createdAt),
]);

SQLite

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

export const posts = sqliteTable('posts', {
  id: integer('id').primaryKey({ autoIncrement: true }),
  title: text('title').notNull(),
  content: text('content'),
  authorId: integer('author_id').notNull(),
  createdAt: integer('created_at', { mode: 'timestamp' }),
}, (table) => [
  index('title_idx').on(table.title),
]);

The third parameter must return an array of constraint builders, not an object. The object syntax is deprecated.

Column types function

Use the callback syntax to access column builders directly:

export const users = pgTable('users', (t) => ({
  id: t.serial('id').primaryKey(),
  name: t.text('name').notNull(),
  email: t.varchar('email', { length: 255 }).notNull(),
}));

This provides autocomplete for all available column types without individual imports.

Custom table names

Create a table creator for custom prefixes:

PostgreSQL

import { pgTableCreator } from 'drizzle-orm/pg-core';

const pgTable = pgTableCreator((name) => `myapp_${name}`);

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
});
// Creates table "myapp_users"

Schema namespaces

PostgreSQL and MySQL support schema namespaces:

PostgreSQL

import { pgSchema } from 'drizzle-orm/pg-core';

const authSchema = pgSchema('auth');

export const users = authSchema.table('users', {
  id: serial('id').primaryKey(),
  email: varchar('email', { length: 255 }).notNull(),
});
// Creates table "auth"."users"

Row-level security (PostgreSQL)

Enable RLS on PostgreSQL tables:

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

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  name: text('name').notNull(),
}).enableRLS();

Enabling RLS only adds the ENABLE ROW LEVEL SECURITY clause. You still need to define policies separately.

Complete example

import { pgTable, serial, varchar, text, integer, timestamp, index, foreignKey } from 'drizzle-orm/pg-core';

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  email: varchar('email', { length: 255 }).notNull().unique(),
  name: text('name').notNull(),
  createdAt: timestamp('created_at').defaultNow().notNull(),
}, (table) => [
  index('email_idx').on(table.email),
]);

export const posts = pgTable('posts', {
  id: serial('id').primaryKey(),
  title: varchar('title', { length: 255 }).notNull(),
  content: text('content'),
  authorId: integer('author_id').notNull().references(() => users.id),
  published: boolean('published').default(false),
  createdAt: timestamp('created_at').defaultNow().notNull(),
  updatedAt: timestamp('updated_at').defaultNow().notNull(),
}, (table) => [
  index('author_idx').on(table.authorId),
  index('published_created_idx').on(table.published, table.createdAt),
]);

API reference

name · string · required

The table name in the database

columns · object | function · required

Column definitions as an object or callback function that receives column builders

extraConfig · function

Optional callback that receives the table columns and returns an array of constraints (indexes, foreign keys, etc.)