README

A soft play area for data!

Goals

1. Very light data analysis
2. Very small app prototyping
3. 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.

Notes

• Appropriate uses for SQLite
• When to use Postgres -vs- SQLite
• Modern SQL and what's changed

Some handy settings

You can save settings in your ~/ home directory with an .sqliterc file!

-- Show table headings and column format
.headers on
.mode column

-- Show pretty printed schema
.schema --indent

Tools

True as of 2025

### GUIs

• 5 best GUIs for SQLite (and Tad for pivots)
• Enso (data prep and visualisation)
• Mockaroo (generate mock data)

Code

There are LOTS of frameworks for dealing with data. I may consider Ocaml/Elixir/Go/Gleam/Roc or another language later ... for now, Python!

• JQ (for manipulating json, and it's playground with some examples)
• VS Code plugin
• Python list of ORMs and here and ice axe
• Piccalo (ORM for FastApi with notes for pragma/types)
• Alternatively, Sanic and Peewee ORM (or non-async API)
• SQL for humans (Just write SQL in Python!)
  • Great for internal admin (where security isn't a concern)
• SQLite Utils (rapid manipulation and CLI)

Books

1. SQLite books

Courses

Google "udemy/coursera sql course"

I think the original SQL courses I did ages back are on Udemy.

• Database design in 8 hours

⚠️ Warnings

Security

1. Writing raw SQL (things to watch out for)
2. About ORMS (safer than raw SQL)

SQLite

Types

Sqlite is very permissive. It isn't at all Type safe!

You can, however, enable strict tables (or strict mode). Be careful with bugs when dealing with SQLite, as it's not as strict as Postgres. For example, if you write improper SQL such as:

-- Creates `null` column name (missing name) 
ALTER TABLE BandMember ADD COLUMN TEXT;
-- These types are allowed 🤦
CREATE TABLE shit_types (a INT, b VARCHAR(10));
INSERT INTO shit_types (a,b) VALUES('123',1234567891011);
-- 123|1234567891011

Pragmas

You must notify SQLite to respect foreign keys for every connection! This will make sure that DELETE cascades (if foreign key deleted)

-- For every connection, set this ...
-- Especially for `INSERT` and `DELETE`
PRAGMA foreign_keys=on;