生成したCREATE TABLEを3つのDBに流したら、25パターン中16で失敗した
はじめに
テーブルをGUIで組み立てて CREATE TABLE 文を出すツールを公開しています。MySQL・PostgreSQL・SQLiteの3方言に対応し、JSONのサンプルからテーブル定義を推論する機能もあります。
これまでのテストは「出力された文字列に PRIMARY KEY が入っているか」を確かめるだけでした。出力したDDLが、実際のデータベースで通るかは一度も試していませんでした。 そこで、本物のデータベースに流してみました。
使ったのは次の3つです。
方言
実際に流した先
MySQL
MariaDB 10.11（MySQL互換。MySQL本体ではありません）
PostgreSQL
PGlite（WASMで動くPostgreSQL）
SQLite
Node.js 22 の node:sqlite
先に結論です。
25パターンを3方言で流したところ、16パターンが少なくとも1つの方言で失敗した。修正後は24パターンが3方言すべてで通る
一番影響が大きかったのは、JSONから推論すると、入れ子の配列が1つでもあればMySQLとPostgreSQLで必ず失敗していたこと
order や user のような予約語のテーブル名、複合主キー、テーブルの並び順でも失敗していた
ついでに、SQL to ER が日本語のテーブル名を読めないことも分かった
試し方
ツールの純ロジック（DdlBuilderTool.generateDdl）でDDLを生成してファイルに書き出し、それを3つのデータベースに流して、成功か失敗かを一覧にしました。パターンは、予約語の名前、複合主キー、テーブルの並び順、デフォルト値、JSONからの推論などの25通りです。
if (d === 'sqlite') {
const db = new DatabaseSync(':memory:');
db.exec('PRAGMA foreign_keys=ON;');
db.exec(sql);
} else if (d === 'postgresql') {
const db = new PGlite();
await db.exec(sql);
} else {
execFileSync('mariadb', ['-e', DROP DATABASE IF EXISTS x; CREATE DATABASE x; USE x; ${sql}]);
}
PGliteは npm i @electric-sql/pglite だけで、本物のPostgreSQLのパーサーと制約チェックが手元で動きます。DDLを出力するツールなら、文字列の比較より先にこれを入れるべきでした。
1. JSONから推論すると、子テーブルが親より先に出る
ツールには、JSONのサンプルからテーブルを推論する機能があります。入れ子の配列は、外部キー付きの子テーブルに分けます。
{ "id": 1, "name": "a", "items": [{ "sku": "x", "qty": 1 }] }
修正前の出力はこうでした。
CREATE TABLE items (
table1_id INTEGER NOT NULL,
id SERIAL PRIMARY KEY,
sku VARCHAR(255) NOT NULL,
qty INTEGER NOT NULL,
FOREIGN KEY (table1_id) REFERENCES table1(id)
);
CREATE TABLE table1 (
id INTEGER PRIMARY KEY,
name VARCHAR(255) NOT NULL
);
子の items が、参照先の table1 より先に作られています。 PostgreSQLは relation "table1" does not exist、MariaDBは Foreign key constraint is incorrectly formed で失敗しました。SQLiteだけは、作成時に参照先の存在を確かめないので通ります。
原因は、推論を再帰で書いていたことです。親のカラムを調べる途中で子テーブルを見つけると、子を先に組み立てて tables に入れます。親は、全部のカラムを調べ終わってから最後に入っていました。
// 子テーブルは再帰の中で先に tables に入るので、親（ルート）を先頭に置く
tables.unshift(buildTableFromRows(rootName, sampleRows, relations, tables));
順番を直しても、まだ通らない
親の id がUUIDの場合です。
{ "id": "3f2b1c4e-0a1b-4c2d-8e9f-0123456789ab", "items": [{ "sku": "x" }] }
子テーブルに足す外部キーの列は、INTEGER に決め打ちしていました。UUIDの id を INTEGER の列で参照することになり、外部キーを作れません。 親の id が大きな数値でBIGINTと推論された場合も、MySQLでは型が一致しないので作れません。外部キーの列の型は、親の id の型に合わせるようにしました。
ほかにも、JSONならではの失敗がありました。
サンプル
修正前に起きたこと
子の要素に table1_id がすでにある
外部キー列を足して、同じ列が2つになる
user-name と user_name と userName
snake_caseにすると全部 user_name になり、列名が重複する
親と同じ名前のキー（{"table1": [...]}）
子テーブルも table1 になり、テーブルが既にあるエラーになる
重複する名前には _2、_3 と番号を振り、子にすでにある親への列は、そのまま外部キーとして使うようにしました。
2. 予約語のテーブル名が通らない
注文テーブルを order と名付けるのは、ごく自然です。
CREATE TABLE order (
id INT PRIMARY KEY AUTO_INCREMENT,
key VARCHAR(255),
desc TEXT
);
3方言すべてで構文エラーです。order・key・desc はどれも予約語です。ほかに、user はPostgreSQLだけ、lines や show や match はMySQLだけが予約語にしています。方言によって予約語が違うので、1つのデータベースで試しただけでは気づけません。
識別子は、必要なときだけクォートするようにしました。
const quoteIdent = (name: string, dialect: Dialect): string => {
if (/^[A-Za-z_][A-Za-z0-9_]*$/.test(name) && !RESERVED_WORDS.has(name.toLowerCase())) return name;
return dialect === 'mysql' ? \${name.replace(//g, '')}\ : "${name.replace(/"/g, '""')}"`;
};
全部の識別子をクォートすれば確実です。ただ、出力が読みにくくなるので、予約語と記号入りの名前に限りました。予約語の一覧は、3方言の予約語のうち、テーブル名や列名に使われそうな約170語です。status や name や type のように、予約語ではない語はクォートしません。
もう1つ注意したのが、大文字を含む名前です。PostgreSQLはクォートしない名前を小文字に変え、クォートした名前は大文字小文字を区別します。UserName を勝手に "UserName" とクォートすると、後から書くSQLでも毎回クォートが必要になります。そのため、英数字とアンダースコアだけの名前はクォートしないようにしています。
3. 主キーが2列あると、主キーが複数あると言われる
ユーザーとロールの中間テーブルでは、主キーが2列になります。
CREATE TABLE user_roles (
user_id INTEGER PRIMARY KEY,
role_id INTEGER PRIMARY KEY
);
列ごとに PRIMARY KEY を付けると、「主キーが2つある」という意味になります。3方言すべてで失敗しました（multiple primary keys for table "user_roles" are not allowed）。主キーの列が2つ以上あるときは、テーブルの末尾にまとめるようにしました。
CREATE TABLE user_roles (
user_id INTEGER NOT NULL,
role_id INTEGER NOT NULL,
PRIMARY KEY (user_id, role_id)
);
4. テーブルの並び順で失敗する
画面でテーブルを足す順番は、利用者の自由です。posts を先に作り、後から users を足して外部キーをつなぐと、DDLも posts が先になります。これも、JSONの推論と同じ理由でMySQLとPostgreSQLで失敗しました。
参照される側が先に来るように、出力時に並べ替えるようにしました。深さ優先探索で親を先に出す、普通のトポロジカルソートです。
困るのは、循環参照（a が b を参照し、b も a を参照する）です。どちらを先にしても、片方の外部キーは参照先がまだありません。この場合は、作れない側の外部キーだけを最後に ALTER TABLE で足すようにしました。
CREATE TABLE a ( ... FOREIGN KEY (b_id) REFERENCES b(id) ); -- b が先に作られている
CREATE TABLE b ( id ..., a_id INTEGER );
ALTER TABLE b ADD FOREIGN KEY (a_id) REFERENCES a(id);
SQLiteだけは別扱いです。SQLiteは ALTER TABLE で外部キーを足せません。その代わり、作成時に参照先を確かめないので、行内に書いたままで通ります。
5. 関数のデフォルトが文字列になる
UUIDの列のデフォルトに gen_random_uuid() と入れると、修正前は DEFAULT 'gen_random_uuid()' と文字列になっていました。PostgreSQLは、この文字列をUUIDとして解釈しようとして失敗します（invalid input syntax for type uuid）。
関数呼び出しの形をしていたら、括弧で囲んで式として出すようにしました。MySQL 8.0.13以降とSQLiteは、式のデフォルトに括弧を要求するので、3方言とも括弧で揃えています。
u UUID DEFAULT (gen_random_uuid())
同じ理由で、MySQLの TEXT と JSON の列は、リテラルのデフォルトも括弧で囲みます。 MySQL 8は、この2つの型にリテラルのデフォルトを許しません。式として書けば通ります。MariaDBはどちらでも通るので、手元の試験では確かめられませんでした。MySQLのドキュメントの記述に合わせています。
おまけ：SQL to ER が日本語のテーブル名を読めなかった
予約語のクォートを足したあと、出力を SQL to ER のパーサーに読み戻すテストを書きました。すると、日本語のテーブル名だけ読み戻せません。
const IDENT = '["]?(\\w+)["]?';
識別子を \w（英数字とアンダースコア）で探していたのが原因でした。顧客 も、クォートした order-items も、テーブルとして見つかりません。画面では「CREATE TABLE文を入力してください」というエラーになり、しかも前に描いたER図が消えずに残っていました。
識別子の本体を「空白・引用符・括弧・区切り記号以外」に広げて、日本語のテーブル名でもER図を描けるようにしました。エラーのときには、古い図を消すようにしています。
まとめ
DDLを出力するツールは、実際のデータベースに流して確かめる。 PGliteと node:sqlite なら、npmとNode.jsだけで手元で動きます
予約語は方言ごとに違う。 user はPostgreSQLだけ、lines はMySQLだけです。1つのデータベースで通っても、他で落ちます
テーブルの並び順は、出力する側で面倒を見る。 外部キーの参照先が先に来るように並べ、循環参照は ALTER TABLE に逃がします
再帰で組み立てると、親が最後になる。 推論の再帰が、そのまま出力の順番になっていました
残った1パターンは、MySQLの列のデフォルトに gen_random_uuid() を入れたものです。PostgreSQLの関数なのでMySQLで通らないのは当然で、ツールの問題ではありません（MySQLなら uuid() です）。
ツールは DDLビルダー で試せます。入力した内容はブラウザの中だけで処理され、サーバーには送信されません。
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.
Mentions
生成したCREATE TABLEを3つのDBに流したら、25パターン中16で失敗した はじめに テーブルをGUIで組み立てて CREATE TABLE 文を出すツールを公開しています。MySQL・PostgreSQL・SQLiteの3方言に対応し、JSONのサンプルからテーブル定義を推論する機能もあります。 これまでのテストは「出力された文字列に PRIMARY KEY が入っているか」を確かめるだけでした。出力したDDLが、実際のデータベースで通るかは一度も試していませんでした。 そこで、本物のデータベースに流してみました。 使ったのは次の3つです。 方言 実際に流した先 MySQL MariaDB 10.11（MySQL互換。MySQL本体ではありません） PostgreSQL PGlite（WASMで動くPostgreSQL） SQLite Node.js 22 の node:sqlite 先に結論です。 25パターンを3方言で流したところ、16パターンが少なくとも1つの方言で失敗した。修正後は24パターンが3方言すべてで通る 一番影響が大きかったのは、JSONから推論すると、入れ子の配列が1つでもあればMySQLとPostgreSQLで必ず失敗していたこと order や user のような予約語のテーブル名、複合主キー、テーブルの並び順でも失敗していた ついでに、SQL to ER が日本語のテーブル名を読めないことも分かった 試し方 ツールの純ロジック（DdlBuilderTool.generateDdl）でDDLを生成してファイルに書き出し、それを3つのデータベースに流して、成功か失敗かを一覧にしました。パターンは、予約語の名前、複合主キー、テーブルの並び順、デフォルト値、JSONからの推論などの25通りです。 if (d === 'sqlite') { const db = new DatabaseSync(':memory:'); db.exec('PRAGMA foreign_keys=ON;'); db.exec(sql); } else if (d === 'postgresql') { const db = new PGlite(); await db.exec(sql); } else { execFileSync('mariadb', ['-e', DROP DATABASE IF EXISTS x; CREATE DATABASE x; USE x; ${sql}]); } PGliteは npm i @electric-sql/pglite だけで、本物のPostgreSQLのパーサーと制約チェックが手元で動きます。DDLを出力するツールなら、文字列の比較より先にこれを入れるべきでした。 1. JSONから推論すると、子テーブルが親より先に出る ツールには、JSONのサンプルからテーブルを推論する機能があります。入れ子の配列は、外部キー付きの子テーブルに分けます。 { "id": 1, "name": "a", "items": [{ "sku": "x", "qty": 1 }] } 修正前の出力はこうでした。 CREATE TABLE items ( table1_id INTEGER NOT NULL, id SERIAL PRIMARY KEY, sku VARCHAR(255) NOT NULL, qty INTEGER NOT NULL, FOREIGN KEY (table1_id) REFERENCES table1(id) ); CREATE TABLE table1 ( id INTEGER PRIMARY KEY, name VARCHAR(255) NOT NULL ); 子の items が、参照先の table1 より先に作られています。 PostgreSQLは relation "table1" does not exist、MariaDBは Foreign key constraint is incorrectly formed で失敗しました。SQLiteだけは、作成時に参照先の存在を確かめないので通ります。 原因は、推論を再帰で書いていたことです。親のカラムを調べる途中で子テーブルを見つけると、子を先に組み立てて tables に入れます。親は、全部のカラムを調べ終わってから最後に入っていました。 // 子テーブルは再帰の中で先に tables に入るので、親（ルート）を先頭に置く tables.unshift(buildTableFromRows(rootName, sampleRows, relations, tables)); 順番を直しても、まだ通らない 親の id がUUIDの場合です。 { "id": "3f2b1c4e-0a1b-4c2d-8e9f-0123456789ab", "items": [{ "sku": "x" }] } 子テーブルに足す外部キーの列は、INTEGER に決め打ちしていました。UUIDの id を INTEGER の列で参照することになり、外部キーを作れません。 親の id が大きな数値でBIGINTと推論された場合も、MySQLでは型が一致しないので作れません。外部キーの列の型は、親の id の型に合わせるようにしました。 ほかにも、JSONならではの失敗がありました。 サンプル 修正前に起きたこと 子の要素に table1_id がすでにある 外部キー列を足して、同じ列が2つになる user-name と user_name と userName snake_caseにすると全部 user_name になり、列名が重複する 親と同じ名前のキー（{"table1": [...]}） 子テーブルも table1 になり、テーブルが既にあるエラーになる 重複する名前には _2、_3 と番号を振り、子にすでにある親への列は、そのまま外部キーとして使うようにしました。 2. 予約語のテーブル名が通らない 注文テーブルを order と名付けるのは、ごく自然です。 CREATE TABLE order ( id INT PRIMARY KEY AUTO_INCREMENT, key VARCHAR(255), desc TEXT ); 3方言すべてで構文エラーです。order・key・desc はどれも予約語です。ほかに、user はPostgreSQLだけ、lines や show や match はMySQLだけが予約語にしています。方言によって予約語が違うので、1つのデータベースで試しただけでは気づけません。 識別子は、必要なときだけクォートするようにしました。 const quoteIdent = (name: string, dialect: Dialect): string => { if (/^[A-Za-z_][A-Za-z0-9_]*$/.test(name) && !RESERVED_WORDS.has(name.toLowerCase())) return name; return dialect === 'mysql' ? \${name.replace(//g, '')}\ : "${name.replace(/"/g, '""')}"`; }; 全部の識別子をクォートすれば確実です。ただ、出力が読みにくくなるので、予約語と記号入りの名前に限りました。予約語の一覧は、3方言の予約語のうち、テーブル名や列名に使われそうな約170語です。status や name や type のように、予約語ではない語はクォートしません。 もう1つ注意したのが、大文字を含む名前です。PostgreSQLはクォートしない名前を小文字に変え、クォートした名前は大文字小文字を区別します。UserName を勝手に "UserName" とクォートすると、後から書くSQLでも毎回クォートが必要になります。そのため、英数字とアンダースコアだけの名前はクォートしないようにしています。 3. 主キーが2列あると、主キーが複数あると言われる ユーザーとロールの中間テーブルでは、主キーが2列になります。 CREATE TABLE user_roles ( user_id INTEGER PRIMARY KEY, role_id INTEGER PRIMARY KEY ); 列ごとに PRIMARY KEY を付けると、「主キーが2つある」という意味になります。3方言すべてで失敗しました（multiple primary keys for table "user_roles" are not allowed）。主キーの列が2つ以上あるときは、テーブルの末尾にまとめるようにしました。 CREATE TABLE user_roles ( user_id INTEGER NOT NULL, role_id INTEGER NOT NULL, PRIMARY KEY (user_id, role_id) ); 4. テーブルの並び順で失敗する 画面でテーブルを足す順番は、利用者の自由です。posts を先に作り、後から users を足して外部キーをつなぐと、DDLも posts が先になります。これも、JSONの推論と同じ理由でMySQLとPostgreSQLで失敗しました。 参照される側が先に来るように、出力時に並べ替えるようにしました。深さ優先探索で親を先に出す、普通のトポロジカルソートです。 困るのは、循環参照（a が b を参照し、b も a を参照する）です。どちらを先にしても、片方の外部キーは参照先がまだありません。この場合は、作れない側の外部キーだけを最後に ALTER TABLE で足すようにしました。 CREATE TABLE a ( ... FOREIGN KEY (b_id) REFERENCES b(id) ); -- b が先に作られている CREATE TABLE b ( id ..., a_id INTEGER ); ALTER TABLE b ADD FOREIGN KEY (a_id) REFERENCES a(id); SQLiteだけは別扱いです。SQLiteは ALTER TABLE で外部キーを足せません。その代わり、作成時に参照先を確かめないので、行内に書いたままで通ります。 5. 関数のデフォルトが文字列になる UUIDの列のデフォルトに gen_random_uuid() と入れると、修正前は DEFAULT 'gen_random_uuid()' と文字列になっていました。PostgreSQLは、この文字列をUUIDとして解釈しようとして失敗します（invalid input syntax for type uuid）。 関数呼び出しの形をしていたら、括弧で囲んで式として出すようにしました。MySQL 8.0.13以降とSQLiteは、式のデフォルトに括弧を要求するので、3方言とも括弧で揃えています。 u UUID DEFAULT (gen_random_uuid()) 同じ理由で、MySQLの TEXT と JSON の列は、リテラルのデフォルトも括弧で囲みます。 MySQL 8は、この2つの型にリテラルのデフォルトを許しません。式として書けば通ります。MariaDBはどちらでも通るので、手元の試験では確かめられませんでした。MySQLのドキュメントの記述に合わせています。 おまけ：SQL to ER が日本語のテーブル名を読めなかった 予約語のクォートを足したあと、出力を SQL to ER のパーサーに読み戻すテストを書きました。すると、日本語のテーブル名だけ読み戻せません。 const IDENT = '["]?(\\w+)["]?'; 識別子を \w（英数字とアンダースコア）で探していたのが原因でした。顧客 も、クォートした order-items も、テーブルとして見つかりません。画面では「CREATE TABLE文を入力してください」というエラーになり、しかも前に描いたER図が消えずに残っていました。 識別子の本体を「空白・引用符・括弧・区切り記号以外」に広げて、日本語のテーブル名でもER図を描けるようにしました。エラーのときには、古い図を消すようにしています。 まとめ DDLを出力するツールは、実際のデータベースに流して確かめる。 PGliteと node:sqlite なら、npmとNode.jsだけで手元で動きます 予約語は方言ごとに違う。 user はPostgreSQLだけ、lines はMySQLだけです。1つのデータベースで通っても、他で落ちます テーブルの並び順は、出力する側で面倒を見る。 外部キーの参照先が先に来るように並べ、循環参照は ALTER TABLE に逃がします 再帰で組み立てると、親が最後になる。 推論の再帰が、そのまま出力の順番になっていました 残った1パターンは、MySQLの列のデフォルトに gen_random_uuid() を入れたものです。PostgreSQLの関数なのでMySQLで通らないのは当然で、ツールの問題ではありません（MySQLなら uuid() です）。 ツールは DDLビルダー で試せます。入力した内容はブラウザの中だけで処理され、サーバーには送信されません。
pgliteにも貢献できた🙌 pglite#1175
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.
PGlite: Embeddable Postgres with real-time, reactive bindings