tessl/npm-sqlite3

Asynchronous, non-blocking SQLite3 bindings for Node.js

—

Pending

Overview

Eval results

Files

Constants and Configuration

Name: tessl/npm-sqlite3
Author: tessl

SQLite constants for database modes, error codes, version information, and configuration limits. These constants are directly exported from the sqlite3 module and correspond to SQLite's native constants.

Capabilities

Database Mode Flags

Constants used when opening database connections to specify access modes and behaviors.

/**
 * Open database in read-only mode
 * Value: 1
 */
const OPEN_READONLY: number;

/**
 * Open database in read-write mode
 * Value: 2
 */
const OPEN_READWRITE: number;

/**
 * Create database if it doesn't exist (used with OPEN_READWRITE)
 * Value: 4
 */
const OPEN_CREATE: number;

/**
 * Enable full mutex locking
 * Value: 0x00010000
 */
const OPEN_FULLMUTEX: number;

/**
 * Enable shared cache mode
 * Value: 0x00020000
 */
const OPEN_SHAREDCACHE: number;

/**
 * Enable private cache mode
 * Value: 0x00040000
 */
const OPEN_PRIVATECACHE: number;

/**
 * Interpret filename as URI
 * Value: 0x00000040
 */
const OPEN_URI: number;

Usage Examples:

const sqlite3 = require('sqlite3').verbose();

// Read-only database
const readOnlyDb = new sqlite3.Database('data.db', sqlite3.OPEN_READONLY);

// Read-write database, create if doesn't exist
const readWriteDb = new sqlite3.Database('data.db', 
    sqlite3.OPEN_READWRITE | sqlite3.OPEN_CREATE);

// Database with specific threading mode
const threadSafeDb = new sqlite3.Database('data.db', 
    sqlite3.OPEN_READWRITE | sqlite3.OPEN_CREATE | sqlite3.OPEN_FULLMUTEX);

// URI-style filename
const uriDb = new sqlite3.Database('file:data.db?cache=shared', 
    sqlite3.OPEN_URI | sqlite3.OPEN_READWRITE);

Error Codes

SQLite result codes that indicate the outcome of database operations.

/**
 * Successful result
 * Value: 0
 */
const OK: number;

/**
 * Generic error
 * Value: 1
 */
const ERROR: number;

/**
 * Internal logic error in SQLite
 * Value: 2
 */
const INTERNAL: number;

/**
 * Access permission denied
 * Value: 3
 */
const PERM: number;

/**
 * Callback routine requested an abort
 * Value: 4
 */
const ABORT: number;

/**
 * The database file is locked
 * Value: 5
 */
const BUSY: number;

/**
 * A table in the database is locked
 * Value: 6
 */
const LOCKED: number;

/**
 * A malloc() failed
 * Value: 7
 */
const NOMEM: number;

/**
 * Attempt to write a readonly database
 * Value: 8
 */
const READONLY: number;

/**
 * Operation terminated by sqlite3_interrupt()
 * Value: 9
 */
const INTERRUPT: number;

/**
 * Some kind of disk I/O error occurred
 * Value: 10
 */
const IOERR: number;

/**
 * The database disk image is malformed
 * Value: 11
 */
const CORRUPT: number;

/**
 * Unknown opcode in sqlite3_file_control()
 * Value: 12
 */
const NOTFOUND: number;

/**
 * Insertion failed because database is full
 * Value: 13
 */
const FULL: number;

/**
 * Unable to open the database file
 * Value: 14
 */
const CANTOPEN: number;

/**
 * Database lock protocol error
 * Value: 15
 */
const PROTOCOL: number;

/**
 * Database is empty
 * Value: 16
 */
const EMPTY: number;

/**
 * The database schema changed
 * Value: 17
 */
const SCHEMA: number;

/**
 * String or BLOB exceeds size limit
 * Value: 18
 */
const TOOBIG: number;

/**
 * Abort due to constraint violation
 * Value: 19
 */
const CONSTRAINT: number;

/**
 * Data type mismatch
 * Value: 20
 */
const MISMATCH: number;

/**
 * Library used incorrectly
 * Value: 21
 */
const MISUSE: number;

