A library for interacting with databases in Zig. Builds on top of zig-odbc to provide a higher-level interaction between the developer and the DB.
Important!: In it's current stage, this is not ready for any serious use-cases. Its features are limited and the implementation has issues.
To use zdb, follow these steps:
Make sure you have an ODBC driver and driver manager installed already. On Windows, this should be pre-installed. On other
systems, a good option is unixODBC
.
Include this repo in your project path. Best way to do this is by running
$ git submodule add https://github.com/mjoerussell/zdb --recurse-submodules
$ cd zdb
$ git submodule update --init --recursive
After this you should have zdb and also it's dependencies fully pulled into your project.
Add this to your project's build.zig
:
const build_zdb = @import("zdb/build_pkg.zig");
pub fn build(b: *std.build.Builder) void {
// ...
build_zdb.buildPkg(exe, "zdb");
}
Wherever you use zdb, include const zdb = @import("zdb");
.
Currently this library is in alpha and is limited in scope. The currently available features include:
It's easy to connect to a database using a connection string:
pub fn main() !void {
var gpa = std.heap.GeneralPurposeAllocator(.{}){};
defer _ = gpa.deinit();
const allocator = &gpa.allocator;
var connection = try DBConnection.init(allocator, "ODBC;driver=PostgreSQL Unicode(x64);DSN=PostgreSQL35W");
defer connection.deinit();
}
You can insert data simply by passing in an array of structs to DBConnection.insert()
. There are a few caveats to remember:
- The struct's fields must match the table column names, and they must all be present (unless the DB column has default values).
- Not all field types are valid. A few general rules for what field types can be passed currently:
- Numeric types
[]const u8
'strings'- Enums with numeric backing types
- Optional types
- Certain structs - see
odbc.Types.CType
andodbc.Types.SqlType
.
Here's an example of inserting data
const OdbcTestType = struct {
id: u8,
name: []u8,
occupation: []u8,
age: u32,
};
fn main() {
// ... initialize connection
var cursor = try connection.getCursor();
defer cursor.deinit();
try cursor.insert(OdbcTestType, "odbc_test_zig", &.{
.{
.id = 1,
.name = "John",
.occupation = "Plumber",
.age = 30
},
.{
.id = 2,
.name = "Sara",
.occupation = "Pilot",
.age = 25
}
});
}
If you only want to execute a statement once, the fastest way to do that is to use executeDirect
. Once you have
a cursor you can use executeDirect
like this:
const OdbcTestType = struct {
id: u8,
name: []u8,
occupation: []u8,
age: u32,
};
var result_set = try cursor.executeDirect(
OdbcTestType,
.{ 20 },
"SELECT * FROM odbc_zig_test WHERE age >= ?"
);
defer result_set.deinit();
Once you have a cursor, you can create prepared statements and add parameters to them.
try cursor.prepare(
.{ 20 },
"SELECT * FROM odbc_zig_test WHERE age >= ?"
);
Results can then be fetched from the statement by providing a target type and calling fetch()
.
var result_set = try cursor.execute(OdbcTestType);
defer result_set.deinit();
std.debug.print("Rows fetched: {}\n", .{result_set.rows_fetched});
while (try result_set.next()) |result| {
std.debug.print("Id: {}\n", .{result.id});
std.debug.print("Name: {s}\n", .{result.name});
std.debug.print("Occupation: {s}\n", .{result.occupation});
std.debug.print("Age: {}\n", .{result.age});
}
Sometimes you want to use structs that don't directly correspond with a table or result set from a SQL query. Currently, you can do
that with a fromRow
function. To get started, define a function on your target struct in this way:
const std = @import("std");
const Allocator = std.mem.Allocator;
const zdb = @import("zdb");
const Row = zdb.Row;
const Target = struct {
fieldA: u32,
fieldB: struct {
inner: []const u8,
},
fieldC: []const u8,
// Column-wise binding will be used if the struct has a function with this signature on it
pub fn fromRow(row: *Row, allocator: *Allocator) !Target {
var t: Target = undefined;
// Get data by calling row.get with the desired return type and the column name
t.fieldA = row.get(u32, "a") catch |_| 0;
t.fieldB.inner = try row.get([]const u8, "column_b");
const c = row.get(f32, "c") orelse 1.0;
t.fieldC = try std.fmt.allocPrint(allocator, "Column C is {d:.2}", .{c});
return t;
}
}
The Cursor functions (executeDirect
, prepare
, etc) and the ResultSet functions (next
, getAllRows
) work the same whether your target
struct is using default field-based binding or fromRow
bindings. So when you're fetching data, you shouldn't have to worry about which
type ends up being used.
If you want to use this package in it's current state, then it would probably be necessary to use the ODBC bindings directly to supplement missing features. You can access the bindings by importing them like this:
const odbc = @import("zdb").odbc;
Please see zig-odbc for more information about these bindings.