drizzle-team / drizzle-team/drizzle-orm

[BUG]: drizzle-kit migrate fails with parsing error on commented-out file from drizzle-kit pull

Open
#4,851 4 comments 9 reactions 0 assignees View on GitHub
bug db/postgres drizzle/kit
Dominant language
TypeScript
Stars
35.8k
Forks
1.6k
Avg merge
2d 7h
Merged PRs (30d)
4

Description

### Report hasn't been filed before.

- [x] I have verified that the bug I'm about to report hasn't been filed before.

### What version of `drizzle-orm` are you using?

^0.44.4

### What version of `drizzle-kit` are you using?

^0.31.4

### Other packages

_No response_

### Describe the Bug

When `drizzle-kit pull` is used to introspect an existing database (postgres in my case), it generates an initial migration file (e.g., `0000_...sql`) with all its contents wrapped in a single block comment (`/* ... */`).

After moving the generated schema files to a new location, updating this schema, and generating a new migration, running `drizzle-kit migrate` fails. The command appears to be attempting to process the initial, fully-commented introspection file instead of skipping it, leading to a `DrizzleQueryError` related to an "unterminated /* comment".

**Steps to Reproduce**

1. Start with a database that has a pre-existing schema.
2. Run `npx drizzle-kit pull` to introspect the database. This generates schema files and an initial migration file (e.g., `0000_...sql`) with the following structure. In my case the migration file was:

```sql
-- Current sql file was generated after introspecting the database
-- If you want to run this migration please uncomment this code before executing migrations
/*
CREATE SCHEMA "neon_auth";
--> statement-breakpoint
CREATE TABLE "neon_auth"."users_sync" (
"raw_json" jsonb NOT NULL,
"id" text PRIMARY KEY GENERATED ALWAYS AS ((raw_json ->> 'id'::text)) STORED NOT NULL,
"name" text GENERATED ALWAYS AS ((raw_json ->> 'display_name'::text)) STORED,
"email" text GENERATED ALWAYS AS ((raw_json ->> 'primary_email'::text)) STORED,
"created_at" timestamp with time zone GENERATED ALWAYS AS (to_timestamp((trunc((((raw_json ->> 'signed_up_at_millis'::text))::bigint)::double precision) / (1000)::double precision))) STORED,
"updated_at" timestamp with time zone,
"deleted_at" timestamp with time zone
);
--> statement-breakpoint
CREATE INDEX "users_sync_deleted_at_idx" ON "neon_auth"."users_sync" USING btree ("deleted_at" timestamptz_ops);
*/
```

3. Modify the `schema.ts` file to add a new table or column. My final `schema.ts` is:
```typescript
import { pgTable, pgSchema, index, jsonb, text, timestamp, bigint, boolean } from "drizzle-orm/pg-core"
import { sql } from "drizzle-orm"

export const neonAuth = pgSchema("neon_auth");


export const usersSyncInNeonAuth = neonAuth.table("users_sync", {
rawJson: jsonb("raw_json").notNull(),
id: text().primaryKey().notNull().generatedAlwaysAs(sql`(raw_json ->> 'id'::text)`),
name: text().generatedAlwaysAs(sql`(raw_json ->> 'display_name'::text)`),
email: text().generatedAlwaysAs(sql`(raw_json ->> 'primary_email'::text)`),
createdAt: timestamp("created_at", { withTimezone: true, mode: 'string' }).generatedAlwaysAs(sql`to_timestamp((trunc((((raw_json ->> 'signed_up_at_millis'::text))::bigint)::double precision) / (1000)::double precision))`),
updatedAt: timestamp("updated_at", { withTimezone: true, mode: 'string' }),
deletedAt: timestamp("deleted_at", { withTimezone: true, mode: 'string' }),
}, (table) => [
index("users_sync_deleted_at_idx").using("btree", table.deletedAt.asc().nullsLast().op("timestamptz_ops")),
]);

export const todos = pgTable('todos', {
id: bigint('id', { mode: 'bigint' }).primaryKey().generatedByDefaultAsIdentity(),
ownerId: text('owner_id')
.notNull()
.references(() => usersSyncInNeonAuth.id),
task: text('task').notNull(),
isComplete: boolean('is_complete').notNull().default(false),
insertedAt: timestamp('inserted_at', { withTimezone: true }).defaultNow().notNull(),
});
```

4. Generate a new migration by running `npx drizzle-kit generate`. This creates a new, valid SQL file (e.g., `0001_...sql`). The contents of this new sql file were:

