Refs:
// Init new db
// If the database file does not exist, it is created
// This happens synchronously, which means you can start executing queries right away
const Database = require('better-sqlite3');
const db = new Database('foobar.db', { verbose: console.log /* function executed with every query */ });
// Check number of tables in database
console.log(db.prepare('SELECT * FROM sqlite_master').all());
// Retrieve the first row of results
db.prepare(`SELECT * FROM genres`).get();
db.prepare(`SELECT * FROM genres WHERE GenreId = ?`).get(25);
// Retrive all rows of results
db.prepare(`SELECT * FROM genres`).all();
db.prepare(`SELECT * FROM genres WHERE GenreId > @limit`).all({limit: 10});
// Iterate the results: retrieve the rows one by one
const stmt = db.prepare('SELECT id, name FROM cats');
for(const cat of stmt.iterate()) {
if(cat.name === 'Joey') {
console.log('found him!');
break;
}
}
// Only return the first column of any rows, rather than the entire row object
// Only on statements that return data
stmt.pluck().all(); // [1,2,3,4,5]
// Binding Anonymous Params: All of the following are the same
const stmt = db.prepare('INSERT INTO people VALUES (?, ?, ?)');
stmt.run('John', 'Smith', 45);
stmt.run(['John', 'Smith', 45]);
stmt.run(['John'], ['Smith', 45]);
// Binding Named Params: All of the following are the same
db.prepare('INSERT INTO people VALUES (@firstName, @lastName, @age)');
db.prepare('INSERT INTO people VALUES (:firstName, :lastName, :age)');
db.prepare('INSERT INTO people VALUES ($firstName, $lastName, $age)');
db.prepare('INSERT INTO people VALUES (@firstName, :lastName, $age)');
// Binding params to the statement permanently
// This method is primarily used as a performance optimization
// when you need to execute the same prepared statement many times with the same param(s)
const rows = db
.prepare(
`SELECT * FROM genres WHERE GenreId > :genreMin AND LENGTH(Name) < :nameLen`
).bind({ genreMin: 10, nameLen: 5 })
.all();
// Run the statement, only on statements that do not return data (CRUD)
const stmt = db.prepare('INSERT INTO cats (name, age) VALUES (?, ?)');
const info = stmt.run('Joey', 2);
console.log(info.changes); // => 1
// Creates a function that always runs inside a transaction.
// When the function is invoked, it will begin a new transaction.
// When the function returns, the transaction will be committed.
// If an exception is thrown, the transaction will be rolled back (and the exception will propagate as usual).
const insert = db.prepare('INSERT INTO cats (name, age) VALUES (@name, @age)');
const insertMany = db.transaction((cats) => {
for (const cat of cats) insert.run(cat);
});
insertMany([
{ name: 'Joey', age: 2 },
{ name: 'Sally', age: 4 },
{ name: 'Junior', age: 1 },
]);