Better-SQLite3 Node.js Integration Made Simple

Better-SQLite3: A Node.js SQLite Library

Better-SQLite3 is a powerful, efficient, and easy-to-use SQLite library for Node.js. Unlike other SQLite libraries, Better-SQLite3 is synchronous, prioritizing simplicity and performance while maintaining robust features. It enables developers to execute queries, manage transactions, and handle database connections with minimal effort. This guide will cover the installation, syntax, and usage of Better-SQLite3 with examples and code.

Installing Better-SQLite3

To use Better-SQLite3, you need to install it using npm:

npm install better-sqlite3

Basic Syntax and Usage

1. Import the Library

const Database = require('better-sqlite3');

2. Create or Connect to a Database

// Connect to an SQLite database or create one if it doesn't exist
const db = new Database('example.db');

Examples of Better-SQLite3

1. Creating a Table

Code:

// Import the Better-SQLite3 module
const Database = require('better-sqlite3');

// Create a connection to the database
const db = new Database('example.db');

// Execute a query to create a table
db.exec(`
  CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL
  )
`);

// Close the database connection
db.close();

Explanation:

• db.exec: Executes a single SQL statement.
• CREATE TABLE: Creates a users table with id, name, and email columns.
• NOT NULL: Ensures values are mandatory.
• AUTOINCREMENT: Automatically increments id.

2. Inserting Data

Code:

// Import the Better-SQLite3 module
const Database = require('better-sqlite3');

// Connect to the database
const db = new Database('example.db');

// Prepare an SQL statement for inserting data
const insert = db.prepare('INSERT INTO users (name, email) VALUES (?, ?)');

// Execute the statement with different values
insert.run('Alice', '[email protected]');
insert.run('Bob', '[email protected]');

// Close the database connection
db.close();

Explanation:

• db.prepare: Prepares an SQL statement for execution.
• run: Executes the prepared statement with specific values.

3. Querying Data

Code:

// Import the Better-SQLite3 module
const Database = require('better-sqlite3');

// Connect to the database
const db = new Database('example.db');

// Query the database for all users
const rows = db.prepare('SELECT * FROM users').all();

// Display the results
console.log(rows);

// Close the database connection
db.close();

Explanation:

• SELECT: Retrieves all records from the users table.
• all: Executes the query and returns all results as an array.

4. Using Transactions

Code:

// Import the Better-SQLite3 module
const Database = require('better-sqlite3');

// Connect to the database
const db = new Database('example.db');

// Create a transaction for batch inserts
const insertUsers = db.transaction((users) => {
  const stmt = db.prepare('INSERT INTO users (name, email) VALUES (?, ?)');
  for (const user of users) {
    stmt.run(user.name, user.email);
  }
});

// Execute the transaction
insertUsers([
  { name: 'Charlie', email: '[email protected]' },
  { name: 'Dana', email: '[email protected]' }
]);

// Close the database connection
db.close();

Explanation:

• transaction: Groups multiple statements into a single operation for efficiency and reliability.
• stmt.run: Inserts each user into the database.

Why Use Better-SQLite3?

1. Ease of Use: Synchronous API reduces complexity in asynchronous code.

2. Performance: Optimized for high-speed queries and transactions.

3. Robust Transactions: Simplified transaction management.

4. Security: Automatically escapes input to prevent SQL injection.

Additional Features

• Parameterized Queries: Protect against SQL injection.
• Custom Functions: Add custom SQL functions using db.function.
• Memory Databases: Use :memory: for temporary, in-memory databases.

Conclusion

Better-SQLite3 is a powerful tool for integrating SQLite into Node.js applications. With its simplicity, performance, and robust features, it is ideal for projects requiring efficient and reliable database management. The synchronous API makes it beginner-friendly while retaining advanced capabilities for seasoned developers.

Practical Guides to SQLite Snippets and Examples.