Sqlite

Created: 2020-02-20 13:33:52 -0800 Modified: 2020-09-16 20:03:24 -0700

Basics

GUIs:
- https://sqlitebrowser.org/
- https://www.beekeeperstudio.io/
To see the SQL required to create a table (like “show create table FOO” in MysQL), run this:

SELECT sql FROM sqlite_master WHERE name = 'TABLE_NAME';

Reading a database
- On Windows, I installed a graphical tool called DB Browser for SQLite.
- On Linux, you can install the “sqlite3” package to quickly read a sqlite database. E.g.
  - $ sudo apt install sqlite3
  - $ sqlite3 ./some_database.sqlite

select * from users;

Command line (reference)

You can’t do something like “describe users;” or “show tables;” in sqlite. Those commands would be “.tables” and “.schema users”. For more information, see “.help”

Optimizing with pragma statements

Basics

You can also apparently reset a pragma like this: “PRAGMA journal_mode=DELETE;“

Suggested setup

It was suggested to me that I run these pragma statements for my Discord bot’s sqlite setup:

PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
PRAGMA locking_mode = EXCLUSIVE;

I didn’t know what these meant, so I looked them up:

journal_mode - sets the journal mode to “WAL”, which is “write-ahead log”. A general summary of this:
- 74c55639 (Twitch-chat user): generally one means that changes are committed to the database and in case of transaction failure they are rolled back and the other means that the changes are stored somewhere and if the transaction is considered successful then they are committed
synchronous - FULL by default, meaning every single change is persisted to the disk. NORMAL would “sync at the most critical moments”, meaning you could get your database corrupted if there’s a power failure or system crash.
locking_mode - NORMAL by default, meaning that the program connecting to the database doesn’t have exclusive access (enforced by the filesystem).

My conclusion is that I don’t want to prematurely optimize, so I’m just going to write down those settings and see how well my database performs on a Raspberry Pi.