database-patterns

spences10

Updated 4 days ago

112 views

Otherdata

About

This skill provides SQLite database patterns using better-sqlite3 with prepared statements for secure CRUD operations. It implements key features including nanoid primary keys, Unix timestamp management, and user-scoped queries with row-level security. Use this when building applications that require structured database interactions with security best practices.

Quick Install

Claude Code

Recommended

Plugin CommandRecommended

/plugin add https://github.com/spences10/devhub-crm

Git CloneAlternative

git clone https://github.com/spences10/devhub-crm.git ~/.claude/skills/database-patterns

Copy and paste this command in Claude Code to install this skill

Documentation

Database Patterns

Quick Start

import { db } from '$lib/server/db';
import { nanoid } from 'nanoid';

// SELECT with user_id (row-level security)
const contact = db
	.prepare('SELECT * FROM contacts WHERE id = ? AND user_id = ?')
	.get(id, user_id) as Contact | undefined;

// INSERT with nanoid and timestamps
const stmt = db.prepare(
	'INSERT INTO contacts (id, user_id, name, created_at, updated_at) VALUES (?, ?, ?, ?, ?)',
);
stmt.run(nanoid(), user_id, name, Date.now(), Date.now());

Core Principles

Prepared statements: Use for all queries (SQL injection prevention)
ID generation: Use nanoid() for all primary keys (no auto-increment)
Timestamps: Store as Unix epoch with Date.now() (milliseconds)
Row-level security: Always include user_id in WHERE clause (never query by ID alone)
Transactions: Use for multi-table operations (all-or-nothing)
Synchronous: better-sqlite3 is sync - no async/await needed

Reference Files

schema.md - Complete schema with columns and types
relationships.md - Table relationships and foreign keys
query-examples.md - Joins, transactions, and advanced patterns

GitHub Repository

spences10/devhub-crm

Path: .claude/skills/database-patterns

Related Skills

content-collections

Meta

This skill provides a production-tested setup for Content Collections, a TypeScript-first tool that transforms Markdown/MDX files into type-safe data collections with Zod validation. Use it when building blogs, documentation sites, or content-heavy Vite + React applications to ensure type safety and automatic content validation. It covers everything from Vite plugin configuration and MDX compilation to deployment optimization and schema validation.

View skill

hybrid-cloud-networking

Meta

This skill configures secure hybrid cloud networking between on-premises infrastructure and cloud platforms like AWS, Azure, and GCP. Use it when connecting data centers to the cloud, building hybrid architectures, or implementing secure cross-premises connectivity. It supports key capabilities such as VPNs and dedicated connections like AWS Direct Connect for high-performance, reliable setups.

View skill

csv-data-summarizer

Meta

This skill automatically analyzes CSV files to generate comprehensive statistical summaries and visualizations using Python's pandas and matplotlib/seaborn. It should be triggered whenever a user uploads or references CSV data without prompting for analysis preferences. The tool provides immediate insights into data structure, quality, and patterns through automated analysis and visualization.

View skill

llamaindex

Meta

LlamaIndex is a data framework for building RAG-powered LLM applications, specializing in document ingestion, indexing, and querying. It provides key features like vector indices, query engines, and agents, and supports over 300 data connectors. Use it for document Q&A, chatbots, and knowledge retrieval when building data-centric applications.

View skill