drizzle-kit migrate fails with "relation users already exists" after push
TL;DR
drizzle-kit migrate fails on its very first file with relation "users" already exists when the tables were created by drizzle-kit push. Push compares your schema with the database and changes the database directly. It never writes to Drizzle's migration log, drizzle.__drizzle_migrations. migrate reads only that log, finds it empty, and replays 0000_*.sql from the top.
Do not add IF NOT EXISTS to the files. Insert a row into the log for the newest migration the database already matches, using the file's sha256 and its when value from the journal. migrate then skips everything up to that point and applies only what is new. After that, use one workflow per database: push or generate + migrate, never both.
The error
The report is drizzle-orm issue #2825, on SQLite (Turso). The reporter generated a migration, ran drizzle-kit push, changed the schema, generated again and pushed again. Then everything failed "with an error that the table already exists", and the reporter asked for IF NOT EXISTS in the generated CREATE TABLE. A Drizzle maintainer closed it with the answer that matters: drizzle-kit push "is not applying any migrations. It will just find the difference between your schema and database and apply those. To apply all generated migrations you should use drizzle-kit migrate".
The reproduction ran the sequence the maintainer points to, push and then migrate, on PostgreSQL. It used drizzle-kit 0.31.11 and drizzle-orm 0.45.4 on Windows 11 with Node v26.7.0. The database was PGlite 0.5.13, which is PostgreSQL 18.6 compiled to WebAssembly and run in-process (driver: "pglite" in drizzle.config.ts), not a PostgreSQL server. The reporter's own sequence, push twice with a generate in between, completed without any error there: the second push simply added the new column. That matches the maintainer's statement that push applies no migration files. The failure came from migrate. push created users from the first schema, a second generate wrote ALTER TABLE "users" ADD COLUMN "email_verified", and then drizzle-kit migrate ran:
$ npx drizzle-kit migrate
No config path provided, using default 'drizzle.config.ts'
Reading config file '<app>\drizzle.config.ts'
[⣷] applying migrations...[⣷] applying migrations...exit=1
The CLI printed only its spinner and exited with code 1. That silence is a separate drizzle-kit behaviour, covered in the page on drizzle-kit migrate stopping with no error. To see the database error, call the same migrator from a script (migrate() from drizzle-orm/pglite/migrator here; use drizzle-orm/node-postgres/migrator for pg):
$ node migrate.mjs
migration failed: relation "users" already exists (SQLSTATE 42P07)
exit=1
Why migrate replays your first migration
The two commands keep state in different places. The drizzle-kit push docs describe push as reading your schema, introspecting the database, generating the SQL for the difference and applying it. No migration file and no log row are involved. The drizzle-kit migrate docs describe the other side: migrate fetches entries from the migrations log table, then "Based on previously applied migrations it will decide which new migrations to run". By default that table is __drizzle_migrations in the drizzle schema.
The decision itself is short. In pg-core/dialect.ts at drizzle-orm 0.45.4, the migrator creates the schema and the log table if missing, outside any transaction. Then it reads one row: order by created_at desc limit 1. Inside a transaction it runs every journal entry whose when is greater than that row's created_at, or every entry when the table is empty. The hash comes from migrator.ts, a sha256 of the .sql file. It is stored but never compared.
On a database built by push, there is no log yet. The reproduction checked right after the failure:
$ node q.mjs select id, hash, created_at from drizzle.__drizzle_migrations
[]
exit=0
$ node q.mjs select column_name from information_schema.columns where table_name = 'users' order by ordinal_position
[{"column_name":"id"},{"column_name":"email"}]
exit=0
The log table now exists, because it was created before the transaction, and it is empty. users is untouched, because the failed transaction rolled back.
Two controls confirm the mechanism. On an empty database, migrate alone applied both files and wrote two rows. A second run changed nothing. The generated files themselves are fine. And a second push after generate added email_verified directly. Afterwards drizzle.__drizzle_migrations did not exist at all (relation "drizzle.__drizzle_migrations" does not exist (SQLSTATE 42P01)), so push never reads or writes the migrations folder's history.
Fix: baseline the migrations the database already has
The idea is the same as supabase migration repair --status applied, which the Supabase db push page uses after a 42P07: record a migration as applied without running it. Drizzle has no command for this, so you write the row yourself. Back up the database first if it holds data you care about.
1. Find the newest migration the database already matches
Open each file in drizzle/ in order and compare it with the live database. In the reproduction, the database had id and email but no email_verified. It matched 0000 and not 0001. If push ran after your latest generate, the database usually matches the last file.
2. Read that migration's when and compute its hash
when is in drizzle/meta/_journal.json:
{
"idx": 0,
"version": "7",
"when": 1793381648727,
"tag": "0000_zippy_giant_girl",
"breakpoints": true
}
The hash is the sha256 of the file, exactly as the migrator computes it:
node -e 'console.log(require("crypto").createHash("sha256").update(require("fs").readFileSync(process.argv[1])).digest("hex"))' drizzle/0000_zippy_giant_girl.sql
3. Insert one row into the log
Run this with psql or your SQL client, with your own hash and when. The first two statements are the ones the migrator itself runs, so they are harmless if the table already exists:
CREATE SCHEMA IF NOT EXISTS drizzle;
CREATE TABLE IF NOT EXISTS drizzle.__drizzle_migrations (
id SERIAL PRIMARY KEY,
hash text NOT NULL,
created_at bigint
);
INSERT INTO drizzle.__drizzle_migrations (hash, created_at)
VALUES ('c9dc9641b6c6f4bdc644fcf1e6ea8b5fed2405ef127db1d46948f5f5800c98af', 1793381648727);
If drizzle.config.ts sets migrations.table or migrations.schema, use those names instead. The migrate docs show both options.
The SQL above is the PostgreSQL form, and only PostgreSQL was reproduced. On SQLite and Turso, as in the issue, the table has no schema prefix. It is plain __drizzle_migrations, with created_at declared numeric. The selection rule is the same: newest created_at first, then every journal entry with a later when. The source shows this in sqlite-core/dialect.ts and in the libSQL driver's libsql/migrator.ts.
4. Run migrate again
$ npx drizzle-kit migrate
No config path provided, using default 'drizzle.config.ts'
Reading config file '<app>\drizzle.config.ts'
[⣷] applying migrations...[✓] migrations applied successfully!exit=0
Only 0001 ran. The log now holds both timestamps and the column exists:
$ node q.mjs select id, created_at from drizzle.__drizzle_migrations order by id
[{"id":1,"created_at":1793381648727},{"id":2,"created_at":1793381668028}]
exit=0
$ node q.mjs select column_name from information_schema.columns where table_name = 'users' order by ordinal_position
[{"column_name":"id"},{"column_name":"email"},{"column_name":"email_verified"}]
exit=0
If push had already applied every file, baseline only the last one. The migrator reads just the newest row, so there is no need to insert one row per file. In the reproduction, a database where push had added email_verified got a single row for 0001, created by hand together with the table. migrate then exited 0 and applied nothing.
Verify the fix
Two checks change nothing. Run drizzle-kit migrate a second time: it should exit 0 and add no row to drizzle.__drizzle_migrations. Then query the columns of each table the last migration touched, as above, and compare them with that migration's SQL.
On a development database or a copy, you can also let push compare the whole schema with the database. Run it as npx drizzle-kit push --verbose --strict. With nothing to change it stops at once, as on the baselined database in the reproduction (run there without --strict):
$ npx drizzle-kit push --verbose
No config path provided, using default 'drizzle.config.ts'
Reading config file '<app>\drizzle.config.ts'
[⣷] Pulling schema from database...
[✓] Pulling schema from database...
[i] No changes detected
exit=0
Keep --strict in that command: according to drizzle-kit push --help it makes push "Always ask for confirmation". Without it, a database that is behind does not get a report. Push prints the statements and applies them. On the database from the failing run, it added email_verified itself, which brings you back to the mixed state this page fixes. If it lists statements, decline them and finish with migrate.
When IF NOT EXISTS seems like the answer
The issue asks Drizzle to emit CREATE TABLE IF NOT EXISTS, and a comment there links a script that rewrites the generated files with a regular expression. Drizzle removed this on purpose. The drizzle-kit 0.30.0 release notes (9 December 2024) say the PostgreSQL dialect "will no longer include IF NOT EXISTS, $DO, or similar statements, which could cause incorrect DDL statements to not fail when an object already exists in the database and should actually fail."
The reproduction shows why the rewrite only moves the failure. With CREATE TABLE IF NOT EXISTS "users" in 0000 and an empty log, the migrator passed on the database where push had applied only the first schema. On the database where push had also added the column, it stopped at the next file:
$ node migrate.mjs ./drizzle-ine
migration failed: column "email_verified" of relation "users" already exists (SQLSTATE 42701)
exit=1
ALTER TABLE ... ADD COLUMN, constraints and foreign keys have no clause that covers every case. The rewrite also skips a table whose definition differs from the file without saying so. The baseline row records the true state and leaves the generated SQL as it is.
Keep push and migrate on separate databases
The migrations overview presents push and generate + migrate as separate options. Mixing them on one database is what creates this state. A common split is push on a throwaway local database and generate + migrate everywhere else. If a development database is already mixed and holds nothing you need, resetting it is quicker than a baseline. Dropping every table covers public, but drop the drizzle schema too, or the old log rows will make migrate skip files on the empty database. The issue itself was on Turso, where the CLI talks to SQLite. Push also has dialect-specific failures there, such as the run()/get() TypeError on libSQL.
If migrate exits 1 again after the baseline with no message, it is hitting a different SQL error. Run the migrator script to print it, as described in the silent drizzle-kit migrate page. If the error names a table you did not expect, check the log's newest created_at against the journal before you insert anything else. A row with a later timestamp than the database really has will make migrate skip work without any error.
---
Originally published on iloveblogs.blog. Drafted with AI assistance; the reproduction (drizzle-kit 0.31.11, drizzle-orm 0.45.4, PGlite) was run and logged before publishing.
@rphlmr @DrizzleORM I've been working with RC4 in production for a while now, and I have two issues that are failing in RC5.
drizzle-orm#5992
drizzle-orm#6564
2 likes · 60 views
코딩 에이전트 덕분인가, 오래 걸리긴 했어도 Drizzle ORM RC 최신 판에 요청했던 버그 수정 사항들이 대부분 반영되었다.
github.com/drizzle-team/drizzle-orm/releases/tag/…
4 likes · 193 views
Drizzle v1 RC5 is finally out and it is absolutely gigantic!
github.com/drizzle-team/drizzle-orm/releases/tag/…
3 likes
Exciting @DrizzleORM milestone! 🎉
RC5 being the final release candidate means v1.0 is coming soon!
Time to test upgrades, review breaking changes, and prepare migrations before the stable release 🌧️🚀
github.com/drizzle-team/drizzle-orm/releases/tag/…
59 likes · 2 reposts · 1,400 views
drizzle-orm#6557
drizzle-kit이 SQLite에서 이름은 같고 unique 여부만 바뀐 인덱스를 변경으로 인식 못해요.,.,. ㅠㅠ 고쳐줘
3 likes · 332 views
drizzle-orm#1628
onConflictDoNothing()을 사용할 때 where 조건절이 잘못된 SQL 위치에 생성되는 오류가 있는데
psql만 고쳐지고 sqlite 는 안고쳐졌어
3 likes · 189 views
Integration testing with PostgreSQL, Drizzle and Vitest
For a new TypeScript project I recently had to come up with a concept for integration testing. It was already decided that we will use the following technologies:
- PostgreSQL as database
- Drizzle as ORM framework
- Vitest as testing framework
The main question was how to setup a database on which the tests can run. I didn't find much on the topic but the article How to Test Your Node.js & Postgres App Using Drizzle & PGlite gave me a starting point. I implemented a POC with PGlite but wasn't really happy with it because it seemed quite slow. Besides performance there is another consideration when using PGlite: you don't use the same kind of database that you use for your real app. I can't really tell under which circumstances this would become problematic, but why take the risk if not necessary?
The article also mentioned Testcontainers as a possible solution but discouraged it - and also to me, creating throwaway containers sounded like a lot of overhead. Then a friend had the idea to create throwaway databases in a running PostgreSQL instance instead and I followed that path.
In the end I came up with four approaches, first the approach with PGlite and then three different approaches for creating throwaway databases in a PostgreSQL instance. Following I'll introduce the implementations and show some measurements I took on the performance.
I implemented the POCs in a repository I created a while ago for architectural study: dyedwiper/cinesmall You can find the respective code in the "ddd" folder and in the branches called "int-tests-*".
General notes on the usage of Vitest and Drizzle
All approaches make use of Vitest's setupFiles and some of globalSetup.
For pushing the schema programmatically I used a function from drizzle-kit which is not documented. I read about it in a discussion on Github: Push schema programatically?
General notes on the tests
In all approaches I used the same tests. I also added some unit tests to see how they can be distinguished from integration tests. (Although this is a different topic, I left them in because I don't think they distract too much.)
Writing the integration tests I especially took care that they fail if the database is not correctly cleaned up between tests. Here is the code from one of the integration test files:
describe('addScreening', () => {
test('db is cleared after each test', async () => {
const screenings = await db.query.screenings.findMany();
expect(screenings).toHaveLength(0);
});
test('adds a screening', async () => {
const weekplanId = crypto.randomUUID();
await db.insert(weekplans).values({ id: weekplanId, startDate: '2026-07-16' });
const dto = {
weekplanId: weekplanId,
date: '2026-07-17',
hallNumber: 1,
film: 'Xaver und sein außerirdischer Freund',
duration: 90,
};
await addScreening(dto);
const result = await db.query.weekplans.findFirst({
where: { id: weekplanId },
with: { screenings: true },
});
expect(result?.screenings).toHaveLength(1);
});
test('db is cleared after each test', async () => {
const screenings = await db.query.screenings.findMany();
expect(screenings).toHaveLength(0);
});
});
You can find the above test file here (or in the other branches respectively): github.com/dyedwiper/cinesmall/blob/int-tests-pgl…
The four approaches
1. Create a PGlite database for each test file
In the setupFile the drizzle instance is mocked with an instance connected to PGlite. The package @electric-sql/pglite is used to create the PGlite instance.
const db = drizzle({ client: new PGlite(), relations: schema.relations });
await (await pushSchema(schema, db)).apply();
// Inspired by https://github.com/drizzle-team/drizzle-orm/issues/4205
vi.doMock(import('../db/index.js'), async () => ({ db }));
afterEach(async () => {
await reset(db, schema);
});
Also an afterEach hook is added to clear the database after each test.
The setupFile is specified to run before each test in the Vitest config:
export default defineProject({
test: {
// ...
projects: [
{
test: {
name: 'Integration Tests',
include: [`**/*.int-test.ts`],
setupFiles: 'src/shared/test/dbSetup.ts',
},
},
{
test: {
name: 'Unit Tests',
include: [`**/*.test.ts`],
},
},
],
},
});
2. Creating one throwaway database for all test files
Here, the idea is to spin up a database on a running PostgreSQL instance and clean it up between all tests.
The project in the Vitest config looks like this:
test: {
name: 'Integration Tests',
include: [`**/*.int-test.ts`],
env: loadEnv('test', process.cwd(), ''),
globalSetup: 'src/shared/test/globalSetup.ts',
setupFiles: 'src/shared/test/clearDb.ts',
fileParallelism: false,
},
There are two important things here:
- Environment variables are loaded from .env.test which contains the connection to the PostgreSQL instance. These will then be available to the tests, see: vitest.dev and vitest.dev.
- fileParellelism is set to false. Running the test files in parallel on the same database is unreliable because of missing isolation.
In globalSetup the test database is created and then the schema is pushed to it. (You need two pools, the first to create the database and the second to connect to it.)
// This name must be the same as PGDATABASE in .env.test
const testDbName = `cinesmall-test`;
export async function setup() {
process.loadEnvFile();
const pool1 = new Pool();
await pool1.query(`CREATE DATABASE "${testDbName}";`);
await pool1.end();
const pool2 = new Pool({ database: testDbName });
const db = drizzle({ client: pool2, relations: schema.relations });
await (await pushSchema(schema, db)).apply();
await pool2.end();
}
export async function teardown() {
const pool = new Pool();
await pool.query(`DROP DATABASE "${testDbName}";`);
await pool.end();
}
Because of the above mentioned env config, all tests will use this database. The setupFile just adds an afterEach to clear the database between each test.
afterEach(() => {
reset(db, schema);
});
3. Creating a throwaway database for each test file
As mentioned above you need to run test files sequentially when using a single database for all. This is not the case when you create a distinct database for each test file.
The database is created in the setupFile. The name of the database must be unique among all test files running in parallel. Using UUIDs is an easy way to achieve this.
process.loadEnvFile();
const dbName = `cinesmall-test-${crypto.randomUUID()}`;
const adminPool = new Pool();
await adminPool.query(`CREATE DATABASE "${dbName}";`);
const testPool = new Pool({ database: dbName });
const db = drizzle({ client: testPool, relations: schema.relations });
await (await pushSchema(schema, db)).apply();
// Inspired by https://github.com/drizzle-team/drizzle-orm/issues/4205
vi.doMock(import('../db/index.js'), async () => ({ db }));
afterEach(async () => {
await reset(db, schema);
});
afterAll(async () => {
await testPool.end();
await adminPool.query(`DROP DATABASE "${dbName}";`);
await adminPool.end();
});
Unlike in the previous approach and similar to the PGlite approach, the drizzle instance is mocked with an instance that is connected to the throwaway database.
4. Creating a throwaway database from a template for each test file
In PostgreSQL it is possible to create databases from custom templates. A colleague suspected this could be faster than pushing the schema to each throwaway database.
In globalSetup such a template database is created and the schema is pushed to it:
const templateDbName = 'cinesmall-test-template';
export async function setup() {
process.loadEnvFile();
const pool1 = new Pool();
await pool1.query(`CREATE DATABASE "${templateDbName}";`);
await pool1.end();
const pool2 = new Pool({ database: templateDbName });
const db = drizzle({ client: pool2, relations: schema.relations });
await (await pushSchema(schema, db)).apply();
await pool2.end();
}
// ...
Then in the setupFile the throwaway database is created from this template:
process.loadEnvFile();
const dbName = `cinesmall-test-${crypto.randomUUID()}`;
const adminPool = new Pool();
await adminPool.query(`CREATE DATABASE "${dbName}" TEMPLATE "cinesmall-test-template";`);
const testPool = new Pool({ database: dbName });
const db = drizzle({ client: testPool, relations: schema.relations });
// Inspired by https://github.com/drizzle-team/drizzle-orm/issues/4205
vi.doMock(import('../db/index.js'), async () => ({ db }));
// ...
The rest works just the same as in the third approach.
Performance comparison
For the measurements it was important to be aware of Vitest's handling of parallelism, especially that "By default, Vitest runs test files in parallel across multiple workers." In the second approach tests have to run sequentially but for the others it is sensible to make use of parallelism. To make the measurements more reliable I set maxWorkers because otherwise Vitest asks the OS for available parallelism and that can vary depending on what your machine is doing besides. I set it to 4 because this seemed low enough that my machine is always capable of handling it and also high enough to see an impact compared to running the tests sequentially.
I created 20 copies of clean.int-test.ts (which I didn't commit) to have a more realistic number of test files and to increase the impact of database setup on the duration.
Since the durations of the tests are pretty consistent, I just paste one example per approach.
1. PGlite database per test file
Test Files 25 passed (25)
Tests 32 passed (32)
Start at 11:56:23
Duration 48.91s (setup 98%, tests 1%)
2. Global throwaway database
Test Files 25 passed (25)
Tests 32 passed (32)
Start at 12:02:04
Duration 12.47s (setup 83%, tests 11%, transform 4%, import 1%, worker 1%)
3. Throwaway database per test file
Test Files 25 passed (25)
Tests 32 passed (32)
Start at 12:04:01
Duration 8.85s (setup 76%, tests 20%, transform 2%, import 1%)
4. Throwaway database per test file created from template
Test Files 25 passed (25)
Tests 32 passed (32)
Start at 12:06:06
Duration 7.25s (setup 63%, tests 33%, transform 2%, import 1%, worker 1%)
Conclusion
It turns out that creating one throwaway database per test file on a running PostgreSQL instance is the best solution. It is way faster than spinning up a PGlite database for each test file and also considerably faster than running test files sequentially on a single database.
It further turned out that creating the database from a template is indeed faster than pushing the schema each time.
ざーっとここのディスカッションを読んだんですがどうやらこれに関してPRは出てないですねえ
drizzle-orm#956
1 like
So Drizzle is faster than Prisma - until it isn't 🫠.
I am currently benchmarking @drizzle.team with Nitropage and the relational queries are 2-3 times slower compared to Prisma 6.19 🙈.
drizzle-orm#5245
2 likes
Mentions
drizzle-kit migrate fails with "relation users already exists" after push TL;DR drizzle-kit migrate fails on its very first file with relation "users" already exists when the tables were created by drizzle-kit push. Push compares your schema with the database and changes the database directly. It never writes to Drizzle's migration log, drizzle.__drizzle_migrations. migrate reads only that log, finds it empty, and replays 0000_*.sql from the top. Do not add IF NOT EXISTS to the files. Insert a row into the log for the newest migration the database already matches, using the file's sha256 and its when value from the journal. migrate then skips everything up to that point and applies only what is new. After that, use one workflow per database: push or generate + migrate, never both. The error The report is drizzle-orm issue #2825, on SQLite (Turso). The reporter generated a migration, ran drizzle-kit push, changed the schema, generated again and pushed again. Then everything failed "with an error that the table already exists", and the reporter asked for IF NOT EXISTS in the generated CREATE TABLE. A Drizzle maintainer closed it with the answer that matters: drizzle-kit push "is not applying any migrations. It will just find the difference between your schema and database and apply those. To apply all generated migrations you should use drizzle-kit migrate". The reproduction ran the sequence the maintainer points to, push and then migrate, on PostgreSQL. It used drizzle-kit 0.31.11 and drizzle-orm 0.45.4 on Windows 11 with Node v26.7.0. The database was PGlite 0.5.13, which is PostgreSQL 18.6 compiled to WebAssembly and run in-process (driver: "pglite" in drizzle.config.ts), not a PostgreSQL server. The reporter's own sequence, push twice with a generate in between, completed without any error there: the second push simply added the new column. That matches the maintainer's statement that push applies no migration files. The failure came from migrate. push created users from the first schema, a second generate wrote ALTER TABLE "users" ADD COLUMN "email_verified", and then drizzle-kit migrate ran: $ npx drizzle-kit migrate No config path provided, using default 'drizzle.config.ts' Reading config file '<app>\drizzle.config.ts' [⣷] applying migrations...[⣷] applying migrations...exit=1 The CLI printed only its spinner and exited with code 1. That silence is a separate drizzle-kit behaviour, covered in the page on drizzle-kit migrate stopping with no error. To see the database error, call the same migrator from a script (migrate() from drizzle-orm/pglite/migrator here; use drizzle-orm/node-postgres/migrator for pg): $ node migrate.mjs migration failed: relation "users" already exists (SQLSTATE 42P07) exit=1 Why migrate replays your first migration The two commands keep state in different places. The drizzle-kit push docs describe push as reading your schema, introspecting the database, generating the SQL for the difference and applying it. No migration file and no log row are involved. The drizzle-kit migrate docs describe the other side: migrate fetches entries from the migrations log table, then "Based on previously applied migrations it will decide which new migrations to run". By default that table is __drizzle_migrations in the drizzle schema. The decision itself is short. In pg-core/dialect.ts at drizzle-orm 0.45.4, the migrator creates the schema and the log table if missing, outside any transaction. Then it reads one row: order by created_at desc limit 1. Inside a transaction it runs every journal entry whose when is greater than that row's created_at, or every entry when the table is empty. The hash comes from migrator.ts, a sha256 of the .sql file. It is stored but never compared. On a database built by push, there is no log yet. The reproduction checked right after the failure: $ node q.mjs select id, hash, created_at from drizzle.__drizzle_migrations [] exit=0 $ node q.mjs select column_name from information_schema.columns where table_name = 'users' order by ordinal_position [{"column_name":"id"},{"column_name":"email"}] exit=0 The log table now exists, because it was created before the transaction, and it is empty. users is untouched, because the failed transaction rolled back. Two controls confirm the mechanism. On an empty database, migrate alone applied both files and wrote two rows. A second run changed nothing. The generated files themselves are fine. And a second push after generate added email_verified directly. Afterwards drizzle.__drizzle_migrations did not exist at all (relation "drizzle.__drizzle_migrations" does not exist (SQLSTATE 42P01)), so push never reads or writes the migrations folder's history. Fix: baseline the migrations the database already has The idea is the same as supabase migration repair --status applied, which the Supabase db push page uses after a 42P07: record a migration as applied without running it. Drizzle has no command for this, so you write the row yourself. Back up the database first if it holds data you care about. 1. Find the newest migration the database already matches Open each file in drizzle/ in order and compare it with the live database. In the reproduction, the database had id and email but no email_verified. It matched 0000 and not 0001. If push ran after your latest generate, the database usually matches the last file. 2. Read that migration's when and compute its hash when is in drizzle/meta/_journal.json: { "idx": 0, "version": "7", "when": 1793381648727, "tag": "0000_zippy_giant_girl", "breakpoints": true } The hash is the sha256 of the file, exactly as the migrator computes it: node -e 'console.log(require("crypto").createHash("sha256").update(require("fs").readFileSync(process.argv[1])).digest("hex"))' drizzle/0000_zippy_giant_girl.sql 3. Insert one row into the log Run this with psql or your SQL client, with your own hash and when. The first two statements are the ones the migrator itself runs, so they are harmless if the table already exists: CREATE SCHEMA IF NOT EXISTS drizzle; CREATE TABLE IF NOT EXISTS drizzle.__drizzle_migrations ( id SERIAL PRIMARY KEY, hash text NOT NULL, created_at bigint ); INSERT INTO drizzle.__drizzle_migrations (hash, created_at) VALUES ('c9dc9641b6c6f4bdc644fcf1e6ea8b5fed2405ef127db1d46948f5f5800c98af', 1793381648727); If drizzle.config.ts sets migrations.table or migrations.schema, use those names instead. The migrate docs show both options. The SQL above is the PostgreSQL form, and only PostgreSQL was reproduced. On SQLite and Turso, as in the issue, the table has no schema prefix. It is plain __drizzle_migrations, with created_at declared numeric. The selection rule is the same: newest created_at first, then every journal entry with a later when. The source shows this in sqlite-core/dialect.ts and in the libSQL driver's libsql/migrator.ts. 4. Run migrate again $ npx drizzle-kit migrate No config path provided, using default 'drizzle.config.ts' Reading config file '<app>\drizzle.config.ts' [⣷] applying migrations...[✓] migrations applied successfully!exit=0 Only 0001 ran. The log now holds both timestamps and the column exists: $ node q.mjs select id, created_at from drizzle.__drizzle_migrations order by id [{"id":1,"created_at":1793381648727},{"id":2,"created_at":1793381668028}] exit=0 $ node q.mjs select column_name from information_schema.columns where table_name = 'users' order by ordinal_position [{"column_name":"id"},{"column_name":"email"},{"column_name":"email_verified"}] exit=0 If push had already applied every file, baseline only the last one. The migrator reads just the newest row, so there is no need to insert one row per file. In the reproduction, a database where push had added email_verified got a single row for 0001, created by hand together with the table. migrate then exited 0 and applied nothing. Verify the fix Two checks change nothing. Run drizzle-kit migrate a second time: it should exit 0 and add no row to drizzle.__drizzle_migrations. Then query the columns of each table the last migration touched, as above, and compare them with that migration's SQL. On a development database or a copy, you can also let push compare the whole schema with the database. Run it as npx drizzle-kit push --verbose --strict. With nothing to change it stops at once, as on the baselined database in the reproduction (run there without --strict): $ npx drizzle-kit push --verbose No config path provided, using default 'drizzle.config.ts' Reading config file '<app>\drizzle.config.ts' [⣷] Pulling schema from database... [✓] Pulling schema from database... [i] No changes detected exit=0 Keep --strict in that command: according to drizzle-kit push --help it makes push "Always ask for confirmation". Without it, a database that is behind does not get a report. Push prints the statements and applies them. On the database from the failing run, it added email_verified itself, which brings you back to the mixed state this page fixes. If it lists statements, decline them and finish with migrate. When IF NOT EXISTS seems like the answer The issue asks Drizzle to emit CREATE TABLE IF NOT EXISTS, and a comment there links a script that rewrites the generated files with a regular expression. Drizzle removed this on purpose. The drizzle-kit 0.30.0 release notes (9 December 2024) say the PostgreSQL dialect "will no longer include IF NOT EXISTS, $DO, or similar statements, which could cause incorrect DDL statements to not fail when an object already exists in the database and should actually fail." The reproduction shows why the rewrite only moves the failure. With CREATE TABLE IF NOT EXISTS "users" in 0000 and an empty log, the migrator passed on the database where push had applied only the first schema. On the database where push had also added the column, it stopped at the next file: $ node migrate.mjs ./drizzle-ine migration failed: column "email_verified" of relation "users" already exists (SQLSTATE 42701) exit=1 ALTER TABLE ... ADD COLUMN, constraints and foreign keys have no clause that covers every case. The rewrite also skips a table whose definition differs from the file without saying so. The baseline row records the true state and leaves the generated SQL as it is. Keep push and migrate on separate databases The migrations overview presents push and generate + migrate as separate options. Mixing them on one database is what creates this state. A common split is push on a throwaway local database and generate + migrate everywhere else. If a development database is already mixed and holds nothing you need, resetting it is quicker than a baseline. Dropping every table covers public, but drop the drizzle schema too, or the old log rows will make migrate skip files on the empty database. The issue itself was on Turso, where the CLI talks to SQLite. Push also has dialect-specific failures there, such as the run()/get() TypeError on libSQL. If migrate exits 1 again after the baseline with no message, it is hitting a different SQL error. Run the migrator script to print it, as described in the silent drizzle-kit migrate page. If the error names a table you did not expect, check the log's newest created_at against the journal before you insert anything else. A row with a later timestamp than the database really has will make migrate skip work without any error. --- Originally published on iloveblogs.blog. Drafted with AI assistance; the reproduction (drizzle-kit 0.31.11, drizzle-orm 0.45.4, PGlite) was run and logged before publishing.
@rphlmr @DrizzleORM I've been working with RC4 in production for a while now, and I have two issues that are failing in RC5. drizzle-orm#5992 drizzle-orm#6564
코딩 에이전트 덕분인가, 오래 걸리긴 했어도 Drizzle ORM RC 최신 판에 요청했던 버그 수정 사항들이 대부분 반영되었다. github.com/drizzle-team/drizzle-orm/releases/tag/…
Drizzle v1 RC5 is finally out and it is absolutely gigantic! github.com/drizzle-team/drizzle-orm/releases/tag/…
Exciting @DrizzleORM milestone! 🎉 RC5 being the final release candidate means v1.0 is coming soon! Time to test upgrades, review breaking changes, and prepare migrations before the stable release 🌧️🚀 github.com/drizzle-team/drizzle-orm/releases/tag/…
drizzle-orm#6557 drizzle-kit이 SQLite에서 이름은 같고 unique 여부만 바뀐 인덱스를 변경으로 인식 못해요.,.,. ㅠㅠ 고쳐줘
drizzle-orm#1628 onConflictDoNothing()을 사용할 때 where 조건절이 잘못된 SQL 위치에 생성되는 오류가 있는데 psql만 고쳐지고 sqlite 는 안고쳐졌어
Integration testing with PostgreSQL, Drizzle and Vitest For a new TypeScript project I recently had to come up with a concept for integration testing. It was already decided that we will use the following technologies: - PostgreSQL as database - Drizzle as ORM framework - Vitest as testing framework The main question was how to setup a database on which the tests can run. I didn't find much on the topic but the article How to Test Your Node.js & Postgres App Using Drizzle & PGlite gave me a starting point. I implemented a POC with PGlite but wasn't really happy with it because it seemed quite slow. Besides performance there is another consideration when using PGlite: you don't use the same kind of database that you use for your real app. I can't really tell under which circumstances this would become problematic, but why take the risk if not necessary? The article also mentioned Testcontainers as a possible solution but discouraged it - and also to me, creating throwaway containers sounded like a lot of overhead. Then a friend had the idea to create throwaway databases in a running PostgreSQL instance instead and I followed that path. In the end I came up with four approaches, first the approach with PGlite and then three different approaches for creating throwaway databases in a PostgreSQL instance. Following I'll introduce the implementations and show some measurements I took on the performance. I implemented the POCs in a repository I created a while ago for architectural study: dyedwiper/cinesmall You can find the respective code in the "ddd" folder and in the branches called "int-tests-*". General notes on the usage of Vitest and Drizzle All approaches make use of Vitest's setupFiles and some of globalSetup. For pushing the schema programmatically I used a function from drizzle-kit which is not documented. I read about it in a discussion on Github: Push schema programatically? General notes on the tests In all approaches I used the same tests. I also added some unit tests to see how they can be distinguished from integration tests. (Although this is a different topic, I left them in because I don't think they distract too much.) Writing the integration tests I especially took care that they fail if the database is not correctly cleaned up between tests. Here is the code from one of the integration test files: describe('addScreening', () => { test('db is cleared after each test', async () => { const screenings = await db.query.screenings.findMany(); expect(screenings).toHaveLength(0); }); test('adds a screening', async () => { const weekplanId = crypto.randomUUID(); await db.insert(weekplans).values({ id: weekplanId, startDate: '2026-07-16' }); const dto = { weekplanId: weekplanId, date: '2026-07-17', hallNumber: 1, film: 'Xaver und sein außerirdischer Freund', duration: 90, }; await addScreening(dto); const result = await db.query.weekplans.findFirst({ where: { id: weekplanId }, with: { screenings: true }, }); expect(result?.screenings).toHaveLength(1); }); test('db is cleared after each test', async () => { const screenings = await db.query.screenings.findMany(); expect(screenings).toHaveLength(0); }); }); You can find the above test file here (or in the other branches respectively): github.com/dyedwiper/cinesmall/blob/int-tests-pgl… The four approaches 1. Create a PGlite database for each test file In the setupFile the drizzle instance is mocked with an instance connected to PGlite. The package @electric-sql/pglite is used to create the PGlite instance. const db = drizzle({ client: new PGlite(), relations: schema.relations }); await (await pushSchema(schema, db)).apply(); // Inspired by https://github.com/drizzle-team/drizzle-orm/issues/4205 vi.doMock(import('../db/index.js'), async () => ({ db })); afterEach(async () => { await reset(db, schema); }); Also an afterEach hook is added to clear the database after each test. The setupFile is specified to run before each test in the Vitest config: export default defineProject({ test: { // ... projects: [ { test: { name: 'Integration Tests', include: [`**/*.int-test.ts`], setupFiles: 'src/shared/test/dbSetup.ts', }, }, { test: { name: 'Unit Tests', include: [`**/*.test.ts`], }, }, ], }, }); 2. Creating one throwaway database for all test files Here, the idea is to spin up a database on a running PostgreSQL instance and clean it up between all tests. The project in the Vitest config looks like this: test: { name: 'Integration Tests', include: [`**/*.int-test.ts`], env: loadEnv('test', process.cwd(), ''), globalSetup: 'src/shared/test/globalSetup.ts', setupFiles: 'src/shared/test/clearDb.ts', fileParallelism: false, }, There are two important things here: - Environment variables are loaded from .env.test which contains the connection to the PostgreSQL instance. These will then be available to the tests, see: vitest.dev and vitest.dev. - fileParellelism is set to false. Running the test files in parallel on the same database is unreliable because of missing isolation. In globalSetup the test database is created and then the schema is pushed to it. (You need two pools, the first to create the database and the second to connect to it.) // This name must be the same as PGDATABASE in .env.test const testDbName = `cinesmall-test`; export async function setup() { process.loadEnvFile(); const pool1 = new Pool(); await pool1.query(`CREATE DATABASE "${testDbName}";`); await pool1.end(); const pool2 = new Pool({ database: testDbName }); const db = drizzle({ client: pool2, relations: schema.relations }); await (await pushSchema(schema, db)).apply(); await pool2.end(); } export async function teardown() { const pool = new Pool(); await pool.query(`DROP DATABASE "${testDbName}";`); await pool.end(); } Because of the above mentioned env config, all tests will use this database. The setupFile just adds an afterEach to clear the database between each test. afterEach(() => { reset(db, schema); }); 3. Creating a throwaway database for each test file As mentioned above you need to run test files sequentially when using a single database for all. This is not the case when you create a distinct database for each test file. The database is created in the setupFile. The name of the database must be unique among all test files running in parallel. Using UUIDs is an easy way to achieve this. process.loadEnvFile(); const dbName = `cinesmall-test-${crypto.randomUUID()}`; const adminPool = new Pool(); await adminPool.query(`CREATE DATABASE "${dbName}";`); const testPool = new Pool({ database: dbName }); const db = drizzle({ client: testPool, relations: schema.relations }); await (await pushSchema(schema, db)).apply(); // Inspired by https://github.com/drizzle-team/drizzle-orm/issues/4205 vi.doMock(import('../db/index.js'), async () => ({ db })); afterEach(async () => { await reset(db, schema); }); afterAll(async () => { await testPool.end(); await adminPool.query(`DROP DATABASE "${dbName}";`); await adminPool.end(); }); Unlike in the previous approach and similar to the PGlite approach, the drizzle instance is mocked with an instance that is connected to the throwaway database. 4. Creating a throwaway database from a template for each test file In PostgreSQL it is possible to create databases from custom templates. A colleague suspected this could be faster than pushing the schema to each throwaway database. In globalSetup such a template database is created and the schema is pushed to it: const templateDbName = 'cinesmall-test-template'; export async function setup() { process.loadEnvFile(); const pool1 = new Pool(); await pool1.query(`CREATE DATABASE "${templateDbName}";`); await pool1.end(); const pool2 = new Pool({ database: templateDbName }); const db = drizzle({ client: pool2, relations: schema.relations }); await (await pushSchema(schema, db)).apply(); await pool2.end(); } // ... Then in the setupFile the throwaway database is created from this template: process.loadEnvFile(); const dbName = `cinesmall-test-${crypto.randomUUID()}`; const adminPool = new Pool(); await adminPool.query(`CREATE DATABASE "${dbName}" TEMPLATE "cinesmall-test-template";`); const testPool = new Pool({ database: dbName }); const db = drizzle({ client: testPool, relations: schema.relations }); // Inspired by https://github.com/drizzle-team/drizzle-orm/issues/4205 vi.doMock(import('../db/index.js'), async () => ({ db })); // ... The rest works just the same as in the third approach. Performance comparison For the measurements it was important to be aware of Vitest's handling of parallelism, especially that "By default, Vitest runs test files in parallel across multiple workers." In the second approach tests have to run sequentially but for the others it is sensible to make use of parallelism. To make the measurements more reliable I set maxWorkers because otherwise Vitest asks the OS for available parallelism and that can vary depending on what your machine is doing besides. I set it to 4 because this seemed low enough that my machine is always capable of handling it and also high enough to see an impact compared to running the tests sequentially. I created 20 copies of clean.int-test.ts (which I didn't commit) to have a more realistic number of test files and to increase the impact of database setup on the duration. Since the durations of the tests are pretty consistent, I just paste one example per approach. 1. PGlite database per test file Test Files 25 passed (25) Tests 32 passed (32) Start at 11:56:23 Duration 48.91s (setup 98%, tests 1%) 2. Global throwaway database Test Files 25 passed (25) Tests 32 passed (32) Start at 12:02:04 Duration 12.47s (setup 83%, tests 11%, transform 4%, import 1%, worker 1%) 3. Throwaway database per test file Test Files 25 passed (25) Tests 32 passed (32) Start at 12:04:01 Duration 8.85s (setup 76%, tests 20%, transform 2%, import 1%) 4. Throwaway database per test file created from template Test Files 25 passed (25) Tests 32 passed (32) Start at 12:06:06 Duration 7.25s (setup 63%, tests 33%, transform 2%, import 1%, worker 1%) Conclusion It turns out that creating one throwaway database per test file on a running PostgreSQL instance is the best solution. It is way faster than spinning up a PGlite database for each test file and also considerably faster than running test files sequentially on a single database. It further turned out that creating the database from a template is indeed faster than pushing the schema each time.
ざーっとここのディスカッションを読んだんですがどうやらこれに関してPRは出てないですねえ drizzle-orm#956
So Drizzle is faster than Prisma - until it isn't 🫠. I am currently benchmarking @drizzle.team with Nitropage and the relational queries are 2-3 times slower compared to Prisma 6.19 🙈. drizzle-orm#5245