MySQL

import mysql is a client for MySQL 5.7 and 8.x and MariaDB 10.x and 11.x. It speaks the MySQL client/server protocol itself, so programs need no client library: the module brings TLS, the authentication plugins, the text and binary protocols, prepared statements and a thread-safe connection pool, and links the same OpenSSL as http.

This guide is published at ironlang.dev/mysql, generated from this file; every Iron example in it is compiled in CI. The module's declarations are in src/stdlib/mysql.iron.

Connecting

import mysql

func main() {
    var conn = Mysql.connect_url("mysql://app:secret@127.0.0.1:3306/shop")
    if conn.error.failed() {
        println("connect failed: {conn.error.code} {conn.error.message}")
        return
    }
    println("connected to {conn.server_version()}")
}

A connection is a MysqlConn. It owns a socket, so it is nocopy and is closed (with COM_QUIT) when its binding goes out of scope; close() closes it earlier. Bind it with var: the methods that talk to the server change the connection's state. A function that uses a caller's connection takes conn: *var MysqlConn, and the call passes the binding as is.

Mysql.connect(config) takes a MysqlConfig. Construct one with the host, port, user, password and database, then adjust the rest:

func main() {
    var cfg = MysqlConfig("db.internal", 3306, "app", "secret", "shop")
    cfg.tls = MysqlTls.VerifyIdentity
    cfg.ca_file = "/etc/ssl/db-ca.pem"
    cfg.connect_timeout = 3000
    cfg.timeout = 5000
    var conn = Mysql.connect(cfg)
    if conn.ok() {
        println("TLS: {conn.is_tls()}, auth: {conn.auth_plugin()}")
    }
}

Mysql.parse_url(url) returns (MysqlConfig, MysqlError) and Mysql.connect_url(url) connects with it. The URL is mysql://user:password@host:port/database?option=value&... (mariadb:// also works); user, password, database and values are percent-decoded, and an IPv6 host goes in brackets. The options are the fields above: tls (or ssl-mode) with disabled, preferred, required, verify_ca or verify_identity; ca, server_public_key, allow_public_key_retrieval, allow_cleartext_password, collation, connect_timeout, timeout, multi_statements, max_allowed_packet and init_command.

Authentication

The client supports caching_sha2_password (the MySQL 8 default, both the fast path and full authentication), mysql_native_password, sha256_password and mysql_clear_password, and follows the server's auth switch requests. Full authentication with caching_sha2_password or sha256_password sends the password inside TLS, or, on a connection without TLS, encrypted with the server's RSA public key (from server_public_key, or fetched when allow_public_key_retrieval is set). Without either, the client refuses to send the password and reports IRON_ERR_MYSQL_INSECURE_AUTH (7013) rather than exposing it.

Queries

exec(sql, params) runs SQL with ? placeholders as a prepared statement over the binary protocol. It is the safe way to include values from outside the program, and the fast one: each connection prepares a statement once and keeps it in a cache keyed by the SQL text (256 per connection, least recently used first out), so a repeated query costs one round trip. query(sql) runs SQL with the text protocol, and run(sql) runs SQL that returns no rows and reports only the error.

func main() {
    var conn = Mysql.connect_url("mysql://app:secret@127.0.0.1:3306/shop")
    if conn.error.failed() {
        return
    }
    conn.run("CREATE TABLE IF NOT EXISTS users (id BIGINT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(100) NOT NULL, email VARCHAR(200), born DATE)")
    val ins = conn.exec("INSERT INTO users (name, email, born) VALUES (?, ?, ?)",
        [MysqlValue.Text("Ada"), MysqlValue.Null, MysqlValue.Date(Mysql.date(1815, 12, 10))])
    if ins.ok() {
        println("inserted id {ins.last_insert_id()}, {ins.affected_rows()} row")
    }
    val res = conn.exec("SELECT id, name, email, born FROM users WHERE name = ?", [MysqlValue.Text("Ada")])
    if res.failed() {
        println("query failed: {res.error.message}")
        return
    }
    var row = 0
    while row < res.row_count() {
        val email = res.text(row, res.col("email"))
        println("{res.int(row, 0)} {res.text(row, 1)} email={email} born={res.text(row, 3)}")
        row += 1
    }
}

