Does not work with identity columns in Postgres

[kawpii]

Bug report

Describe the bug

If you have identity column in a table you will get error like

error: relation "app_public.app_public.demo_id_seq" does not exist

To Reproduce

Create a table like below

CREATE TABLE IF NOT EXISTS app_public.demo
(
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
   name TEXT NOT NULL
 );

Follow instructions given here, there with npx tsx seed.ts you will get an error.

Expected behavior

Support identity columns in Postgres even though there is not visible sequence created by Postgres with identity columns unlike serial columns. (identity sequences are manged internally by Postgres)

System information

• OS: Linux fedora 6.10.7-200.fc40.x86_64
• Version of Node.js: v20.11.1

Additional context

https://www.postgresql.org/docs/17/ddl-identity-columns.html

[star26bsd]

We're experiencing the same issue with @snaplet/seed v0.98.0. Here's our analysis and a working solution:

Our Setup

• PostgreSQL table class_schedule with uid bigint GENERATED ALWAYS AS IDENTITY
• Standard foreign key relationship to class table
• The class_schedule table is the only one in our schema using GENERATED ALWAYS AS IDENTITY so far (all others use sequences)

Root Cause Analysis

The issue occurs in @snaplet/seed/dist/src/core/plan/constraints.js:155 during constraint validation. Snaplet:

1. Adds uid: null to the model data for the identity column
2. Attempts to validate the unique constraint
3. Interprets null as NaN during validation
4. Throws the "Unique constraint violated" error

This happens even when we don't specify the identity field at all in our seed data - Snaplet internally adds it with a null value.

Our Workaround

Since Snaplet can't handle GENERATED ALWAYS AS IDENTITY during the generation phase, we bypass Snaplet for this table and generate raw SQL instead:

  // Instead of using seed.class_schedule(), we generate SQL directly:
  const classScheduleInserts: string[] = []

  scheduleMapping.forEach((mapping, index) => {
    const startTime = generateStartTime(index * 7)
    const duration = generateDuration(index * 11)

    classScheduleInserts.push(
      `INSERT INTO "dojomanager"."class_schedule" ("class_uid", "day_of_week", "start_time", 
  "duration_minutes") ` +
      `VALUES (${mapping.class_uid}, '${mapping.day_of_week}', '${startTime}', ${duration});`
    )
  })

  // Output the SQL statements to console so they get included in the generated seed file
  console.log('\n-- Manual INSERT statements for class_schedule (GENERATED ALWAYS AS IDENTITY 
  workaround)')
  classScheduleInserts.forEach(sql => console.log(sql))

This workaround:

• Completely bypasses Snaplet's model generation for the affected table
• Generates valid SQL that properly omits the identity column
• Gets included in the final seed.sql output
• Works reliably with PostgreSQL's identity column behavior

Additional Context

• GENERATED ALWAYS AS IDENTITY is the SQL standard and PostgreSQL's recommended approach over SERIAL
• The database correctly auto-generates the identity values when the column is omitted from INSERT statements
• This is a blocking issue for tables with this constraint type

Hope this helps! Happy to provide more details or test potential fixes.

[star26bsd]

After investigating the source code, I found the issue in line 274 of packages/seed/src/core/plan/plan.ts. The handleScalarField function contains:

  // the field is always generated by the database
  if (!field.isId && field.isGenerated) {
    return;
  }

This condition skips non-ID generated fields but continues processing ID fields that are generated (like GENERATED ALWAYS AS IDENTITY primary keys). This causes Snaplet to add uid: null to the model data, triggering the constraint validation error.

Why the !field.isId condition exists

The reasoning appears to be:

1. SERIAL vs IDENTITY columns: Traditional SERIAL columns (and GENERATED BY DEFAULT AS IDENTITY) allow explicit value insertion, which Snaplet uses to maintain referential integrity across related tables. The tool can provide IDs and update sequences afterward.
2. Cross-table relationships: Snaplet needs to know ID values when seeding related tables with foreign keys. For SERIAL-style columns, this works fine.
3. The problem: GENERATED ALWAYS AS IDENTITY is stricter - it NEVER accepts provided values (unless using OVERRIDING SYSTEM VALUE). The current logic doesn't distinguish between these two types.

Potential Fix

A simple fix would be removing the !field.isId check, but this could break existing functionality for SERIAL columns. A better solution would be distinguishing between:

• GENERATED ALWAYS AS IDENTITY → must skip entirely
• GENERATED BY DEFAULT AS IDENTITY / SERIAL → can provide values

This would require enhancing Snaplet's introspection to detect the generation type, e.g.

  // Skip fields that are ALWAYS generated (can't accept values)
  if (field.isGenerated && field.generationType === 'ALWAYS') {
    return;
  }

  // For SERIAL-style IDs (GENERATED BY DEFAULT), continue processing
  if (!field.isId && field.isGenerated) {
    return;
  }