Next.js Tutorial - Part 4 - API Routes using SQL Database

https://app.gitbook.com/@learnreac/s/nextjs/sqlite-su-dung-sqlite-migrations-ok

C:\Users\Administrator\Desktop\nextjs\migrations\001-abc.sql

-- Up
CREATE TABLE Person (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT,
  email TEXT
);
CREATE TABLE Vehicle (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  brand TEXT,
  model TEXT,
  ownerId INTEGER REFERENCES Person(id)
);
INSERT INTO Person (name, email) values ('bruno', '[e-mail]');
INSERT INTO Person (name, email) values ('jack', '[e-mail]');
INSERT INTO Vehicle (brand, model, ownerId) values('audi', 'R8', 1);
INSERT INTO Vehicle (brand, model, ownerId) values('audi', 'R6', 1);
INSERT INTO Vehicle (brand, model, ownerId) values('mercedes', 'benz', 2);
-- Down
DROP TABLE Person;
DROP TABLE Vehicle;

Get all vehicles

C:\Users\Administrator\Desktop\nextjs\pages\api\vehicles.ts

import { NextApiRequest, NextApiResponse } from 'next';
import sqlite from 'sqlite';
export default async function getAllVehicles(req: NextApiRequest, res: NextApiResponse) {
  const db = await sqlite.open('./mydb.sqlite');
  const vehicle = await db.all('select * from vehicle');
  res.json(vehicle);
} 

Cập nhật lại bản sqlite3

pages\api\vehicles.ts

import { NextApiRequest, NextApiResponse } from 'next';
const sqlite = require('sqlite');
const sqlite3 = require('sqlite3');
async function openDb() {
  return sqlite.open({
    filename: './pages/data/mydb.sqlite',
    driver: sqlite3.Database,
  });
}
export default async function getAllVehicles(req: NextApiRequest, res: NextApiResponse) {
  const db = await openDb();
  const vehicle = await db.all('SELECT * FROM Vehicle');
  res.json(vehicle);
} 

pages\data\mydb.sqlite

750B

mydb.zip

archive

get person by id

pages\api\person\[id]\index.ts

import { NextApiRequest, NextApiResponse } from 'next';
import sqlite from 'sqlite';
export default async function getPersonById (req: NextApiRequest, res: NextApiResponse) {
  const db = await sqlite.open('./mydb.sqlite');
  const person = await db.get('select * from person where id = ?', req.query.id);
  res.json(person);
}

Cập nhật lại

pages\api\person[id]\index.ts

import { NextApiRequest, NextApiResponse } from 'next';
const sqlite = require('sqlite');
const sqlite3 = require('sqlite3');
async function openDb() {
  return sqlite.open({
    filename: './pages/data/mydb.sqlite',
    driver: sqlite3.Database,
  });
};
export default async function getPersonById(req: NextApiRequest, res: NextApiResponse) {
  const db = await openDb();
  const person = await db.get('select * from person where id = ?', req.query.id);
  res.json(person);
} 

put person by id

C:\Users\Administrator\Desktop\nextjs\pages\api\person\[id]\index.ts

import { NextApiRequest, NextApiResponse } from 'next';
import sqlite from 'sqlite';
export default async function getPersonById (req: NextApiRequest, res: NextApiResponse) {
  const db = await sqlite.open('./mydb.sqlite');
  if (req.method === 'PUT') {
    const statement = await db.prepare('UPDATE person SET name= ?, email = ? where id = ?');
    const result = await statement.run(req.body.name,req.body.email,req.query.id);
    result.finalize();
  }
  const person = await db.get('select * from person where id = ?', req.query.id);
  res.json(person);
}

Cập nhật lại

pages\api\person[id]\index.ts

import { NextApiRequest, NextApiResponse } from 'next';
const sqlite = require('sqlite');
const sqlite3 = require('sqlite3');
async function openDb() {
  return sqlite.open({
    filename: './pages/data/mydb.sqlite',
    driver: sqlite3.Database,
  });
};
export default async function getPersonById(req: NextApiRequest, res: NextApiResponse) {
  const db = await openDb();
  if (req.method === 'PUT') {
    const statement = await db.prepare('UPDATE person SET name= ?, email = ? where id = ?');
    await statement.run(req.body.name,req.body.email,req.query.id);
  }
  const person = await db.get('select * from person where id = ?', req.query.id);
  res.json(person);
}