Parameters

A parameter is a MysqlValue:

Mysql.date(y, m, d) and Mysql.datetime(y, m, d, h, mi, s) build the date values. Passing more or fewer parameters than the statement has placeholders is IRON_ERR_MYSQL_PARAM_COUNT (7011).

Reading results

Every result is read from the server before exec or query returns: the connection is free for the next command at once, and a MysqlResult holds its rows in one buffer until its binding goes out of scope. row_count() and column_count() give its shape, col(name) finds a column by name or label (case-insensitive; -1 when missing), and the readers take a row and a column index:

The short readers return null for SQL NULL and for a value that does not convert (text to Int, 1.5 to Int, a BIGINT UNSIGNED over the Int range); check them before use (v != null and v > 0), or read with an _or form when a default will do (res.int_or(0, 1, 0)). The get_ forms (get_int, get_text, ...) return the value with a MysqlError that says which: IRON_ERR_MYSQL_NULL (7005), IRON_ERR_MYSQL_CONVERSION (7006) or IRON_ERR_MYSQL_RANGE (7007). The text and binary protocols read the same way: text of a DOUBLE or a DATETIME gives the same string whichever produced it.

func main() {
    var conn = Mysql.connect_url("mysql://app:secret@127.0.0.1:3306/shop")
    val res = conn.query("SELECT 41 + 1 AS answer, NULL AS nothing")
    val (answer, err) = res.get_int(0, res.col("answer"))
    if err.ok() {
        println("answer {answer}")
    }
    val (_, null_err) = res.get_int(0, 1)
    println("NULL is error {null_err.code}")
    match res.value(0, 0) {
        MysqlValue.Int(v) -> println("an Int: {v}")
        else -> println("something else")
    }
}

column(i) describes a column: name, org_name, table, org_table, schema, the wire type and its SQL type_name, flags, decimals, length and the charset collation id, with is_unsigned(), is_nullable(), is_primary_key() and is_binary(). affected_rows(), last_insert_id(), warnings() and info() report what a statement changed.

Several result sets

With multi_statements set, one query may hold several statements, and a stored procedure returns a result set per SELECT it runs (through exec too). The result starts at the first set; next_result() moves to the next one and returns false after the last. An error stops the batch: the sets before it stay readable and error describes the failure.

func main() {
    var cfg = MysqlConfig("127.0.0.1", 3306, "app", "secret", "shop")
    cfg.multi_statements = true
    var conn = Mysql.connect(cfg)
    var res = conn.query("SELECT 1 AS a; SELECT 'two' AS b")
    println("{res.result_count()} sets, first {res.int(0, 0)}")
    if res.next_result() {
        println("second {res.text(0, 0)}")
    }
}

Prepared statements

exec is usually all a program needs. prepare(sql) returns a MysqlStmt to manage a statement directly: exec(params), reset(), close(), param_count() and column_count(). A statement belongs to its connection and must not outlive it; using one after its connection closed or went back to its pool is IRON_ERR_MYSQL_STMT_CLOSED (7015).

Escaping

Prefer parameters. When SQL text must be built, conn.escape(s) escapes a string for a quoted literal following the session's NO_BACKSLASH_ESCAPES mode, conn.quote(s) also adds the quotes, Mysql.escape(s) assumes backslash escapes, and Mysql.quote_identifier(name) quotes a table or column name in backticks.

Transactions

begin(), commit() and rollback() return a MysqlError; in_transaction() reports the server's view.

func transfer(conn: *var MysqlConn, from: Int, to: Int, amount: Int) -> MysqlError {
    val started = conn.begin()
    if started.failed() {
        return started
    }
    val debit = conn.exec("UPDATE accounts SET balance = balance - ? WHERE id = ? AND balance >= ?",
        [MysqlValue.Int(amount), MysqlValue.Int(from), MysqlValue.Int(amount)])
    if debit.failed() or debit.affected_rows() != 1 {
        conn.rollback()
        return debit.error
    }
    val credit = conn.exec("UPDATE accounts SET balance = balance + ? WHERE id = ?",
        [MysqlValue.Int(amount), MysqlValue.Int(to)])
    if credit.failed() {
        conn.rollback()
        return credit.error
    }
    return conn.commit()
}

