TypeScript + Bun + SQLite な環境にて、SQLのDDLをDrizzle ORM で書いてみたり、初期データの投入(seed)をしてみた

前回の記事では Drizzle ORM のマイグレーション機能を中心に色々試していました。
TypeScript + Bun + SQLite + Drizzle ORM な環境にて、Drizzle Kit の各コマンドを試してみた - メモ的な思考的な

今回は SQLite に対して、SQL の DDL を実行したり、初期データの投入(seed)をしてみたりします。

　
なお、Drizzle ORMでSQLのDDLを実行する方法は、Githubリポジトリ「Drizzle ORM | SQLite」のREADMEにも詳しく書かれています。
https://github.com/drizzle-team/drizzle-orm/blob/main/drizzle-orm/src/sqlite-core/README.md

また、制約やインデックスについては、Drizzle ORMの公式ドキュメントにも記載があります。
Drizzle ORM - Indexes & Constraints

　
目次

環境

• Windows11 WSL2
• TypeScript 5.4.5
• Bun 1.1.6
• SQLite
• Drizzle ORM 0.30.9
• Drizzle Kit 0.20.17

準備

前回の記事の続きから、実装を進めます。

ただ、前回は結果も記録したかったため、SQLiteファイルもコミットしてしまっていました。

　
そこで、今回は前回とは別のSQLiteファイル my_data.db を使用するよう、以下の2ファイルを変更しておきます。

migrate.ts

const sqlite = new Database("my_data.db")

　
drizzle.config.ts

export default {
  // ...
  dbCredentials: {
    url: "./my_data.db"
  }
} satisfies Config

主キーについて

ドキュメントによると、単一主キー・複合主キーとも設定できそうでした。
https://orm.drizzle.team/docs/indexes-constraints#primary-key

主キーなし

まずは主キーなしのテーブルを作ってみます。

スキーマ no_pks.ts を用意します。

import { text, sqliteTable } from 'drizzle-orm/sqlite-core'

export const noPks = sqliteTable("no_pks", {
  name: text("name"),
})

　
続いて drizzle-kit generate:sqlite を実行すると、以下のSQLを持つマイグレーションファイルが自動生成されました。

CREATE TABLE `no_pks` (
    `name` text
);

　
bun run migrate にてマイグレーションを実行すると、SQLiteにテーブルができました。

単一主キーあり

READMEより、Drizzle ORMでは primaryKey() を使うことで、単一主キーを設定できそうでした。
https://github.com/drizzle-team/drizzle-orm/blob/0.30.9/drizzle-orm/src/sqlite-core/README.md#column-types

サロゲートキーを主キーにする

今回はORMでよく見かけるサロゲートキーを主キーにしてみます。

なお、SQLiteのinteger型で自動インクリメント & 採番した番号は再利用しない目的で、 autoIncrement: true を追加しています。
SQLiteで主キーにAUTOINCREMENTを指定すると遅くなる #C# - Qiita

スキーマはこんな感じです。

import { text, integer, sqliteTable } from 'drizzle-orm/sqlite-core'

export const writers = sqliteTable("writers", {
  id: integer("id", { mode: "number" }).primaryKey({ autoIncrement: true }),
  name: text("name"),
})

　
スキーマを元に生成されるマイグレーションファイルです。

