Node.js＋TypeScriptでSQLite 3を使う

ですが、node-sqlite3はバージョンが5を超えているのにこちらの型定義はバージョン3で止まっています。
またnode-sqlite3のリポジトリもmapbox/node-sqlite3からTryGhost/node-sqlite3に移っているのですが、そのあたりの事情も反映されていない
ようです。

ここで、node-sqlite3自体のTypeScriptに関する情報を見ると、どうもnode-sqlite3自体にTypeScriptの型定義が入ったようです。

TypeScript support · Issue #809 · TryGhost/node-sqlite3 · GitHub

Added library types file by bpasero · Pull Request #1527 · TryGhost/node-sqlite3 · GitHub

こちらですね。

https://github.com/TryGhost/node-sqlite3/blob/v5.1.4/lib/sqlite3.d.ts

なので、node-sqlite3をTypeScriptから使う際には、型定義を別途インストールする必要はなさそうです。

お題

今回は、以下のお題でNode.jsからSQLite 3を使ってみたいと思います。

Jestを使ったテストコードで動作確認を行う
ソースコードはTypeScriptで書く
node-sqlite3はPromiseに対応していないので、util.promisifyを使ってPromiseで扱う
データの保存先は、メモリとファイルの2パターンを使う
扱うデータのお題は書籍とする

環境

今回の環境は、こちらです。

$ node --version
v18.12.1


$ npm --version
8.19.2

準備

まずは、Node.jsのプロジェクトを作成して

$ npm init -y
$ npm i -D typescript
$ npm i -D prettier
$ npm i -D @types/node@v18
$ npm i -D jest @types/jest
$ npm i -D esbuild esbuild-jest
$ mkdir src test

node-sqlite3をインストール。

$ npm i sqlite3

依存関係は、このようになりました。

  "devDependencies": {
    "@types/jest": "^29.2.5",
    "@types/node": "^18.11.18",
    "esbuild": "^0.16.13",
    "esbuild-jest": "^0.5.0",
    "jest": "^29.3.1",
    "prettier": "^2.8.1",
    "typescript": "^4.9.4"
  },
  "dependencies": {
    "sqlite3": "^5.1.4"
  }

scripts。

  "scripts": {
    "build": "tsc --project .",
    "build:watch": "tsc --project . --watch",
    "typecheck": "tsc --project ./tsconfig.typecheck.json",
    "typecheck:watch": "tsc --project ./tsconfig.typecheck.json --watch",
    "test": "jest",
    "format": "prettier --write src test"
  },

各種設定ファイル。

tsconfig.json

{
  "compilerOptions": {
    "target": "esnext",
    "module": "commonjs",
    "moduleResolution": "node",
    "lib": ["esnext"],
    "baseUrl": "./src",
    "outDir": "dist",
    "strict": true,
    "forceConsistentCasingInFileNames": true,
    "noFallthroughCasesInSwitch": true,
    "noImplicitOverride": true,
    "noImplicitReturns": true,
    "noPropertyAccessFromIndexSignature": true,
    "esModuleInterop": true
  },
  "include": [
    "src"
  ]
}

tsconfig.typecheck.json

{
  "extends": "./tsconfig",
  "compilerOptions": {
    "baseUrl": "./",
    "noEmit": true
  },
  "include": [
    "src", "test"
  ]
}

.prettierrc.json

{
  "singleQuote": true,
  "printWidth": 120
}

jest.config.js

module.exports = {
  testEnvironment: 'node',
  transform: {
    "^.+\\.tsx?$": "esbuild-jest"
  }
};

SQLite 3をNode.jsから使う

node-sqlite3でのソースコード例はこちら。

node-sqlite3 / Usage

APIリファレンスは、こちらです。

API · TryGhost/node-sqlite3 Wiki · GitHub

基本的な書き方はDatabase#serializeを使ったり、Database#parallelizeを使ったりすることのようです。

Control Flow · TryGhost/node-sqlite3 Wiki · GitHub

これは、同時に実行できるステートメントはひとつとするか？、それともステートメントを並列に実行可能とするか？といった選択になります。

ですが、デフォルトの実行モードは並列になっているようなので、今回はPromiseを使うこともあってこれらのモードは使わないことにします。

The default mode is to execute statements in parallel.

ここからは、SQLite 3をインメモリーとファイルそれぞれで使うパターンで書いていきますが、共通で使う型定義を先に書いておきます。

test/index.d.ts

type Book = {
  isbn: string;
  title: string;
  price: number;
};

type BookRow = {
  isbn: string;
  title: string;
  price: number;
};

type CountRow = {
  count: number;
};

インメモリーで使う

SQLite 3をNode.js＋TypeScriptからインメモリーで使ってみます。

作成したテストコードは、こちら。

test/in-memory.test.ts

import { Database } from 'sqlite3';
import util from 'node:util';

