sqlite3-best-practices

Enforce SQLite3 schema conventions for tables, columns, and indexes.

1|Updated Aug 29, 2023

Search Tags:#indexing #sqlite #best-practices #schema-design #database-design #foreign-keys #sqlite3

Ready to run

npx skills add https://github.com/alicanerdogan/dotfiles --skill sqlite3-best-practices

💬 Prompt for ChatGPT / Claude Chat▼

Please help me install this Agent Skill.
Skill: sqlite3-best-practices
Source: https://github.com/alicanerdogan/dotfiles/tree/main/pi/skills/sqlite3-best-practices
Command: npx skills add https://github.com/alicanerdogan/dotfiles --skill sqlite3-best-practices

SYSTEM DOCUMENTATION & REQUIREMENTS

What problem does it solve?

Sqlite3 best practices. Use when you need to design a table schema or a query pattern if sqlite3 is involved.

Core Features & Use Cases

DB schema conventions
All table names: PascalCase and singular
All column names: prefixed with snake_case version of table name
All IDs: ULID (primary keys)
All tables include: created_at, updated_at, archived_at
Foreign key constraints enforced
No boolean flag columns; use timestamps (e.g., favorited_at) or enum columns instead
Use composite indexes for common query patterns
Name indexes with idx__{table}__{column1}_{column2}_...
Notes
- Feel free to use TEXT columns for storing JSON data for flexibility and keep in mind query patterns when using them. If you find yourself needing to query specific fields within JSON, consider normalizing that data into separate tables or using generated columns for indexing.

Quick Start

Apply these conventions to your SQLite3 schemas to ensure consistent, scalable database design.

Frequently Asked Questions about sqlite3-best-practices

High-intent search queries and answers about installing and using this skill.

FAQPage Schema

What are the best practices for SQLite3 schema design and naming conventions?▼

SQLite3 schema design best practices include using singular PascalCase table names, snake_case column prefixes, ULID primary keys, enforced foreign key constraints, and standardized timestamp columns for consistent database structure.

How do I design SQLite3 tables for mobile apps and embedded systems?▼

Design SQLite3 tables for mobile apps by applying standardized naming conventions, ULID primary keys, timestamp tracking columns, and composite indexes for common query patterns to ensure scalable lightweight database performance.

Should I use boolean columns or timestamps in SQLite3 database design?▼

In SQLite3 database design, avoid boolean flag columns. Use timestamp columns like `favorited_at` or enum columns instead to track state changes, providing better historical data context and query flexibility.

How do I name indexes and handle JSON data in SQLite3 queries?▼

Name SQLite3 indexes using the `idx__{table}__{column1}_{column2}` format. Store JSON data in TEXT columns for flexibility, but normalize data into separate tables or use generated columns if you need to query specific JSON fields.

Can I use composite indexes and foreign key constraints in SQLite3?▼

Yes, SQLite3 supports composite indexes for optimizing common query patterns and foreign key constraints to enforce relational integrity. Combine these with ULID primary keys and standardized timestamps for robust schema design.

When should I normalize JSON data into separate SQLite3 tables?▼

Normalize JSON data into separate SQLite3 tables or use generated columns when you find yourself needing to query specific fields within the JSON frequently, ensuring efficient indexing and query performance.