Prisma @default(autoincrement()) to SQL SERIAL and AUTO_INCREMENT

Autoincrement Mapping

Prisma's @default(autoincrement()) is the standard way to define auto-incrementing primary keys. Each SQL dialect handles this differently.

Dialect-Specific Output

Prisma	PostgreSQL	MySQL	SQLite
Int @id @default(autoincrement())	SERIAL PRIMARY KEY	INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY	INTEGER PRIMARY KEY AUTOINCREMENT
BigInt @id @default(autoincrement())	BIGSERIAL PRIMARY KEY	BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY	INTEGER PRIMARY KEY AUTOINCREMENT

Example Prisma Schema

model Category {
  id   Int    @id @default(autoincrement())
  name String @unique

  @@map("categories")
}

PostgreSQL Output

CREATE TABLE categories (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL UNIQUE
);

PostgreSQL's SERIAL is a convenience type that creates an INTEGER column backed by a sequence. BIGSERIAL does the same for BIGINT.

MySQL Output

CREATE TABLE categories (
  id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL UNIQUE
);

MySQL uses the AUTO_INCREMENT keyword on the column definition. Only one column per table can be AUTO_INCREMENT, and it must be indexed.

SQLite Output

CREATE TABLE categories (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name VARCHAR(255) NOT NULL UNIQUE
);

SQLite's AUTOINCREMENT ensures that the rowid is always larger than any previously used value, even after deletions. Without it, SQLite may reuse deleted rowids.

Non-Primary-Key Autoincrement

While uncommon, Prisma technically allows @default(autoincrement()) on non-primary-key fields. In PostgreSQL, this still generates SERIAL. In MySQL, the column must be indexed for AUTO_INCREMENT to work, so you may need to add an index manually.