test('in-memory database test', async () => {
  const database = new Database(':memory:');

  // create table
  // Database#run
  await util.promisify(database.run).call(
    database,
    `\
create table book(
  isbn text,
  title text,
  price integer,
  primary key(isbn)
)`
  );

  const books: Book[] = [
    {
      isbn: '978-4873119700',
      title: 'JavaScript 第7版',
      price: 5060,
    },
    {
      isbn: ' 978-4295010562',
      title: 'JavaScriptモダンプログラミング完全ガイド [堅牢なコードを効率的に開発できる! ] (impress top gearシリーズ)',
      price: 3300,
    },
    {
      isbn: '978-4873117836',
      title: '初めてのJavaScript 第3版 ―ES2015以降の最新ウェブ開発',
      price: 3520,
    },
    {
      isbn: '978-4873119236',
      title: 'ハンズオンNode.js',
      price: 4070,
    },
    {
      isbn: '978-4873119045',
      title: 'プログラミングTypeScript ―スケールするJavaScriptアプリケーション開発',
      price: 3740,
    },
  ];

  // insert
  // Statement#run
  const insertStatement = database.prepare('insert into book(isbn, title, price) values(?, ?, ?)');

  for (const book of books) {
    await util
      .promisify(insertStatement.run.bind(insertStatement, [book.isbn, book.title, book.price]))
      .call(insertStatement);
  }

  insertStatement.finalize();

  // select
  // Database#all
  const countResult = (await util
    .promisify(database.all)
    .call(database, 'select count(*) as count from book')) as CountRow[];
  expect(countResult[0].count).toBe(5);

  const javascript7Result = (await util
    .promisify(database.all.bind(database, 'select isbn, title, price from book where isbn = ?', '978-4873119700'))
    .call(database)) as BookRow[];

  expect(javascript7Result[0].title).toBe('JavaScript 第7版');
  expect(javascript7Result[0].price).toBe(5060);

  // select
  // Statement#all
  const queryStatement = database.prepare('select isbn, title, price from book where price > ? order by price desc');
  const booksResults = (await util
    .promisify(queryStatement.all.bind(queryStatement, 4000))
    .call(queryStatement)) as BookRow[];

  expect(booksResults).toHaveLength(2);
  expect(booksResults[0].title).toBe('JavaScript 第7版');
  expect(booksResults[1].title).toBe('ハンズオンNode.js');

  queryStatement.finalize();

  await util.promisify(database.close).call(database);
});

ちょっとずつ説明を。

Databaseのコンストラクタに:memory:を指定すると、インメモリーデータベースになります。

  const database = new Database(':memory:');

Database#runを使って、ステートメントを実行。ここでは、テーブルを作成しています。

  // create table
  // Database#run
  await util.promisify(database.run).call(
    database,
    `\
create table book(
  isbn text,
  title text,
  price integer,
  primary key(isbn)
)`
  );

util.promisifyを使ってnode-sqlite3をPromiseに対応させていますが、このあとの関数呼び出し時にthisを指定しないと動かなくなるので、
call（applyでもいいですが）やbindで都度thisに相当するものを指定することになりました…。

ステートメントは、Databaseで直接実行するだけでなく、Statementを作成してからの実行もできます。

  // insert
  // Statement#run
  const insertStatement = database.prepare('insert into book(isbn, title, price) values(?, ?, ?)');

  for (const book of books) {
    await util
      .promisify(insertStatement.run.bind(insertStatement, [book.isbn, book.title, book.price]))
      .call(insertStatement);
  }

  insertStatement.finalize();

Statementの場合、使い終わったらStatement#finalizeしましょう。

Database、Statementを使ったselect文実行の例。

  // select
  // Database#all
  const countResult = (await util
    .promisify(database.all)
    .call(database, 'select count(*) as count from book')) as CountRow[];
  expect(countResult[0].count).toBe(5);

  const javascript7Result = (await util
    .promisify(database.all.bind(database, 'select isbn, title, price from book where isbn = ?', '978-4873119700'))
    .call(database)) as BookRow[];

  expect(javascript7Result[0].title).toBe('JavaScript 第7版');
  expect(javascript7Result[0].price).toBe(5060);

  // select
  // Statement#all
  const queryStatement = database.prepare('select isbn, title, price from book where price > ? order by price desc');
  const booksResults = (await util
    .promisify(queryStatement.all.bind(queryStatement, 4000))
    .call(queryStatement)) as BookRow[];

  expect(booksResults).toHaveLength(2);
  expect(booksResults[0].title).toBe('JavaScript 第7版');
  expect(booksResults[1].title).toBe('ハンズオンNode.js');

  queryStatement.finalize();

結果を一括で受け取る時は、Database#allまたはStatement#allを使います。
結果の行ごとにコールバックを受け取る時は、Database#eachまたはStatement#eachを使います。

