# 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.