[SQLITE] Sử dụng sqlite migrations (ok)

C:\Users\Administrator\Desktop\part4\mydb.sqlite

871B

mydb.zip

archive

C:\Users\Administrator\Desktop\part4\database-test.js

const sqlite = require('sqlite');

async function setup() {
    const db = await sqlite.open('./mydb.sqlite');
    await db.migrate({force: 'last'});

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

    const vehicles = await db.all('SELECT * FROM vehicle');
    console.log('ALL VEHICLES', JSON.stringify(vehicles, null, 2));
}

setup();

Fix sqlite

database-test.js

const sqlite3 = require('sqlite3');
const sqlite = require('sqlite');
async function openDb() {
  return sqlite.open({
    filename: './mydb.sqlite',
    driver: sqlite3.Database,
  });
}
async function setup() {
  const db = await openDb();
  const people = await db.all('SELECT * FROM Person');
  console.log('all person', JSON.stringify(people, null, 2));
  const vehicle = await db.all(`SELECT a.*, b.* FROM Person as a LEFT JOIN Vehicle as b ON a.id = b.ownerId`);
  console.log('all vehicles', JSON.stringify(vehicle, null, 2));
}
setup();

Hoặc nếu để trong folder data

data\database-test.js

const sqlite3 = require('sqlite3');
const sqlite = require('sqlite');
async function openDb() {
  return sqlite.open({
    filename: './data/mydb.sqlite',
    driver: sqlite3.Database,
  });
}
async function setup() {
  const db = await openDb();
  const people = await db.all('SELECT * FROM Person');
  console.log('all person', JSON.stringify(people, null, 2));
  const vehicle = await db.all(`SELECT a.*, b.* FROM Person as a LEFT JOIN Vehicle as b ON a.id = b.ownerId`);
  console.log('all vehicles', JSON.stringify(vehicle, null, 2));
}
setup();

C:\Users\Administrator\Desktop\part4\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;

C:\Users\Administrator\Desktop\part4\package.json

{
  "name": "youtube-2020-march-nextjs-part4",
  "version": "0.1.0",
  "private": true,
  "scripts": {
    "dev": "node ./database-test.js && next dev",
    "build": "next build",
    "start": "next start",
    "mock-server": "json-server --watch ./db.json -p 4001 -d 1500"
  },
  "dependencies": {
    "isomorphic-unfetch": "3.0.0",
    "next": "9.2.1",
    "react": "16.12.0",
    "react-dom": "16.12.0",
    "sqlite": "3.0.3",
    "swr": "0.1.17"
  },
  "devDependencies": {
    "@types/node": "13.7.7",
    "@types/react": "16.9.23",
    "json-server": "0.16.1",
    "typescript": "3.8.3"
  }
}