Sequences & Identity

Generate unique sequential values with database sequences, identity columns, and custom patterns.

Overview

Sequences provide a way to generate unique sequential numbers independent of tables. They're useful for order numbers, invoice IDs, and other business identifiers.

Feature	PostgreSQL	MySQL	SQLite	MSSQL	MongoDB
Sequences	✅	❌	❌	✅	❌
Identity Columns	✅	✅ AUTO_INCREMENT	✅ AUTOINCREMENT	✅ IDENTITY	✅ ObjectId
Custom Start/Increment	✅	✅	❌	✅	✅*
nextval/currval	✅	❌	❌	✅	✅*

* MongoDB uses counter collections with findAndModify

Creating Sequences

Build sequences with the Sequence::builder() API.

Sequence Operations

Get, set, and use sequence values in queries.

Auto-Increment Helpers

Cross-database utilities for auto-incrementing columns.

MongoDB Counter Pattern

MongoDB doesn't have built-in sequences, but you can implement them with atomic findAndModify.

Common Patterns

Pre-built patterns for common sequence use cases.

Prefixed Sequence

Order numbers like ORD-2024-00001

Yearly Reset

Invoice numbers that reset each year

Round Robin

Distribute work across workers evenly

Countdown

Limited inventory with atomic decrement

Identity Columns

Configure auto-incrementing primary keys in your schema.

Sequence Migrations

Add sequences via migrations.

Best Practices

Use CACHE for Performance

Pre-allocate sequence values to reduce database round-trips. Higher cache values improve performance but can leave gaps on restart.

Prefer IDENTITY over SERIAL

In PostgreSQL, use GENERATED AS IDENTITY (SQL standard) instead of SERIAL (PostgreSQL-specific) for new tables.

Expect Gaps

Sequences can have gaps due to rollbacks, cache invalidation, and concurrent access. Never rely on sequential values being contiguous.

Separate Sequences from PKs

Use auto-increment for internal IDs, but separate sequences for business numbers like order numbers that may need resets or prefixes.