nestjs-better-sqlite3

NestJS Better SQLite3

Minimal SQLite3 module for NestJS with type safety.

Motivation: ORM's are great at abstracting a lot of complexity but often introduce bugs and produce slow queries; In addition, I rarely find myself wanting to switch between different databases, so I figured I write a lightweight wrapper around better-sqlite3.

Features

• Some query helpers (create, findOne, findAll, update, delete, count)
• Migrations (SQL)
• Type safe
• Prevent SQL Injections

Setup

npm install nestjs-better-sqlite3

Configuration

// Basic
@Module({
    imports: [Sqlite3Module.forRoot({ dataSource: 'sqlite.db' })]
})

// With options
@Module({
    imports: [Sqlite3Module.forRoot({
        dataSource: 'app.db',
        enableWal: true,
        runMigrations: true,
        migrationsDir: './migrations',
        entities: [User, Post]
    })]
})

Migrations

Migrations are organized in folders with up.sql and down.sql files:

migrations/
├── 1732471234-create-users/
│   ├── up.sql
│   └── down.sql
└── 1732471456-create-posts/
    ├── up.sql
    └── down.sql

Example migration files:

-- migrations/1732471234-create-users/up.sql
CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL
);

-- migrations/1732471234-create-users/down.sql
DROP TABLE users;

CLI Commands

# Generate new migration
npx sqlite3-migrate migration:generate create-users

# Run pending migrations
npx sqlite3-migrate migration:run

# Revert last migration
npx sqlite3-migrate migration:revert

# Custom paths
npx sqlite3-migrate migration:run --db app.db --migrations ./db/migrations

Usage

Entities

@Entity('users')
class User {
    id: number
    name: string
    email: string
}

Repository Methods

const repo = this.db.getRepository(User)

// CRUD operations
repo.create({ name: 'John', email: '[e-mail]' })
repo.findAll({ limit: 10, offset: 5, orderBy: { column: 'name', direction: 'ASC' } })
repo.findOne({ email: '[e-mail]' })
repo.update({ id: 1 }, { name: 'Jane' })
repo.delete({ id: 1 })
repo.count({ active: true })

Raw Queries

// Type-safe raw queries
this.db.query<User>('SELECT * FROM users WHERE age > ?', [18])
this.db.queryOne<User>('SELECT * FROM users WHERE id = ?', [1])
this.db.execute('INSERT INTO users (name) VALUES (?)', ['Alice'])

Transactions

this.db.transaction((db) => {
    const stmt = db.prepare('INSERT INTO users (name) VALUES (?)')
    stmt.run('User 1')
    stmt.run('User 2')
})

Tests

npm run test

TODO

• The Better SQLite 3 logic should be part of a generic library