sqlite3

sqlite.js

const sqlite3 = require('sqlite3').verbose()
// verbose is for debugging
// In production mode, remove verbose: const sqlite3 = require('sqlite3')
const db = new sqlite3.Database(':memory:')
// :memory: means in memory database.
// Use a file name to create a file based database
// Empty string also creates an anonymous file based database but will be destroyed after closing database.

// db.serialize guarantees each statement runs in order
db.serialize(() => {
    db.run("CREATE TABLE test (no number, info TEXT)")

    // db.prepare
    const stmt = db.prepare("INSERT INTO test VALUES (?, ?)")
    for (let i = 0; i < 10; i++) {
        stmt.run([i + 1, "something " + i])
    }
    stmt.finalize()
    // when prepared statement finishes and will never be used again, finalize to destroy it.

    // db.run
    db.run("INSERT INTO test VALUES (?, ?)", [20, "good"])
    db.run("INSERT INTO test VALUES ($no, $info)", {$no: 30, $info: "great"})

    // db.get returns first row matched
    db.get("SELECT * FROM test WHERE no < ?", [5], (err, row) => {
        console.log(row)
    })

    // db.all
    db.all("SELECT * FROM test WHERE no < ?", [5], (err, rows) => {
        console.log(rows)
    })

    // db.each
    db.each("SELECT rowid, no, info FROM test", (err, row) => {
        console.log(row)
    })
})

db.close()