CREATE TABLE `writers` (
    `id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
    `name` text
);

　
マイグレーションすると、 writers テーブルができました。

SQLiteでは、後から主キーを追加できない

本題からはずれますが、SQLiteの公式ドキュメントに

4. ALTER TABLE ADD COLUMN

The ADD COLUMN syntax is used to add a new column to an existing table. The new column is always appended to the end of the list of existing columns. The column-def rule defines the characteristics of the new column. The new column may take any of the forms permissible in a CREATE TABLE statement, with the following restrictions:

• The column may not have a PRIMARY KEY or UNIQUE constraint.
• ...

https://www.sqlite.org/lang_altertable.html

とあったため、試してみます。

　
まずは主キーのない既存のスキーマに、主キーを追加します。

export const writers = sqliteTable("no_pks", {
  id: integer("id", { mode: "number" }).primaryKey({ autoIncrement: true }),  // 追加
  name: text("name"),
})

　
続いて generate:sqlite すると、以下のSQLを持つマイグレーションファイルが生成されました。

ALTER TABLE writers ADD `id` integer PRIMARY KEY NOT NULL;

　
最後に、 migrate したところエラーが表示されました。公式ドキュメント通りです。

$ bun run migrate.ts
...
DrizzleError: Failed to run the query 'ALTER TABLE no_pks ADD `id` integer PRIMARY KEY NOT NULL;'
...
SQLiteError: Cannot add a PRIMARY KEY column

複合主キーあり

READMEより、Drizzle ORMでは compositePk を使うことで、複合主キーを設定できそうでした。
https://github.com/drizzle-team/drizzle-orm/blob/0.30.9/drizzle-orm/src/sqlite-core/README.md#customizing-column-data-type

　
今回は、複合自然キーができるか試してみます。

複合自然キーの例として、今回は住所を使います。例えば 伊達市 の住所を特定するには都道府県も持っておく必要があります。

• 「サロゲートキー vs 複合キー」という間違った対立 - ネットの海の片隅で
• 「他と同じ名前の市」があったら紛らわしいけど、作ってもいいの？ - ねとらぼ
• 同名の市区町村を調べる #住所 - Qiita

　
まず、 都道府県 と 市区町村 を複合自然キーとするスキーマ addresses.ts を用意します。

ちなみに、複合主キーを作りたい場合、READMEには compositePk: primaryKey(pkExample.id, pkExample.name) と書かれています。

ただ、v0.30.9ではその書き方はdeprecatedです。
https://github.com/drizzle-team/drizzle-orm/blob/0.30.9/drizzle-orm/src/sqlite-core/primary-keys.ts#L11

その代わり、 compositePk: primaryKey({columns: [address.prefecture, address.municipality]}) という書き方にすればよいようです。

　
スキーマの全体は以下です。

import {primaryKey, sqliteTable, text} from 'drizzle-orm/sqlite-core'

export const addresses = sqliteTable("addresses", {
  prefecture: text("prefecture"),
  municipality: text("municipality"),
}, (address) => ({
  compositePk: primaryKey({columns: [address.prefecture, address.municipality]})
}))

　
スキーマから生成されたマイグレーションファイルはこちら。

CREATE TABLE `addresses` (
    `prefecture` text,
    `name` text,
    PRIMARY KEY(`name`, `prefecture`)
);

　
マイグレーションすると、複合主キーを持ったテーブル addresses ができました。

制約

次に色々な制約をためしてみます。

　
なお、SQLiteでは ALTER TABLE が限定的なサポートになっています。
SQL Features That SQLite Does Not Implement

そこで、制約を定義する場合に Drizzle ORM がどこまでサポートしてくれるのかも確認していきます。

NOT NULL制約

ドキュメントより、Drizzle ORMでは notNull() を使うことで NOT NULL制約を追加できそうでした。

• https://orm.drizzle.team/docs/indexes-constraints#not-null
• https://github.com/drizzle-team/drizzle-orm/blob/0.30.9/drizzle-orm/src/sqlite-core/README.md#column-types

既存のテーブルに、NOT NULL 制約ありの列を追加

スキーマでは、 notNull() メソッドを使って NOT NULL 制約を追加します。

export const writers = sqliteTable("writers", {
  pseudonym: text("pseudonym").notNull()  // 追加
})

　
マイグレーションファイルを生成します。

ALTER TABLE writers ADD `pseudonym` text NOT NULL;

　
マイグレーションすると、 writers テーブルに NOT NULL 制約付きで pseudonym が追加されました。

WebStormのDatabase Toolsで見るとこんな感じです。

既存のテーブル・列への NOT NULL 制約追加は自動生成不可

SQLiteの場合、ALTER TABLE 機能はあるものの、ALTER COLUMN はないようです。

• https://www.sqlite.org/lang_altertable.html
• ALTER COLUMN in sqlite - Stack Overflow

　
とはいえ、Drizzle ORM ではどうなるかを試してみます。

今回は、既存のテーブル writers の列 name に NOT NULL 制約を追加してみます。

まずはスキーマを修正します。

export const writers = sqliteTable("writers", {
  name: text("name").notNull()  // NOT NULL制約を追加
})

　
マイグレーションファイルを生成します。

$ drizzle-kit generate:sqlite                                                                                                                                                                           

drizzle-kit: v0.20.17
drizzle-orm: v0.30.9
...
[✓] Your SQL migration file ➜ drizzle/0006_flimsy_proteus.sql 🚀

　
生成されたマイグレーションファイルを見てみると、コメントが記載されていました。もし実装したい場合は、自分で実装する必要があるようです。

/*
 SQLite does not support "Set not null to column" out of the box, we do not generate automatic migration for that, so it has to be done manually
 Please refer to: https://www.techonthenet.com/sqlite/tables/alter_table.php
                  https://www.sqlite.org/lang_altertable.html
                  https://stackoverflow.com/questions/2083543/modify-a-columns-type-in-sqlite3

 Due to that we don't generate migration automatically and it has to be done manually
*/

UNIQUE制約

ドキュメントより、Drizzle ORMでは uniqueIndex() を使うことで UNIQUE制約を追加できそうでした。

• https://orm.drizzle.team/docs/indexes-constraints#unique
• https://github.com/drizzle-team/drizzle-orm/blob/0.30.9/drizzle-orm/src/sqlite-core/README.md#customizing-column-data-type

　
今回は、列 pseudonym に UNIQUE 制約を追加してみます。

また、UNIQUE制約を付与する際のインデックス名は unique_pseudonym とします。

export const writers = sqliteTable("writers", {
  // ...
  pseudonym: text("pseudonym").notNull()  // この列に対し、UNIQUE制約を追加
}, (writer) => ({
  uniqueIdx: uniqueIndex("unique_pseudonym").on(writer.pseudonym)
}))

　
マイグレーションファイルを生成したところ、以下のSQLができました。SQLite的にはIndexの生成のようです。

• https://www.sqlite.org/lang_createtable.html#unique_constraints
• https://www.sqlite.org/lang_createindex.html

CREATE UNIQUE INDEX `unique_pseudonym` ON `writers` (`pseudonym`);

　
マイグレーションすると、UNIQUE 制約の index ができていました。

v0.30.9 時点では、CHECK制約は未実装

ドキュメントでは、CHECK制約は未実装と書かれていました。
https://orm.drizzle.team/docs/indexes-constraints#check

次にGithubを見たところ、CHECK 制約に関するissueがOpenのままでした。
[FEATURE]: Add check support in drizzle-kit · Issue #880 · drizzle-team/drizzle-orm

ちなみに、上記 issue には現時点での回避策も記載されていました。 default() の中で生SQLを書くと対応できるケースもあるようです。
https://github.com/drizzle-team/drizzle-orm/issues/880#issuecomment-1814869720

DEFAULT制約

READMEより、Drizzle ORMでは default() を使うことで DEFAULT 制約の追加ができそうでした。
https://github.com/drizzle-team/drizzle-orm/blob/0.30.9/drizzle-orm/src/sqlite-core/README.md#column-types

ただ、SQLiteには ALTER COLUMN がないことから、今回は列を追加する時にDEFAULT制約を設定してみます。

　
まずは、デフォルト値として空文字を設定する comment 列をスキーマへ追加します。

export const writers = sqliteTable("writers", {
  comment: text("comment").default(""),  // 列を追加

　
マイグレーションファイルを生成したところ、以下のSQLが生成されました。

ALTER TABLE writers ADD `comment` text DEFAULT '';

　
マイグレーションすると、DEFAULT値を持つ列 comment が追加されていました。

外部キー制約

ドキュメントによると、Drizzle ORMでは単一・複合・自己参照の各外部キーが設定できるようです。

• https://orm.drizzle.team/docs/indexes-constraints#foreign-key
• https://github.com/drizzle-team/drizzle-orm/blob/0.30.9/drizzle-orm/src/sqlite-core/README.md#customizing-column-data-type

単一外部キー

単一外部キーを試すために、2つのスキーマを作成します。

まずは、参照先の publishers を作成します。

export const publishers = sqliteTable("publishers", {
  id: integer("id", { mode: "number" }).primaryKey({ autoIncrement: true }),
  name: text("name"),
})

　
続いて外部キー制約のある books を作成します。

import {integer, sqliteTable, text} from "drizzle-orm/sqlite-core";
import {publishers} from "./publishers.ts";

export const books = sqliteTable("books", {
  id: integer("id", { mode: "number" }).primaryKey({ autoIncrement: true }),
  name: text("name"),
  publisherId: integer("publisher_id").references(() => publishers.id) 
})

　
生成されたマイグレーションファイルには、以下のSQLが記載されていました。

CREATE TABLE `books` (
    `id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
    `name` text,
    `publisher_id` integer,
    FOREIGN KEY (`publisher_id`) REFERENCES `publishers`(`id`) ON UPDATE no action ON DELETE no action
);
--> statement-breakpoint
CREATE TABLE `publishers` (
    `id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
    `name` text
);

　
マイグレーションすると、2つのテーブルができていました。

publishers テーブルはこちら。

　
books テーブルには外部キーが設定されています。

複合外部キー

SQLiteやDrizzle ORM では複合外部キーもサポートされているので、試してみます。

• https://www.sqlite.org/foreignkeys.html#fk_composite
• https://orm.drizzle.team/docs/indexes-constraints#foreign-key

新規テーブル作成時に複合外部キーを設定

新規テーブル shops を追加し、 addresses テーブルへの複合外部キーを設定してみます。

なお、ソースコードによると foreignKey() はコールバックを使うと deprecated のようなので、オブジェクトを渡す形にします。
https://github.com/drizzle-team/drizzle-orm/blob/0.30.9/drizzle-orm/src/sqlite-core/foreign-keys.ts#L101

export const shops = sqliteTable("shops", {
  id: integer("id", { mode: "number" }).primaryKey({ autoIncrement: true }),
  name: text("name"),
  prefecture: text("prefecture"),
  municipality: text("municipality"),
}, (table) => (
  {
    fk: foreignKey({
      columns: [table.prefecture, table.municipality],
      foreignColumns: [addresses.prefecture, addresses.municipality],
      name: "address_names"
    })
  }
))

　
生成されたマイグレーションファイルには、以下のSQLが記載されていました。

CREATE TABLE `shops` (
    `id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
    `name` text,
    `prefecture` text,
    `municipality` text,
    FOREIGN KEY (`prefecture`,`municipality`) REFERENCES `addresses`(`prefecture`,`municipality`) ON UPDATE no action ON DELETE no action
);

　
マイグレーションすると shops テーブルに外部複合キーが設定されていました。

