drizzle-team / drizzle-team/drizzle-orm
[BUG]: drizzle-kit migrate fails with parsing error on commented-out file from drizzle-kit pull
- 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
Assessment
This issue has not been assessed yet.