SQLite Node.js: Run, Get, All Methods

Create a free account to save your progress

Earn XP, track streaks, and sync your dashboard across devices.

Lesson

You've seen how simple SQLite is conceptually. Now let's connect it to Node.js and build real applications. There are two popular approaches: the async sqlite wrapper for sqlite3, and the synchronous (but faster) better-sqlite3.

The async approach: sqlite + sqlite3

The sqlite package is a modern wrapper around sqlite3 that adds PromiseWhat is promise?An object that represents a value you don't have yet but will get in the future, letting your code keep running while it waits. support. It's the most popular choice for web applications.

Installation:

npm install sqlite sqlite3

Basic usage:

import sqlite3 from 'sqlite3';
import { open } from 'sqlite';

// Open database connection
async function getDb() {
  return open({
    filename: process.env.DATABASE_URL || './database.db',
    driver: sqlite3.Database
  });
}

// Create connection
const db = await getDb();

// Create table
await db.exec(`
  CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT UNIQUE,
    age INTEGER,
    created_at TEXT DEFAULT CURRENT_TIMESTAMP
  )
`);

CRUDWhat is crud?Create, Read, Update, Delete - the four basic operations almost every application performs on data. operations

Here's a complete repositoryWhat is repository?A project folder tracked by Git that stores your files along with the complete history of every change, inside a hidden .git directory. pattern for SQLite:

class UserRepository {
  constructor(db) {
    this.db = db;
  }
  
  // CREATE - Insert a new user
  async create(user) {
    const result = await this.db.run(
      'INSERT INTO users (name, email, age) VALUES (?, ?, ?)',
      [user.name, user.email, user.age]
    );
    
    // result.lastID contains the auto-generated ID
    return { 
      id: result.lastID, 
      ...user,
      created_at: new Date().toISOString()
    };
  }
  
  // READ one - Get single user by ID
  async findById(id) {
    return this.db.get(
      'SELECT * FROM users WHERE id = ?',
      [id]
    );
  }
  
  // READ many - Get all users with pagination
  async findAll(options = {}) {
    const { limit = 20, offset = 0 } = options;
    return this.db.all(
      'SELECT * FROM users ORDER BY created_at DESC LIMIT ? OFFSET ?',
      [limit, offset]
    );
  }
  
  // UPDATE - Modify existing user
  async update(id, updates) {
    const fields = Object.keys(updates);
    const values = Object.values(updates);
    
    // Build dynamic SET clause
    const setClause = fields.map(f => `${f} = ?`).join(', ');
    
    await this.db.run(
      `UPDATE users SET ${setClause} WHERE id = ?`,
      [...values, id]
    );
    
    return this.findById(id);
  }
  
  // DELETE - Remove user
  async delete(id) {
    const result = await this.db.run(
      'DELETE FROM users WHERE id = ?',
      [id]
    );
    // result.changes tells how many rows were affected
    return result.changes > 0;
  }
  
  // SEARCH - Find users by name or email
  async search(query) {
    const searchTerm = `%${query}%`;
    return this.db.all(
      'SELECT * FROM users WHERE name LIKE ? OR email LIKE ?',
      [searchTerm, searchTerm]
    );
  }
}

// Usage
const users = new UserRepository(db);

const newUser = await users.create({
  name: 'Bob',
  email: 'bob@example.com',
  age: 25
});
console.log('Created:', newUser);

const found = await users.findById(newUser.id);
console.log('Found:', found);

The ? placeholder

Always use parameterized queries with ? placeholders. Never concatenate user input into SQL strings, that's how SQL injection attacks happen.

The synchronous approach: better-sqlite3

For performance-critical applications, better-sqlite3 offers significant speed improvements. It's synchronous, but in practice this rarely matters for SQLite since it's file-based anyway.

npm install better-sqlite3

import Database from 'better-sqlite3';

const db = new Database('mydata.db');

// Prepare statements (compiles once, runs many times)
const insertUser = db.prepare(
  'INSERT INTO users (name, email) VALUES (?, ?)'
);

// Execute
const result = insertUser.run('Alice', 'alice@example.com');
console.log('Inserted ID:', result.lastInsertRowid);

// SELECT one
const getUser = db.prepare('SELECT * FROM users WHERE id = ?');
const user = getUser.get(1);

// SELECT many
const getAllUsers = db.prepare('SELECT * FROM users LIMIT ?');
const users = getAllUsers.all(10);

