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:

  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;