A soft play area for data!
- Very light data analysis
- Very small app prototyping
- Storing Marketing/Ads data
Examples such as the Programming Flashcards App, Google Ads, Simple Analytics research, Library Data, and so on. Should involve basically ZERO database admin, unless absolutely necessary.
An sqlite cheat sheet ...
- Appropriate uses for SQLite
- When to use Postgres -vs- SQLite
- Modern SQL and what's changed
/* Launch sqlite */
sqlite3 database.sqlite
/* Schema information */
.schema --indent
.schema Table --indent
/* Table information (orm)*/
pragma table_info(Table);
pragma table_xinfo(Table);
/* Column types (real) */
select typeof(Column) from Table
limit 1;
/* Clear lines */
.shell clear
/* Quit sqlite */
.quit
.exitSave SQLite settings in
~/.sqliterc
-- Show table headings and column format
.headers on
.mode columnTo save space remove all unecessary folders once you're done.
# Delete the environment (use `uv sync` to restore)
rm -rf .venv && rm uv.lock
# Check the command before running it (delete all cache folders)
find . -type d -name "__pycache__" | wc -l
find . -type d -name "__pycache__" -exec echo rm -r {} \; # `-f` is dangerous!
# Run the command (add the `-v` flag to show all files deleted)
find . -type d -name "node_modules" -exec echo rm -r {} \;True as of 2025. There are lots of frameworks for dealing with data ...
Python isn't the ideal language for performant data transfer, but it's quite easy to get started with. Other languages like Ocaml, Elixir/Go/Gleam/Roc, might be worth a look at some point (other popular languages here). Remember the "sunk cost fallacy" and "opportunity cost" and cognitive load it takes to learn a new thing. Is the performance 10x better? Worthy of investing 3 months of your time?
- 5 best GUIs for SQLite (and Tad for pivots)
- Enso (data prep and visualisation)
- Mockaroo (generate mock data)
There's async and sync and not all ORMs will work for async.
- Piccalo works well with FastApi (careful with types and async writes)
- Peewee is great (
synconly with these API frameworks) - Records: SQL for humans for raw SQL (alternative to
sqlite-utilswith.csvetc) - Ice axe could be one for the future (Postgres only)
- A list of other ORM options here and here
These are great for quickly working with your data
- JQ (for manipulating
json, and it's playground with some examples) - SQLite Utils (rapid manipulation and CLI)
- VS Code plugin
SQLite is growing in popularity for production servers
- Litestream for backups (costs pennies per day)
- Litefs for clusters and local-first (like Turso)
Google "udemy/coursera sql course"
I think the original SQL courses I did ages back are on Udemy.
- Database design in 8 hours
- Writing raw SQL (things to watch out for)
- About ORMS (safer than raw SQL)
Sqlite is very permissive. It isn't at all Type safe!
SQLite datatypes are very simple, with main ones being NULL, INTEGER, TEXT (and occasionally BLOB). You can enable strict tables (or strict mode), but some ORMs use custom types so these will restrict you somewhat. By default SQLite will guarantee NOT NULL and UNIQUE values, but you've got to take care not to introduce bugs when INSERTing row values. Postgres is far more strict. An example below:
-- Creates `null` column name (missing name)
ALTER TABLE BandMember ADD COLUMN TEXT;
-- Not strict by default! 🤦
-- Ignores types and bounds; infers types if incorrect.
CREATE TABLE shit_types (a INT, b VARCHAR(10));
INSERT INTO shit_types (a,b) VALUES('123',1234567891011);
-- 123|1234567891011You must notify SQLite to respect foreign keys for every connection! This will make sure that
DELETEcascades (if foreign key deleted)
-- For every connection, set this ...
-- Especially for `INSERT` and `DELETE`
PRAGMA foreign_keys=on;