Database Module

Query provides a powerful and intuitive database module through the Database class. This module allows you to interact with SQLite databases directly in your Query functions, offering both read and write operations with support for transactions, prepared statements, and query caching.

Basic Usage

import { Database } from "query:database";

// Create or connect to a database
const db = new Database("example.sql");

// Execute a query
const results = db.query(
    "SELECT * FROM users WHERE age > ?",
    [18]
);

API Reference

Constructor

new Database(dbName)

Creates a new database connection or connects to an existing database.

Methods

query(sql, params?)

Executes an SQL query with optional parameters.

Returns: Promise resolving to query results

Query Parameters

Array Parameters

Use ? placeholders for array parameters:

const results = db.query(
    "SELECT * FROM users WHERE age > ? AND city = ?",
    [18, "New York"]
);

Named Parameters

Use :name, $name, or @name placeholders for object parameters:

const results = db.query(
    "SELECT * FROM users WHERE age > :age AND city = :city",
    { ":age": 18, ":city": "New York" }
);

Examples

Creating a Table

const db = new Database("example.sql");

db.query(`
    CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        email TEXT UNIQUE,
        age INTEGER,
        created_at INTEGER DEFAULT (strftime('%s', 'now'))
    )
`);

Inserting Data

// Single insert
db.query(
    "INSERT INTO users (name, email, age) VALUES (?, ?, ?)",
    ["John Doe", "john@example.com", 25]
);

// Multiple inserts using named parameters
db.query(`
    INSERT INTO users (name, email, age)
    VALUES (:name1, :email1, :age1),
           (:name2, :email2, :age2)
`, {
    ":name1": "John",  ":email1": "john@example.com",  ":age1": 25,
    ":name2": "Jane",  ":email2": "jane@example.com",  ":age2": 23
});

Selecting Data

// Basic select
const allUsers = db.query("SELECT * FROM users");

// With conditions
const activeUsers = db.query(
    "SELECT * FROM users WHERE active = ? AND age > ?",
    [true, 18]
);

// With joins
const userPosts = db.query(`
    SELECT users.name, posts.title
    FROM users
    JOIN posts ON users.id = posts.user_id
    WHERE users.id = ?
`, [userId]);

Updating Data

db.query(
    "UPDATE users SET age = :age WHERE id = :id",
    { ":age": 26, ":id": 1 }
);

Deleting Data

db.query(
    "DELETE FROM users WHERE id = ?",
    [userId]
);

Best Practices

1. Use Prepared Statements: Always use parameterized queries to prevent SQL injection:

// Good
db.query("SELECT * FROM users WHERE id = ?", [userId]);

// Bad - Don't do this!
db.query(`SELECT * FROM users WHERE id = ${userId}`);

1. Error Handling: Implement proper error handling for database operations:

try {
    const result = db.query("SELECT * FROM users");
} catch (error) {
    console.error("Database error:", error);
    // Handle error appropriately
}