Building a Real-World API with RepoDB

What We're Building

We are going to build the backend for a simple CRM (Customer Relationship Management) tool. We need to store users, track their status, and log when they were added.

1. Designing the Schema

SQLite is dynamically typed, but RepoDB enforces data integrity. We recommend being strict with your types.

const setupQuery = `
    -- Enable foreign keys just in case we extend later
    PRAGMA foreign_keys = ON;

    CREATE TABLE IF NOT EXISTS customers (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        full_name TEXT NOT NULL,
        email TEXT UNIQUE NOT NULL CHECK(email LIKE '%@%'),
        status TEXT DEFAULT 'lead' CHECK(status IN ('lead', 'customer', 'churned')),
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP
    );
    
    CREATE INDEX IF NOT EXISTS idx_email ON customers(email);
`;

await db.execute(setupQuery);
console.log("Schema initialized successfully.");

Pro Tip: Creating indexes on columns you search frequently (like email) can speed up your queries by 100x.

2. Secure Data Insertion

The #1 vulnerability in web apps is SQL Injection. RepoDB solves this by supporting parameterized queries out of the box. Never inject strings directly!

async function addCustomer(name, email) {
    const query = `
        INSERT INTO customers (full_name, email) 
        VALUES (?, ?) 
        RETURNING id, created_at
    `;
    
    try {
        // Pass user input as the second argument array
        const result = await db.execute(query, [name, email]);
        return result.rows[0];
    } catch (error) {
        if (error.message.includes('UNIQUE constraint failed')) {
            throw new Error('This email is already registered.');
        }
        throw error;
    }
}

3. Complex Querying & Filtering

Let's find all recent customers. Note how effortless it is to write standard SQL.

const result = await db.execute(`
    SELECT * FROM customers 
    WHERE status = 'customer' 
    ORDER BY created_at DESC 
    LIMIT 20
`);

console.table(result.rows);

With RepoDB, you are writing native SQL. You don't have to learn a proprietary Query Language or a complex ORM syntax. If you know SQL, you know RepoDB.