在Node.js中使用SQLite資料庫

在Node.js中要使用SQLite資料庫必須要安裝 sqlite3模組。

$>npm install sqlite3

建立資料庫

  • 在記憶體中建立資料庫
var sqlite3 = require('sqlite3').verbose();
var db = new sqlite3.Database(':memory:');
  • 將資料庫建立於檔案中
var sqlite3 = require('sqlite3').verbose();
var db = new sqlite3.Database('testDB.db');

建立資料表

db.serialize(function () {
  db.run("CREATE TABLE Test (col1, col2, col3)");

  db.run("INSERT INTO Test VALUES (?, ?, ?)", ['a1', 'b1', 'c1']);
  db.run("INSERT INTO Test VALUES (?, ?, ?)", ['a2', 'b2', 'c2']);
  db.run("INSERT INTO Test VALUES (?, ?, ?)", ['a3', 'b3', 'c3']);

  db.each("SELECT * FROM Test", function (err, row) {
    console.log(row);
  });
});
db.close();

select 指令

db.serialize(function () {
  db.each("SELECT * FROM Test", function (err, row) {
    console.log(row);
  });
});
import express from 'express';
import db from 'sqlite';                                       // <=
import Promise from 'bluebird';

const app = express();
const port = process.env.PORT || 3000;

app.get('/posts', async (req, res, next) => {
  try {
    const posts = await db.all('SELECT * FROM Post LIMIT 10'); // <=
    res.send(posts);
  } catch (err) {
    next(err);
  }
});

Promise.resolve()
  // First, try to open the database
  .then(() => db.open('./database.sqlite', { Promise }))      // <=
  // Update db schema to the latest version using SQL-based migrations
  .then(() => db.migrate({ force: 'last' }))                  // <=
  // Display error message if something went wrong
  .catch((err) => console.error(err.stack))
  // Finally, launch the Node.js app
  .finally(() => app.listen(port));