How Auto-Increment IDs Are Handled in Seed Data

Auto-Increment Column Handling

Auto-increment columns are managed by the database engine and should typically be omitted from INSERT statements. The seed generator detects and excludes them automatically.

Detection Patterns

The generator recognizes auto-increment columns through several SQL patterns:

Pattern	Dialect	Example
SERIAL	PostgreSQL	id SERIAL PRIMARY KEY
BIGSERIAL	PostgreSQL	id BIGSERIAL PRIMARY KEY
SMALLSERIAL	PostgreSQL	id SMALLSERIAL PRIMARY KEY
AUTO_INCREMENT	MySQL	id INT AUTO_INCREMENT PRIMARY KEY
AUTOINCREMENT	SQLite	id INTEGER PRIMARY KEY AUTOINCREMENT
IDENTITY	SQL Server	id INT IDENTITY(1,1)

SQL INSERT Behavior

When an auto-increment column is detected, it is excluded from the INSERT column list:

-- id is SERIAL, so it is omitted:
INSERT INTO "users" ("username", "email", "is_active") VALUES ('james42', 'james.smith@gmail.com', TRUE);

The database assigns the ID value automatically using its internal sequence.

JSON and CSV Behavior

In JSON and CSV output formats, auto-increment columns are included with sequential values (1, 2, 3, ...). This is because JSON fixtures and CSV imports often need explicit IDs:

{ "id": 1, "username": "james42", "email": "james.smith@gmail.com" }

When You Might Want Explicit IDs

In some testing scenarios, you need explicit IDs in your SQL INSERT statements to create predictable foreign key references. In those cases, change the column from SERIAL to INTEGER PRIMARY KEY and the generator will include it with sequential values.

PostgreSQL Sequence Considerations

After inserting rows with explicit IDs in PostgreSQL, remember to reset the sequence:

SELECT setval('users_id_seq', (SELECT MAX(id) FROM users));

This prevents duplicate key errors when the application later inserts rows using the sequence.