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:

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

MISC

• Create table if not exists:

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