This is a memo on how to use SQLite with JavaScript.
We did not elaborate too much, but focused on the minimum necessary functions.
I will not go into the usage of SQLite itself, but please refer to this way (direction close to the speaker or towards the speaker) for more information.
install
Package "sqlite3" was used.
npm install sqlite3
SQLite binaries are also installed, so it is not necessary to install SQLite, but it is better to install SQLite separately because the SQLite command line tool is useful for some purposes.
document
There is an "API documentation" link on the package's Github page where you can get more information.
sample
import * as sqlite3 from 'sqlite3'; (async () => { try { const db = new sqlite3.Database('db.sqlite3'); db.on('trace', (sql) => { console.log(sql); }); await new Promise((resolve, reject)=>{ db.all( `INSERT INTO user_table VALUES(?, ?)`, [0, 'TEST'], (err, rows)=>{ if(err){ reject(err); } else { resolve(rows); } } ) }); const rows:Array<any> = await new Promise((resolve, reject)=>{ db.all( `SELECT * FROM user_table`, [], (err, rows)=>{ if(err){ reject(err); } else { resolve(rows); } } ) }); rows.forEach((v: any) => console.log(v)); await new Promise<void>((resolve, reject)=>{ db.close((err)=>{ if(err){ reject(err); } else { resolve(); } }); }); } catch (err) { console.error(err); } })();
Description.
synchronous execution
Postscript (2020-01-26)
Promise using util.promisify() was described, but has been modified to use Promise. The query results are called back in a callback, asynchronous style library.
There is a function for synchronous execution, but it is not used. Instead, the callback is made Promise and async/await is used for synchronous execution.
query execution
There are several functions to execute the query, but Database.all() will do most of the work.
Database.all() returns the result of the execution as an array (rows)." INSERT, for example, ignores the return value if an array of execution results is not needed.
The first argument is the query and the second argument is an array of variable values to be embedded in the query. Then the variable values are expanded at the "? of the query.
Although it is not impossible to write variable values directly into the query, it is definitely better to use the embedding function, as it will do the escaping for you.
debug
To see the actual query after variable expansion, register an Database.on('trace') event to display the query in the console as it is created.
