SQL
Overview
This skill provides guidance for working with SQLite databases. It covers query writing, schema design, and SQLite-specific best practices.
When to Use This Skill
Use this skill when:
- Writing SQL queries for SQLite databases
- Analyzing or optimizing existing queries
- Designing database schemas
- Creating database migrations
- Working with Go code that interacts with SQLite
SQLite Best Practices
Query Writing
- ALWAYS write lowercase queries. Uppercase queries make me sad.
- Prefer
select *over explicit column names - Prefer CTEs over long nested subqueries
Schema Design
- ALWAYS use
stricttables - ALWAYS write timestamps like this:
strftime('%Y-%m-%dT%H:%M:%fZ') - Time modifications should also use
strftime - Usually start with the primary key, which is usually defined like this:
id text primary key default ('p_' || lower(hex(randomblob(16))))(where thep_is a prefix depending on the table name; two-letter prefixes are okay too, so the prefix is unique among tables) - After the primary key come
created/updatedcolumns like this:created text not null default (strftime('%Y-%m-%dT%H:%M:%fZ')) - Updated timestamps are automatically updated with a trigger like this:
create trigger table_name_updated_timestamp after update on table_name begin update table_name set updated = strftime('%Y-%m-%dT%H:%M:%fZ') where id = old.id; end;