SQLite3

Back

Installation

Debian/Ubuntu

sudo apt update
sudo apt install sqlite3
sqlite3 --version

macOS

# usually sqlite is preinstalled
sqlite3 --version
# or 
brew install sqlite

Setup

# creating a database
sqlite3 mydb.db

# exit the sqlite shell
.exit

# import data from CSV
CREATE TABLE mytable (id INTEGER PRIMARY KEY, name TEXT, age INTEGER);  
.mode csv  
.import data.csv mytable

# Export data to CSV
.headers on  
.mode csv  
.output data.csv  
SELECT * FROM mytable;  

Security

File Permissions

SQLite databases are files. Use file system permissions to restrict access

chmod 600 mydb.db

Encryption

We can use extensions like SQLCipher for encryption

sqlcipher mydb.db
PRAGMA key = mysecretkey

Backup

sqlite3 mydb ".dump" > backup.sql

Meta-Commands

# list tables
.tables

# describe a table
.schema users

# export data
.mode csv  
.output users.csv  
SELECT * FROM users;

# import data
.mode csv  
.import users.csv users  

Advanced Features

Transaction

BEGIN;  
UPDATE users SET email = '[email protected]' WHERE id = 1;  
COMMIT;  

Indexes

CREATE INDEX idx_users_email ON users (email);

Views

CREATE VIEW active_users AS SELECT * FROM users WHERE active = 1;

Triggers

Automate actions with triggers

CREATE TRIGGER update_timestamp AFTER UPDATE ON users  
BEGIN  
    UPDATE users SET updated_at = CURRENT_TIMESTAMP WHERE id = NEW.id;  
END;  

Troubleshooting

# check database integrity
PRAGMA integrity_check

# recover data from `.dump` to recover data from a corrupted database
sqlite3 corrupted.db ".dump" | sqlite3 new.db

# debug queries
EXPLAIN QUERY PLAN SELECT * FROM users WHERE email = '[email protected]';

Tips

# use in-memory databases
sqlite3 :memory:

# backup automatically
sqlite3 mydb.db ".dump" | gzip > backup_$(date +%F).sql.gz

# optimizations
RAGMA journal_mode = WAL;  # Write-Ahead Logging  
PRAGMA synchronous = NORMAL;

Resources

• https://www.sqlitetutorial.net