Zig - Build PostgreSQL Driver from Scratch (Educational Series)
Going beyond the basics series -> PostgreSQL Wire Protocol
If you’ve ever wondered what happens underneath your database library when you call db.query("SELECT..."), this post is for you.
Today, I’m going to show you how to build a working CLI Todo App that talks directly to PostgreSQL — without any external drivers or libraries. We’re going to implement the PostgreSQL Wire Protocol ourselves.
By the end, you’ll understand exactly how database connections work, stripping away all the magic.
The “Post Office” Analogy: What is a Database Driver, Really?
Imagine an elderly person in a remote Indian village who only speaks the local dialect. They want to send a money order to the city bank.
Here’s how it works:
The Villager (You/The Client): Knows what they want (”Send 100 rupees”), but has no idea how to fill out the official forms.
The Postman (The Protocol/Adapter): Listens to the request, translates it into the official government form (specific boxes, codes, stamps), and carries it to the Head Post Office.
The Head Post Office (The Database): Doesn’t care who the villager is. It only processes correctly-stamped official forms.
The Postman: Returns with a receipt and explains it back to the villager.
PostgreSQL is the Head Post Office. It demands a strict, binary format called the Wire Protocol.
When we build a “PG-Adapter,” we are acting as the Postman: translating function calls into bytes and back.
Prerequisites
Zig Compiler: Version 0.16.x or compatible.
Docker: To run a local PostgreSQL instance.
Start Postgres with Docker:
docker run -d \
--name pg-crud \
-e POSTGRES_HOST_AUTH_METHOD=trust \
-p 5432:5432 \
postgres
Step 1: Create the File and Add Imports
Create main.zig. First, import the standard library.
const std = @import("std");
const Io = std.Io;
std.Io is Zig’s I/O abstraction layer. It provides a unified way to do networking.
Step 2: Define Protocol Message Types
PostgreSQL’s protocol uses single-byte identifiers. We define them as an enum:
const MessageType = enum(u8) {
BackendKeyData = 'K',
CommandComplete = 'C',
DataRow = 'D',
ErrorResponse = 'E',
ParameterStatus = 'S',
ReadyForQuery = 'Z',
RowDescription = 'T',
AuthenticationRequest = 'R',
_,
};
Think of these as official stamps on the post office form.
Step 3: The main Function — Setup
Set up memory allocation, output, and the I/O instance.
pub fn main() !void {
var gpa = std.heap.GeneralPurposeAllocator(.{}){};
defer _ = gpa.deinit();
const allocator = gpa.allocator();
var output_buffer: [4096]u8 = undefined;
var stdout_impl = std.fs.File.stdout().writer(&output_buffer);
const stdout = &stdout_impl.interface;
defer stdout.flush() catch {};
try stdout.print("=== PostgreSQL CRUD Terminal App ===\n\n", .{});
var io_instance = Io.Threaded.init(allocator);
defer io_instance.deinit();
const io = io_instance.io();
Why GeneralPurposeAllocator? Zig has no GC. We manage memory explicitly. GPA is a safe, general-purpose allocator.
Step 4: Connect to PostgreSQL via TCP
Parse the server address and open a TCP stream.
const address: Io.net.IpAddress = .{ .ip4 = try Io.net.Ip4Address.parse("127.0.0.1", 5432) };
var stream = address.connect(io, .{ .mode = .stream }) catch |err| {
try stdout.print("Connection failed: {any}\n", .{err});
return;
};
defer stream.close(io);
var rb: [16384]u8 = undefined;
var wb: [16384]u8 = undefined;
var reader = stream.reader(io, &rb);
var writer = stream.writer(io, &wb);
We now have a raw pipe to the database.
Step 5: Perform the Startup Handshake
Add this call in main:
try startup(allocator, &reader, &writer);
try stdout.print(" ✓ Connected to PostgreSQL.\n", .{});
And define the startup function:
fn startup(allocator: std.mem.Allocator, reader: anytype, writer: anytype) !void {
var payload = std.ArrayListUnmanaged(u8){};
defer payload.deinit(allocator);
try payload.appendSlice(allocator, &[_]u8{ 0, 3, 0, 0 }); // Protocol 3.0
try payload.appendSlice(allocator, "user\x00postgres\x00database\x00postgres\x00\x00");
const total_len = @as(i32, @intCast(payload.items.len + 4));
var len_buf: [4]u8 = undefined;
std.mem.writeInt(i32, &len_buf, total_len, .big); // BIG ENDIAN!
try writer.interface.writeAll(&len_buf);
try writer.interface.writeAll(payload.items);
try writer.interface.flush();
while (true) {
var msg_type_buf: [1]u8 = undefined;
try reader.interface.readSliceAll(&msg_type_buf);
const msg_type: MessageType = @enumFromInt(msg_type_buf[0]);
var msg_len_buf: [4]u8 = undefined;
try reader.interface.readSliceAll(&msg_len_buf);
const msg_len = std.mem.readInt(i32, &msg_len_buf, .big);
const payload_len = @as(usize, @intCast(msg_len - 4));
switch (msg_type) {
.AuthenticationRequest => {
var auth_type_buf: [4]u8 = undefined;
try reader.interface.readSliceAll(&auth_type_buf);
const auth_type = std.mem.readInt(i32, &auth_type_buf, .big);
if (auth_type != 0) return error.AuthRequired;
},
.ReadyForQuery => {
try reader.interface.discardAll(1);
return;
},
.ErrorResponse => {
const err = try reader.interface.readAlloc(allocator, payload_len);
defer allocator.free(err);
return error.BackendError;
},
else => try reader.interface.discardAll(payload_len),
}
}
}
Why Big Endian? Network protocols use Big Endian.
std.mem.writeInt(..., .big)handles this.
Step 6: Initialize the todos Table
try executeQuery(allocator, &reader, &writer,
"CREATE TABLE IF NOT EXISTS todos (id SERIAL PRIMARY KEY, task TEXT NOT NULL, done BOOLEAN DEFAULT FALSE);",
stdout);
try stdout.print(" ✓ Database initialized.\n\n", .{});
Define executeQuery:
fn executeQuery(allocator: std.mem.Allocator, reader: anytype, writer: anytype, sql: []const u8, stdout: anytype) !void {
const q_len = @as(i32, @intCast(sql.len + 5));
var q_len_buf: [4]u8 = undefined;
std.mem.writeInt(i32, &q_len_buf, q_len, .big);
try writer.interface.writeByte('Q');
try writer.interface.writeAll(&q_len_buf);
try writer.interface.writeAll(sql);
try writer.interface.writeByte(0);
try writer.interface.flush();
while (true) {
var msg_type_buf: [1]u8 = undefined;
try reader.interface.readSliceAll(&msg_type_buf);
const msg_type: MessageType = @enumFromInt(msg_type_buf[0]);
var msg_len_buf: [4]u8 = undefined;
try reader.interface.readSliceAll(&msg_len_buf);
const msg_len = std.mem.readInt(i32, &msg_len_buf, .big);
const payload_len = @as(usize, @intCast(msg_len - 4));
switch (msg_type) {
.CommandComplete => {
const res = try reader.interface.readAlloc(allocator, payload_len);
defer allocator.free(res);
try stdout.print(" ✓ {s}\n", .{res});
},
.ReadyForQuery => {
try reader.interface.discardAll(1);
return;
},
.ErrorResponse => {
const err = try reader.interface.readAlloc(allocator, payload_len);
defer allocator.free(err);
try stdout.print(" ! Error: {s}\n", .{err});
return;
},
else => try reader.interface.discardAll(payload_len),
}
}
}
Step 7: The Interactive Menu Loop
var stb: [4096]u8 = undefined;
var stdin_wrapper = std.fs.File.stdin().reader(io, &stb);
const stdin = &stdin_wrapper.interface;
while (true) {
try stdout.print("\n--- TODO MENU ---\n", .{});
try stdout.print("1. List Todos\n2. Add Todo\n3. Toggle Done\n4. Delete Todo\n5. Quit\nSelection: ", .{});
try stdout.flush();
const line = (try stdin.takeDelimiter('\n')) orelse break;
const choice = std.mem.trim(u8, line, " \r\n");
if (std.mem.eql(u8, choice, "1")) {
try listTodos(allocator, &reader, &writer, stdout);
} else if (std.mem.eql(u8, choice, "2")) {
try stdout.print("Enter task: ", .{});
try stdout.flush();
const task_line = (try stdin.takeDelimiter('\n')) orelse break;
const task = std.mem.trim(u8, task_line, " \r\n");
if (task.len > 0) {
const sql = try std.fmt.allocPrint(allocator, "INSERT INTO todos (task) VALUES ('{s}');", .{task});
defer allocator.free(sql);
try executeQuery(allocator, &reader, &writer, sql, stdout);
}
} else if (std.mem.eql(u8, choice, "3")) {
// Toggle logic - similar pattern
} else if (std.mem.eql(u8, choice, "4")) {
// Delete logic - similar pattern
} else if (std.mem.eql(u8, choice, "5")) {
break;
}
}
try stdout.print("\nGoodbye!\n", .{});
}
Step 8: The listTodos Function
Parse DataRow messages to display results.
fn listTodos(allocator: std.mem.Allocator, reader: anytype, writer: anytype, stdout: anytype) !void {
const sql = "SELECT id, task, done FROM todos ORDER BY id ASC;";
// ... send query (same as executeQuery) ...
try stdout.print("\n{s: <5} | {s: <30} | {s}\n", .{ "ID", "TASK", "DONE" });
while (true) {
// ... read message type and length ...
switch (msg_type) {
.DataRow => {
// Read column count
// For each column: read length, then data
// Print the row
},
.ReadyForQuery => {
try reader.interface.discardAll(1);
return;
},
// ...
}
}
}
Running the App
zig run main.zig
Verify:
docker exec -it pg-crud psql -U postgres -c "SELECT * FROM todos;"
What You Just Learned
You built a database client from scratch.
You managed raw TCP sockets.
You wrote and parsed binary protocol messages.
You handled network byte ordering.
This is the foundation of every backend system: Connect → Handshake → Encode → Send → Decode.
The next time you use a fancy ORM, you’ll know exactly what’s happening under the hood.
The full source code is at zig-postgres-todo.zig
PS: This tutorial is for education and not intended to use in production. But understanding the concepts will help you work with existing libraries efficiently.
If you found this useful, consider subscribing for more deep dives into systems programming!