sqlite.md from HariSekhon/Knowledge-Base repo: https://github.com/HariSekhon/Knowledge-Base

sqlite.md

SQLite

• Summary
• Basic Usage
• Config
• Batch / Scripting
• Parameterized Queries
• Atomic Transactions

Summary

https://www.sqlite.org/

SQLite is a small fast local SQL DB that stores data in a simple file usually suffixed with .sqlite.

It is suitable only for small databases which can reasonably fit in a single file without incurring large I/O seek and file rewriting performance problems.

This works well for light desktop applications such as Shazam desktop, sample applications for learning purposes, and localized test DBs for real applications where you still want to use SQL with some representative loaded dummy data.

Basic Usage

$ sqlite3 myfile.sqlite
sqlite>

Then enter SQL commands at the interactive prompt.

See batch mode further down.

To see SQLite specific commands, type:

.help

Config

SQLite configuration is stored in:

.sqliterc

My .sqliterc config is available in the DevOps-Bash-tools repo:

.headers on
.mode column
.nullvalue NULL
-- .prompt "> "
.timer on

Batch / Scripting

For non-interactive SQL commands on a sqlite db file, such as in scripts:

1. use the -batch switch for more efficient batch I/O since commits are expensive in this flat file format,
2. add the -bail switch to exit upon encountering any error so your script can catch any problem exit safely without causing a bigger problem
   1. Remember to set your shell -e switch, see Bash

sqlite3 -batch -bail /path/to/file.sqlite <<EOF
...
...
...
EOF

Parameterized Queries

Use parameterized queries as database best practice to avoid SQL Injection attacks.

This is how you do it in SQLite - this code is taken from shazam_app_delete_track.sh in DevOps-Bash-tools (the Shazam desktop app on macOS uses a local sqlite db):

$ sqlite3 -batch -bail "$dbpath" <<EOF

.parameter init
.parameter set :artist "$artist"
.parameter set :track "$track"

DELETE FROM ZSHTAGRESULTMO
WHERE Z_PK IN (
  SELECT r.Z_PK
  FROM ZSHTAGRESULTMO r
  JOIN ZSHARTISTMO a ON a.ZTAGRESULT = r.Z_PK
  WHERE a.ZNAME = :artist
  AND r.ZTRACKNAME = :track
);
EOF

Atomic Transactions

You can enforce atomicity using BEGIN and COMMIT, similar to other relational databases:

BEGIN;
INSERT ...
UPDATE ...
DELETE ...
COMMIT;

I/O is expensive in SQLite so batching operations is advised for performance reasons as well as logical grouping of instructions to be atomic.