SQL Skill: Помощь в работе с SQLite

--- name: sql description: Guide for working with SQL queries, in particular for SQLite. Use this skill when writing SQL queries, analyzing database schemas, designing migrations, or working with SQLite-related code. license: MIT --- # 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 `strict` tables - 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 the `p_` 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`/`updated` columns like this: `created text not null default (strftime('%Y-%m-%dT%H:%M:%fZ'))` - Updated timestamps are automatically updated with a trigger like this: ```sql 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; ```