valk-mysql

A package to query mysql databases. The package is purely written in Valk and has no os-package dependencies.

Requires Valk 0.7.5 or newer.

API documentation: docs/api.md, docs/api-full.md.

Install

vman install github.com/ctxcode/valk-mysql

Example

// Init
let db = mysql.connect("127.0.0.1", "user", "password", "dbname", 3306) ! panic("Failed to connect: %{E.message}")

// Run query + parameters
db.query("UPDATE users SET name = :name WHERE id = :id", .{ "name" => "test", "id" => 1 }) ! panic("Error: %{E.message}")

// Bind early
db.bind("name", "test")
db.query("UPDATE users SET name = :name WHERE id = :id", .{ "id" => 1 }) ! panic("Error: %{E.message}")

// Nameless parameters
db.bindv("test")
db.bindv(10)
db.query("UPDATE users SET name = ? WHERE id = ?") ! panic("Error: %{E.message}")

// Select
db.query("SELECT * FROM users WHERE id > :id", .{ "id" => 10 }) ! panic("Error: %{E.message}")

// Fetch 1-by-1
let user : Map[mysql.Value] = .{}
while db.fetch_row(user) ! panic("Error: %{E.message}") {
    println("name: " + (user["name"] ?? "/"))
}

// Fetch all
let users = db.fetch_all() ! panic("Error: %{E.message}")

Prepared statements

prepare parses a statement on the server once; running it then only sends the values, and its rows come back in the binary protocol. Values go in by name, and the rows are read from the connection as after query:

let find = db.prepare("SELECT id, name FROM users WHERE age > :age") ! panic("%{E.message}")
defer find.close()

find.query(.{ "age" => 18 }) ! panic("%{E.message}")
let users = db.fetch_all() ! panic("%{E.message}")

let insert = db.prepare("INSERT INTO users (name) VALUES (:name)") ! panic("%{E.message}")
let changed = insert.run(.{ "name" => "Ada" }) ! panic("%{E.message}")
println("new id: " + db.last_inserted_id)

Every name needs a value, and a list cannot be bound. close releases the statement on the server; closing the connection releases them all.

Against a local MariaDB 13 on one connection (bench/), per second:

	query	prepared
select one row by id	37,900	47,600
insert one row, in a transaction	46,200	52,100

TLS

The connection uses TLS whenever the server offers it, without checking the server's certificate: a MySQL server makes a self-signed one of its own, which nothing could vouch for. To check it, give the CA that signed the server certificate:

let db = mysql.connect_with("db.example.com", "user", "password", "app", 3306, mysql.SslOptions {
    mode: mysql.SslMode.verify_full
    ca_file: "/etc/mysql/ca.pem"
}) ! panic("%{E.message}")

SslMode.require refuses a server without TLS, SslMode.disable never uses it, and certificate_file / private_key_file send a client certificate to an account that requires one. db.ssl_enabled() tells whether the connection runs over TLS.

With valk-sql

mysql.database(con) turns a connection into a sql.Db of the valk-sql package, which gives every database the same API: a query builder, migrations, connection pools, and rows read into your own classes. The same program then runs on another database by opening it with that driver instead.

use sql
use mysql

let db = mysql.database(mysql.connect("127.0.0.1", "user", "password", "app", 3306) ! panic("%{E.message}"))
db.exec("INSERT INTO users (name, age) VALUES (:name, :age)", .{ "name" => "Ada", "age" => 36 }) ! panic("%{E.message}")
let rows = db.all("SELECT * FROM users WHERE age > :age", .{ "age" => 18 }) ! panic("%{E.message}")

The connection's own API stays available next to it.

Development

make server starts a MySQL in docker on port 3306 with the user the tests use, and make server-down removes it again; a machine that already runs MySQL there needs neither.

make deps fetches the valk-sql package the database() adapter needs; the tests build against it from vendor/.

make test runs the integration tests against MySQL/MariaDB at 127.0.0.1:3306 with user test and password root. The tests create and use valk_mysql_tests. make example builds and runs the local example, and valk build ./bench --run the benchmark. Override the compiler with make vc=/path/to/valk test.