Learn SQLite Database Operations using Bun

SQLite with Bun: A Lightweight and Fast JavaScript Runtime

Bun is a modern JavaScript runtime that prioritizes speed and developer experience. It comes with built-in SQLite support, making it an ideal choice for projects where database operations need to be tightly integrated with JavaScript applications. SQLite in Bun allows for efficient local database interactions without the need for external libraries, reducing dependency bloat and improving performance.

Why Use SQLite with Bun?

1. Built-In SQLite Support
No need to install additional SQLite drivers or packages; SQLite works natively in Bun.

2. High Performance
Bun's optimized runtime ensures faster database operations compared to traditional runtimes like Node.js.

3. Developer-Friendly APIs
Bun provides simple and intuitive APIs for interacting with SQLite databases.

4. Lightweight and Efficient
Combines Bun's lightweight runtime with SQLite's minimal overhead for efficient application development.

Setting Up SQLite with Bun

Prerequisites

• Install Bun on your system.
• SQLite database file or create a new one using Bun.

Example: Basic SQLite Operations in Bun

Code:

// Import Bun's built-in SQLite module
const db = Bun.open('my_database.db'); // Open or create a SQLite database file

// Create a new table
db.exec(`
  CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL
  )
`);

// Insert data into the table
db.exec(`
  INSERT INTO users (name, email) 
  VALUES ('Alice', '[email protected]')
`);

// Query data from the table
const rows = db.query('SELECT * FROM users').all(); // Fetch all rows from the users table

// Print fetched rows
console.log(rows);

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

Explanation of the Code

1. Open or Create a Database
Bun.open('my_database.db'): Creates or connects to the SQLite database file named my_database.db.

2. Create Table
The CREATE TABLE statement creates a users table if it doesn’t already exist.

3. Insert Data
Data is inserted into the users table using the INSERT INTO SQL statement.

4. Query Data
The query('SELECT * FROM users') fetches all rows from the users table. The .all() method retrieves all matching rows.

5. Close the Database
db.close(): Ensures proper resource management by closing the database connection.

Advanced Example: Parameterized Queries

Code:

// Insert multiple records using parameterized queries
const statement = db.prepare(`
  INSERT INTO users (name, email) VALUES (?, ?)
`);

// Bind values and execute the statement
statement.run('Bob', '[email protected]');
statement.run('Charlie', '[email protected]');

// Query with conditions
const result = db.query(`
  SELECT name FROM users WHERE email = ?
`).get('[email protected]'); // Fetch the row where email is '[email protected]'

console.log(result); // Output the result

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

Explanation:

• db.prepare(): Prepares a SQL statement for parameterized queries, reducing the risk of SQL injection.
• .run(): Executes the prepared statement with the provided parameters.
• .get(): Fetches a single row matching the query conditions.

Advantages of using SQLite with Bun

1. Minimal Dependencies
No need for external libraries; SQLite support is built into Bun.

2. Improved Performance
Bun's high-speed runtime ensures faster execution of SQLite queries.

3. Ease of Use
Simplified APIs make database operations straightforward for developers.

4. Full SQLite Compatibility
Leverages the full power of SQLite while maintaining a lightweight setup.

5. Ideal for Local Development
Perfect for prototyping and developing applications with local databases.

When to use SQLite with Bun?

1. Small to Medium-Scale Applications
Ideal for projects that require a local database without the complexity of a full-fledged database server.

2. Prototyping and Testing
Perfect for creating quick prototypes and testing database interactions.

3. Edge Computing and Embedded Systems
Suitable for applications that require lightweight and embedded database solutions.

Practical Guides to SQLite Snippets and Examples.