sqlite-db

Q: How do I install sqlite-db?

Run npx skills add https://github.com/bfollington/terma --skill sqlite-db in your project. The skills CLI detects the agents you use and copies the skill into the right folder. For Claude Code you can also place the skill folder at ~/.claude/skills/sqlite-db/ (personal) or .claude/skills/sqlite-db/ (project).

by bfollingtonData & Databases

General guide for using the sqlite3 CLI to build composable knowledge databases. Use this skill when creating SQLite databases, designing schemas, querying data, managing relationships, or building new sqlite-based domain skills. Provides the foundational patterns that all specialized sqlite skills build upon.

Installs: 50
Last 24h: —
Trending rank: —
Security: Not audited

Install

Works with every agent the skills CLI supports.

npx skills add https://github.com/bfollington/terma --skill sqlite-db

01
Install
Run the command in your project. The CLI asks which agents to install for.
02
Describe the task
Ask your agent for something this skill covers. It loads sqlite-db automatically when relevant.
03
Review the result
The agent follows the SKILL.md steps, scripts and references bundled with the skill.

SKILL.md

The instructions your agent reads when the skill is triggered.

SQLite Database Skills

Composable knowledge databases via raw SQL.

SQLite databases are portable, self-contained, and require no server. The sqlite3 CLI provides direct access to the full power of relational SQL: indexes, joins, aggregations, window functions, CTEs, full-text search, JSON functions, triggers, and views. This skill teaches agents how to use SQLite as a knowledge management substrate.

Philosophy

SQL is the Interface

No wrapper, no abstraction layer. You compose SQL directly. This gives you the full power of SQLite: complex joins, window functions, CTEs, FTS5, JSON operations, triggers, and views. Verbosity costs tokens, not keystrokes — and the expressiveness pays dividends.

Schemas Are the DDL

No YAML declarations. The CREATE TABLE statements are the schema. Run .schema to see everything. Column types, constraints, foreign keys, indexes — all visible in the DDL. Self-documenting by design.

Composable .db Files

Each domain gets its own .db file. Your notes database, investment tracker, and issue tracker are separate files. Portable — copy, share, back up independently. No central server required.

Agent-Compatible

The sqlite3 CLI is deterministic and stateless per invocation. Output modes (-header -column, .mode json, -line) are parseable. Commands never rely on session state. Perfect for LLM-driven workflows.

Database Targeting

Always pass the database path as the first argument to sqlite3. This ensures stateless, deterministic behavior.

# Single-line command
sqlite3 /path/to/mydata.db "SELECT * FROM notes WHERE status = 'active';"

# Multi-line command via heredoc
sqlite3 /path/to/mydata.db <<'SQL'
SELECT id, title, created_at
FROM notes
WHERE status = 'active'
ORDER BY created_at DESC;
SQL

Output Modes

Choose the output mode based on your needs:

Mode	Use Case	Invocation
Column	Human-readable tables	`sqlite3 -header -column mydata.db "SELECT ..."`
JSON	Agent parsing with jq	`sqlite3 mydata.db "SELECT ..." \| jq` (after `.mode json`)
CSV	Export to spreadsheets	`sqlite3 -csv -header mydata.db "SELECT ..."`
Line	Single record inspection	`sqlite3 -line mydata.db "SELECT * FROM notes WHERE id = 'NOTE-...';"`

JSON Mode Example

# Enable JSON output and query
sqlite3 /path/to/mydata.db <<'SQL'
.mode json
SELECT id, title, tags FROM notes LIMIT 5;
SQL

Then pipe to jq for filtering or transformation:

sqlite3 /path/to/mydata.db "SELECT ..." | jq -r '.[] | select(.status == "active") | .id'

Core Operations

Initialize a Database

Create the database directory and initialize tables with constraints and pragmas:

# Create directory
mkdir -p /path/to/.sqlite

# Initialize database with pragmas and schema
sqlite3 /path/to/.sqlite/mydata.db <<'SQL'
PRAGMA journal_mode = WAL;
PRAGMA foreign_keys = ON;