The key difference: better-sqlite3 encourages preparing statements once and reusing them. This is faster than parsing SQLWhat is sql?A language for querying and managing data in relational databases, letting you insert, read, update, and delete rows across tables. every time.

Handling transactions

Transactions ensure multiple operations succeed or fail together. Essential for maintaining data integrity:

// Transfer money between accounts
async function transferMoney(fromId, toId, amount) {
  try {
    // Start transaction
    await db.exec('BEGIN TRANSACTION');
    
    // Deduct from sender
    await db.run(
      'UPDATE accounts SET balance = balance - ? WHERE id = ?',
      [amount, fromId]
    );
    
    // Add to receiver
    await db.run(
      'UPDATE accounts SET balance = balance + ? WHERE id = ?',
      [amount, toId]
    );
    
    // Record the transaction
    await db.run(
      'INSERT INTO transactions (from_id, to_id, amount) VALUES (?, ?, ?)',
      [fromId, toId, amount]
    );
    
    // Commit if all succeeded
    await db.exec('COMMIT');
    
  } catch (error) {
    // Rollback on any error
    await db.exec('ROLLBACK');
    throw error;
  }
}

Without the transactionWhat is transaction?A group of database operations that either all succeed together or all fail together, preventing partial updates., if the server crashes after deducting from the sender but before adding to the receiver, money disappears. With transactions, it's all-or-nothing.

Error handling

Database errors happen. Handle them gracefully:

async function safeInsertUser(db, user) {
  try {
    return await db.run(
      'INSERT INTO users (email, name) VALUES (?, ?)',
      [user.email, user.name]
    );
  } catch (error) {
    if (error.message.includes('UNIQUE constraint failed')) {
      throw new Error('This email is already registered');
    }
    if (error.message.includes('FOREIGN KEY constraint failed')) {
      throw new Error('Referenced record does not exist');
    }
    // Unknown error, re-throw
    throw error;
  }
}

Simple migrationWhat is migration?A versioned script that changes your database structure (add a column, create a table) so every developer and server stays in sync. system

As your app evolves, your schemaWhat is schema?A formal definition of the structure your data must follow - which fields exist, what types they have, and which are required. needs to change. Here's a lightweight migration system:

const migrations = [
  {
    version: 1,
    description: 'Create users table',
    up: `
      CREATE TABLE users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        email TEXT UNIQUE
      );
    `
  },
  {
    version: 2,
    description: 'Add timestamps to users',
    up: `
      ALTER TABLE users 
      ADD COLUMN created_at TEXT DEFAULT CURRENT_TIMESTAMP;
    `
  },
  {
    version: 3,
    description: 'Create posts table',
    up: `
      CREATE TABLE posts (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        user_id INTEGER REFERENCES users(id),
        title TEXT,
        content TEXT,
        created_at TEXT DEFAULT CURRENT_TIMESTAMP
      );
    `
  }
];

export async function migrate(db) {
  // Create migrations table if it doesn't exist
  await db.exec(`
    CREATE TABLE IF NOT EXISTS migrations (
      version INTEGER PRIMARY KEY,
      applied_at TEXT DEFAULT CURRENT_TIMESTAMP
    )
  `);
  
  // Get list of applied migrations
  const applied = await db.all('SELECT version FROM migrations');
  const appliedVersions = new Set(applied.map(a => a.version));
  
  // Apply pending migrations
  for (const migration of migrations) {
    if (!appliedVersions.has(migration.version)) {
      console.log(`Applying migration ${migration.version}: ${migration.description}`);
      
      await db.exec(migration.up);
      await db.run(
        'INSERT INTO migrations (version) VALUES (?)',
        [migration.version]
      );
      
      console.log(`✓ Migration ${migration.version} applied`);
    }
  }
}

Quick reference: SQLite methods

Method	Returns	Use for
`db.run()`	`{ lastID, changes }`	INSERT, UPDATE, DELETE
`db.get()`	Single object or undefined	SELECT one row
`db.all()`	Array of objects	SELECT multiple rows
`db.exec()`	Nothing	Multiple statements, DDL
`db.each()`	Nothing (callback)	Stream large results

SQLite with Node.js combines simplicity with power. For development, testing, and small applications, it's often all you need. When you're ready to scale, your SQLWhat is sql?A language for querying and managing data in relational databases, letting you insert, read, update, and delete rows across tables. skills transfer directly to PostgreSQL.

Done

Complete & Next