SQLite + Node.js

참고 :

https://www.sqlitetutorial.net/sqlite-nodejs/
https://www.npmjs.com/package/sqlite3
https://github.com/TryGhost/node-sqlite3/wiki/API

준비

1. 설치

$ npm i sqlite3

2. node에서 선언

import  sqlite3 from 'sqlite3';
sqlite3.verbose();

or

const sqlite3 = require('sqlite3').verbose();

Database 연결 / 실행 / 종료

1. Database connection

• new sqlite3.Database(filename [, mode] [, callback])
  • mode (optional): One or more of
    sqlite3.OPEN_READONLY,
    sqlite3.OPEN_READWRITE,
    sqlite3.OPEN_CREATE,
    sqlite3.OPEN_FULLMUTEX,
    sqlite3.OPEN_URI,
    sqlite3.OPEN_SHAREDCACHE,
    sqlite3.OPEN_PRIVATECACHE.
    The default value is OPEN_READWRITE | OPEN_CREATE | OPEN_FULLMUTEX.
  • filename : databasefile or ":memory:" (in-memory database)
• exapmle

const dbFile = './db/db.sqlite3'; //const dbFile = ':memory:';     //  메모리 db는 한번 close하면 데이터가 사라짐

let db = new sqlite3.Database(dbFile ,sqlite3.OPEN_READWRITE,  (err) => {    

  if(err){
    console.error(err.message)
  }
  else{
    console.log('Connected to SQLite database')
  }
})

Database Serialize

db.serialize(() => {
  db.run('CREATE TABLE MY_TABLE (info TEXT)');  // 테이블을 생성한다.

  const stmt = db.prepare('INSERT INTO MY_TABLE VALUES (?)')

  for (let i = 0; i < 10; i++) {
    stmt.run(`Item ${i}`)
  }
  stmt.finalize()
})

db.close( (err) => {
  if(err) {
    console.error(err.message);
  }
  console.log('Close the database connection.');
 })

API ( mentod )

1. Database

• all(sql [, param, ...] [, callback]) : Querying all rows

  let sql = `SELECT DISTINCT Name name FROM playlists ORDER BY name`;

  db.all(sql, [], (err, rows) => {
      if (err) {
             throw err;
      }
      rows.forEach((row) => {
          console.log(row.name);
      });
  });

• get(sql [, param, ...] [, callback]) : Query the first row

let db = new sqlite3.Database('./db/chinook.db');

let sql = `SELECT PlaylistId id,
                  Name name
           FROM playlists
           WHERE PlaylistId  = ?`;
let playlistId = 1;

// first row only
db.get(sql, [playlistId], (err, row) => {
  if (err) {
    return console.error(err.message);
  }
  return row
    ? console.log(row.id, row.name)
    : console.log(`No playlist found with the id ${playlistId}`);
});


<p data-line="109" class="sync-line" style="margin:0;"></p>

• each(sql [, param, ...] [, callback] [, complete]) :

let db = new sqlite3.Database('../db/chinook.db');

let sql = `SELECT FirstName firstName,
                  LastName lastName,
                  Email email
            FROM customers
            WHERE Country = ?
            ORDER BY FirstName`;

each(sql [, param, ...] [, callback] [, complete])
db.each(sql, ['USA'], (err, row) => {
  if (err) {
    throw err;
  }
  console.log(`${row.firstName} ${row.lastName} - ${row.email}`);
});


<p data-line="129" class="sync-line" style="margin:0;"></p>

• exec(sql [, callback]) : Runs all SQL queries in the supplied string
• prepare(sql [, param, ...] [, callback]) : Prepares the SQL statement and optionally binds the specified parameters and calls the callback when done. The function returns a Statement object
• map(sql [, callback]) : Returns results as an object instead of an array.
• loadExtension(path [, callback]) : Loads a compiled SQLite extension into the database connection object.
• interrupt()

2. Statement

• bind([param, ...] [, callback])
• reset([callback])
• finalize([callback])
• run([param, ...] [, callback])
• get([param, ...] [, callback])
• all([param, ...] [, callback])
• each([param, ...] [, callback] [, complete])
• map(sql [, callback])