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.
- Appropriate uses for SQLite
- When to use Postgres -vs- SQLite
- Modern SQL and what's changed
-- SQLite settings:
-- Show table headings and column format
.headers on
.mode column- Writing raw SQL (things to watch out for)
- SQLite books
- About ORMS
- ORM lists and other lists and Piccalo
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|1234567891011You must notify SQLite to respect foreign keys for every connection!
-- For every connection, set this ...
-- Especially for `INSERT` and `DELETE`
PRAGMA foreign_keys=on;Google "udemy/coursera sql course"
I think the original SQL courses I did ages back are on Udemy.
True as of 2025
- Enso (data prep and visualisation)
- VS Code plugin
- Convert files to SQL and database
- 5 best GUIs for SQLite
- SQL for Humans (records tool)
- May be better used for internal use than app use (security)