既存のテーブルには外部複合キーを設定できない

続いて、既存の publishers テーブルから addresses テーブルへの複合外部キーを設定してみます。

pulishers テーブルに列と外部キーを追加するよう、スキーマを修正します。

export const publishers = sqliteTable("publishers", {
  // ...
  // 以下を追加
  prefecture: text("prefecture"),
  municipality: text("municipality"),
}, (table) => (
  {
    fk: foreignKey({
      columns: [table.prefecture, table.municipality],
      foreignColumns: [addresses.prefecture, addresses.municipality],
      name: "address_names"
    })
  }
))

　
生成されたマイグレーションファイルには、以下のSQLが記載されていました。

SQLは出力されているものの、複合外部キーを設定するものとは異なっています。

ALTER TABLE publishers ADD `prefecture` text REFERENCES addresses(prefecture,municipality);--> statement-breakpoint
ALTER TABLE publishers ADD `municipality` text REFERENCES addresses(prefecture,municipality);--> statement-breakpoint
/*
 SQLite does not support "Creating foreign key on existing column" out of the box, we do not generate automatic migration for that, so it has to be done manually
 Please refer to: https://www.techonthenet.com/sqlite/tables/alter_table.php
                  https://www.sqlite.org/lang_altertable.html

 Due to that we don't generate migration automatically and it has to be done manually
*/

　
マイグレーションすると、以下のエラーになりました。あとから複合外部キーは設定できなさそうです。

