---
import DocsLayout from '../../layouts/DocsLayout.astro';
import CodeBlock from '../../components/CodeBlock.astro';
const basicSequence = `use prax::sequence::{Sequence, SequenceBuilder};
// Create a sequence
let order_seq = Sequence::builder()
.name("order_number_seq")
.start(1000)
.increment(1)
.min_value(1)
.max_value(9_999_999)
.cycle(false)
.cache(20) // Pre-allocate 20 values for performance
.build();
// Generate SQL
let sql = order_seq.to_postgres_sql();
// CREATE SEQUENCE order_number_seq
// START WITH 1000
// INCREMENT BY 1
// MINVALUE 1
// MAXVALUE 9999999
// NO CYCLE
// CACHE 20;`;
const sequenceOps = `use prax::sequence::ops;
// Get next value (PostgreSQL/MSSQL)
let next = ops::nextval("order_number_seq").exec(&client).await?;
// PostgreSQL: SELECT nextval('order_number_seq')
// MSSQL: SELECT NEXT VALUE FOR order_number_seq
// Get current value (within session)
let current = ops::currval("order_number_seq").exec(&client).await?;
// PostgreSQL: SELECT currval('order_number_seq')
// MSSQL: SELECT current_value FROM sys.sequences WHERE name = 'order_number_seq'
// Set sequence value
ops::setval("order_number_seq", 5000, true).exec(&client).await?;
// PostgreSQL: SELECT setval('order_number_seq', 5000, true)
// MSSQL: ALTER SEQUENCE order_number_seq RESTART WITH 5000
// Use in INSERT with DEFAULT
let sql = format!(
"INSERT INTO orders (order_number, total) VALUES ({}, $1)",
ops::default_nextval("order_number_seq")
);`;
const autoIncrement = `use prax::sequence::auto_increment;
// Get the column definition for auto-increment
let column = auto_increment::column_definition(DatabaseType::PostgreSQL);
// PostgreSQL: SERIAL / BIGSERIAL or GENERATED ALWAYS AS IDENTITY
// MySQL: AUTO_INCREMENT
// SQLite: INTEGER PRIMARY KEY AUTOINCREMENT
// MSSQL: IDENTITY(1,1)
// Get last inserted ID
let id = auto_increment::last_insert_id(&client).await?;
// PostgreSQL: Uses RETURNING
// MySQL: SELECT LAST_INSERT_ID()
// SQLite: SELECT last_insert_rowid()
// MSSQL: SELECT SCOPE_IDENTITY()
// Set auto-increment starting value
auto_increment::set_start_value(&client, "users", 1000).await?;
// PostgreSQL: ALTER SEQUENCE users_id_seq RESTART WITH 1000
// MySQL: ALTER TABLE users AUTO_INCREMENT = 1000
// MSSQL: DBCC CHECKIDENT ('users', RESEED, 999)`;
const mongoCounter = `use prax::sequence::mongodb::{CounterBuilder, counter};
// MongoDB counter pattern using findAndModify
let counter = CounterBuilder::new("order_numbers")
.initial_value(1000)
.increment(1)
.build();
// Get next value atomically
let next = counter.next(&client).await?;
// db.counters.findAndModify({
// query: { _id: "order_numbers" },
// update: { $inc: { seq: 1 } },
// upsert: true,
// new: true
// })
// Batch allocation (get N values at once)
let batch = counter.next_batch(&client, 100).await?;
// Returns range: start..end (e.g., 1000..1100)
// Use in your model
let order = Order {
order_number: counter::next("order_numbers", &client).await?,
items: vec![...],
total: 99.99,
};`;
const sequencePatterns = `use prax::sequence::patterns;
// Order number with prefix: ORD-2024-00001
let order_number = patterns::prefixed_sequence("orders", "ORD")
.with_year()
.zero_pad(5)
.build();
let num = order_number.next(&client).await?;
// Returns: "ORD-2024-00001", "ORD-2024-00002", etc.
// Invoice number resets yearly
let invoice = patterns::yearly_reset_sequence("invoices")
.format("INV-{year}-{seq:06}")
.build();
// Round-robin assignment (for load balancing)
let worker = patterns::round_robin("workers", 4);
let assigned = worker.next(&client).await?; // 0, 1, 2, 3, 0, 1, ...
// Countdown sequence (for limited inventory)
let tickets = patterns::countdown("event_tickets", 1000);
let ticket_num = tickets.next(&client).await?; // 1000, 999, 998...
if ticket_num == 0 {
return Err(Error::SoldOut);
}`;
const identityColumns = `// PostgreSQL GENERATED AS IDENTITY (preferred over SERIAL)
model User {
id Int @id @default(auto()) @db.Identity
// GENERATED ALWAYS AS IDENTITY
}
// GENERATED BY DEFAULT allows manual inserts
model ImportedUser {
id Int @id @default(auto()) @db.Identity("BY DEFAULT")
// GENERATED BY DEFAULT AS IDENTITY
}
// MSSQL IDENTITY
model Order {
id Int @id @default(auto())
// IDENTITY(1,1)
}
// Custom identity options
model CustomId {
id Int @id @default(auto()) @db.Identity(start: 1000, increment: 10)
// IDENTITY(1000, 10)
}`;
const sequenceMigration = `use prax_migrate::{Migration, MigrationStep};
// Migration for sequence changes
let migration = Migration::new("add_order_sequence")
.up(r#"
CREATE SEQUENCE order_number_seq
START WITH 1000
INCREMENT BY 1
NO CYCLE
CACHE 20;
ALTER TABLE orders
ALTER COLUMN order_number SET DEFAULT nextval('order_number_seq');
"#)
.down(r#"
ALTER TABLE orders ALTER COLUMN order_number DROP DEFAULT;
DROP SEQUENCE order_number_seq;
"#);
// For MSSQL
let mssql_migration = Migration::new("add_order_sequence")
.up(r#"
CREATE SEQUENCE dbo.order_number_seq
START WITH 1000
INCREMENT BY 1
NO CYCLE
CACHE 20;
ALTER TABLE orders
ADD CONSTRAINT DF_orders_number
DEFAULT NEXT VALUE FOR dbo.order_number_seq FOR order_number;
"#);`;
---
<DocsLayout title="Sequences & Identity - Prax ORM">
<article class="max-w-4xl mx-auto px-6 py-12">
<header class="mb-12">
<h1 class="text-4xl font-bold mb-4">Sequences & Identity</h1>
<p class="text-xl text-muted">
Generate unique sequential values with database sequences, identity columns, and custom patterns.
</p>
</header>
<div class="space-y-12">
<!-- Introduction -->
<section>
<h2 class="text-2xl font-semibold mb-4">Overview</h2>
<p class="text-muted mb-4">
Sequences provide a way to generate unique sequential numbers independent of tables.
They're useful for order numbers, invoice IDs, and other business identifiers.
</p>
<div class="overflow-x-auto">
<table class="w-full text-sm">
<thead>
<tr class="border-b border-border">
<th class="text-left py-3 px-4 font-semibold">Feature</th>
<th class="text-left py-3 px-4 font-semibold">PostgreSQL</th>
<th class="text-left py-3 px-4 font-semibold">MySQL</th>
<th class="text-left py-3 px-4 font-semibold">SQLite</th>
<th class="text-left py-3 px-4 font-semibold">MSSQL</th>
<th class="text-left py-3 px-4 font-semibold">MongoDB</th>
</tr>
</thead>
<tbody class="text-muted">
<tr class="border-b border-border">
<td class="py-3 px-4">Sequences</td>
<td class="py-3 px-4"><span class="text-success-400">✅</span></td>
<td class="py-3 px-4"><span class="text-muted">❌</span></td>
<td class="py-3 px-4"><span class="text-muted">❌</span></td>
<td class="py-3 px-4"><span class="text-success-400">✅</span></td>
<td class="py-3 px-4"><span class="text-muted">❌</span></td>
</tr>
<tr class="border-b border-border">
<td class="py-3 px-4">Identity Columns</td>
<td class="py-3 px-4"><span class="text-success-400">✅</span></td>
<td class="py-3 px-4"><span class="text-success-400">✅</span> AUTO_INCREMENT</td>
<td class="py-3 px-4"><span class="text-success-400">✅</span> AUTOINCREMENT</td>
<td class="py-3 px-4"><span class="text-success-400">✅</span> IDENTITY</td>
<td class="py-3 px-4"><span class="text-success-400">✅</span> ObjectId</td>
</tr>
<tr class="border-b border-border">
<td class="py-3 px-4">Custom Start/Increment</td>
<td class="py-3 px-4"><span class="text-success-400">✅</span></td>
<td class="py-3 px-4"><span class="text-success-400">✅</span></td>
<td class="py-3 px-4"><span class="text-muted">❌</span></td>
<td class="py-3 px-4"><span class="text-success-400">✅</span></td>
<td class="py-3 px-4"><span class="text-success-400">✅</span>*</td>
</tr>
<tr class="border-b border-border">
<td class="py-3 px-4">nextval/currval</td>
<td class="py-3 px-4"><span class="text-success-400">✅</span></td>
<td class="py-3 px-4"><span class="text-muted">❌</span></td>
<td class="py-3 px-4"><span class="text-muted">❌</span></td>
<td class="py-3 px-4"><span class="text-success-400">✅</span></td>
<td class="py-3 px-4"><span class="text-success-400">✅</span>*</td>
</tr>
</tbody>
</table>
</div>
<p class="text-muted text-sm mt-2">* MongoDB uses counter collections with findAndModify</p>
</section>
<!-- Basic Sequence -->
<section>
<h2 class="text-2xl font-semibold mb-4">Creating Sequences</h2>
<p class="text-muted mb-4">
Build sequences with the <code class="px-2 py-1 bg-surface-elevated rounded">Sequence::builder()</code> API.
</p>
<CodeBlock code={basicSequence} lang="rust" filename="src/sequences.rs" />
</section>
<!-- Sequence Operations -->
<section>
<h2 class="text-2xl font-semibold mb-4">Sequence Operations</h2>
<p class="text-muted mb-4">
Get, set, and use sequence values in queries.
</p>
<CodeBlock code={sequenceOps} lang="rust" filename="src/main.rs" />
</section>
<!-- Auto Increment -->
<section>
<h2 class="text-2xl font-semibold mb-4">Auto-Increment Helpers</h2>
<p class="text-muted mb-4">
Cross-database utilities for auto-incrementing columns.
</p>
<CodeBlock code={autoIncrement} lang="rust" filename="src/main.rs" />
</section>
<!-- MongoDB Counter -->
<section>
<h2 class="text-2xl font-semibold mb-4">MongoDB Counter Pattern</h2>
<p class="text-muted mb-4">
MongoDB doesn't have built-in sequences, but you can implement them with atomic findAndModify.
</p>
<CodeBlock code={mongoCounter} lang="rust" filename="src/sequences.rs" />
</section>
<!-- Patterns -->
<section>
<h2 class="text-2xl font-semibold mb-4">Common Patterns</h2>
<p class="text-muted mb-4">
Pre-built patterns for common sequence use cases.
</p>
<CodeBlock code={sequencePatterns} lang="rust" filename="src/sequences.rs" />
<div class="mt-4 grid md:grid-cols-2 gap-4">
<div class="p-4 rounded-xl bg-surface border border-border">
<h4 class="font-semibold mb-2 text-primary-400">Prefixed Sequence</h4>
<p class="text-muted text-sm">Order numbers like ORD-2024-00001</p>
</div>
<div class="p-4 rounded-xl bg-surface border border-border">
<h4 class="font-semibold mb-2 text-primary-400">Yearly Reset</h4>
<p class="text-muted text-sm">Invoice numbers that reset each year</p>
</div>
<div class="p-4 rounded-xl bg-surface border border-border">
<h4 class="font-semibold mb-2 text-primary-400">Round Robin</h4>
<p class="text-muted text-sm">Distribute work across workers evenly</p>
</div>
<div class="p-4 rounded-xl bg-surface border border-border">
<h4 class="font-semibold mb-2 text-primary-400">Countdown</h4>
<p class="text-muted text-sm">Limited inventory with atomic decrement</p>
</div>
</div>
</section>
<!-- Identity Columns -->
<section>
<h2 class="text-2xl font-semibold mb-4">Identity Columns</h2>
<p class="text-muted mb-4">
Configure auto-incrementing primary keys in your schema.
</p>
<CodeBlock code={identityColumns} lang="prax" filename="prax/schema.prax" />
</section>
<!-- Migrations -->
<section>
<h2 class="text-2xl font-semibold mb-4">Sequence Migrations</h2>
<p class="text-muted mb-4">
Add sequences via migrations.
</p>
<CodeBlock code={sequenceMigration} lang="rust" filename="migrations/sequences.rs" />
</section>
<!-- Best Practices -->
<section>
<h2 class="text-2xl font-semibold mb-4">Best Practices</h2>
<div class="grid gap-4">
<div class="p-4 rounded-xl bg-surface border border-border">
<h4 class="font-semibold mb-2 text-success-400">Use CACHE for Performance</h4>
<p class="text-muted text-sm">
Pre-allocate sequence values to reduce database round-trips.
Higher cache values improve performance but can leave gaps on restart.
</p>
</div>
<div class="p-4 rounded-xl bg-surface border border-border">
<h4 class="font-semibold mb-2 text-success-400">Prefer IDENTITY over SERIAL</h4>
<p class="text-muted text-sm">
In PostgreSQL, use <code>GENERATED AS IDENTITY</code> (SQL standard) instead of
<code>SERIAL</code> (PostgreSQL-specific) for new tables.
</p>
</div>
<div class="p-4 rounded-xl bg-surface border border-border">
<h4 class="font-semibold mb-2 text-warning-400">Expect Gaps</h4>
<p class="text-muted text-sm">
Sequences can have gaps due to rollbacks, cache invalidation, and concurrent access.
Never rely on sequential values being contiguous.
</p>
</div>
<div class="p-4 rounded-xl bg-surface border border-border">
<h4 class="font-semibold mb-2 text-info-400">Separate Sequences from PKs</h4>
<p class="text-muted text-sm">
Use auto-increment for internal IDs, but separate sequences for business numbers
like order numbers that may need resets or prefixes.
</p>
</div>
</div>
</section>
</div>
</article>
</DocsLayout>