```sql
CREATE TABLE "todos" (
"id" bigint PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (sequence name "todos_id_seq" INCREMENT BY 1 MINVALUE 1 MAXVALUE 9223372036854775807 START WITH 1 CACHE 1),
"owner_id" text NOT NULL,
"task" text NOT NULL,
"is_complete" boolean DEFAULT false NOT NULL,
"inserted_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
ALTER TABLE "todos" ADD CONSTRAINT "todos_owner_id_users_sync_id_fk" FOREIGN KEY ("owner_id") REFERENCES "neon_auth"."users_sync"("id") ON DELETE no action ON UPDATE no action;
```

5. Attempt to apply the migrations by running `npx drizzle-kit migrate`.
6. The command fails with a parsing error.

**Expected behavior**
`drizzle-kit migrate` should successfully ignore the fully commented `0000_...sql` file and apply only the new, valid migrations (e.g., `0001_...sql`).

**Actual behavior**
The command fails with a `DrizzleQueryError`. See the full error log below.

Click to expand error log

```bash
npx drizzle-kit migrate
No config path provided, using default 'drizzle.config.ts'
Reading config file '/workspaces/drizzle-pull-bug/drizzle.config.ts'
[dotenv@17.2.1] injecting env (0) from .env.local -- tip: ⚙️ override existing env vars with { override: true }
Using 'pg' driver for database querying
[⣻] applying migrations...DrizzleQueryError: Failed query: -- Current sql file was generated after introspecting the database
-- If you want to run this migration please uncomment this code before executing migrations
/*
CREATE SCHEMA "neon_auth";

params:
at NodePgPreparedQuery.queryWithCache (/workspaces/drizzle-pull-bug/node_modules/src/pg-core/session.ts:73:11)
at process.processTicksAndRejections (node:internal/process/task_queues:105:5)
at async (/workspaces/drizzle-pull-bug/node_modules/src/pg-core/dialect.ts:102:7)
... 2 lines matching cause stack trace ...
at async migrate (/workspaces/drizzle-pull-bug/node_modules/src/node-postgres/migrator.ts:10:2) {
query: '-- Current sql file was generated after introspecting the database\n' +
'-- If you want to run this migration please uncomment this code before executing migrations\n' +
'/*\n' +
'CREATE SCHEMA "neon_auth";\n',
params: [],
cause: error: unterminated /* comment at or near "/*
CREATE SCHEMA "neon_auth";
"
at /workspaces/drizzle-pull-bug/node_modules/pg/lib/client.js:545:17
at process.processTicksAndRejections (node:internal/process/task_queues:105:5)
at async (/workspaces/drizzle-pull-bug/node_modules/src/node-postgres/session.ts:149:14)
at async NodePgPreparedQuery.queryWithCache (/workspaces/drizzle-pull-bug/node_modules/src/pg-core/session.ts:71:12)
at async (/workspaces/drizzle-pull-bug/node_modules/src/pg-core/dialect.ts:102:7)
at async NodePgSession.transaction (/workspaces/drizzle-pull-bug/node_modules/src/node-postgres/session.ts:258:19)
at async PgDialect.migrate (/workspaces/drizzle-pull-bug/node_modules/src/pg-core/dialect.ts:95:3)
at async migrate (/workspaces/drizzle-pull-bug/node_modules/src/node-postgres/migrator.ts:10:2) {
length: 131,
severity: 'ERROR',
code: '42601',
detail: undefined,
hint: undefined,
position: '160',
internalPosition: undefined,
internalQuery: undefined,
where: undefined,
schema: undefined,
table: undefined,
column: undefined,
dataType: undefined,
constraint: undefined,
file: 'scan.l',
line: '1244',
routine: 'scanner_yyerror'
}
}
```

`Node.js version`: [v22.17.0]

Here is my `drizzle.config.ts` if needed:
```typescript
import { defineConfig } from 'drizzle-kit';
import { config } from 'dotenv';

config({ path: './.env.local' });

export default defineConfig({
dialect: 'postgresql',
schema: './app/db/schema.ts',
out: './drizzle',
dbCredentials: {
url: process.env.DATABASE_URL!,
},
schemaFilter: ['public', 'neon_auth'],
});
```

A temporary workaround in my case was to manually delete all the contents inside `0000_...sql` file before running `migrate`, but this feels like an unintended manual step in the workflow.

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.