sql
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.
When & Why to Use This Skill
This Claude skill provides expert guidance for SQL development, specifically optimized for SQLite environments. It assists developers in writing efficient queries, designing robust database schemas with strict typing, and managing complex migrations while adhering to modern best practices for data integrity and performance.
Use Cases
- Writing and optimizing SQLite queries using Common Table Expressions (CTEs) and specific formatting rules for better maintainability.
- Designing database schemas with strict tables, automated ISO 8601 timestamps, and unique prefixed ID generation strategies.
- Implementing database triggers to automate 'updated' timestamp management for consistent data auditing.
- Developing and debugging Go-based applications that require reliable and structured SQLite database interactions and migrations.
| 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
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;