libasynql (Node.js)

A Node.js port of the PocketMine-MP libasynql library, bringing named queries, dialect abstraction, and a powerful annotation-based SQL file format to Node.js applications.

🚀 Key Features

• Named Queries: Forget about writing SQL strings in your code. Execute queries by their logical names.
• PSF (Prepared Statement File) Support: Organise your SQL in .sql files with special annotations for cleaner, more maintainable code.
• Dialect Abstraction: Write different SQL syntax for MySQL/MariaDB and SQLite in the same file; the library automatically picks the correct version for your driver.
• Variable Typing: Define variable types (int, float, bool, string) and default values directly in your SQL comments.
• Async/Await: A modern, fully asynchronous API built with Promises.
• Auto-Casting: Automatic type conversion from TypeScript to SQL-compatible values.

📦 Installation

npm install npm-libasynql

🗄️ Database Drivers

You must install the driver corresponding to your database:

• MySQL / MariaDB:

  npm install mysql2

• SQLite3:

  npm install better-sqlite3

📝 The PSF (Prepared Statement File) Format

libasynql uses a special format for .sql files that allows for powerful annotations.

1. Dialect Markers

Use #!dialect to specify which database system a block of queries belongs to.

• -- #!mysql: Following blocks are for MySQL/MariaDB.
• -- #!sqlite: Following blocks are for SQLite.
• -- #!generic: (Default) Following blocks are shared across all drivers.

2. Query Blocks

Each query is wrapped in a named block:

-- # { query.name
-- SQL goes here
-- # }

3. Variables

Define variables with -- # :variableName type [defaultValue].

• Types: int, float, bool, string.
• Default values: Optional. If not provided, the variable is mandatory.

🚩 Example queries.sql

-- #!mysql
-- # { players.init
CREATE TABLE IF NOT EXISTS players (
    id INT AUTO_INCREMENT PRIMARY KEY,
    uuid VARCHAR(36) UNIQUE,
    username VARCHAR(16),
    experience INT DEFAULT 0
);
-- # }

-- #!sqlite
-- # { players.init
CREATE TABLE IF NOT EXISTS players (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    uuid TEXT UNIQUE,
    username TEXT,
    experience INTEGER DEFAULT 0
);
-- # }

-- #!generic
-- # { players.register
-- #    :uuid string
-- #    :username string
INSERT INTO players (uuid, username) VALUES (:uuid, :username);
-- # }

-- # { players.add_xp
-- #    :uuid string
-- #    :xp int
UPDATE players SET experience = experience + :xp WHERE uuid = :uuid;
-- # }

-- # { players.get_top
-- #    :limit int 10
SELECT username, experience FROM players ORDER BY experience DESC LIMIT :limit;
-- # }

💻 Usage Example

import { DataConnector, SQLiteDriver, MySQLDriver } from "npm-libasynql";

async function main() {
    // 1. Choose and setup your driver
    const driver = new SQLiteDriver("database.db");
    // OR: const driver = new MySQLDriver({ host: "localhost", user: "root", database: "test" });

    // 2. Setup the connector with the matching dialect name ("sqlite" or "mysql")
    const connector = new DataConnector(driver, "sqlite");
    
    try {
        await connector.connect();
        
        // 3. Load your SQL files (or strings)
        connector.loadSqlFile("./queries.sql");
        
        // 4. Execute queries by name
        
        // Generic: No return value
        await connector.executeGeneric("players.init");
        
        // Insert: Returns the last inserted ID
        const id = await connector.executeInsert("players.register", {
            uuid: "abc-123",
            username: "Steve"
        });
        
        // Change: Returns the number of affected rows
        const affected = await connector.executeChange("players.add_xp", {
            uuid: "abc-123",
            xp: 500
        });
        
        // Select: Returns an array of rows
        const players = await connector.executeSelect("players.get_top", {
            limit: 5
        });
        
        console.log("Top Players:", players);

    } catch (error) {
        console.error("Database error:", error);
    } finally {
        await connector.close();
    }
}

main();

🛠️ API Reference

DataConnector

The main interface for interacting with your database.

| Method | Description | | :--- | :--- | | connect() | Establishes the database connection. | | close() | Closes the database connection. | | loadSqlFile(path) | Reads and parses a PSF file. | | loadSqlString(sql) | Parses a raw string containing PSF blocks. | | executeGeneric(name, params) | Executes a query and returns nothing. | | executeSelect(name, params) | Executes a query and returns an array of results. | | executeInsert(name, params) | Executes a query and returns the insertId. | | executeChange(name, params) | Executes a query and returns the number of affectedRows. |

🏗️ Architecture

libasynql is designed with modularity in mind, separating SQL parsing from database execution.

1. DataConnector (The Orchestrator)

The DataConnector is the central hub. It stores all loaded statements and acts as the bridge between your application logic and the database driver. It handles:

• Statement lookup by name.
• Dialect selection (choosing the best SQL version for your database).
• Variable substitution and automatic type casting.

2. The Parser (PSF Engine)

The Parser is responsible for reading .sql files. It interprets the special annotations (-- #!dialect, -- # { block }, etc.) and transforms them into structured Statement objects.

3. The Driver Layer (Abstraction)

Everything related to database communication is abstracted behind the DatabaseDriver interface. This allows the library to support multiple database engines while providing a unified API to the user.

• SQLiteDriver: Wraps better-sqlite3.
• MySQLDriver: Wraps mysql2 (supports MariaDB).

4. Statements & Variables

• Statement: Represents a single SQL query with its metadata (name, dialect, required variables).
• Variable: Defines the name, type, and optional default value of a parameter within a statement.

The repository contains two ready-to-use demos to test the library:

1. SQLite Demo: No setup required.

   npm run demo

2. MariaDB/MySQL Demo: Requires a running MariaDB/MySQL instance.

   # Optional: configure via env variables
   # DB_HOST, DB_USER, DB_PASSWORD, DB_NAME, DB_PORT
   npm run demo-sql

📜 License

This project is licensed under the MIT License.

👤 Author

• JblusItsMe - GitHub