SQLite

What and Why

• SQLite is not comparable with big client/server databases like Oracle, MySQL, PostgreSQL.
• SQLite is a file-based, simple, economical, reliable RDBMS.

When to use

1. Embedded devices and IoT

• Like cellphones, set-top boxes, watches, drones.

2. Application file format

• On-disk file format for desktop apps like VCS, record keeping programs.

3. Websites

• Any low traffic site that gets fewer than 100K hits/day should work fine with SQLite (A rather conservative estimate).

See full list here

Use sqlite

1. Download. There are three releases for windows: x32 dll package, x64 dll package and exe package. Here let’s use exe package to demonstrate.

2. Run sqlite3 test.db in command line. This will create a database file test.db and start the REPL client for sqlite.

3. Now just play with sql. Data will be persisted to test.db when the REPL client quits.

Node.js sqlite

Install: npm install sqlite3

You may encounter the following error:

npm ERR! gyp ERR! find VS **************************************************************
npm ERR! gyp ERR! find VS You need to install the latest version of Visual Studio
npm ERR! gyp ERR! find VS including the "Desktop development with C++" workload.
npm ERR! gyp ERR! find VS For more information consult the documentation at:
npm ERR! gyp ERR! find VS https://github.com/nodejs/node-gyp#on-windows
npm ERR! gyp ERR! find VS **************************************************************

Visual studio build tools are missing. The easiest way to install it is through choco:

choco install visualstudio2017buildtools

If still not able to install sqlite3 after it, reboot the computer.
If still not able to install, repeatedly run the command until it is installed.
Sometimes sqlite3 cannot be installed with yarn add -dev, try to install it with yarn add and then modify package.json and then install with yarn add -dev.

Use:

	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()

view raw sqlite.js hosted with ❤ by GitHub

As sqlite3 does not support Promise, there’s another wrapper library sqlite which provides Promise:

	// if promise is needed, use the wrapper library sqlite together with sqlite3
	// yarn add sqlite
	// yarn add sqlite3
	var sqlite3 = require('sqlite3').verbose()
	var { open } = require('sqlite')
	async function main () {
	try {
	const db = await open({
	filename: 'test.db',
	driver: sqlite3.Database
	})
	await db.exec('CREATE TABLE tbl (col TEXT)')
	await db.exec('INSERT INTO tbl VALUES ("test")')
	const result = await db.get('SELECT col FROM tbl WHERE col = ?', 'test')
	console.log(result)
	const result1 = await db.get('SELECT col FROM tbl WHERE col = ?', ['test'])
	const result2 = await db.get('SELECT col FROM tbl WHERE col = :test', {
	':test': 'test'
	})
	const result3 = await db.all('SELECT col FROM tbl')
	const result4 = await db.run(
	'INSERT INTO tbl (col) VALUES (?)',
	'foo'
	)
	// { stmt: Statement { stmt: undefined }, lastID: 2, changes: 1 }
	} catch (e) {
	console.log(e)
	}
	}
	main()

view raw sqlite_wrapper.js hosted with ❤ by GitHub

MISC

• Create table if not exists:

CREATE TABLE if not exists users(username TEXT primary key, email TEXT unqiue, password TEXT not null)