/**
 * Uses OS features not supported on host
 * Value: 22
 */
const NOLFS: number;

/**
 * Authorization denied
 * Value: 23
 */
const AUTH: number;

/**
 * Auxiliary database format error
 * Value: 24
 */
const FORMAT: number;

/**
 * 2nd parameter to sqlite3_bind out of range
 * Value: 25
 */
const RANGE: number;

/**
 * File opened that is not a database file
 * Value: 26
 */
const NOTADB: number;

Usage Examples:

const sqlite3 = require('sqlite3').verbose();
const db = new sqlite3.Database('example.db');

db.run("INVALID SQL STATEMENT", (err) => {
    if (err) {
        console.log('Error occurred:', err.message);
        console.log('Error code:', err.errno);
        
        // Check specific error types
        switch (err.errno) {
            case sqlite3.ERROR:
                console.log('Generic SQL error');
                break;
            case sqlite3.BUSY:
                console.log('Database is busy, retry later');
                break;
            case sqlite3.READONLY:
                console.log('Attempted to write to read-only database');
                break;
            case sqlite3.CONSTRAINT:
                console.log('Constraint violation occurred');
                break;
            default:
                console.log('Other error type');
        }
    }
});

// Error handling in backup operations
const backup = db.backup('backup.db');
backup.retryErrors = [sqlite3.BUSY, sqlite3.LOCKED];

SQLite Version Information

Constants providing information about the SQLite version being used.

/**
 * SQLite version string (e.g., "3.44.2")
 */
const VERSION: string;

/**
 * SQLite source ID string
 */
const SOURCE_ID: string;

/**
 * SQLite version as integer (e.g., 3044002)
 */
const VERSION_NUMBER: number;

Usage Examples:

const sqlite3 = require('sqlite3').verbose();

console.log('SQLite Version:', sqlite3.VERSION);
console.log('SQLite Version Number:', sqlite3.VERSION_NUMBER);
console.log('SQLite Source ID:', sqlite3.SOURCE_ID);

// Version checking
if (sqlite3.VERSION_NUMBER >= 3035000) {
    console.log('SQLite version supports RETURNING clause');
} else {
    console.log('SQLite version does not support RETURNING clause');
}

Database Limit Constants

Constants for configuring SQLite database limits using the configure() method.

/**
 * Maximum length of a string or BLOB
 */
const LIMIT_LENGTH: number;

/**
 * Maximum length of an SQL statement
 */
const LIMIT_SQL_LENGTH: number;

/**
 * Maximum number of columns in a table, index, or view
 */
const LIMIT_COLUMN: number;

/**
 * Maximum depth of the parse tree
 */
const LIMIT_EXPR_DEPTH: number;

/**
 * Maximum number of terms in a compound SELECT statement
 */
const LIMIT_COMPOUND_SELECT: number;

/**
 * Maximum number of instructions in a virtual machine program
 */
const LIMIT_VDBE_OP: number;

/**
 * Maximum number of arguments on a function
 */
const LIMIT_FUNCTION_ARG: number;

/**
 * Maximum number of attached databases
 */
const LIMIT_ATTACHED: number;

/**
 * Maximum length of the pattern argument to the LIKE or GLOB operators
 */
const LIMIT_LIKE_PATTERN_LENGTH: number;

/**
 * Maximum index number of any parameter in an SQL statement
 */
const LIMIT_VARIABLE_NUMBER: number;

/**
 * Maximum depth of recursion for triggers
 */
const LIMIT_TRIGGER_DEPTH: number;

/**
 * Maximum number of auxiliary worker threads
 */
const LIMIT_WORKER_THREADS: number;

Usage Examples:

const sqlite3 = require('sqlite3').verbose();
const db = new sqlite3.Database('example.db');

// Configure database limits
db.configure("limit", sqlite3.LIMIT_SQL_LENGTH, 1000000);  // 1MB SQL limit
db.configure("limit", sqlite3.LIMIT_COLUMN, 2000);        // 2000 column limit
db.configure("limit", sqlite3.LIMIT_EXPR_DEPTH, 1000);    // Expression depth limit

// Set busy timeout
db.configure("busyTimeout", 30000); // 30 second timeout

