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

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)