sqlite

name	sqlite
description	[Applies to: **/*] Definitive guidelines for writing robust, performant, and secure SQLite code. Focuses on schema design, query optimization, and transaction management.
source	cursor_mdc

sqlite Best Practices

SQLite is the go-to embedded SQL engine for local, reliable storage. Adhere to these rules to ensure your SQLite code is maintainable, performant, and secure.

1. Data Modeling & Schema Design

Design your schema for integrity and performance from day one.

• Primary Keys: Always use INTEGER PRIMARY KEY AUTOINCREMENT for ID columns. This optimizes rowid lookups and simplifies ID generation.

  • ❌ BAD:

    CREATE TABLE users (
        id TEXT PRIMARY KEY, -- Manual UUIDs or similar
        name TEXT NOT NULL
    );
    

  • ✅ GOOD:

    CREATE TABLE users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL
    );
    

• Data Types & Constraints: Declare appropriate data types and enforce integrity with NOT NULL, UNIQUE, and FOREIGN KEY constraints.

  • ❌ BAD:

    CREATE TABLE products (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT, -- Allows NULL, no uniqueness
        price REAL
    );
    

  • ✅ GOOD:

    CREATE TABLE products (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL UNIQUE,
        price REAL NOT NULL,
        stock INTEGER DEFAULT 0,
        category_id INTEGER,
        FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL
    );
    

• Naming Conventions: Use lower_case_snake_case for all table, column, and index names. Avoid SQLite keywords as identifiers.

  • ❌ BAD: CREATE TABLE My_Users ( UserId INTEGER PRIMARY KEY );
  • ✅ GOOD: CREATE TABLE my_users ( user_id INTEGER PRIMARY KEY );

2. Performance Considerations

Optimize for speed by minimizing I/O and leveraging the SQLite engine.

• Enable WAL Mode: Always enable Write-Ahead Logging for better concurrency and write performance.

  • ❌ BAD: Default journal mode (DELETE).
  • ✅ GOOD (at database open or once):

    PRAGMA journal_mode = WAL;
    

• Relax Synchronous Mode: When using WAL, set synchronous to NORMAL for faster commits, accepting minimal risk of data loss on power failure (not app crash).

  • ❌ BAD: Default synchronous = FULL.
  • ✅ GOOD (at database open or once):

    PRAGMA synchronous = NORMAL;
    

• Indexes: Create indexes on columns frequently used in WHERE, ORDER BY, GROUP BY, or JOIN clauses. Avoid over-indexing.

  • ❌ BAD:

    SELECT * FROM users WHERE email = '[e-mail]'; -- No index on email
    

  • ✅ GOOD:

    CREATE INDEX idx_users_email ON users(email);
    SELECT id, name FROM users WHERE email = '[e-mail]';
    

  • Multi-column Indexes: For queries filtering/sorting on multiple columns, create a multi-column index matching the query order.

    CREATE INDEX idx_products_category_price ON products(category_id, price);
    SELECT * FROM products WHERE category_id = 1   price ;
    

3. Transactions & Concurrency

Ensure data consistency and improve write performance with explicit transactions.

• Wrap Writes in Transactions: Group multiple INSERT, UPDATE, DELETE operations within a single transaction. This significantly reduces disk I/O.

  • ❌ BAD:

    INSERT INTO logs (action) VALUES ('User created');
    INSERT INTO users (name) VALUES ('New User');
    INSERT INTO logs (action) VALUES ('User name updated');
    UPDATE users SET name = 'Updated User' WHERE id = 1;
    

  • ✅ GOOD:

    BEGIN;
    INSERT INTO logs (action) VALUES ('User created');
    INSERT INTO users (name) VALUES ('New User');
    INSERT INTO logs (action) VALUES ('User name updated');
    UPDATE users SET name = 'Updated User' WHERE id = 1;
    COMMIT;
    

• Error Handling: Use ROLLBACK to revert all changes if any operation within a transaction fails.

  • ✅ GOOD:

    BEGIN;
    -- Perform operations
    INSERT INTO users (name) VALUES ('Valid User');
    INSERT INTO users (name)  (); 
    
    ;
    
    ;
    

4. Security Best Practices

Prevent common vulnerabilities like SQL injection.

• Prepared Statements: Always use prepared statements with bound parameters. NEVER concatenate user input directly into SQL queries.

  • ❌ BAD:

    String name = userInput.getName();
    String sql = "INSERT INTO users (name) VALUES ('" + name + "');"; // SQL Injection risk!
    

  • ✅ GOOD (using a typical API pattern):

    PreparedStatement stmt = connection.prepareStatement("INSERT INTO users (name) VALUES (?);");
    stmt.setString(1, userInput.getName());
    stmt.executeUpdate();
    

• Enable Foreign Key Enforcement: Always enable foreign key constraints at runtime. SQLite defaults to OFF for backward compatibility.

  • ❌ BAD: Forgetting to enable foreign keys, leading to orphaned records.
  • ✅ GOOD (at database open or once per connection):

    PRAGMA foreign_keys = ON;
    

• File Permissions: Store database files in write-protected directories and set restrictive file permissions to limit unauthorized access. This is OS-specific but critical.

5. Common Pitfalls & Gotchas

Avoid these common mistakes that lead to bugs and performance issues.

• Forgetting PRAGMA foreign_keys = ON;: This is the most common pitfall. Always enable it.
• Selecting *: Only retrieve the columns you actually need.
• Application-level Filtering/Sorting: Delegate these operations to SQL for better performance, especially on large datasets.
• Not Using Transactions: Leads to slow writes and potential data inconsistencies.
• Using SQLite for High-Concurrency Writes: SQLite is a single-writer database. If multiple processes need to write concurrently, consider a client-server RDBMS.

6. Testing Approaches

Ensure your data access logic is robust and correct.

• In-Memory Databases: Use :memory: databases for fast, isolated unit and integration tests of your data access layer.

  • ✅ GOOD (example in Python, similar patterns exist in other languages):

    import sqlite3
    conn = sqlite3.connect(':memory:')
    cursor = conn.cursor()
    cursor.execute("CREATE TABLE test_data (id INTEGER PRIMARY KEY, value TEXT)")
    # ... run tests ...
    conn.close() # Database vanishes
    

• Seed Data: Create consistent, reproducible test data for your tests.

• Mocking: For higher-level tests, mock your database interactions to focus on business logic.