A modern, type-safe SQLite wrapper built on top of better-sqlite3 that provides a fluent, chainable API for database operations. This library offers automatic resource management, WAL mode by default, and comprehensive TypeScript support.
using keyword and Symbol.dispose# npm
npm install @arcmantle/sqlite-wrapper
# yarn
yarn add @arcmantle/sqlite-wrapper
# pnpm
pnpm add @arcmantle/sqlite-wrapper
import { Query } from '@arcmantle/sqlite-wrapper';
// Create a new query instance
using query = new Query('database.db');
// Create a table
query.define('users')
.primaryKey('id')
.column('name', 'TEXT')
.column('email', 'TEXT', { nullable: false })
.column('age', 'INTEGER', { value: 0 })
.query();
// Insert data
query.insert('users')
.values({ name: 'John Doe', email: 'john@example.com', age: 30 })
.query();
// Query data
const users = query.from('users')
.select('name', 'email')
.where(filter => filter.eq('age', 30))
.query();
interface User {
id: number;
name: string;
email: string;
age: number;
}
using query = new Query('database.db');
// Type-safe operations
const adults = query.from<User>('users')
.select('name', 'email') // TypeScript will validate these field names
.where(filter => filter
.and(
filter.exists('email'),
filter.oneOf('age', 18, 21, 25)
)
)
.orderBy('name', 'asc')
.limit(10)
.query();
The main entry point for database operations.
new Query(filename?: string) - Creates a new query instance with optional database file pathfrom<T>(table: string): SelectBuilder<T> - Creates a SELECT query builderinsert<T>(table: string): InsertBuilder<T> - Creates an INSERT query builderupdate<T>(table: string): UpdateBuilder<T> - Creates an UPDATE query builderdelete<T>(table: string): DeleteBuilder<T> - Creates a DELETE query builderdefine<T>(table: string): DefineBuilder<T> - Creates a table definition buildertransaction(fn: (query: Query) => void): void - Executes operations in a transactionquery.from('users')
.select('name', 'email') // Specify columns
.where(filter => filter.eq('active', true)) // Add conditions
.groupBy('department') // Group results
.orderBy('name', 'asc', true) // Sort with nulls last
.limit(50) // Limit results
.offset(10) // Skip rows
.query(); // Execute query
query.insert('users')
.values({
name: 'Jane Smith',
email: 'jane@example.com',
age: 28
})
.query();
query.update('users')
.values({ age: 29 })
.where(filter => filter.eq('id', 1))
.query();
query.delete('users')
.where(filter => filter.eq('active', false))
.query();
query.define('products')
.primaryKey('id')
.column('name', 'TEXT', { nullable: false })
.column('price', 'REAL', { value: 0.0 })
.column('description', 'TEXT', { nullable: true })
.column('in_stock', 'INTEGER', { value: true })
.query();
The filter system provides comprehensive condition building:
query.from('users')
.where(filter => filter
.and(
filter.eq('active', true), // Equality
filter.startsWith('name', 'John'), // String prefix
filter.contains('email', '@gmail'), // String contains
filter.oneOf('age', 25, 30, 35), // Value in list
filter.exists('phone'), // Not null
filter.glob('name', 'J*n') // Pattern matching
)
)
.query();
Available filter methods:
eq(field, value) - Equality comparisonstartsWith(field, value) - String starts withendsWith(field, value) - String ends withcontains(field, value) - String containsoneOf(field, ...values) - Value in listnotOneOf(field, ...values) - Value not in listexists(field) - Field is not nullnotExists(field) - Field is nullglob(field, pattern) - Unix shell-style pattern matchingand(...conditions) - Logical ANDor(...conditions) - Logical ORimport { tableExists, dropColumn, getCreateQuery, getTableColumns } from '@arcmantle/sqlite-wrapper';
// Check if table exists
if (tableExists('users')) {
console.log('Users table exists');
}
// Get table creation SQL
const createSQL = getCreateQuery(db, 'users');
// Get column information
const columns = getTableColumns(db, 'users');
// Drop a column (recreates table)
dropColumn(db, 'users', 'old_column');
import { sql, escapeString } from '@arcmantle/sqlite-wrapper';
// Template literal for raw SQL
const rawQuery = sql`SELECT * FROM users WHERE id = ${userId}`;
// Escape strings for SQL
const safeString = escapeString("Don't break SQL");
The library uses branded types to provide additional type safety:
import type { Branded } from '@arcmantle/sqlite-wrapper';
type UserId = Branded<number, 'UserId'>;
Utility type for making specific properties optional:
import type { Optional } from '@arcmantle/sqlite-wrapper';
type CreateUser = Optional<User, 'id'>; // Makes 'id' optional
Extend the DataModel class for automatic property assignment:
import { DataModel } from '@arcmantle/sqlite-wrapper';
class User extends DataModel {
id!: number;
name!: string;
email!: string;
constructor(values: any) {
super(values); // Automatically assigns all properties
}
static parse(data: unknown): User {
// Custom parsing logic
return new User(data);
}
}
The library supports automatic resource cleanup:
// Using 'using' keyword (ES2023)
using query = new Query('database.db');
// Database connection automatically closed when scope ends
// Manual disposal
const query = new Query('database.db');
query[Symbol.dispose](); // Manually close connection
Execute multiple operations atomically:
using query = new Query('database.db');
query.transaction(tx => {
tx.insert('users').values({ name: 'John' }).query();
tx.insert('profiles').values({ userId: 1 }).query();
// All operations committed together, or all rolled back on error
});
For complex queries, you can access the underlying prepared statements:
const builder = query.from('users').select('*');
const sqlString = builder.queryAsString; // Get the SQL string
console.log(sqlString); // Useful for debugging
All query methods include built-in error handling:
// SELECT queries return empty arrays on error
const users = query.from('users').query(); // [] if error occurs
// INSERT/UPDATE/DELETE return undefined on error
const result = query.insert('users').values(data).query();
if (result) {
console.log(`Inserted row with ID: ${result.lastInsertRowid}`);
}
using keyword support)This project is licensed under the Apache License 2.0.
This library is designed to be a lightweight, type-safe wrapper around better-sqlite3. When contributing: