JS to a Local SQLite Database Get Certified

SQLite is a lightweight, file-based database that needs no separate server. In this lesson we use a clean mock database that works exactly like the real better-sqlite3 / node:sqlite API, so you can learn the patterns safely in the browser.

Creating a Database and Table

What this code does: Creates an in-memory mock database and a simple users table.

function createMockDatabase() {
    const tables = {};

    return {
        exec(sql) {
            const match = sql.match(/CREATE TABLE(?: IF NOT EXISTS)?\s+(\w+)\s*\(([^)]+)\)/i);
            if (match) {
                const name = match[1];
                tables[name] = { rows: [], nextId: 1 };
                console.log(`Table "${name}" created`);
            }
        },
        prepare(sql) {
            // Will be expanded in next examples
            return { run() {}, all() { return []; }, get() { return undefined; } };
        },
        // helper for demos
        _tables: tables
    };
}

const db = createMockDatabase();
db.exec(`CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, email TEXT)`);
console.log("Database ready");

Inserting Data

What this code does: Prepares an INSERT statement and inserts two users using safe ? placeholders.

function createMockDatabase() {
    const tables = {};

    return {
        exec(sql) {
            const m = sql.match(/CREATE TABLE(?: IF NOT EXISTS)?\s+(\w+)/i);
            if (m) tables[m[1]] = { rows: [], nextId: 1 };
        },
        prepare(sql) {
            const insertMatch = sql.match(/INSERT INTO\s+(\w+)\s*\(([^)]+)\)\s*VALUES\s*\(([^)]+)\)/i);
            if (insertMatch) {
                const tableName = insertMatch[1];
                const cols = insertMatch[2].split(",").map(c => c.trim());
                return {
                    run(...params) {
                        const table = tables[tableName];
                        const row = { id: table.nextId++ };
                        cols.forEach((col, i) => row[col] = params[i]);
                        table.rows.push(row);
                        return { lastInsertRowid: row.id, changes: 1 };
                    }
                };
            }
            return { run() {}, all() { return []; } };
        }
    };
}

const db = createMockDatabase();
db.exec(`CREATE TABLE users (id, name, email)`);

const insert = db.prepare(`INSERT INTO users (name, email) VALUES (?, ?)`);

const r1 = insert.run("Ada Lovelace", "ada@example.com");
const r2 = insert.run("Grace Hopper", "grace@example.com");

console.log("Inserted Ada with id:", r1.lastInsertRowid);
console.log("Inserted Grace with id:", r2.lastInsertRowid);

Querying Data (.all and .get)

What this code does: Inserts users, then uses .all() to get every row and .get() to fetch one specific user.

function createMockDatabase() {
    const tables = {};

    return {
        exec(sql) {
            const m = sql.match(/CREATE TABLE(?: IF NOT EXISTS)?\s+(\w+)/i);
            if (m) tables[m[1]] = { rows: [], nextId: 1 };
        },
        prepare(sql) {
            // INSERT
            const insertMatch = sql.match(/INSERT INTO\s+(\w+)\s*\(([^)]+)\)\s*VALUES/i);
            if (insertMatch) {
                const tableName = insertMatch[1];
                const cols = insertMatch[2].split(",").map(c => c.trim());
                return {
                    run(...params) {
                        const table = tables[tableName];
                        const row = { id: table.nextId++ };
                        cols.forEach((c, i) => row[c] = params[i]);
                        table.rows.push(row);
                        return { lastInsertRowid: row.id, changes: 1 };
                    }
                };
            }

            // SELECT
            const selectMatch = sql.match(/SELECT\s+.+\s+FROM\s+(\w+)(?:\s+WHERE\s+(\w+)\s*=\s*\?)?/i);
            if (selectMatch) {
                const tableName = selectMatch[1];
                const whereCol = selectMatch[2];
                return {
                    all(...params) {
                        let rows = tables[tableName].rows.slice();
                        if (whereCol) rows = rows.filter(r => r[whereCol] === params[0]);
                        return rows;
                    },
                    get(...params) {
                        return this.all(...params)[0];
                    }
                };
            }

            return { run() {}, all() { return []; }, get() {} };
        }
    };
}

const db = createMockDatabase();
db.exec(`CREATE TABLE users (id, name, email)`);

const insert = db.prepare(`INSERT INTO users (name, email) VALUES (?, ?)`);
insert.run("Ada Lovelace", "ada@example.com");
insert.run("Grace Hopper", "grace@example.com");
insert.run("Alan Turing", "alan@example.com");

const allUsers = db.prepare(`SELECT * FROM users`).all();
console.log("All users:", allUsers);

const ada = db.prepare(`SELECT * FROM users WHERE name = ?`).get("Ada Lovelace");
console.log("Found Ada:", ada);

Updating and Deleting Rows

What this code does: Updates Ada’s email, deletes Grace, then shows the remaining rows.

function createMockDatabase() {
    const tables = {};

    return {
        exec(sql) {
            const m = sql.match(/CREATE TABLE(?: IF NOT EXISTS)?\s+(\w+)/i);
            if (m) tables[m[1]] = { rows: [], nextId: 1 };
        },
        prepare(sql) {
            // INSERT
            if (/INSERT INTO/i.test(sql)) {
                const tableName = sql.match(/INSERT INTO\s+(\w+)/i)[1];
                const cols = sql.match(/\(([^)]+)\)/)[1].split(",").map(c => c.trim());
                return {
                    run(...params) {
                        const table = tables[tableName];
                        const row = { id: table.nextId++ };
                        cols.forEach((c, i) => row[c] = params[i]);
                        table.rows.push(row);
                        return { lastInsertRowid: row.id, changes: 1 };
                    }
                };
            }

            // UPDATE
            if (/UPDATE/i.test(sql)) {
                const tableName = sql.match(/UPDATE\s+(\w+)/i)[1];
                const setCol = sql.match(/SET\s+(\w+)/i)[1];
                const whereCol = sql.match(/WHERE\s+(\w+)/i)[1];
                return {
                    run(setValue, whereValue) {
                        let changes = 0;
                        tables[tableName].rows.forEach(row => {
                            if (row[whereCol] === whereValue) {
                                row[setCol] = setValue;
                                changes++;
                            }
                        });
                        return { changes };
                    }
                };
            }

            // DELETE
            if (/DELETE FROM/i.test(sql)) {
                const tableName = sql.match(/DELETE FROM\s+(\w+)/i)[1];
                const whereCol = sql.match(/WHERE\s+(\w+)/i)[1];
                return {
                    run(whereValue) {
                        const before = tables[tableName].rows.length;
                        tables[tableName].rows = tables[tableName].rows.filter(r => r[whereCol] !== whereValue);
                        return { changes: before - tables[tableName].rows.length };
                    }
                };
            }

            // SELECT
            if (/SELECT/i.test(sql)) {
                const tableName = sql.match(/FROM\s+(\w+)/i)[1];
                return {
                    all() { return tables[tableName].rows.slice(); }
                };
            }

            return { run() {}, all() { return []; } };
        }
    };
}

const db = createMockDatabase();
db.exec(`CREATE TABLE users (id, name, email)`);

const insert = db.prepare(`INSERT INTO users (name, email) VALUES (?, ?)`);
insert.run("Ada Lovelace", "ada@example.com");
insert.run("Grace Hopper", "grace@example.com");

const update = db.prepare(`UPDATE users SET email = ? WHERE name = ?`);
const updateResult = update.run("ada.lovelace@newmail.com", "Ada Lovelace");
console.log("Rows updated:", updateResult.changes);

const remove = db.prepare(`DELETE FROM users WHERE name = ?`);
const deleteResult = remove.run("Grace Hopper");
console.log("Rows deleted:", deleteResult.changes);

console.log("Remaining users:", db.prepare(`SELECT * FROM users`).all());

Practice: Todo List Database

What this code does: Creates a todos table, inserts tasks, marks one as done, and shows the remaining pending tasks.

function createMockDatabase() {
    const tables = {};

    return {
        exec(sql) {
            const m = sql.match(/CREATE TABLE(?: IF NOT EXISTS)?\s+(\w+)/i);
            if (m) tables[m[1]] = { rows: [], nextId: 1 };
        },
        prepare(sql) {
            if (/INSERT INTO/i.test(sql)) {
                const tableName = sql.match(/INSERT INTO\s+(\w+)/i)[1];
                const cols = sql.match(/\(([^)]+)\)\s*VALUES/i)[1].split(",").map(c => c.trim());
                return {
                    run(...params) {
                        const table = tables[tableName];
                        const row = { id: table.nextId++ };
                        cols.forEach((c, i) => row[c] = params[i]);
                        table.rows.push(row);
                        return { lastInsertRowid: row.id, changes: 1 };
                    }
                };
            }

            if (/UPDATE/i.test(sql)) {
                const tableName = sql.match(/UPDATE\s+(\w+)/i)[1];
                const setCol = sql.match(/SET\s+(\w+)/i)[1];
                const whereCol = sql.match(/WHERE\s+(\w+)/i)[1];
                return {
                    run(setValue, whereValue) {
                        let changes = 0;
                        tables[tableName].rows.forEach(row => {
                            if (row[whereCol] === whereValue) {
                                row[setCol] = setValue;
                                changes++;
                            }
                        });
                        return { changes };
                    }
                };
            }

            if (/SELECT/i.test(sql)) {
                const tableName = sql.match(/FROM\s+(\w+)/i)[1];
                const whereMatch = sql.match(/WHERE\s+(\w+)\s*=\s*\?/i);
                return {
                    all(...params) {
                        let rows = tables[tableName].rows.slice();
                        if (whereMatch) {
                            rows = rows.filter(r => r[whereMatch[1]] === params[0]);
                        }
                        return rows;
                    }
                };
            }

            return { run() {}, all() { return []; } };
        }
    };
}

const db = createMockDatabase();
db.exec(`CREATE TABLE todos (id, task, done)`);

const insert = db.prepare(`INSERT INTO todos (task, done) VALUES (?, ?)`);
insert.run("Write SQLite lesson", false);
insert.run("Review pull requests", false);
insert.run("Deploy to production", false);

console.log("All todos:", db.prepare(`SELECT * FROM todos`).all());

const markDone = db.prepare(`UPDATE todos SET done = ? WHERE task = ?`);
markDone.run(true, "Review pull requests");

console.log("--- after marking one done ---");
const pending = db.prepare(`SELECT * FROM todos WHERE done = ?`).all(false);
console.log("Still pending:", pending);

Quick Summary

db.exec() → create tables / run schema SQL
db.prepare(sql) → compile a reusable statement
stmt.run(...) → INSERT / UPDATE / DELETE
stmt.all() → get every matching row
stmt.get() → get the first matching row
Always use ? placeholders instead of string concatenation

Tip: The patterns you learn with this mock database are exactly the same ones used with real libraries like better-sqlite3 and Node’s built-in node:sqlite.

🧪 Test Your JavaScript Code

Edit the code on the left and click “Run Code” to see the console output.

📝 JavaScript Code

👁 Console Output

Click “Run Code” to see the output here.

Ctrl + Enter to run Esc to close