// Check current limits (would require custom implementation)
function checkLimits(database) {
    console.log('Current database limits:');
    console.log('- SQL Length Limit ID:', sqlite3.LIMIT_SQL_LENGTH);
    console.log('- Column Limit ID:', sqlite3.LIMIT_COLUMN);
    console.log('- Expression Depth Limit ID:', sqlite3.LIMIT_EXPR_DEPTH);
}

checkLimits(db);

Configuration Examples

Database Opening with Multiple Flags

const sqlite3 = require('sqlite3').verbose();

// Combine multiple flags using bitwise OR
const flags = sqlite3.OPEN_READWRITE | 
              sqlite3.OPEN_CREATE | 
              sqlite3.OPEN_FULLMUTEX | 
              sqlite3.OPEN_SHAREDCACHE;

const db = new sqlite3.Database('shared.db', flags, (err) => {
    if (err) {
        console.error('Database open failed:', err.message);
    } else {
        console.log('Database opened with custom flags');
        
        // Configure additional settings
        db.configure("busyTimeout", 10000);
        db.configure("limit", sqlite3.LIMIT_ATTACHED, 10);
    }
});

Error Code Checking and Handling

const sqlite3 = require('sqlite3').verbose();
const db = new sqlite3.Database('example.db');

function handleDatabaseError(err, operation) {
    if (!err) return;
    
    console.log(`Error during ${operation}:`, err.message);
    
    // Handle specific error codes
    switch (err.errno) {
        case sqlite3.OK:
            console.log('Operation successful');
            break;
        case sqlite3.BUSY:
            console.log('Database busy - consider retry with backoff');
            break;
        case sqlite3.LOCKED:
            console.log('Table locked - transaction may be needed');
            break;
        case sqlite3.CONSTRAINT:
            console.log('Constraint violation - check data integrity');
            break;
        case sqlite3.CORRUPT:
            console.log('Database corruption detected - backup and repair needed');
            break;
        case sqlite3.READONLY:
            console.log('Read-only database - check permissions or mode');
            break;
        case sqlite3.CANTOPEN:
            console.log('Cannot open database - check file path and permissions');
            break;
        default:
            console.log(`Unhandled error code: ${err.errno}`);
    }
}

// Usage in database operations
db.run("CREATE TABLE test (id INTEGER PRIMARY KEY)", (err) => {
    handleDatabaseError(err, 'table creation');
});

db.run("INSERT INTO test (id) VALUES (1)", (err) => {
    handleDatabaseError(err, 'insert operation');
});

Version-Dependent Feature Usage

const sqlite3 = require('sqlite3').verbose();

function checkSQLiteFeatures() {
    console.log(`SQLite Version: ${sqlite3.VERSION}`);
    console.log(`Version Number: ${sqlite3.VERSION_NUMBER}`);
    
    // Check for specific version features
    const features = {
        'RETURNING clause': sqlite3.VERSION_NUMBER >= 3035000,
        'STRICT tables': sqlite3.VERSION_NUMBER >= 3037000,
        'UPSERT': sqlite3.VERSION_NUMBER >= 3024000,
        'Window functions': sqlite3.VERSION_NUMBER >= 3025000,
        'Common Table Expressions': sqlite3.VERSION_NUMBER >= 3008003,
        'JSON1 extension': sqlite3.VERSION_NUMBER >= 3038000,
    };
    
    console.log('Available features:');
    Object.entries(features).forEach(([feature, available]) => {
        console.log(`- ${feature}: ${available ? 'Yes' : 'No'}`);
    });
    
    return features;
}

// Use version checking in application logic
const features = checkSQLiteFeatures();

const db = new sqlite3.Database('example.db');

if (features['RETURNING clause']) {
    db.run("INSERT INTO users (name) VALUES (?) RETURNING id", ["Alice"], function(err) {
        if (!err) {
            console.log('Used RETURNING clause, new ID:', this.lastID);
        }
    });
} else {
    db.run("INSERT INTO users (name) VALUES (?)", ["Alice"], function(err) {
        if (!err) {
            console.log('Used traditional INSERT, new ID:', this.lastID);
        }
    });
}

Install with Tessl CLI