func main() {
    var conn = Mysql.connect_url("mysql://app:secret@127.0.0.1:3306/bank")
    val err = transfer(conn, 1, 2, 50)
    println("transfer: {err.code}")
}

Connection pools

A service that handles requests concurrently shares an rc MysqlPool. Any number of tasks may use it at once; each borrows its own connection.

import http

func handle(db: rc MysqlPool, connection: HttpConnection) -> Int {
    val request = HttpConnection.read_request(connection, 65536, 1048576, 5000)
    var status = 404
    var body = "\{\"error\":\"not found\"\}"
    if request.error == 0 and request.path == "/user" {
        val id = request.query.replace("id=", "").to_int()
        val res = db.exec("SELECT name FROM users WHERE id = ?", [MysqlValue.Int(id)])
        if res.failed() {
            status = 500
            body = "\{\"error\":\"database\"\}"
        } elif res.row_count() == 1 {
            status = 200
            body = "\{\"name\":\"{res.text_or(0, 0, "")}\"\}"
        }
    }
    val response = Http.json_response(status, body)
    val sent = HttpConnection.send_response(connection, response, 5000)
    HttpConnection.close(connection)
    return sent
}

func main() {
    val (cfg, _) = Mysql.parse_url("mysql://app:secret@127.0.0.1:3306/shop")
    var options = MysqlPoolOptions(4, 32)
    options.acquire_timeout = 2000
    val db = Mysql.open_pool(cfg, options)
    if not db.ok() {
        println("pool: {db.error.message}")
        return
    }
    val listening = Http.listen("127.0.0.1", 8080)
    if listening.error != 0 {
        return
    }
    while true {
        val accepted = HttpServer.accept(listening.server, 60000)
        if accepted.error == 0 {
            val connection = accepted.connection
            spawn("request") {
                return handle(db, connection)
            }
        }
    }
}

query, exec and run on the pool borrow a connection for one statement. acquire() hands out a MysqlConn for several statements, such as a transaction; it goes back to the pool when its binding goes out of scope, and a transaction still open then is rolled back.

func move_stock(db: rc MysqlPool, item: Int) -> Bool {
    var conn = db.acquire()
    if conn.error.failed() {
        return false
    }
    conn.begin()
    conn.exec("UPDATE stock SET qty = qty - 1 WHERE item = ?", [MysqlValue.Int(item)])
    conn.exec("INSERT INTO moves (item) VALUES (?)", [MysqlValue.Int(item)])
    return conn.commit().ok()
}

func main() {
    val db = Mysql.open_pool(MysqlConfig("127.0.0.1", 3306, "app", "secret", "shop"), MysqlPoolOptions(1, 8))
    println("{move_stock(db, 7)} size={db.size()} idle={db.idle()} in use={db.in_use()}")
}

MysqlPoolOptions(min, max) sets the size; min connections open with the pool, so a bad configuration fails at open_pool. The other fields:

Before handing out an idle connection, the pool checks that the server has not closed it (a wait_timeout, a restart, KILL): dead connections are replaced without the caller seeing an error. close() closes idle connections and refuses new work; connections in use close when they come back. size(), idle() and in_use() report the counts.

Errors

Every operation returns a MysqlError, directly or in the error field of what it returns, and nothing panics on a server or network failure. ok() and failed() test it, and is_connection_error() says whether the connection is unusable afterwards (a closed connection then answers every command with IRON_ERR_MYSQL_CLOSED, 7004; open a new one, or let the pool do it).

Limits

• A packet of 16 MB or more is split and reassembled as the protocol requires, in both directions. Before sending a statement over 1 MB the client asks the server for its max_allowed_packet once and refuses a larger statement with 7003 without breaking the connection. A packet from the server larger than the client's max_allowed_packet ends the connection with 7003.
• Results are read completely into memory. Page large queries with LIMIT, or keep max_allowed_packet to the size you expect.
• LOAD DATA LOCAL INFILE is refused (7017), compression is not negotiated, connections are TCP only (no Unix socket or named pipe), and MariaDB's client_ed25519 plugin is not supported.
• A connection is for one task at a time; share a pool across tasks.