SQLiteError: foreign key on prefecture should reference only one column of table addresses

自己参照外部キー

Drizzle ORMのドキュメントを参考に、新規テーブル作成時に自己参照外部キーを設定してみます。
https://orm.drizzle.team/docs/indexes-constraints#foreign-key

既存テーブルへの追加については、他の制約同様設定できなさそうなので、今回は省略します。

　
まずは自己参照外部キーを設定する organizers のスキーマを作成します。

parentId が自己参照外部キーです。

import {type AnySQLiteColumn, integer, sqliteTable, text} from "drizzle-orm/sqlite-core";

export const organizations = sqliteTable("organizations", {
  id: integer("id", { mode: "number" }).primaryKey({ autoIncrement: true }),
  name: text("name"),
  parentId: integer("parent_id").references((): AnySQLiteColumn => organizations.id),
})

　
生成されたマイグレーションファイルには、以下のSQLが記載されていました。

stackoverflowの回答と同じようなSQLになっています。
https://stackoverflow.com/questions/6516066/recursive-foreign-keys-in-sqlite

CREATE TABLE `organizations` (
    `id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
    `name` text,
    `parent_id` integer,
    FOREIGN KEY (`parent_id`) REFERENCES `organizations`(`id`) ON UPDATE no action ON DELETE no action
);

　
マイグレーションすると、自己参照外部キーが設定されている organizations テーブルができました。

外部キー制約のActionについて

Drizzle ORM のドキュメントによると、外部キー制約のActionも定義できるようです。
https://orm.drizzle.team/docs/rqb#foreign-key-actions

また、デフォルトのActionは

• NO ACTION : This is the default action

とのことで、今まで生成された外部キーのSQLにあった ON UPDATE no action ON DELETE no action という定義と一致しています。

　
では実際に試してみます。(現実的にはあまりないケースですが) 今回は生成されるSQLを確認したいため、

• 更新時は SET NULL
• 削除時は CASCADE

と設定を分けてみます。

　
members テーブル新規作成するスキーマ members.ts を作成します。

import {integer, sqliteTable, text} from "drizzle-orm/sqlite-core";
import {organizations} from "./organizations.ts";

export const members = sqliteTable("members", {
  id: integer("id", { mode: "number" }).primaryKey({ autoIncrement: true }),
  name: text("name"),
  organizerId: integer("organizer_id")
    .references(() => organizations.id, {
      onUpdate: 'set null',
      onDelete: 'cascade',
    })
})

　
生成されたマイグレーションファイルには、以下のSQLが記載されていました。

ON UPDATE と ON DELETE にスキーマで指定した値が設定されています。

CREATE TABLE `members` (
    `id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
    `name` text,
    `organizer_id` integer,
    FOREIGN KEY (`organizer_id`) REFERENCES `organizations`(`id`) ON UPDATE set null ON DELETE cascade
);

　
マイグレーション後、Database Toolで確認すると、スキーマの設定が反映されていました。

