Primary Keys and Serial Columns in Drizzle

Primary Keys in Drizzle

Primary keys are the most fundamental constraint in any database table. Drizzle provides several ways to define them depending on the column type and whether the key is simple or composite.

Simple Primary Key with SERIAL

PostgreSQL:

CREATE TABLE users (
  id SERIAL PRIMARY KEY
);

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

export const users = pgTable("users", {
  id: serial("id").primaryKey(),
});

The serial() builder in Drizzle's pg-core implicitly makes the column NOT NULL and auto-incrementing, just like PostgreSQL's SERIAL type.

MySQL AUTO_INCREMENT

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY
);

import { mysqlTable, serial } from "drizzle-orm/mysql-core";

export const users = mysqlTable("users", {
  id: serial("id").primaryKey(),
});

MySQL's serial() maps to BIGINT UNSIGNED NOT NULL AUTO_INCREMENT.

UUID Primary Key

CREATE TABLE documents (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY
);

import { pgTable, uuid } from "drizzle-orm/pg-core";
import { sql } from "drizzle-orm";

export const documents = pgTable("documents", {
  id: uuid("id").default(sql`gen_random_uuid()`).primaryKey(),
});

BIGSERIAL for Large Tables

CREATE TABLE events (
  id BIGSERIAL PRIMARY KEY
);

import { bigserial, pgTable } from "drizzle-orm/pg-core";

export const events = pgTable("events", {
  id: bigserial("id", { mode: "number" }).primaryKey(),
});

Composite Primary Key

When a table has a multi-column primary key, individual columns do not get .primaryKey(). Instead, a primaryKey() constraint is added in the table's third argument:

import { integer, pgTable, primaryKey } from "drizzle-orm/pg-core";

export const postTags = pgTable("post_tags", {
  postId: integer("post_id").notNull(),
  tagId: integer("tag_id").notNull(),
}, (table) => [
  primaryKey({ columns: [table.postId, table.tagId] }),
]);

SQLite Primary Key

In SQLite, INTEGER PRIMARY KEY creates an alias for the ROWID, which auto-increments by default. The AUTOINCREMENT keyword only prevents rowid reuse and is not needed for basic auto-incrementing behavior.