Cheatsheet: SQLite

Use INSERT ... ON CONFLICT(column) DO UPDATE SET ..., referencing the rejected row's values with excluded.: INSERT INTO users (email, name) VALUES ('ada@example.com', 'Ada2') ON CONFLICT(email) DO UPDATE SET name = excluded.name;. This requires a UNIQUE or PRIMARY KEY constraint on the conflict column. Use ON CONFLICT DO NOTHING instead if you just want to silently skip duplicates.

Not strictly -- SQLite uses dynamic typing with type affinity. A column declared INTEGER can still store a TEXT or REAL value unless you add a STRICT table (SQLite 3.37+) or a CHECK constraint. Use typeof(column) in a query to see the actual stored type of a value, e.g. SELECT typeof(x) FROM t;.

Use the CLI's dot-commands, not SQL: .tables lists tables and views, .schema table_name prints the CREATE statement for a specific table, and plain .schema prints the whole database schema. These only work in the interactive sqlite3 shell/CLI, not as SQL you can run through a driver -- from SQL, query sqlite_master instead (as in the SQLite Specific Commands section above).

For a full binary copy of the live database (safe even while it's in use), use the CLI's .backup command: sqlite3 source.db ".backup backup.db". For a portable SQL script you can replay elsewhere, use .dump: sqlite3 source.db .dump > backup.sql, then restore with sqlite3 new.db < backup.sql.

A column declared INTEGER PRIMARY KEY already auto-generates sequential rowids and is enough for almost all use cases. Adding AUTOINCREMENT (INTEGER PRIMARY KEY AUTOINCREMENT) additionally guarantees IDs are never reused, even after rows are deleted, by tracking the highest-ever-used ID in an internal sqlite_sequence table -- at the cost of slightly more overhead. Use plain INTEGER PRIMARY KEY unless you specifically need that never-reuse guarantee.