Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Save dannluciano/34a3c54bbbe90ffd3778541ba0b866c0 to your computer and use it in GitHub Desktop.
Save dannluciano/34a3c54bbbe90ffd3778541ba0b866c0 to your computer and use it in GitHub Desktop.
better-sqlite3 cheatsheet

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 },
]);

Error: Cannot read property 'indexOf' of undefined

// add this to `webpack.config.js`
// if you are using `electron-vue` boilerplate,
// depends on you use `better-sqlite3` in `main` or `renderer`,
// paste this to the corresponding `webpack.[main/renderer].config.js`
externals: [
  ...
  {
    'better-sqlite3': 'commonjs better-sqlite3'
  }
  ...
],

Error: NODE_MODULE_VERSION 72. This version of Node.js requires NODE_MODULE_VERSION 73. Please try re-compiling or re-installing

// do not save better-sqlite3 in devDependencies, that case electron-rebuild will do nothing
npm install --save better-sqlite3
npm install --save-dev electron-rebuild

// Rebuilds native Node.js modules against the version of Node.js that your Electron project is using.
// This allows you to use native Node.js modules in Electron apps without your system version of Node.js matching exactly
node_modules/.bin/electron-rebuild -f -w better-sqlite3

// or this if you have more than one module that needs building like (bcypt)
node_modules/.bin/electron-rebuild
SELECT id,
code,
customer_type_id,
staff_id,
actual_discount_rate,
order_sum,
actual_order_sum,
order_content,
status,
note,
created_at,
updated_at
FROM receipts
WHERE date(created_at, 'localtime') = date('now', 'localtime')
ORDER BY created_at DESC
LIMIT 5;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment