nextjs with typescript:11 sqliteへの接続とAPIを使ったメソッド制御

【１】モジュールインストール

sqliteとsqlite3のnpmモジュールをインストール。

yarn add sqlite
yarn add sqlite3
typesync
yarn

【２】データベースマイグレーションを仕込む

開発用のデータベースを簡単にリセットできるようにマイグレーションを仕込む。マイグレーションとはDBに保存されているデータを保持したまま、テーブルの作成やカラムの変更などを行うための機能。

■作り方
・migrationsフォルダをつくり、配下にSQLファイルを作成
・ルートフォルダ配下にマイグレーションを起動するスクリプトを用意する

【database-test.js】※開発テスト用なのでjsで作成

const sqlite = require('sqlite');
const sqlite3= require('sqlite3');


async function setup() {
   const db = await sqlite.open({filename:'./mydb.sqlite',driver: sqlite3.Database});
   await db.migrate({force: true});

   const people = await db.all('SELECT * FROM person');
   console.log('ALL PEOPLE', JSON.stringify(people, null, 2));

   const vgroups = await db.all('SELECT * FROM vgroup');
   console.log('ALL VGROUP', JSON.stringify(vgroups, null, 2));
}

setup();

【migrations/001-initial.sql】

-- Up
CREATE TABLE Vgroup (
   id INTEGER PRIMARY KEY AUTOINCREMENT,
   name TEXT
);


CREATE TABLE Person (
   id INTEGER PRIMARY KEY AUTOINCREMENT,
   name TEXT,
   details TEXT,
   vgroupId INTEGER REFERENCES Vgroup(id)
);

INSERT INTO Vgroup (name) values ('freelance');
INSERT INTO Vgroup (name) values ('VOMS');
INSERT INTO Vgroup (name) values ('holostars');

INSERT INTO Person (name, details, vgroupId) values('bafuko', 'Her eyes are red, her hair and clothes are green, and her ribbons and star symbol are yellow.', 1);
INSERT INTO Person (name, details, vgroupId) values('hourei tenten', 'Cook Maid. Fluent in Japanese, Chinese and English.', 1);
INSERT INTO Person (name, details, vgroupId) values('otome oto', 'She is on a journey to collect money.Her dream is to build a big castle and live happily with beautiful girls.', 1);
INSERT INTO Person (name, details, vgroupId) values('kurousagi uru', 'he singer who posted the video.', 1);
INSERT INTO Person (name, details, vgroupId) values('amano pikamee', 'Her soul food is donuts. She definitely eats them before any kind of competition and has quite a bit of them stocked up at home.', 2);
INSERT INTO Person (name, details, vgroupId) values('hisaka tomoshika', 'When the flame in her head goes out, her identity is gone and she dies. Quite often, the flame in her head goes out.', 2);
INSERT INTO Person (name, details, vgroupId) values('yukoku roberu', 'A bar master who loves to talk.Chanchadondon', 3);


-- Down
DROP TABLE Vgroup;
DROP TABLE Person;

◆使い方

node database-test.js

上記コマンドを実行するとmydb.sqliteが作成される。

■DB Browser for SQLite で作成状況を確認

【３】APIエンドポイントを作って接続確認

【pages/api/people.ts】

import { NextApiRequest, NextApiResponse } from 'next';
import sqlite3 from 'sqlite3';
import {open} from 'sqlite';

//personテーブルからデータを取得する
export default async function getPeople(req:NextApiRequest,res:NextApiResponse){
   
   //db接続
   const db = await open(
       {filename:'./mydb.sqlite',
       driver: sqlite3.Database}
   );

   const people = await db.all('select * from person');


   res.json(people);
}

API経由でDB接続しデータを取得ができている。

【４】ダイナミックルーティングとプレースホルダ

クエリストリングの値を取得してプレースホルダに設定すればよい。

【pages/api/vgroups/[id].tsx】指定のvgroupIdに紐づくvtuberを全取得

import { NextApiRequest, NextApiResponse } from 'next';
import sqlite3 from 'sqlite3';
import {open} from 'sqlite';

export default async function getVtubersByVgroupId(req:NextApiRequest,res:NextApiResponse){

   const db = await open(
       {   
           filename:'./mydb.sqlite',
           driver: sqlite3.Database
       }
   );

   //vgroupIdが同じものをpersonテーブルからとってくる
   const vtubers = await db.all('select * from person where vgroupId = ?', [req.query.id]);

   res.json(vtubers);
}

【５】メソッド制御のよる処理制御

【pages/api/vtuber/[id].ts】

import { NextApiRequest, NextApiResponse } from 'next';
import sqlite3 from 'sqlite3';
import {open} from 'sqlite';

export default async function getVtuberById(req:NextApiRequest,res:NextApiResponse){

   const db = await open(
       {   
           filename:'./mydb.sqlite',
           driver: sqlite3.Database
       }
   );

   if(req.method === 'PUT'){
       const statement = await db.prepare('update Person set name = ? where id = ? ');
       const result = await statement.run(req.body.name,req.query.id);

       console.log(result);

   }
   
   //idが一致したデータを取得
   const vtuber = await db.get('select * from person where id = ?', [req.query.id]);

   res.json(vtuber);
}

■postmanでBODYを作成してPUTリクエストを投げる

なおブラウザの開発ツールからも例えば、以下のようにfetchAPIを使い、PUTメソッドでJSONをなげてもいい

const res = await fetch('http://localhost:3000/api/vtuber/7',{method: 'PUT', headers:{'Content-Type':'application/json'},body:JSON.stringify({name:'oshaberi monster'})}); [Enterをおす]
await res.json(); [Enterをおす]

■補足
updateしたデータベースをリセットしたいとき、マイグレーション機能が便利。

node database-test.js

これでリセットされる。