database-patterns

spences10

Updated Yesterday

71 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.

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

Quick Install

/plugin add https://github.com/spences10/devhub-crm/tree/main/database-patterns

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

GitHub 仓库

spences10/devhub-crm

Path: .claude/skills/database-patterns

Related Skills

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

Excel Analysis

Meta

This skill enables developers to analyze Excel files and perform data operations using pandas. It can read spreadsheets, create pivot tables, generate charts, and conduct data analysis on .xlsx files and tabular data. Use it when working with Excel files, spreadsheets, or any structured tabular data within Claude Code.

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

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