今回は、一律でallの方を使いました。

なぜかbindを使った後でcallを使ったりしているのですが、これはDatabase#allやStatement#allがオーバーロードされていて、
バインドするパラメーターを取るようなパターンの呼び出しがうまく解決できなかったからです…。

  const javascript7Result = (await util
    .promisify(database.all.bind(database, 'select isbn, title, price from book where isbn = ?', '978-4873119700'))
    .call(database)) as BookRow[];

最後は、Databaseをクローズしておきます。

  await util.promisify(database.close).call(database);

今回の使い方はインメモリーデータベースとなるので、プログラムが終了するとデータもなくなります。

ファイルで使う

次は、データの保存先をファイルにします。保存先のディレクトリを作成。

$ mkdir data

テストコードは、こちら。

test/file-database.test.ts

import { Database } from 'sqlite3';
import util from 'node:util';

test('file database test', async () => {
  const database = new Database('data/test.db');

  // drop table if exists
  // Database#run
  await util.promisify(database.run).call(database, 'drop table if exists book');

  // create table
  // Database#run
  await util.promisify(database.run).call(
    database,
    `\
create table book(
  isbn text,
  title text,
  price integer,
  primary key(isbn)
)`
  );

  const books: Book[] = [
    {
      isbn: '978-4873119700',
      title: 'JavaScript 第7版',
      price: 5060,
    },
    {
      isbn: ' 978-4295010562',
      title: 'JavaScriptモダンプログラミング完全ガイド [堅牢なコードを効率的に開発できる! ] (impress top gearシリーズ)',
      price: 3300,
    },
    {
      isbn: '978-4873117836',
      title: '初めてのJavaScript 第3版 ―ES2015以降の最新ウェブ開発',
      price: 3520,
    },
    {
      isbn: '978-4873119236',
      title: 'ハンズオンNode.js',
      price: 4070,
    },
    {
      isbn: '978-4873119045',
      title: 'プログラミングTypeScript ―スケールするJavaScriptアプリケーション開発',
      price: 3740,
    },
  ];

  // insert
  // Statement#run
  const insertStatement = database.prepare('insert into book(isbn, title, price) values(?, ?, ?)');

  for (const book of books) {
    await util
      .promisify(insertStatement.run.bind(insertStatement, [book.isbn, book.title, book.price]))
      .call(insertStatement);
  }

  insertStatement.finalize();

  // select
  // Database#all
  const countResult = (await util
    .promisify(database.all)
    .call(database, 'select count(*) as count from book')) as CountRow[];
  expect(countResult[0].count).toBe(5);

  const javascript7Result = (await util
    .promisify(database.all.bind(database, 'select isbn, title, price from book where isbn = ?', '978-4873119700'))
    .call(database)) as BookRow[];

  expect(javascript7Result[0].title).toBe('JavaScript 第7版');
  expect(javascript7Result[0].price).toBe(5060);

  // select
  // Statement#all
  const queryStatement = database.prepare('select isbn, title, price from book where price > ? order by price desc');
  const booksResults = (await util
    .promisify(queryStatement.all.bind(queryStatement, 4000))
    .call(queryStatement)) as BookRow[];

  expect(booksResults).toHaveLength(2);
  expect(booksResults[0].title).toBe('JavaScript 第7版');
  expect(booksResults[1].title).toBe('ハンズオンNode.js');

  queryStatement.finalize();

  await util.promisify(database.close).call(database);
});

インメモリーの時とほぼ同じテストコードですが、以下の2点が異なります。

まず、Databaseのコンストラクタに指定する値が、データの保存先のファイルパスになります。

  const database = new Database('data/test.db');

次に、テストを2回以降実行するとテーブル定義やデータが残っていることになるので、今回はすでにテーブルが存在していた場合は
dropするようにしました。

  // drop table if exists
  // Database#run
  await util.promisify(database.run).call(database, 'drop table if exists book');

これでテストを実行すると、指定したファイルパスにファイルが作成され、データが保存されます。

$ ll data
合計 20
drwxrwxr-x 2 xxxxx xxxxx  4096  1月  3 22:39 ./
drwxrwxr-x 6 xxxxx xxxxx  4096  1月  3 21:37 ../
-rw-r--r-- 1 xxxxx xxxxx 12288  1月  3 22:39 test.db

こんなところでしょうか。

まとめ

Node.js（とTypeScript）からSQLite 3を使ってみました。

node-sqlite3とTypeScriptに関する事情だったり、Promiseを使おうとしたあたりにだいぶてこずりましたが、とりあえずはなんとかなりました…。

けっこう大変だったので、途中で諦めようかなと思いかけましたが。
でも、実際使うならなにか別のデータベースアクセスライブラリを介してアクセスした方が良い感じがしますね。
PrismaやTypeORMあたりでしょうか。