mysql

MySQL / MariaDB driver for bnl. Implements the MySQL client/server text protocol directly over net, so it runs anywhere bnl runs.

Install

bpm install mysql

Use

import "mysql" as mysql;

function main() {
    var db = wait mysql.connect({
        host:     "127.0.0.1",
        port:     3306,
        user:     "bnl",
        password: "bnl",
        database: "bnl_test"
    });

    wait db.exec(
        "CREATE TABLE IF NOT EXISTS users ("
        + " id INT PRIMARY KEY AUTO_INCREMENT,"
        + " name VARCHAR(64))");

    wait db.exec("INSERT INTO users (name) VALUES (?)", ["Alice"]);
    print("id:", db.last_insert_id());

    var rows = wait db.query("SELECT * FROM users WHERE id > ?", [0]);
    var i = 0;
    while (i < rows.length) {
        print(rows[i].id, rows[i].name);
        i = i + 1;
    }

    db.close();
}

main();

Every method returns a Future — pair it with wait for sequential code.

API

mysql.connect(opts) → Future<db>

Opts:

Key	Type	Default	Notes
host	string	—	required
port	number	3306
user	string	"root"
password	string	""
database	string	none	sent in handshake; equivalent to USE <db>
charset	string | number	"utf8mb4"	handshake collation: "utf8mb4", "utf8", "latin1", "ascii", "binary", or a raw byte
ssl	bool | string	false	true / "required" upgrades the wire to TLS right after the server greeting (MySQL CLIENT_SSL flow)
ssl_verify	bool	true	when ssl is on, verifies the server cert against the system root store + Mozilla bundle

Resolves to a db handle on successful auth. Rejects with a structured error on TCP/handshake/auth failure (see Errors below).

Errors

Server errors are rejected as a {code, sqlstate, message} map so callers can branch on the error type:

try {
    wait db.exec("INSERT INTO users (id) VALUES (1)");
} catch (e) {
    if (type(e) == "map" and e.code == 1062) {
        print("duplicate key:", e.message);
    } else {
        throw e;
    }
}

Field	Type	Notes
code	number	Server error number (e.g. 1062 for duplicate entry, 1146 for unknown table)
sqlstate	string	5-char SQLSTATE (e.g. "42S02")
message	string	Human-readable text from the server

Client-side errors (TCP failure, protocol violation, bad arguments) are rejected as plain strings prefixed with "mysql: ".

db.exec(sql, params?) → Future<null>

Non-query statement (CREATE, INSERT, UPDATE, DELETE, BEGIN, …). ? placeholders are substituted left-to-right with params. Rejects with the server error message on SQL error.

After a successful DML statement:

• db.last_insert_id() — AUTO_INCREMENT id of the last INSERT.
• db.changes() — rows affected by the last INSERT/UPDATE/DELETE.

db.query(sql, params?) → Future<list of map>

SELECT (or any rows-returning statement). Resolves to a list of {col_name: value, ...} maps.

db.query_one(sql, params?) → Future<map | null>

First matching row, or null if none. Still fetches the whole result set — add LIMIT 1 yourself for very large tables.

db.ping() → Future<null>

Server liveness check (COM_PING).

db.close()

Sends COM_QUIT (best effort) and closes the socket. Idempotent.

mysql.transaction(db, fn) → Future<value>

Runs fn(db) inside START TRANSACTION / COMMIT. fn returns a Future; if it rejects (or fn throws), the transaction is rolled back and the outer Future rejects with the same reason. Resolves to whatever fn's Future resolves to.

wait mysql.transaction(db, function (d) {
    return d.exec("UPDATE balances SET amount = amount - ? WHERE id = ?", [100, 1])
        .next(function (_) {
            return d.exec("UPDATE balances SET amount = amount + ? WHERE id = ?", [100, 2]);
        });
});

mysql.insert(db, table, row) → Future<id>

Builds an INSERT from a {col: value} map. Resolves to db.last_insert_id(). table is dropped into the SQL as-is — pass a name you control.

mysql.migrate(db, statements) → Future<count>

Runs the list of statements inside a transaction. Resolves to the number of statements applied.

mysql.version() → string

Driver version, e.g. "0.1.0".

mysql.pool(opts) → pool

Connection pool. opts accepts everything mysql.connect does plus max (default 10). The returned pool exposes the same call sites as a single db plus a .run(fn) helper:

var pool = mysql.pool({
    host: "127.0.0.1", user: "bnl", password: "bnl",
    database: "bnl_test", max: 20
});

// Returns {last_insert_id, affected_rows} instead of null — pool can't surface
// per-connection getters once the connection has been released.
var ins = wait pool.exec("INSERT INTO users (name) VALUES (?)", ["Alice"]);
print("id:", ins.last_insert_id);

var rows = wait pool.query("SELECT * FROM users");

// pool.run gives explicit access to a single conn for the duration of fn —
// useful for multi-statement work (transactions, RETURNING-style reads).
wait pool.run(function (db) {
    return mysql.transaction(db, function (d) {
        return d.exec("UPDATE balances SET amount = amount - 1 WHERE id = ?", [1]);
    });
});

pool.close();

Method	Notes
pool.exec(sql, params?)	Returns Future<{last_insert_id, affected_rows}>
pool.query(sql, params?)	Same as db.query
pool.query_one(sql, params?)	Same as db.query_one
pool.ping()	Same as db.ping
pool.run(fn)	Calls fn(db), returns whatever fn resolves to; releases the conn on settle
pool.size()	Live connections
pool.idle_size()	Idle (ready-to-reuse) connections
pool.close()	Shuts the pool: closes idle conns, rejects waiters; in-flight conns close after their current command finishes

Param substitution & escaping

Placeholders are MySQL-style ?. The driver substitutes left-to-right with proper escaping for the default sql_mode (no NO_BACKSLASH_ESCAPES):

bnl value	SQL form
null	NULL
true / false	1 / 0
integer-valued number	123
non-integer number	123.45
string	'…' with \0 \n \r \Z \" \' \\ escaping

Bigints

bnl numbers are doubles, so integer columns whose value falls outside the safe-integer range (|n| > 2^53 - 1 = 9007199254740991) are decoded as strings to preserve precision. Smaller integers come through as numbers. DECIMAL / NEWDECIMAL columns also stay as strings for the same reason.

Caveat: if your server runs with sql_mode containing NO_BACKSLASH_ESCAPES, MySQL expects '' (doubled apostrophe), not \'. This driver uses backslash escaping. Either keep the default mode, or escape user input yourself.

License

MIT.