ハンズオン Node.jsの7章データストレージ(sqlite3)をTypescriptで試したメモ

概要

前回の続き。
ハンズオン Node.jsの7章データストレージをtypescriptで試す。今回はsqlite。

ソース

環境

package.json

{
  "name": "node-app",
  "version": "1.0.0",
  "description": "",
  "main": "index.js",
  "scripts": {
    "start": "node ./bin/www",
    "file-system": "node ./bin/www",
    "sqlite": "node ./bin/www",
    "tsc": "tsc",
    "watch": "tsc --watch"
  },
  "keywords": [],
  "author": "",
  "license": "MIT",
  "engines": {
    "node": "14.x"
  },
  "dependencies": {
    "express": "^4.17.1",
    "sqlite3": "^5.0.0",
    "uuid": "^8.3.1"
  },
  "devDependencies": {
    "@types/express": "^4.17.9",
    "@types/sqlite3": "^3.1.6",
    "@types/uuid": "^8.3.0",
    "@typescript-eslint/eslint-plugin": "^4.8.2",
    "@typescript-eslint/parser": "^4.8.2",
    "eslint": "^7.14.0",
    "eslint-config-prettier": "^6.15.0",
    "eslint-plugin-prettier": "^3.1.4",
    "isomorphic-fetch": "^3.0.0",
    "prettier": "^2.2.0",
    "typescript": "^4.1.2"
  }
}

ソース

• promisifyしたときの型をtype PromiseDbGet = <T>(arg: string, arg2?: any) => Promise<T>としているが、もうちょっとよい方法ないだろうか

src/fqlite/index.ts

import { promisify } from 'util'
import { join } from 'path'
import type { ID, Todo, DataStorage } from '../types'
import type { RunResult, sqlite3 as Sqlite3 } from 'sqlite3'

interface TodoSQLite {
  id: ID
  title: string
  completed: 0 | 1
}
type SQLiteArgs = [sql: string, ...params: any[]]
type PromiseDbGet = <T>(arg: string, arg2?: any) => Promise<T>
type PromiseDbAll = <T>(arg: string, arg2?: any) => Promise<T>

const sqlite3: Sqlite3 =
  process.env.NODE_ENV === 'production'
    ? require('sqlite3')
    : require('sqlite3').verbose()
const db = new sqlite3.Database(join(__dirname, 'sqlite'))

const dbGet: PromiseDbGet = promisify(db.get.bind(db))

const dbRun = function (...args: SQLiteArgs) {
  return new Promise<RunResult>((resolve, reject) =>
    db.run.apply(db, [
      ...args,
      function (this: RunResult, err: any) {
        err ? reject(err) : resolve(this)
      },
    ]),
  )
}

const dbAll: PromiseDbAll = promisify(db.all.bind(db))

dbRun(`CREATE TABLE IF NOT EXISTS todo (
  id TEXT PRIMARY KEY,
  title TEXT NOT NULL,
  completed BOOLEAN NOT NULL
)`).catch((err) => {
  console.log(err)
  process.exit(1)
})

function rowToTodo(row: TodoSQLite): Todo {
  return { ...row, completed: !!row.completed }
}

const exportsObj: DataStorage<Todo> = {
  fetchAll: () =>
    dbAll<TodoSQLite[]>('SELECT * FROM todo').then((rows) =>
      rows.map(rowToTodo),
    ),
  fetchByCompleted: (completed) =>
    dbAll<TodoSQLite[]>(
      'SELECT * FROM todo WHERE completed = ?',
      completed,
    ).then((rows: TodoSQLite[]) => rows.map(rowToTodo)),
  create: async (todo) => {
    await dbRun(
      'INSERT INTO todo VALUES (?,?,?)',
      todo.id,
      todo.title,
      todo.completed,
    )
  },
  update: (id, update) => {
    const setColumns = []
    const values = []
    for (const column of ['title', 'completed'] as const) {
      if (column in update) {
        setColumns.push(` ${column} = ? `)
        values.push(update[column])
      }
    }
    values.push(id)
    return dbRun(
      `UPDATE todo SET ${setColumns.join()} WHERE id = ?`,
      values,
    ).then(({ changes }) =>
      changes === 1
        ? dbGet<TodoSQLite>('SELECT * FROM todo WHERE id = ?', id).then(
            rowToTodo,
          )
        : null,
    )
  },
  remove: (id) =>
    dbRun('DELETE FROM todo WHERE id = ?', id).then(({ changes }) =>
      changes === 1 ? id : null,
    ),
}

export default exportsObj

参考

TypeScriptの型入門
TypeScriptでthisの型を指定する
TypeScriptの型システム