列のデータ型について

公式ドキュメントにまとまっているため、今回は特にふれません。
Drizzle ORM - SQLite column types

初期データの投入 (Seed)

v0.30.9 時点では自分で実装する

Drizzle ORMやDrizzle Kitのドキュメントを探しましたが、seedについては記載が見当たりませんでした。

そのため、以下の記事や動画のように自分でseedするプログラムを書くことになりそうです。

• Seeding Database With Drizzle ORM - DEV Community
• Drizzle ORM #8: Seeding - YouTube

　
実際にためしてみます。

ルートディレクトリに seed.ts を作り、上記記事を参考に実装します。

ちなみに、 insert する時に await がないとテーブルにデータが保存されません。

import {Database} from "bun:sqlite";
import {drizzle} from "drizzle-orm/bun-sqlite";
import {publishers} from "./src/schema/publishers.ts";

const main = async () => {
  const sqlite = new Database("my_data.db")
  const db = drizzle(sqlite)

  const data: (typeof publishers.$inferInsert)[] = [
    { name: "foo" },
    { name: "bar" },
  ]

  console.log("start ------------->")
  await db.insert(publishers).values(data)
  console.log("<----------------end")
}

main()

　
package.json に seed 用の scripts 設定を追加します。

なお、今回は Bun を使っているため、 TypeScript ファイルを直接実行できます。
bun run – Runtime | Bun Docs

"scripts": {
  "seed": "bun run seed.ts"
},

　
最後にコマンドを実行します。

# 実行
$ bun run seed

# ログ
$ bun run seed.ts
start ------------->
<----------------end

　
SQLiteファイルを開くと、seed.tsの内容が保存されていました。

ちなみに、何回か繰り返しているので、 id 列は 1 始まりではないです。

ソースコード

Githubに上げました。
https://github.com/thinkAmi-sandbox/drizzle_with_bun-example

今回のプルリクはこちら。
https://github.com/thinkAmi-sandbox/drizzle_with_bun-example/pull/2