Quick start with SQLite3 CLI

Tired of installing a full blown database just to store a few rows of config and regret your life choices later Use SQLite3 instead It is a zero config SQL database that lives in a single file and does exactly what you expect

Open a shell and create or open a database file

sqlite3 mydb.db

Define a table

Use plain SQL to define a schema Here is a simple users table

CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, email TEXT)

The database file stores both the schema and the rows so deployment is basically copy the file and move on with your life

Insert data safely

From a quick shell or from application code insert rows Parameter binding is your friend to avoid SQL injection

INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com')

From application code use prepared statements and bindings rather than interpolating strings It keeps things predictable and you avoid being that person who left a SQL hole in production

Queries and filters that do actual work

SELECT is all you need to get useful data Use WHERE ORDER BY and LIMIT to slice and dice

SELECT id, name FROM users WHERE name LIKE '%Al%' ORDER BY id LIMIT 10

Transactions indexes and backups

Wrap related writes in a transaction so a crash does not leave your database in a sad state

BEGIN TRANSACTION
INSERT INTO users (name, email) VALUES ('Bob', 'bob@example.com')
COMMIT

Add an index to speed up common lookups

CREATE INDEX idx_users_email ON users (email)

For backups a simple copy of the database file works fine for small apps You can also use your language driver backup functions if you prefer

Schema changes and migrations

SQLite supports ALTER TABLE for basic changes For complex migrations the trick is create a new table copy rows and rename tables Keep migration scripts version controlled so your team does not blame the intern

ALTER TABLE users ADD COLUMN created_at TEXT

Best practices and quick tips

• Keep transactions short to avoid locking the database
• Use indexes on columns you filter on often but do not index everything
• Use PRAGMA statements for tuning when needed
• Version control migration scripts and test them on copies of the database

Recap and what to try next

This tutorial showed how to install and use SQLite3 create and populate tables run queries and manage schema and backups It is ideal for local apps prototypes and small production services where a single file database is a feature not a limitation

Now go make a tiny database and feel superior to anyone running a full server for a single table