CREATE TABLE IF NOT EXISTS notes (
  id TEXT PRIMARY KEY,
  title TEXT NOT NULL,
  body TEXT,
  status TEXT NOT NULL CHECK (status IN ('draft', 'active', 'archived')) DEFAULT 'draft',
  tags TEXT CHECK (json_valid(tags)),
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE INDEX IF NOT EXISTS idx_notes_status ON notes(status);
CREATE INDEX IF NOT EXISTS idx_notes_created ON notes(created_at DESC);
SQL

Important pragmas:

PRAGMA journal_mode = WAL; — enables concurrent reads and better performance
PRAGMA foreign_keys = ON; — enforces referential integrity

Generate IDs

Use inline SQL expressions to generate unique, time-ordered, human-readable IDs:

'PREFIX-' || strftime('%Y%m%d', 'now') || '-' || lower(hex(randomblob(4)))

Examples:

'NOTE-' || strftime('%Y%m%d', 'now') || '-' || lower(hex(randomblob(4))) → NOTE-20260208-a3f8c291
'TASK-' || strftime('%Y%m%d', 'now') || '-' || lower(hex(randomblob(4))) → TASK-20260208-7b2e9f41

Prefix conventions:

Notes: NOTE-
Tasks: TASK-
Resources: RES-
Clippings: CLIP-
Breadcrumbs: CRUMB-
Reflections: REFL-

Create Records

Insert records with inline ID generation:

sqlite3 /path/to/.sqlite/mydata.db <<'SQL'
INSERT INTO notes (id, title, body, status, tags)
VALUES (
  'NOTE-' || strftime('%Y%m%d', 'now') || '-' || lower(hex(randomblob(4))),
  'Understanding Composability',
  'Systems that compose are systems that scale...',
  'active',
  json_array('systems', 'design', 'composability')
);
SQL

Note: Use json_array() for JSON array fields, not string concatenation.

Query Records

# Simple query with filtering and ordering
sqlite3 -header -column /path/to/.sqlite/mydata.db <<'SQL'
SELECT id, title, status, created_at
FROM notes
WHERE status = 'active'
ORDER BY created_at DESC
LIMIT 10;
SQL

Pagination example:

SELECT id, title
FROM notes
ORDER BY created_at DESC
LIMIT 20 OFFSET 40;  -- Page 3 (20 per page)

JSON output for scripting:

sqlite3 /path/to/.sqlite/mydata.db <<'SQL'
.mode json
SELECT id, title, tags FROM notes WHERE status = 'active';
SQL

Show a Record

Use -line mode for human-readable single-record display:

sqlite3 -line /path/to/.sqlite/mydata.db <<'SQL'
SELECT * FROM notes WHERE id = 'NOTE-20260208-a3f8c291';
SQL

Output:

        id = NOTE-20260208-a3f8c291
     title = Understanding Composability
      body = Systems that compose are systems that scale...
    status = active
      tags = ["systems","design","composability"]
created_at = 2026-02-08 14:32:01
updated_at = 2026-02-08 14:32:01

Update Records

sqlite3 /path/to/.sqlite/mydata.db <<'SQL'
UPDATE notes
SET
  status = 'archived',
  updated_at = datetime('now')
WHERE id = 'NOTE-20260208-a3f8c291';
SQL

Batch update example:

UPDATE notes
SET status = 'archived', updated_at = datetime('now')
WHERE created_at < date('now', '-1 year');

Delete Records

sqlite3 /path/to/.sqlite/mydata.db <<'SQL'
DELETE FROM notes WHERE id = 'NOTE-20260208-a3f8c291';
SQL

Relationships

SQLite supports two relationship styles, each with distinct use cases.

Structural Relationships (Foreign Key Columns)

Use foreign key columns for parent-child ownership and 1:1 or N:1 relationships:

CREATE TABLE clippings (
  id TEXT PRIMARY KEY,
  content TEXT NOT NULL,
  resource_id TEXT,  -- Foreign key to resources table
  clipped_at TEXT NOT NULL DEFAULT (datetime('now')),
  FOREIGN KEY (resource_id) REFERENCES resources(id) ON DELETE CASCADE
);

CREATE INDEX idx_clippings_resource ON clippings(resource_id);

Query pattern:

-- All clippings from a specific resource
SELECT c.id, c.content, c.clipped_at
FROM clippings c
WHERE c.resource_id = 'RES-20260208-f1a2b3c4';

-- Join to get resource details
SELECT c.id, c.content, r.title AS resource_title
FROM clippings c
JOIN resources r ON c.resource_id = r.id
WHERE r.status = 'finished';

Flexible Relationships (Links Table)

Use a generic links table for many-to-many, ad-hoc, named relationships:

CREATE TABLE links (
  source_id TEXT NOT NULL,
  target_id TEXT NOT NULL,
  rel_type TEXT NOT NULL,
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  PRIMARY KEY (source_id, target_id, rel_type)
);

CREATE INDEX idx_links_source ON links(source_id, rel_type);
CREATE INDEX idx_links_target ON links(target_id, rel_type);

Create links:

-- Single link
INSERT INTO links (source_id, target_id, rel_type)
VALUES ('NOTE-20260208-a3f8c291', 'NOTE-20260205-b2c3d4e5', 'linksTo');

-- Batch link creation
INSERT INTO links (source_id, target_id, rel_type)
SELECT 'CRUMB-20260208-f1f2f3f4', id, 'analyzedNotes'
FROM notes
WHERE tags LIKE '%systems%' AND created_at > date('now', '-7 days');

Query outgoing links:

SELECT l.rel_type, n.id, n.title
FROM links l
JOIN notes n ON l.target_id = n.id
WHERE l.source_id = 'NOTE-20260208-a3f8c291';

Query incoming links:

SELECT l.rel_type, n.id, n.title
FROM links l
JOIN notes n ON l.source_id = n.id
WHERE l.target_id = 'NOTE-20260208-a3f8c291';

Remove links:

DELETE FROM links
WHERE source_id = 'NOTE-20260208-a3f8c291'
  AND target_id = 'NOTE-20260205-b2c3d4e5'
  AND rel_type = 'linksTo';

Common relationship types:

linksTo — general connection
derivedFrom — content derived from another note
partOf — belongs to a container/collection
analyzedNotes — breadcrumb analyzed these notes
basedOnNotes — reflection based on these notes
promotedTo — reflection promoted to note

Views as Saved Queries

Views are more powerful than memhub's saved queries — they can use joins, aggregations, and reference other views.

Create a View

CREATE VIEW active_notes AS
SELECT id, title, status, created_at
FROM notes
WHERE status = 'active'
ORDER BY created_at DESC;

Query a View

SELECT * FROM active_notes LIMIT 10;

List All Views

SELECT name FROM sqlite_master WHERE type = 'view';

Drop a View

DROP VIEW IF EXISTS active_notes;

Complex View Example

-- Note graph view with link counts
CREATE VIEW note_graph AS
SELECT
  n.id,
  n.title,
  n.status,
  COUNT(DISTINCT lo.target_id) AS outgoing_links,
  COUNT(DISTINCT li.source_id) AS incoming_links
FROM notes n
LEFT JOIN links lo ON n.id = lo.source_id
LEFT JOIN links li ON n.id = li.target_id
GROUP BY n.id, n.title, n.status;

Triggers for Automation

Triggers automate repetitive tasks like timestamp updates and FTS synchronization.

Auto-Update Timestamps

CREATE TRIGGER update_notes_timestamp
AFTER UPDATE ON notes
FOR EACH ROW
BEGIN
  UPDATE notes SET updated_at = datetime('now') WHERE id = OLD.id;
END;

FTS Sync Triggers

See "Full-Text Search" section below for complete examples.

Full-Text Search (FTS5)

SQLite's FTS5 extension provides ranked full-text search. Memhub cannot do this.

Create FTS Virtual Table

CREATE VIRTUAL TABLE notes_fts USING fts5(
  id UNINDEXED,
  title,
  body,
  tags,
  content='notes',
  content_rowid='rowid'
);

Sync Triggers

Keep the FTS index synchronized with the base table:

-- Trigger: insert
CREATE TRIGGER notes_fts_insert AFTER INSERT ON notes BEGIN
  INSERT INTO notes_fts(rowid, id, title, body, tags)
  VALUES (NEW.rowid, NEW.id, NEW.title, NEW.body, NEW.tags);
END;

-- Trigger: update
CREATE TRIGGER notes_fts_update AFTER UPDATE ON notes BEGIN
  UPDATE notes_fts
  SET title = NEW.title, body = NEW.body, tags = NEW.tags
  WHERE rowid = OLD.rowid;
END;

-- Trigger: delete
CREATE TRIGGER notes_fts_delete AFTER DELETE ON notes BEGIN
  DELETE FROM notes_fts WHERE rowid = OLD.rowid;
END;

Search Queries

-- Simple search
SELECT id, title FROM notes_fts WHERE notes_fts MATCH 'composability';

-- Boolean operators
SELECT id, title FROM notes_fts WHERE notes_fts MATCH 'systems AND composability';

-- Phrase search
SELECT id, title FROM notes_fts WHERE notes_fts MATCH '"knowledge management"';

-- Ranked search with snippets
SELECT
  n.id,
  n.title,
  snippet(notes_fts, 1, '**', '**', '...', 32) AS snippet,
  bm25(notes_fts) AS rank
FROM notes_fts
JOIN notes n ON notes_fts.id = n.id
WHERE notes_fts MATCH 'composability'
ORDER BY rank
LIMIT 10;

JSON Functions

SQLite provides robust JSON support for array and object fields.

Creating JSON Arrays

-- Inline array
INSERT INTO notes (id, title, tags)
VALUES (
  'NOTE-' || strftime('%Y%m%d', 'now') || '-' || lower(hex(randomblob(4))),
  'Example Note',
  json_array('tag1', 'tag2', 'tag3')
);

Querying JSON Arrays

-- Check if array contains a value
SELECT id, title
FROM notes
WHERE EXISTS (
  SELECT 1 FROM json_each(notes.tags)
  WHERE json_each.value = 'systems'
);

-- Count array elements
SELECT id, title, json_array_length(tags) AS tag_count
FROM notes
WHERE json_array_length(tags) > 3;

-- Extract unique tags across all notes
SELECT DISTINCT json_each.value AS tag
FROM notes, json_each(notes.tags)
WHERE notes.status = 'active'
ORDER BY tag;

-- Tag cloud (aggregation)
SELECT
  json_each.value AS tag,
  COUNT(*) AS note_count
FROM notes, json_each(notes.tags)
GROUP BY json_each.value
ORDER BY note_count DESC;

Updating JSON Arrays

-- Add a tag (append to array)
UPDATE notes
SET tags = json_insert(tags, '$[#]', 'new-tag')
WHERE id = 'NOTE-20260208-a3f8c291';

-- Remove a tag (requires rebuilding array)
UPDATE notes
SET tags = (
  SELECT json_group_array(value)
  FROM json_each(notes.tags)
  WHERE value != 'old-tag'
)
WHERE id = 'NOTE-20260208-a3f8c291';

JSON Validation

Use json_valid() in CHECK constraints:

CREATE TABLE notes (
  id TEXT PRIMARY KEY,
  title TEXT NOT NULL,
  tags TEXT CHECK (json_valid(tags)),
  metadata TEXT CHECK (json_valid(metadata) OR metadata IS NULL)
);

CHECK Constraints for Validation

CHECK constraints replace YAML enum and pattern validation.

Enum-Style Constraints

CREATE TABLE notes (
  id TEXT PRIMARY KEY,
  status TEXT NOT NULL CHECK (status IN ('draft', 'active', 'archived')) DEFAULT 'draft',
  epistemic TEXT CHECK (epistemic IN ('hypothesis', 'tested', 'validated', 'outdated'))
);

Range Constraints

CREATE TABLE resources (
  id TEXT PRIMARY KEY,
  rating INTEGER CHECK (rating >= 1 AND rating <= 5)
);

Pattern Constraints (Regex)

SQLite doesn't have native regex in CHECK constraints, but you can validate formats:

CREATE TABLE resources (
  id TEXT PRIMARY KEY,
  url TEXT CHECK (url LIKE 'http%')
);

For complex validation, use application-level checks before INSERT.

Aggregations

SQLite supports GROUP BY, COUNT, SUM, AVG, MIN, MAX, and more. Memhub cannot do this.

Basic Aggregations

-- Notes per status
SELECT status, COUNT(*) AS count
FROM notes
GROUP BY status;

-- Average rating per resource type
SELECT resource_type, AVG(rating) AS avg_rating
FROM resources
WHERE rating IS NOT NULL
GROUP BY resource_type;

-- Monthly note creation counts
SELECT
  strftime('%Y-%m', created_at) AS month,
  COUNT(*) AS note_count
FROM notes
GROUP BY month
ORDER BY month DESC;

Advanced Aggregations

-- Tag cloud with percentages
WITH tag_counts AS (
  SELECT
    json_each.value AS tag,
    COUNT(*) AS count
  FROM notes, json_each(notes.tags)
  GROUP BY json_each.value
)
SELECT
  tag,
  count,
  ROUND(100.0 * count / SUM(count) OVER (), 2) AS percentage
FROM tag_counts
ORDER BY count DESC
LIMIT 20;

Building a SQLite-DB Skill

Specialized sqlite-db skills follow a consistent structure. They teach agents how to manage a specific domain using SQLite.

Directory Layout

skills/sqlite-<domain>/
├── SKILL.md                # Skill instructions (when to use, workflows, SQL examples)
├── assets/
│   ├── schema.sql          # DDL: tables, indexes, FTS, triggers
│   └── views.sql           # Reusable views
├── scripts/
│   ├── setup.sh            # Idempotent initialization script
│   └── examples.sh         # Demo workflows
└── references/
    └── queries.md          # Complex query recipes

What a SQLite Skill Should Define

Tables — DDL for all domain entities with constraints, indexes, and foreign keys
Indexes — B-tree indexes for common queries, FTS indexes for search
FTS — Virtual tables and sync triggers for full-text search
Views — Saved queries as first-class database objects
Triggers — Auto-timestamps, FTS sync, validation
Links vocabulary — Named relationship types (same as memhub: linksTo, derivedFrom, partOf, etc.)
Database path — Where the .db file lives (e.g., .sqlite/notes.db)
ID prefixes — Conventions for generating human-readable IDs
Workflows — Step-by-step SQL examples for common tasks

Design Principles

Schemas encode domain knowledge. The DDL is the documentation. Use meaningful column names, CHECK constraints, foreign keys, and indexes. The schema should tell you what's important.

Target database explicitly. Every sqlite3 command should specify the full path to the database. Stateless invocation only.

Views are your menu. Create views for common queries. Views compose — they can reference other views, use joins, and include aggregations. They're more powerful than memhub's saved queries.

Use both relationship styles. Structural foreign keys for ownership (clipping→resource), flexible links table for ad-hoc graph relationships (note→note).

Include setup scripts. Provide an idempotent setup.sh that creates the database, runs the DDL, and initializes views. Users should be able to initialize a working database with one command.

Show real workflows. Don't just list SQL patterns — show the full flow of creating records, linking them, querying, updating, and analyzing over time.

Be honest about tradeoffs. SQL is verbose. String escaping is hazardous. But the power (JOINs, FTS, aggregations, window functions) makes it worthwhile for certain domains.

FAQ

What does the sqlite-db skill do?

How do I install sqlite-db?

Run npx skills add https://github.com/bfollington/terma --skill sqlite-db in your project. The skills CLI detects the agents you use and copies the skill into the right folder. For Claude Code you can also place the skill folder at ~/.claude/skills/sqlite-db/ (personal) or .claude/skills/sqlite-db/ (project).

Which AI agents can use sqlite-db?

Any agent that supports the open Agent Skills format (a folder with a SKILL.md file), including Claude Code, Codex, Cursor, GitHub Copilot, Gemini CLI, Windsurf, Cline, OpenCode and more.

Is sqlite-db safe to install?

No partner security audit is available yet. Skills can run commands and scripts, so read the SKILL.md and any bundled files before installing.

Who maintains sqlite-db?

It is published by bfollington in bfollington/terma. It has 50 recorded installs.

More from bfollington/terma

View all →

shadertoy

bfollington/terma

This skill should be used when working with Shadertoy shaders, GLSL fragment shaders, or creating procedural graphics for the web. Use when writing .glsl files, implementing visual effects, creating generative art, or working with WebGL shader code. This skill provides GLSL ES syntax reference, common shader patterns, and Shadertoy-specific conventions.

Media & Creative1.5K installs

bevy

bfollington/terma

Open the skill to read its SKILL.md instructions.

255 installs

strudel

bfollington/terma

Open the skill to read its SKILL.md instructions.

142 installs

godot

bfollington/terma

This skill should be used when working on Godot Engine projects. It provides specialized knowledge of Godot's file formats (.gd, .tscn, .tres), architecture patterns (component-based, signal-driven, resource-based), common pitfalls, validation tools, code templates, and CLI workflows. The godot command is available for running the game, validating scripts, importing resources, and exporting builds. Use this skill for tasks involving Godot game development, debugging scene/resource files, implementing game systems, or creating new Godot components.

Media & Creative115 installs

skill-improver

bfollington/terma

Use at a checkpoint after real friction — a task where something was repeatedly confusing, a skill misfired or failed to fire, or a workaround was invented that should not have been needed. Concerns turning friction into a change to the skills themselves, and knowing when the right change is to cut rather than add. Trigger at session end after substantial work, or when the same problem has now happened more than once. Do NOT use reflexively after routine tasks, or mid-task.

Productivity & Automation87 installs

domain-driven-design

bfollington/terma

This skill should be used whenever domain modeling is taking place. It provides specialized guidance for type-driven and data-driven design based on Rich Hickey and Scott Wlaschin's principles. The skill helps contextualize current modeling within the existing domain model, identifies inconsistencies, builds ubiquitous language, and creates visualizations (Mermaid, Graphviz/DOT, ASCII diagrams) to communicate domain concepts clearly. Use this skill when designing types, modeling business domains, refactoring domain logic, or ensuring domain consistency across a codebase.

Code Quality & Workflow70 installs

Popular data & databases skills

View category →

supabase-postgres-best-practices

supabase/agent-skills

Postgres best practices maintained by Supabase, for Postgres running anywhere. Load this skill BEFORE writing or changing anything that lives in a Postgres database: creating or altering tables and columns (including choosing column types), schema design, migrations and declarative schema files, RLS policies and the tests that verify them, indexes, triggers, database functions, queues and scheduled jobs (pgcron, pgmq), vector/semantic search (pgvector), and restoring dumps (pgrestore) or importing data. Also load it when diagnosing slow queries, high CPU, timeouts, EXPLAIN plans, connection exhaustion, locking, bloat, or rows visible to the wrong user or tenant. This is not just a performance guide — schema, migration, security, and SQL authoring tasks need these rules too, even for a one-column change or a single query.

OfficialData & Databases462.2K installs

prisma-client-api

prisma/skills

Prisma Client API reference covering model queries, filters, operators, and client methods. Use when writing database queries, using CRUD operations, filtering data, or configuring Prisma Client. Triggers on "prisma query", "findMany", "create", "update", "delete", "$transaction".

OfficialData & Databases393.5K installs

prisma-database-setup

prisma/skills

Deprecated compatibility name for prisma-orm-setup. Use only when a user or existing integration explicitly invokes prisma-database-setup; otherwise select prisma-orm-setup.

OfficialData & Databases392.3K installs

prisma-cli

prisma/skills

Prisma ORM 7 CLI reference for existing Prisma 7 applications or explicitly selected Prisma 7 workflows. Covers prisma init, generate, migrate, db, dev, studio, and mcp. For setup, connection repair, or Prisma 8, use prisma-orm-setup; not the Prisma Platform CLI.

OfficialData & Databases390.8K installs

prisma-postgres

prisma/skills

Deprecated compatibility name for prisma-postgres-setup. Use only when a user or existing integration explicitly invokes prisma-postgres; otherwise select prisma-postgres-setup.

OfficialData & Databases384.4K installs

prisma-driver-adapter-implementation

prisma/skills

Required reference for Prisma ORM 7 SQL driver adapter work. Use when implementing or modifying adapters, adding database drivers, or touching SqlDriverAdapter, Transaction, savepoint, result mapping, or DriverAdapterError behavior. Covers current transaction lifecycle, optional savepoint hooks, original database-error preservation, and verification.

OfficialData & Databases378.9K installs

sqlite-db

Install

Install

Describe the task

Review the result

SKILL.md

SQLite Database Skills

Philosophy

SQL is the Interface

Schemas Are the DDL

Composable .db Files

Agent-Compatible

Database Targeting

Output Modes

JSON Mode Example

Core Operations

Initialize a Database

Generate IDs

Create Records

Query Records

Show a Record

Update Records

Delete Records

Relationships

Structural Relationships (Foreign Key Columns)

Flexible Relationships (Links Table)

Views as Saved Queries

Create a View

Query a View

List All Views

Drop a View

Complex View Example

Triggers for Automation

Auto-Update Timestamps

FTS Sync Triggers

Full-Text Search (FTS5)

Create FTS Virtual Table

Sync Triggers

Search Queries

JSON Functions

Creating JSON Arrays

Querying JSON Arrays

Updating JSON Arrays

JSON Validation

CHECK Constraints for Validation

Enum-Style Constraints

Range Constraints

Pattern Constraints (Regex)

Aggregations

Basic Aggregations

Advanced Aggregations

Building a SQLite-DB Skill

Directory Layout

What a SQLite Skill Should Define

Design Principles

FAQ

More from bfollington/terma

Popular data & databases skills