myzql

driver mariadb mysql sql

MyZql

MySQL and MariaDB driver in native zig

Status

Beta

Version Compatibility

| MyZQL | Zig | |————-|—————————| | 0.0.9.1 | 0.12.0 | | 0.13.2 | 0.13.0 | | 0.14.0 | 0.14.0 | | 0.15.1 | 0.15.1 | | 0.16.0 | 0.16.0 | | main | 0.16.0 |

Features

Native Zig code, no external dependencies
TCP and Unix socket connections
Prepared Statement
Structs from query result
Data insertion
MySQL DateTime and Time support

Requirements

MySQL/MariaDB 5.7.5 and up

TODOs

Config from URL
Connection Pooling
TLS support

Add as dependency to your Zig project

Fetch dependency

zig fetch --save git+https://github.com/speed2exe/myzql#0.16.0

build.zig.zon

  // ...
  .dependencies = .{
    .myzql = .{
      // choose a tag according to "Version Compatibility" table
      .url = "https://github.com/speed2exe/myzql/archive/refs/tags/0.16.0.tar.gz",
      .hash = "1220582ea45580eec6b16aa93d2a9404467db8bc1d911806d367513aa40f3817f84c",
    }
  },
  // ...

Import in your project

build.zig

  //...
  const myzql_dep = b.dependency("myzql", .{});
  const myzql = myzql_dep.module("myzql");
  exe.root_module.addImport("myzql", myzql);
  //...

Usage

Project integration example: Usage

Connection

const std = @import("std");
const myzql = @import("myzql");
const Conn = myzql.conn.Conn;

pub fn main() !void {
    const io = std.Io.default();

    // TCP connection (default)
    var client = try Conn.init(
        allocator,
        io,
        &.{
            .username = "some-user",   // default: "root"
            .password = "password123", // default: ""
            .database = "customers",   // default: ""

            // Default: 127.0.0.1:3306
            .address = .{ .ip = std.Io.net.IpAddress.parseLiteral("127.0.0.1:3306") catch unreachable },
        },
    );
    defer client.deinit(allocator, io);

    try client.ping(io);
}

Unix Socket Connection

pub fn main() !void {
    const io = std.Io.default();

    var client = try Conn.init(
        allocator,
        io,
        &.{
            .password = "password123",
            .address = .{ .unix = try std.Io.net.UnixAddress.init("/var/run/mysqld/mysqld.sock") },
        },
    );
    defer client.deinit(allocator, io);

    try client.ping(io);
}

Querying

const OkPacket = protocol.generic_response.OkPacket;

pub fn main() !void {
    const io = std.Io.default();
    // ...
    // You can do a text query (text protocol) by using `query` method on `Conn`
    const result = try c.query(io, "CREATE DATABASE testdb");

    // Query results can have a few variant:
    // - ok:   OkPacket     => query is ok
    // - err:  ErrorPacket  => error occurred
    // In this example, res will either be `ok` or `err`.
    // We are using the convenient method `expect` for simplified error handling.
    // If the result variant does not match the kind of result you have specified,
    // a message will be printed and you will get an error instead.
    const ok: OkPacket = try result.expect(.ok);

    // Alternatively, you can also handle results manually for more control.
    // Here, we do a switch statement to handle all possible variant or results.
    switch (result.value) {
        .ok => |ok| {},

        // `asError` is also another convenient method to print message and return as zig error.
        // You may also choose to inspect individual fields for more control.
        .err => |err| return err.asError(),
    }
}

Querying returning rows (Text Results)

If you want to have query results to be represented by custom created structs, this is not the section, scroll down to “Executing prepared statements returning results” instead. ```zig const myzql = @import(“myzql”); const ResultSet = myzql.result.ResultSet; const TextResultRow = myzql.result.TextResultRow; const TextElems = myzql.result.TextElems; const TextElemIter = myzql.result.TextElemIter;

pub fn main() !void { const io = std.Io.default(); const result = try c.queryRows(allocator, io, “SELECT * FROM customers.purchases”);

// This is a query that returns rows, you have to collect the result.
// you can use `expect(.rows)` to try interpret query result as ResultSet(TextResultRow)
const rows: ResultSet(TextResultRow) = try result.expect(.rows);

// Allocation-free iterator over rows
const rows_iter = rows.iter();
while (try rows_iter.next()) |row| { // TextResultRow
    // Option 1: Iterate through every element in the row
    var elems_iter: TextElemIter = row.iter();
    while (elems_iter.next()) |elem| { // ?[]const u8
        std.debug.print("{?s} ", .{elem});
    }

    // Option 2: Collect all elements in the row into a slice
    const text_elems: TextElems = try row.textElems(allocator);
    defer text_elems.deinit(allocator); // elems are valid until deinit is called
    const elems: []const ?[]const u8 = text_elems.elems;
    std.debug.print("elems: {any}\n", .{elems});
} } ```

    // You can also use `tableTexts` to collect all rows at once.
    // Under the hood, it does network calls and allocations, until EOF or error.
    // Results are valid until `deinit` is called on TableTexts.
    const result = try c.queryRows(allocator, io, "SELECT * FROM customers.purchases");
    const rows: ResultSet(TextResultRow) = try result.expect(.rows);
    const table = try rows.tableTexts(allocator);
    defer table.deinit(allocator); // table is valid until deinit is called
    std.debug.print("table: {any}\n", .{table.table});

Data Insertion

Let’s assume that you have a table of this structure:

CREATE TABLE test.person (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255),
  age INT
)

const myzql = @import("myzql");
const QueryResult = myzql.result.QueryResult;
const PreparedStatement = myzql.result.PreparedStatement;
const OkPacket = myzql.protocol.generic_response.OkPacket;

pub fn main() void {
    const io = std.Io.default();
    // In order to do a insertion, you would first need to do a prepared statement.
    // Allocation is required as we need to store metadata of parameters and return type
    const prep_res = try c.prepare(allocator, io, "INSERT INTO test.person (name, age) VALUES (?, ?)");
    defer prep_res.deinit(allocator);
    const prep_stmt: PreparedStatement = try prep_res.expect(.stmt);

    // Data to be inserted
    const params = .{
        .{ "John", 42 },
        .{ "Sam", 24 },
    };
    inline for (params) |param| {
        const exe_res = try c.execute(io, &prep_stmt, param);
        const ok: OkPacket = try exe_res.expect(.ok); // expecting ok here because there's no rows returned
        const last_insert_id: u64 = ok.last_insert_id;
        std.debug.print("last_insert_id: {any}\n", .{last_insert_id});
    }

    // Currently only tuples are supported as an argument for insertion.
    // There are plans to include named structs in the future.
}

Executing prepared statements returning results as structs

const QueryResultRows = myzql.result.QueryResultRows;
const BinaryResultRow = myzql.result.BinaryResultRow;
const ResultSet = myzql.result.ResultSet;

fn main() !void {
    const io = std.Io.default();
    const prep_res = try c.prepare(allocator, io, "SELECT name, age FROM test.person");
    defer prep_res.deinit(allocator);
    const prep_stmt: PreparedStatement = try prep_res.expect(.stmt);

    // This is the struct that represents the columns of a single row.
    const Person = struct {
        name: []const u8,
        age: u8,
    };

    { // Iterating over rows, scanning into struct or creating struct
        const query_res = try c.executeRows(allocator, io, &prep_stmt, .{}); // no parameters because there's no ? in the query
        const rows: ResultSet(BinaryResultRow) = try query_res.expect(.rows);
        const rows_iter = rows.iter();
        while (try rows_iter.next()) |row| {
            { // Option 1: scanning into preallocated person
                var person: Person = undefined;
                try row.scan(&person);
                person.greet();
                // Important: if any field is a string, it will be valid until the next row is scanned
                // or next query. If your rows return have strings and you want to keep the data longer,
                // use the method below instead.
            }
            { // Option 2: passing in allocator to create person
                const person_ptr = try row.structCreate(Person, allocator);

                // Important: please use BinaryResultRow.structDestroy
                // to destroy the struct created by BinaryResultRow.structCreate
                // if your struct contains strings.
                // person is valid until BinaryResultRow.structDestroy is called.
                defer BinaryResultRow.structDestroy(person_ptr, allocator);
                person_ptr.greet();
            }
        }
    }

    { // collect all rows into a table ([]const Person)
        const query_res = try c.executeRows(allocator, io, &prep_stmt, .{}); // no parameters because there's no ? in the query
        const rows: ResultSet(BinaryResultRow) = try query_res.expect(.rows);
        const rows_iter = rows.iter();
        const person_structs = try rows_iter.tableStructs(Person, allocator);
        defer person_structs.deinit(allocator); // data is valid until deinit is called
        std.debug.print("person_structs: {any}\n", .{person_structs.struct_list.items});
    }
}

Temporal Types Support (DateTime, Time)

Example of using DateTime and Time MySQL column types.

Let’s assume you already got this table set up:

CREATE TABLE test.temporal_types_example (
  event_time DATETIME(6) NOT NULL,
  duration TIME(6) NOT NULL
)

const DateTime = myzql.temporal.DateTime;
const Duration = myzql.temporal.Duration;

