database-patterns

What problem does it solve?

This Skill provides robust and secure patterns for interacting with SQLite databases using better-sqlite3. It addresses common challenges like SQL injection, data integrity, user-specific data access (row-level security), and efficient data manipulation, ensuring your database operations are safe, performant, and maintainable.

Core Features & Use Cases

• Prepared Statements: Prevents SQL injection vulnerabilities by using parameterized queries for all database interactions, enhancing security.
• Row-Level Security: Enforces data isolation by ensuring all queries include a user_id filter, preventing unauthorized access to other users' data.
• Transactions: Guarantees data consistency for multi-step operations (e.g., creating a contact with associated tags) by ensuring all steps succeed or fail together.
• Use Case: Implement a CRM system where each user can only view and manage their own contacts. This Skill provides the necessary patterns to ensure that all contact, interaction, and tag data is strictly scoped to the authenticated user, preventing data leaks and ensuring data privacy.

Quick Start

To select a contact with row-level security, use db.prepare('SELECT * FROM contacts WHERE id = ? AND user_id = ?').get(id, user_id). For inserting new data, generate IDs with nanoid() and timestamps with Date.now(). Wrap multi-table operations in db.transaction() to ensure atomicity. Always use prepared statements for all queries to prevent SQL injection.