fn main() !void {
    const io = std.Io.default();
    { // Insert
        const prep_res = try c.prepare(allocator, io, "INSERT INTO test.temporal_types_example VALUES (?, ?)");
        defer prep_res.deinit(allocator);
        const prep_stmt: PreparedStatement = try prep_res.expect(.stmt);

        const my_time: DateTime = .{
            .year = 2023,
            .month = 11,
            .day = 30,
            .hour = 6,
            .minute = 50,
            .second = 58,
            .microsecond = 123456,
        };
        const my_duration: Duration = .{
            .days = 1,
            .hours = 23,
            .minutes = 59,
            .seconds = 59,
            .microseconds = 123456,
        };
        const params = .{.{ my_time, my_duration }};
        inline for (params) |param| {
            const exe_res = try c.execute(io, &prep_stmt, param);
            _ = try exe_res.expect(.ok);
        }
    }

    { // Select
        const DateTimeDuration = struct {
            event_time: DateTime,
            duration: Duration,
        };
        const prep_res = try c.prepare(allocator, io, "SELECT * FROM test.temporal_types_example");
        defer prep_res.deinit(allocator);
        const prep_stmt: PreparedStatement = try prep_res.expect(.stmt);
        const res = try c.executeRows(allocator, io, &prep_stmt, .{});
        const rows: ResultSet(BinaryResultRow) = try res.expect(.rows);
        const rows_iter = rows.iter();

        const structs = try rows_iter.tableStructs(DateTimeDuration, allocator);
        defer structs.deinit(allocator);
        std.debug.print("structs: {any}\n", .{structs.struct_list.items}); // structs.struct_list.items: []const DateTimeDuration
        // Do something with structs
    }
}

Arrays Support

Assume that you have the SQL table:

CREATE TABLE test.array_types_example (
  name VARCHAR(16) NOT NULL,
  mac_addr BINARY(6)
)

fn main() !void {
    const io = std.Io.default();
    { // Insert
        const prep_res = try c.prepare(allocator, io, "INSERT INTO test.array_types_example VALUES (?, ?)");
        defer prep_res.deinit(allocator);
        const prep_stmt: PreparedStatement = try prep_res.expect(.stmt);

        const params = .{
            .{ "John", &[_]u8 { 0xFE } ** 6 },
            .{ "Alice", null }
        };
        inline for (params) |param| {
            const exe_res = try c.execute(io, &prep_stmt, param);
            _ = try exe_res.expect(.ok);
        }
    }

    { // Select
        const Client = struct {
            name: [16:1]u8,
            mac_addr: ?[6]u8,
        };
        const prep_res = try c.prepare(allocator, io, "SELECT * FROM test.array_types_example");
        defer prep_res.deinit(allocator);
        const prep_stmt: PreparedStatement = try prep_res.expect(.stmt);
        const res = try c.executeRows(allocator, io, &prep_stmt, .{});
        const rows: ResultSet(BinaryResultRow) = try res.expect(.rows);
        const rows_iter = rows.iter();

        const structs = try rows_iter.tableStructs(Client, allocator);
        defer structs.deinit(allocator);
        std.debug.print("structs: {any}\n", .{structs.struct_list.items}); // structs.struct_list.items: []const Client
        // Do something with structs
    }
}

Arrays will be initialized by their sentinel value. In this example, the value of the name field corresponding to John’s row will be [16:1]u8 { 'J', 'o', 'h', 'n', 1, 1, 1, ... }
If the array doesn’t have a sentinel value, it will be zero-initialized.
Insufficiently sized arrays will silently truncate excess data

Unit Tests

zig build unit_test

Integration Tests

Start up mysql/mariadb in docker:

# MySQL (TCP)
docker run --name some-mysql --env MYSQL_ROOT_PASSWORD=password -p 3306:3306 -d mysql

# MySQL (Unix socket)
mkdir -p /tmp/mysql
docker run --name some-mysql-unix --env MYSQL_ROOT_PASSWORD=password -v /tmp/mysql:/var/run/mysqld -d mysql

# MariaDB
docker run --name some-mariadb --env MARIADB_ROOT_PASSWORD=password -p 3306:3306 -d mariadb

Run all the tests from the root directory of the project:
```
zig build integration_test --summary all
```

Run with a filter:

zig build -Dtest-filter="..." integration_test

Run unix socket tests:

zig build integration_test -Dunix-socket-path=/tmp/mysql/mysqld.sock -Dtest-filter="unix socket" --summary all

Philosophy

Correctness

Focused on correct representation of server client protocol.

Low-level and High-level APIs

Low-level apis should contain all functionality you need. High-level apis are built on top of low-level ones for convenience and developer ergonomics.

Binary Column Types support

MySQL Colums Types to Zig Values ```
Null -> ?T
Int -> u64, u32, u16, u8
Float -> f32, f64
String -> []u8, []const u8, enum ```

Install

zig f -S zub.sh/myzql

Repository

License

MIT

Category

embedded

Author

speed2exe

Dependencies

No explicit dependencies.

On this page

Status
Version Compatibility
Features
Requirements
TODOs
Add as dependency to your Zig project
Usage
Querying
Querying returning rows (Text Results)
Unit Tests
Integration Tests
Philosophy