Something went wrong. Try again.
Native PostgreSQL driver / client for Zig
Something went wrong. Try again.
12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273747576777879808182838485868788899091929394959697989910010110210310410510610710810911011111211311411511611711811912012112212312412512612712812913013113213313413513613713813914014114214314414514614714814915015115215315415515615715815916016116216316416516616716816917017117217317417517617717817918018118218318418518618718818919019119219319419519619719819920020120220320420520620720820921021121221321421521621721821922022122222322422522622722822923023123223323423523623723823924024124224324424524624724824925025125225325425525625725825926026126226326426526626726826927027127227327427527627727827928028128228328428528628728828929029129229329429529629729829930030130230330430530630730830931031131231331431531631731831932032132232332432532632732832933033133233333433533633733833934034134234334434534634734834935035135235335435535635735835936036136236336436536636736836937037137237337437537637737837938038138238338438538638738838939039139239339439539639739839940040140240340440540640740840941041141241341441541641741841942042142242342442542642742842943043143243343443543643743843944044144244344444544644744844945045145245345445545645745845946046146246346446546646746846947047147247347447547647747847948048148248348448548648748848949049149249349449549649749849950050150250350450550650750850951051151251351451551651751851952052152252352452552652752852953053153253353453553653753853954054154254354454554654754854955055155255355455555655755855956056156256356456556656756856957057157257357457557657757857958058158258358458558658758858959059159259359459559659759859960060160260360460560660760860961061161261361461561661761861962062162262362462562662762862963063163263363463563663763863964064164264364464564664764864965065165265365465565665765865966066166266366466566666766866967067167267367467567667767867968068168268368468568668768868969069169269369469569669769869970070170270370470570670770870971071171271371471571671771871972072172272372472572672772872973073173273373473573673773873974074174274374474574674774874975075175275375475575675775875976076176276376476576676776876977077177277377477577677777877978078178278378478578678778878979079179279379479579679779879980080180280380480580680780880981081181281381481581681781881982082182282382482582682782882983083183283383483583683783883984084184284384484584684784884985085185285385485585685785885986086186286386486586686786886987087187287387487587687787887988088188288388488588688788888989089189289389489589689789889990090190290390490590690790890991091191291391491591691791891992092192292392492592692792892993093193293393493593693793893994094194294394494594694794894995095195295395495595695795895996096196296396496596696796896997097197297397497597697797897998098198298398498598698798898999099199299399499599699799899910001001100210031004100510061007100810091010101110121013101410151016101710181019102010211022102310241025102610271028102910301031103210331034103510361037103810391040104110421043104410451046104710481049105010511052105310541055105610571058105910601061106210631064106510661067106810691070107110721073107410751076107710781079108010811082108310841085108610871088108910901091109210931094109510961097109810991100110111021103110411051106110711081109111011111112111311141115111611171118111911201121112211231124112511261127112811291130113111321133113411351136113711381139114011411142114311441145114611471148114911501151115211531154115511561157115811591160116111621163116411651166116711681169117011711172117311741175117611771178117911801181118211831184118511861187118811891190119111921193119411951196119711981199120012011202120312041205120612071208120912101211121212131214121512161217121812191220122112221223122412251226122712281229123012311232123312341235123612371238123912401241124212431244124512461247124812491250125112521253125412551256125712581259126012611262126312641265126612671268126912701271127212731274127512761277127812791280128112821283128412851286128712881289129012911292129312941295129612971298129913001301130213031304130513061307130813091310131113121313131413151316131713181319132013211322132313241325132613271328132913301331133213331334133513361337133813391340134113421343134413451346134713481349135013511352135313541355135613571358135913601361136213631364136513661367136813691370137113721373137413751376137713781379138013811382138313841385138613871388138913901391139213931394139513961397139813991400140114021403140414051406140714081409141014111412141314141415141614171418141914201421142214231424142514261427142814291430143114321433143414351436143714381439144014411442144314441445144614471448144914501451145214531454145514561457145814591460146114621463146414651466146714681469147014711472147314741475147614771478147914801481148214831484148514861487148814891490149114921493149414951496149714981499150015011502150315041505150615071508150915101511151215131514151515161517151815191520152115221523152415251526152715281529153015311532153315341535153615371538153915401541154215431544154515461547154815491550155115521553155415551556155715581559156015611562156315641565156615671568156915701571157215731574157515761577157815791580158115821583158415851586158715881589159015911592159315941595159615971598159916001601160216031604160516061607160816091610161116121613161416151616161716181619162016211622162316241625162616271628162916301631163216331634163516361637163816391640164116421643164416451646164716481649165016511652165316541655165616571658165916601661166216631664166516661667166816691670167116721673167416751676167716781679168016811682168316841685168616871688168916901691169216931694169516961697169816991700170117021703170417051706170717081709171017111712171317141715171617171718171917201721172217231724172517261727172817291730173117321733173417351736173717381739174017411742174317441745174617471748174917501751175217531754175517561757175817591760176117621763176417651766176717681769177017711772177317741775177617771778177917801781178217831784178517861787178817891790179117921793179417951796179717981799180018011802180318041805180618071808180918101811181218131814181518161817181818191820182118221823182418251826182718281829183018311832183318341835183618371838183918401841184218431844184518461847184818491850185118521853185418551856185718581859186018611862186318641865186618671868186918701871187218731874187518761877187818791880188118821883188418851886188718881889189018911892189318941895189618971898189919001901190219031904190519061907190819091910191119121913191419151916191719181919192019211922192319241925192619271928192919301931193219331934193519361937193819391940194119421943194419451946194719481949195019511952195319541955195619571958195919601961196219631964196519661967196819691970197119721973197419751976197719781979198019811982198319841985198619871988198919901991199219931994199519961997199819992000200120022003200420052006200720082009201020112012201320142015201620172018201920202021202220232024202520262027202820292030203120322033203420352036203720382039204020412042204320442045204620472048204920502051205220532054205520562057205820592060206120622063206420652066206720682069207020712072207320742075207620772078207920802081const std = @import("std");const lib = @import("lib.zig");const Buffer = @import("buffer").Buffer;
const proto = lib.proto;const types = lib.types;const Pool = lib.Pool;const Stmt = lib.Stmt;const SSLCtx = lib.SSLCtx;const Reader = lib.Reader;const Result = lib.Result;const Stream = lib.Stream;const Timeout = lib.Timeout;const QueryRow = lib.QueryRow;const QueryRowUnsafe = lib.QueryRowUnsafe;const has_openssl = lib.has_openssl;
const os = std.os;const Allocator = std.mem.Allocator;const ArenaAllocator = std.heap.ArenaAllocator;const Io = std.Io;
pub const Conn = struct { // If we own the ssl context (which only happens if the connection is // created directly and NOT through a pool), then we have to free it _ssl_ctx: ?*SSLCtx,
// If we get a postgreSQL error, this will be set. err: ?proto.Error,
// The underlying data for err _err_data: ?[]const u8,
_stream: Stream,
_pool: ?*Pool = null,
// The current transation state, this is whatever the last ReadyForQuery // message told us _state: State,
// A buffer used for writing to PG. This can grow dynamically as needed. _buf: Buffer,
// Used to read data from PG. Has its own buffer which can grow dynamically _reader: Reader,
_allocator: Allocator,
_io: Io,
// Holds information describing the query that we're executing. If the query // returns more columns than an appropriately sized ResultState is created as // needed. _result_state: Result.State,
// Holds information describing the parameters that PG is expecting. If the // query has more parameters, than an appropriately sized one is created. // This is separate from _result_state because: // (a) they are populated separately // (b) have distinct lifetimes // (c) they likely have different lengths; _param_oids: []i32,
// cache_name => data necessary to re-execute previously prepared statement. _prepared_statements: std.hash_map.StringHashMapUnmanaged(Stmt.Describe),
const State = enum { idle,
// something bad happened fail,
// we're doing a query query,
// we're in a transaction transaction, };
pub const Opts = struct { host: ?[]const u8 = null, port: ?u16 = null, write_buffer: ?u16 = null, read_buffer: ?u16 = null, result_state_size: u16 = 32, tls: TLS = .off, _hostz: ?[:0]const u8 = null,
// tcp keepalive settings (null timer = OS default) keepalive: bool = true, keepalive_idle: ?u32 = 30, keepalive_interval: ?u32 = 10, keepalive_count: ?u32 = 3,
pub const TLS = union(enum) { off: void, require: void, verify_full: ?[]const u8, }; };
pub const AuthOpts = struct { username: []const u8 = "postgres", password: ?[]const u8 = null, database: ?[]const u8 = null, timeout: u32 = 10_000, application_name: ?[]const u8 = null, startup_parameters: ?std.hash_map.StringHashMap([]const u8) = null, };
pub const QueryOpts = struct { timeout: ?u32 = null, column_names: bool = lib.default_column_names,
allocator: ?Allocator = null, // Whether a call to result.deinit() should automatically release the // connection back to the pool. Meant to be used internally by pool.query() // and the other pool utility wrappers, but applications might find it useful // to use in their own helpers release_conn: bool = false,
// When not null, the prepared statement will be cached and re-used // by subsequent queries using the same name. cache_name: ?[]const u8 = null, };
pub fn openAndAuthUri(io: Io, allocator: Allocator, uri: std.Uri) !Conn { var po = try lib.parseOpts(uri, allocator); defer po.deinit(); return try openAndAuth(io, allocator, po.opts.connect, po.opts.auth); }
pub fn openAndAuth(io: Io, allocator: Allocator, opts: Opts, ao: AuthOpts) !Conn { var conn = try open(io, allocator, opts); errdefer conn.deinit();
try conn.auth(ao); return conn; }
pub fn open(io: Io, allocator: Allocator, opts: Opts) !Conn { var ssl_ctx: ?*SSLCtx = null; switch (opts.tls) { .off => {}, else => |tls_config| { if (comptime lib.has_openssl == false) { return error.OpenSSLNotConfigured; } ssl_ctx = try lib.initializeSSLContext(tls_config); }, } errdefer lib.freeSSLContext(ssl_ctx); var conn = try openWithContext(io, allocator, opts, ssl_ctx); conn._ssl_ctx = ssl_ctx; return conn; }
pub fn openWithContext(io: Io, allocator: Allocator, opts: Opts, ssl_ctx: ?*SSLCtx) !Conn { var stream = try Stream.connect(io, allocator, opts, ssl_ctx); errdefer stream.close();
const buf = try Buffer.init(allocator, @max(opts.write_buffer orelse 2048, 128)); errdefer buf.deinit();
const reader = try Reader.init(allocator, opts.read_buffer orelse 4096, stream); errdefer reader.deinit();
const result_state = try Result.State.init(allocator, opts.result_state_size); errdefer result_state.deinit(allocator);
const param_oids = try allocator.alloc(i32, opts.result_state_size); errdefer param_oids.deinit(allocator);
return .{ .err = null, ._buf = buf, ._ssl_ctx = null, ._reader = reader, ._stream = stream, ._err_data = null, ._state = .idle, ._allocator = allocator, ._io = io, ._param_oids = param_oids, ._result_state = result_state, ._prepared_statements = .{}, }; }
pub fn deinit(self: *Conn) void { const allocator = self._allocator; if (self._err_data) |err_data| { allocator.free(err_data); } self._buf.deinit(); self._reader.deinit(); allocator.free(self._param_oids); self._result_state.deinit(allocator);
lib.sendTerminate(&self._stream, self._io); lib.freeSSLContext(self._ssl_ctx); self._stream.close();
var it = self._prepared_statements.valueIterator(); while (it.next()) |value_ptr| { value_ptr.arena.deinit(); } self._prepared_statements.deinit(self._allocator); }
pub fn release(self: *Conn) void { var pool = self._pool orelse { self.deinit(); return; }; self.err = null; pool.release(self); }
pub fn auth(self: *Conn, opts: AuthOpts) !void { if (try lib.auth.auth(self._io, &self._stream, &self._buf, &self._reader, opts)) |raw_pg_err| { return self.setErr(raw_pg_err); }
while (true) { const msg = try self.read(); switch (msg.type) { 'Z' => return, 'K' => {}, // TODO: BackendKeyData else => return self.unexpectedDBMessage(), } } }
pub fn prepare(self: *Conn, sql: []const u8) !Stmt { return self.prepareOpts(sql, .{}); }
pub fn prepareOpts(self: *Conn, sql: []const u8, opts: QueryOpts) !Stmt { var stmt = try Stmt.init(self, opts); errdefer stmt.deinit(); try stmt.prepare(sql, null); return stmt; }
pub fn query(self: *Conn, sql: []const u8, values: anytype) !*Result { return self.queryOpts(sql, values, .{}); }
pub fn queryOpts(self: *Conn, sql: []const u8, values: anytype, opts: QueryOpts) !*Result { if (self.canQuery() == false) { self.maybeRelease(opts.release_conn); return error.ConnectionBusy; }
var cached = false; var stmt: Stmt = undefined; const name = opts.cache_name;
if (name) |n| { if (self._prepared_statements.getPtr(n)) |describe| { cached = true; stmt = try Stmt.fromDescribe(self, describe, opts); errdefer stmt.deinit();
try self._reader.startFlow(stmt.arena.allocator(), opts.timeout); // Send a "SYNC" command try self.write(&.{ 'S', 0, 0, 0, 4 }); stmt.buf.reset(); try stmt.prepareForBind(@intCast(describe.param_oids.len)); } }
if (cached == false) { // either this isn't supposed to be cached, or it is, but we don't // have it in our cache stmt = Stmt.init(self, opts) catch |err| { self.maybeRelease(opts.release_conn); return err; };
errdefer stmt.deinit(); if (name) |n| { var describe_arena = ArenaAllocator.init(self._allocator); errdefer describe_arena.deinit(); try stmt.prepare(sql, describe_arena.allocator());
// When prepare is called with our describe arena, than its // param_oids and result_state will be create with it specifically // so that we can copy them here. const owned_name = try describe_arena.allocator().dupe(u8, n); try self._prepared_statements.put(self._allocator, owned_name, .{ .arena = describe_arena, .param_oids = stmt.param_oids, .result_state = stmt.result_state, }); } else { try stmt.prepare(sql, null); } }
{ errdefer stmt.deinit(); if (values.len != stmt.param_count) { return error.WrongNumberOfParameters; }
inline for (values) |value| { try stmt.bind(value); } }
return stmt.execute() catch |err| { stmt.deinit(); self.maybeRelease(opts.release_conn); return err; }; }
pub fn row(self: *Conn, sql: []const u8, values: anytype) !?QueryRow { return self._row(.safe, sql, values, .{}); }
pub fn rowUnsafe(self: *Conn, sql: []const u8, values: anytype) !?QueryRowUnsafe { return self._row(.unsafe, sql, values, .{}); }
pub fn rowOpts(self: *Conn, sql: []const u8, values: anytype, opts: QueryOpts) !?QueryRow { return self._row(.safe, sql, values, opts); }
pub fn rowUnsafeOpts(self: *Conn, sql: []const u8, values: anytype, opts: QueryOpts) !?QueryRowUnsafe { return self._row(.unsafe, sql, values, opts); }
fn _row(self: *Conn, comptime fail_mode: lib.FailMode, sql: []const u8, values: anytype, opts: QueryOpts) !(if (fail_mode == .safe) ?QueryRow else ?QueryRowUnsafe) { var result = try self.queryOpts(sql, values, opts); errdefer result.deinit();
if (comptime fail_mode == .safe) { return .{ .result = result, .row = try result.next() orelse { result.deinit(); return null; }, }; }
return .{ .result = result, .row = try result.nextUnsafe() orelse { result.deinit(); return null; }, }; }
// Execute a query that does not return rows pub fn exec(self: *Conn, sql: []const u8, values: anytype) !?i64 { return self.execOpts(sql, values, .{}); }
pub fn execOpts(self: *Conn, sql: []const u8, values: anytype, opts: QueryOpts) !?i64 { if (self.canQuery() == false) { return error.ConnectionBusy; } var buf = &self._buf; buf.reset();
if (values.len == 0) { try self._reader.startFlow(opts.allocator, opts.timeout); defer self._reader.endFlow() catch { // this can only fail in extreme conditions (OOM) and it will only impact // the next query (and if the app is using the pool, the pool will try to // recover from this anyways) self._state = .fail; }; const simple_query = proto.Query{ .sql = sql }; try simple_query.write(buf); // no longer idle, we're now in a query lib.metrics.query(); self._state = .query; try self.write(buf.string()); } else { // TODO: there's some optimization opportunities here, since we know // we aren't expecting any result. We don't have to ask PG to DESCRIBE // the returned columns (there should be none). This is very significant // as it would remove 1 back-and-forth. We could just: // Parse + Bind + Exec + Sync // Instead of having to do: // Parse + Describe + Sync ... read response ... Bind + Exec + Sync const result = try self.queryOpts(sql, values, opts); result.deinit(); }
// affected can be null, so we need a separate boolean to track if we // actually have a response. var affected: ?i64 = null; while (true) { const msg = self.read() catch |err| { if (err == error.PG) try self.recoverFromError(); return err; }; switch (msg.type) { 'C' => { const cc = try proto.CommandComplete.parse(msg.data); affected = cc.rowsAffected(); }, 'Z' => return affected, 'T' => affected = 0, 'D' => affected = (affected orelse 0) + 1, else => return self.unexpectedDBMessage(), } } }
pub fn begin(self: *Conn) !void { self._state = .transaction; _ = try self.execOpts("begin", .{}, .{}); }
pub fn commit(self: *Conn) !void { _ = try self.execOpts("commit", .{}, .{}); }
// We don't use `execOpts` here because rollback can be called at any point // and we want to send this command even if the conn is in a fail state. // So we issue the rollback, no matter what state we're in. // It's also possible rollback was called while we were reading results, // so we need to keep reading replies until we get a ready to query state, // just skipping over any data rows or any other in-flight messages there // might be. pub fn rollback(self: *Conn) !void { return self.execIgnoringState("rollback"); }
pub fn tryRollback(self: *Conn) !void { if (self._state != .idle) { try self.rollback(); } }
pub fn execIgnoringState(self: *Conn, sql: []const u8) !void { var buf = &self._buf; buf.reset();
const state = self._state;
const simple_query = proto.Query{ .sql = sql }; try simple_query.write(buf); try self.write(buf.string()); while (true) { const msg = self.read() catch |err| { if (state != .fail and err == error.PG) try self.recoverFromError(); return err; }; switch (msg.type) { 'Z' => return, 'C', 'T', 'D', 'n' => {}, else => return self.unexpectedDBMessage(), } } }
pub fn deallocate(self: *Conn, cache_name: []const u8) !void { if (self._prepared_statements.fetchRemove(cache_name)) |kv| { kv.value.arena.deinit(); } const allocator = self._allocator; const sql = try std.fmt.allocPrint(allocator, "deallocate {s}", .{cache_name}); defer allocator.free(sql); _ = try self.execOpts(sql, .{}, .{}); }
// Should not be called directly pub fn peekForError(self: *Conn) !void { const data = (try self._reader.peekForError()) orelse return; try self.readyForQuery(); return self.setErr(data); }
// Should not be called directly pub fn read(self: *Conn) !lib.Message { var reader = &self._reader; while (true) { const msg = reader.next() catch |err| { self._state = .fail; return err; }; switch (msg.type) { 'Z' => { self._state = switch (msg.data[0]) { 'I' => .idle, 'T' => .transaction, 'E' => .fail, else => unreachable, }; return msg; }, 'S' => {}, // TODO: ParameterStatus, 'N' => {}, // TODO: NoticeResponse 'E' => return self.setErr(msg.data), else => return msg, } } }
pub fn write(self: *Conn, data: []const u8) !void { self._stream.writeAll(data) catch |err| { self._state = .fail; return err; }; }
fn setErr(self: *Conn, data: []const u8) error{ PG, OutOfMemory } { const allocator = self._allocator;
// The proto.Error that we're about to create is going to reference data. // But data is owned by our Reader and its lifetime doesn't necessarily match // what we want here. So we're going to dupe it and make the connection own // the data so it can tie its lifecycle to the error.
// That means clearing out any previous duped error data we had if (self._err_data) |err_data| { allocator.free(err_data); }
const owned = try allocator.dupe(u8, data); self._err_data = owned; self.err = proto.Error.parse(owned); return error.PG; }
pub fn unexpectedDBMessage(self: *Conn) error{UnexpectedDBMessage} { self._state = .fail; return error.UnexpectedDBMessage; }
fn canQuery(self: *const Conn) bool { const state = self._state; if (state == .idle or state == .transaction) { return true; } return false; }
inline fn maybeRelease(self: *Conn, rel: bool) void { if (rel) { self.release(); } }
// should not be called directly pub fn readyForQuery(self: *Conn) !void { const msg = try self.read(); if (msg.type != 'Z') { return self.unexpectedDBMessage(); } }
// Drain the trailing ReadyForQuery after a server error so the connection // stays usable. Best-effort, but never swallow a cancellation. pub fn recoverFromError(self: *Conn) error{Canceled}!void { self.readyForQuery() catch |err| { if (err == error.Canceled) return error.Canceled; }; }};
const t = lib.testing;test "Conn: auth trust (no pass)" { var conn = try Conn.open(t.io, t.allocator, .{}); defer conn.deinit(); try conn.auth(.{ .username = "pgz_user_nopass", .database = "postgres" });}
test "Conn: auth unknown user" { var conn = try Conn.open(t.io, t.allocator, .{}); defer conn.deinit(); try t.expectError(error.PG, conn.auth(.{ .username = "does_not_exist" })); try t.expectEqual(true, std.mem.find(u8, conn.err.?.message, "user \"does_not_exist\"") != null);}
test "Conn: auth cleartext password" { { var conn = try Conn.open(t.io, t.allocator, .{}); defer conn.deinit(); try t.expectError(error.PG, conn.auth(.{ .username = "pgz_user_clear" })); try t.expectString("empty password returned by client", conn.err.?.message); }
{ var conn = try Conn.open(t.io, t.allocator, .{}); defer conn.deinit(); try t.expectError(error.PG, conn.auth(.{ .username = "pgz_user_clear", .password = "wrong" })); try t.expectString("password authentication failed for user \"pgz_user_clear\"", conn.err.?.message); }
{ var conn = try Conn.open(t.io, t.allocator, .{}); defer conn.deinit(); try conn.auth(.{ .username = "pgz_user_clear", .password = "pgz_user_clear_pw", .database = "postgres" }); }}
test "Conn: auth scram-sha-256 password" { { var conn = try Conn.open(t.io, t.allocator, .{}); defer conn.deinit(); try t.expectError(error.PG, conn.auth(.{ .username = "pgz_user_scram_sha256" })); try t.expectString("password authentication failed for user \"pgz_user_scram_sha256\"", conn.err.?.message); }
{ var conn = try Conn.open(t.io, t.allocator, .{}); defer conn.deinit(); try t.expectError(error.PG, conn.auth(.{ .username = "pgz_user_scram_sha256", .password = "wrong" })); try t.expectString("password authentication failed for user \"pgz_user_scram_sha256\"", conn.err.?.message); }
{ var conn = try Conn.open(t.io, t.allocator, .{}); defer conn.deinit(); try conn.auth(.{ .username = "pgz_user_scram_sha256", .password = "pgz_user_scram_sha256_pw", .database = "postgres" }); }}
test "Conn: exec rowsAffected" { var c = try t.connect(.{}); defer c.deinit();
{ const n = try c.exec("insert into simple_table values ('exec_insert_a'), ('exec_insert_b')", .{}); try t.expectEqual(2, n.?); }
{ const n = try c.exec("update simple_table set value = 'exec_insert_a' where value = 'exec_insert_a'", .{}); try t.expectEqual(1, n.?); }
{ const n = try c.exec("delete from simple_table where value like 'exec_insert%'", .{}); try t.expectEqual(2, n.?); }
{ try t.expectEqual(null, try c.exec("begin", .{})); try t.expectEqual(null, try c.exec("end", .{})); }}
test "Conn: exec with values rowsAffected" { var c = try t.connect(.{}); defer c.deinit();
{ const n = try c.exec("insert into simple_table values ($1), ($2)", .{ "exec_insert_args_a", "exec_insert_args_b" }); try t.expectEqual(2, n.?); }}
test "Conn: exec query that returns rows" { var c = try t.connect(.{}); defer c.deinit(); _ = try c.exec("insert into simple_table values ('exec_sel_1'), ('exec_sel_2')", .{}); try t.expectEqual(0, c.exec("select * from simple_table where value = 'none'", .{})); try t.expectEqual(2, c.exec("select * from simple_table where value like $1", .{"exec_sel_%"}));}
test "Conn: parse error" { var c = try t.connect(.{}); defer c.deinit(); try t.expectError(error.PG, c.query("selct 1", .{}));
const err = c.err.?; try t.expectString("42601", err.code); try t.expectString("ERROR", err.severity); try t.expectString("syntax error at or near \"selct\"", err.message);
// connection is still usable try t.expectEqual(2, t.scalar(&c, "select 2"));}
test "Conn: Query within Query error" { var c = try t.connect(.{}); defer c.deinit(); var rows = try c.query("select 1", .{}); defer rows.deinit();
try t.expectError(error.ConnectionBusy, c.row("select 2", .{})); try t.expectEqual(1, (try rows.nextUnsafe()).?.get(i32, 0));}
test "PG: type support" { defer t.reset();
var c = try t.connect(.{}); defer c.deinit(); var bytea1 = [_]u8{ 0, 1 }; var bytea2 = [_]u8{ 255, 253, 253 };
{ const result = c.exec( \\ \\ insert into all_types ( \\ id, \\ col_int2, col_int2_arr, \\ col_int4, col_int4_arr, \\ col_int8, col_int8_arr, \\ col_float4, col_float4_arr, \\ col_float8, col_float8_arr, \\ col_bool, col_bool_arr, \\ col_text, col_text_arr, \\ col_bytea, col_bytea_arr, \\ col_enum, col_enum_arr, \\ col_uuid, col_uuid_arr, \\ col_numeric, col_numeric_arr, \\ col_timestamp, col_timestamp_arr, \\ col_timestamptz, col_timestamptz_arr, \\ col_json, col_json_arr, \\ col_jsonb, col_jsonb_arr, \\ col_char, col_char_arr, \\ col_charn, col_charn_arr, \\ col_cidr, col_cidr_arr, \\ col_inet, col_inet_arr, \\ col_macaddr, col_macaddr_arr, \\ col_macaddr8, col_macaddr8_arr \\ ) values ( \\ $1, \\ $2, $3, \\ $4, $5, \\ $6, $7, \\ $8, $9, \\ $10, $11, \\ $12, $13, \\ $14, $15, \\ $16, $17, \\ $18, $19, \\ $20, $21, \\ $22, $23, \\ $24, $25, \\ $26, $27, \\ $28, $29, \\ $30, $31, \\ $32, $33, \\ $34, $35, \\ $36, $37, \\ $38, $39, \\ $40, $41, \\ $42, $43 \\ ) , .{ 1, @as(i16, 382), [_]i16{ -9000, 9001 }, @as(i32, -96534), [_]i32{-4929123}, @as(i64, 8983919283), [_]i64{ 8888848483, 0, -1 }, @as(f32, 1.2345), [_]f32{ 4.492, -0.000021 }, @as(f64, -48832.3233231), [_]f64{ 393.291133, 3.1144 }, true, [_]bool{ false, true }, "a text column", [_][]const u8{ "it's", "over", "9000" }, [_]u8{ 0, 0, 2, 255, 255, 255 }, &[_][]u8{ &bytea1, &bytea2 }, "val1", [_][]const u8{ "val1", "val2" }, "b7cc282f-ec43-49be-8e09-aafab0104915", [_][]const u8{ "166B4751-D702-4FB9-9A2A-CD6B69ED18D6", "ae2f475f-8070-41b7-ba33-86bba8897bde" }, 1234.567, [_]f64{ 0, -1.1, std.math.nan(f64), std.math.inf(f32), 12345.000101 }, "2023-10-23T15:33:13Z", [_][]const u8{ "2010-02-10T08:22:07Z", "0003-04-05T06:07:08.123456" }, "2024-11-23T16:34:14Z", [_][]const u8{ "2011-03-11T09:23:05Z", "0002-03-04T05:06:02.0000991" }, "{\"count\":1.3}", [_][]const u8{ "[1,2,3]", "{\"rows\":[{\"a\": true}]}" }, "{\"over\":9000}", [_][]const u8{ "[true,false]", "{\"cols\":[{\"z\": 0.003}]}" }, 79, [_]u8{ '1', 'z', '!' }, "Teg", [_][]const u8{ &.{ 78, 82 }, "hi" }, "192.168.100.128/25", [_][]const u8{ "10.1.2", "2001:4f8:3:ba::/64" }, "::ffff:1.2.3.0/120", [_][]const u8{ "127.0.0.1/32", "2001:4f8:3:ba:2e0:81ff:fe22:d1f1/128" }, "08:00:2b:01:02:03", [_][]const u8{ "08002b:010203", "0800-2b01-0204" }, "09:01:3b:21:21:03:04:05", [_][]const u8{ "ffeeddccbbaa9988", "01-02-03-04-05-06-07-09" }, }); if (result) |affected| { try t.expectEqual(1, affected); } else |err| { try t.fail(c, err); } }
var result = try c.query( \\ select \\ id, \\ col_int2, col_int2_arr, \\ col_int4, col_int4_arr, \\ col_int8, col_int8_arr, \\ col_float4, col_float4_arr, \\ col_float8, col_float8_arr, \\ col_bool, col_bool_arr, \\ col_text, col_text_arr, \\ col_bytea, col_bytea_arr, \\ col_enum, col_enum_arr, \\ col_uuid, col_uuid_arr, \\ col_numeric, col_numeric_arr, \\ col_timestamp, col_timestamp_arr, \\ col_timestamptz, col_timestamptz_arr, \\ col_json, col_json_arr, \\ col_jsonb, col_jsonb_arr, \\ col_char, col_char_arr, \\ col_charn, col_charn_arr, \\ col_cidr, col_cidr_arr, \\ col_inet, col_inet_arr, \\ col_macaddr, col_macaddr_arr, \\ col_macaddr8, col_macaddr8_arr \\ from all_types where id = $1 , .{1}); defer result.deinit();
// used for our arrays const aa = t.arena.allocator();
const row = (try result.nextUnsafe()) orelse unreachable; try t.expectEqual(1, row.get(i32, 0));
{ // smallint & smallint[] try t.expectEqual(382, row.get(i16, 1)); try t.expectSlice(i16, &.{ -9000, 9001 }, try row.iterator(i16, 2).alloc(aa)); }
{ // int & int[] try t.expectEqual(-96534, row.get(i32, 3)); try t.expectSlice(i32, &.{-4929123}, try row.iterator(i32, 4).alloc(aa)); }
{ // bigint & bigint[] try t.expectEqual(8983919283, row.get(i64, 5)); try t.expectSlice(i64, &.{ 8888848483, 0, -1 }, try row.iterator(i64, 6).alloc(aa)); }
{ // float4, float4[] try t.expectEqual(1.2345, row.get(f32, 7)); try t.expectSlice(f32, &.{ 4.492, -0.000021 }, try row.iterator(f32, 8).alloc(aa)); }
{ // float8, float8[] try t.expectEqual(-48832.3233231, row.get(f64, 9)); try t.expectSlice(f64, &.{ 393.291133, 3.1144 }, try row.iterator(f64, 10).alloc(aa)); }
{ // bool, bool[] try t.expectEqual(true, row.get(bool, 11)); try t.expectSlice(bool, &.{ false, true }, try row.iterator(bool, 12).alloc(aa)); }
{ // text, text[] try t.expectString("a text column", row.get([]u8, 13)); const arr = try row.iterator([]const u8, 14).alloc(aa); try t.expectEqual(3, arr.len); try t.expectString("it's", arr[0]); try t.expectString("over", arr[1]); try t.expectString("9000", arr[2]); }
{ // bytea, bytea[] try t.expectSlice(u8, &.{ 0, 0, 2, 255, 255, 255 }, row.get([]const u8, 15)); const arr = try row.iterator([]u8, 16).alloc(aa); try t.expectEqual(2, arr.len); try t.expectSlice(u8, &bytea1, arr[0]); try t.expectSlice(u8, &bytea2, arr[1]); }
{ // enum, emum[] try t.expectString("val1", row.get([]u8, 17)); const arr = try row.iterator([]const u8, 18).alloc(aa); try t.expectEqual(2, arr.len); try t.expectString("val1", arr[0]); try t.expectString("val2", arr[1]); }
{ //uuid, uuid[] try t.expectSlice(u8, &.{ 183, 204, 40, 47, 236, 67, 73, 190, 142, 9, 170, 250, 176, 16, 73, 21 }, row.get([]u8, 19)); const arr = try row.iterator([]const u8, 20).alloc(aa); try t.expectEqual(2, arr.len); try t.expectSlice(u8, &.{ 22, 107, 71, 81, 215, 2, 79, 185, 154, 42, 205, 107, 105, 237, 24, 214 }, arr[0]); try t.expectSlice(u8, &.{ 174, 47, 71, 95, 128, 112, 65, 183, 186, 51, 134, 187, 168, 137, 123, 222 }, arr[1]); }
{ // numeric, numeric[] try t.expectEqual(1234.567, row.get(f64, 21)); const arr = try row.iterator(types.Numeric, 22).alloc(aa); try t.expectEqual(5, arr.len); try expectNumeric(arr[0], "0.0"); try expectNumeric(arr[1], "-1.1"); try expectNumeric(arr[2], "nan"); try expectNumeric(arr[3], "inf"); try expectNumeric(arr[4], "12345.000101"); }
{ //timestamp, timestamp[] try t.expectEqual(1698075193000000, row.get(i64, 23)); try t.expectSlice(i64, &.{ 1265790127000000, -62064381171876544 }, try row.iterator(i64, 24).alloc(aa)); }
{ //timestamptz, timestamptz[] try t.expectEqual(1732379654000000, row.get(i64, 25)); try t.expectSlice(i64, &.{ 1299835385000000, -62098685637999901 }, try row.iterator(i64, 26).alloc(aa)); }
{ // json, json[] try t.expectString("{\"count\":1.3}", row.get([]u8, 27)); const arr = try row.iterator([]const u8, 28).alloc(aa); try t.expectEqual(2, arr.len); try t.expectString("[1,2,3]", arr[0]); try t.expectString("{\"rows\":[{\"a\": true}]}", arr[1]); }
{ // jsonb, jsonb[] try t.expectString("{\"over\": 9000}", row.get([]u8, 29)); const arr = try row.iterator([]const u8, 30).alloc(aa); try t.expectEqual(2, arr.len); try t.expectString("[true, false]", arr[0]); try t.expectString("{\"cols\": [{\"z\": 0.003}]}", arr[1]); }
{ // char, char[] try t.expectEqual(79, row.get(u8, 31)); const arr = try row.iterator(u8, 32).alloc(aa); try t.expectEqual(3, arr.len); try t.expectEqual('1', arr[0]); try t.expectEqual('z', arr[1]); try t.expectEqual('!', arr[2]); }
{ // charn, charn[] try t.expectString("Teg", row.get([]u8, 33)); const arr = try row.iterator([]u8, 34).alloc(aa); try t.expectEqual(2, arr.len); try t.expectString("NR", arr[0]); try t.expectString("hi", arr[1]); }
{ // cidr, cidr[] const cidr = row.get(types.Cidr, 35); try t.expectEqual(25, cidr.netmask); try t.expectEqual(.v4, cidr.family); try t.expectString(&.{ 192, 168, 100, 128 }, cidr.address);
const arr = try row.iterator(types.Cidr, 36).alloc(aa); try t.expectEqual(2, arr.len);
try t.expectEqual(24, arr[0].netmask); try t.expectEqual(.v4, arr[0].family); try t.expectString(&.{ 10, 1, 2, 0 }, arr[0].address);
try t.expectEqual(64, arr[1].netmask); try t.expectEqual(.v6, arr[1].family); try t.expectSlice(u8, &.{ 32, 1, 4, 248, 0, 3, 0, 186, 0, 0, 0, 0, 0, 0, 0, 0 }, arr[1].address); }
{ // inet, inet[] const inet = row.get(types.Cidr, 37); try t.expectEqual(120, inet.netmask); try t.expectEqual(.v6, inet.family); try t.expectString(&.{ 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 255, 255, 1, 2, 3, 0 }, inet.address);
const arr = try row.iterator(types.Cidr, 38).alloc(aa); try t.expectEqual(2, arr.len);
try t.expectEqual(32, arr[0].netmask); try t.expectEqual(.v4, arr[0].family); try t.expectString(&.{ 127, 0, 0, 1 }, arr[0].address);
try t.expectEqual(128, arr[1].netmask); try t.expectEqual(.v6, arr[1].family); try t.expectSlice(u8, &.{ 32, 1, 4, 248, 0, 3, 0, 186, 2, 224, 129, 255, 254, 34, 209, 241 }, arr[1].address); }
{ // macaddr, macaddr[] try t.expectSlice(u8, &.{ 8, 0, 43, 1, 2, 3 }, row.get([]u8, 39));
const arr = try row.iterator([]u8, 40).alloc(aa); try t.expectEqual(2, arr.len); try t.expectSlice(u8, &.{ 8, 0, 43, 1, 2, 3 }, arr[0]); try t.expectSlice(u8, &.{ 8, 0, 43, 1, 2, 4 }, arr[1]); }
{ // macaddr8, macaddr8[] try t.expectSlice(u8, &.{ 9, 1, 59, 33, 33, 3, 4, 5 }, row.get([]u8, 41));
const arr = try row.iterator([]u8, 42).alloc(aa); try t.expectEqual(2, arr.len); try t.expectSlice(u8, &.{ 255, 238, 221, 204, 187, 170, 153, 136 }, arr[0]); try t.expectSlice(u8, &.{ 1, 2, 3, 4, 5, 6, 7, 9 }, arr[1]); }
try t.expectEqual(null, try result.next());}
// For ambiguous types, the above "type support" test is using the text-representation// This test will use the binary representation of each of the ambiguous typestest "PG: binary support" { defer t.reset();
var c = try t.connect(.{}); defer c.deinit();
{ const result = c.exec( \\ \\ insert into all_types ( \\ id, \\ col_uuid, col_uuid_arr, \\ col_timestamp, col_timestamp_arr, \\ col_timestamptz, col_timestamptz_arr, \\ col_numeric, col_numeric_arr, \\ col_macaddr, col_macaddr_arr, \\ col_macaddr8, col_macaddr8_arr \\ ) values ( \\ $1, \\ $2, $3, \\ $4, $5, \\ $6, $7, \\ $8, $9, \\ $10, $11, \\ $12, $13 \\ ) , .{ 2, &[_]u8{ 142, 243, 93, 100, 249, 159, 77, 126, 167, 54, 150, 204, 170, 222, 98, 124 }, [_][]const u8{ &.{ 53, 140, 59, 37, 1, 148, 72, 139, 130, 197, 181, 40, 44, 109, 127, 165 }, &.{ 57, 203, 218, 97, 37, 38, 70, 107, 182, 116, 24, 125, 236, 123, 117, 247 } }, 169804639500713, [_]i64{ 169804639500713, -94668480000000 }, 169804639500714, [_]i64{ 169804639500714, -94668480000001 }, "-394956.2221", [_][]const u8{ "1.0008", "-987.110", "-inf" }, &[_]u8{ 1, 2, 3, 4, 5, 6 }, [_][]const u8{ &.{ 0, 1, 0, 2, 0, 3 }, &.{ 255, 0, 254, 1, 253, 2 } }, &[_]u8{ 1, 2, 3, 4, 5, 6, 7, 8 }, [_][]const u8{ &.{ 0, 1, 0, 2, 0, 3, 4, 0 }, &.{ 255, 0, 254, 1, 253, 2, 3, 252 } }, }); if (result) |affected| { try t.expectEqual(1, affected); } else |err| { try t.fail(c, err); } }
var result = try c.query( \\ select \\ col_uuid, col_uuid_arr, \\ col_timestamp, col_timestamp_arr, \\ col_timestamptz, col_timestamptz_arr, \\ col_numeric, col_numeric_arr, \\ col_macaddr, col_macaddr_arr, \\ col_macaddr8, col_macaddr8_arr \\ from all_types where id = $1 , .{2}); defer result.deinit();
// used for our arrays const aa = t.arena.allocator();
const row = (try result.nextUnsafe()) orelse unreachable;
{ //uuid, uuid[] try t.expectString("8ef35d64-f99f-4d7e-a736-96ccaade627c", &(try types.UUID.toString(row.get([]u8, 0))));
const arr = try row.iterator([]const u8, 1).alloc(aa); try t.expectEqual(2, arr.len); try t.expectString("358c3b25-0194-488b-82c5-b5282c6d7fa5", &(try types.UUID.toString(arr[0]))); try t.expectString("39cbda61-2526-466b-b674-187dec7b75f7", &(try types.UUID.toString(arr[1]))); }
{ //timestamp, timestamp[] try t.expectEqual(169804639500713, row.get(i64, 2)); try t.expectSlice(i64, &.{ 169804639500713, -94668480000000 }, try row.iterator(i64, 3).alloc(aa)); }
{ //timestamptz, timestamptz[] try t.expectEqual(169804639500714, row.get(i64, 4)); try t.expectSlice(i64, &.{ 169804639500714, -94668480000001 }, try row.iterator(i64, 5).alloc(aa)); }
{ //numeric, numeric[] try t.expectEqual(-394956.2221, row.get(f64, 6)); try t.expectSlice(f64, &.{ 1.0008, -987.110, -std.math.inf(f64) }, try row.iterator(f64, 7).alloc(aa)); }
{ //macaddr, macaddr[] try t.expectSlice(u8, &.{ 1, 2, 3, 4, 5, 6 }, row.get([]u8, 8)); const arr = try row.iterator([]u8, 9).alloc(aa); try t.expectSlice(u8, &.{ 0, 1, 0, 2, 0, 3 }, arr[0]); try t.expectSlice(u8, &.{ 255, 0, 254, 1, 253, 2 }, arr[1]); }
{ //macaddr8, macaddr8[] try t.expectSlice(u8, &.{ 1, 2, 3, 4, 5, 6, 7, 8 }, row.get([]u8, 10)); const arr = try row.iterator([]u8, 11).alloc(aa); try t.expectSlice(u8, &.{ 0, 1, 0, 2, 0, 3, 4, 0 }, arr[0]); try t.expectSlice(u8, &.{ 255, 0, 254, 1, 253, 2, 3, 252 }, arr[1]); }
try t.expectEqual(null, try result.next());}
test "PG: null support" { var c = try t.connect(.{}); defer c.deinit(); { const result = c.exec( \\ \\ insert into all_types ( \\ id, \\ col_int2, col_int2_arr, \\ col_int4, col_int4_arr, \\ col_int8, col_int8_arr, \\ col_float4, col_float4_arr, \\ col_float8, col_float8_arr, \\ col_bool, col_bool_arr, \\ col_text, col_text_arr, \\ col_bytea, col_bytea_arr, \\ col_enum, col_enum_arr, \\ col_uuid, col_uuid_arr, \\ col_numeric, col_numeric_arr, \\ col_timestamp, col_timestamp_arr, \\ col_json, col_json_arr, \\ col_jsonb, col_jsonb_arr, \\ col_char, col_char_arr, \\ col_charn, col_charn_arr \\ ) values ( \\ $1, \\ $2, $3, \\ $4, $5, \\ $6, $7, \\ $8, $9, \\ $10, $11, \\ $12, $13, \\ $14, $15, \\ $16, $17, \\ $18, $19, \\ $20, $21, \\ $22, $23, \\ $24, $25, \\ $26, $27, \\ $28, $29, \\ $30, $31, \\ $32, $33 \\ ) , .{ 3, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, }); if (result) |affected| { try t.expectEqual(1, affected); } else |err| { try t.fail(c, err); } }
var result = try c.query( \\ select \\ id, \\ col_int2, col_int2_arr, \\ col_int4, col_int4_arr, \\ col_int8, col_int8_arr, \\ col_float4, col_float4_arr, \\ col_float8, col_float8_arr, \\ col_bool, col_bool_arr, \\ col_text, col_text_arr, \\ col_bytea, col_bytea_arr, \\ col_enum, col_enum_arr, \\ col_uuid, col_uuid_arr, \\ col_numeric, 'numeric[] placeholder', \\ col_timestamp, col_timestamp_arr, \\ col_json, col_json_arr, \\ col_jsonb, col_jsonb_arr, \\ col_char, col_char_arr, \\ col_charn, col_charn_arr \\ from all_types where id = $1 , .{3}); defer result.deinit();
const row = (try result.nextUnsafe()) orelse unreachable; try t.expectEqual(null, row.get(?i16, 1)); try t.expectEqual(true, row.iterator(i16, 2).is_null);
try t.expectEqual(null, row.get(?i32, 3)); try t.expectEqual(true, row.iterator(i32, 4).is_null);
try t.expectEqual(null, row.get(?i64, 5)); try t.expectEqual(true, row.iterator(i64, 6).is_null);
try t.expectEqual(null, row.get(?f32, 7)); try t.expectEqual(true, row.iterator(f32, 8).is_null);
try t.expectEqual(null, row.get(?f64, 9)); try t.expectEqual(true, row.iterator(f64, 10).is_null);
try t.expectEqual(null, row.get(?bool, 11)); try t.expectEqual(true, row.iterator(bool, 12).is_null);
try t.expectEqual(null, row.get(?[]u8, 13)); try t.expectEqual(true, row.iterator([]u8, 14).is_null);
try t.expectEqual(null, row.get(?[]const u8, 15)); try t.expectEqual(true, row.iterator([]const u8, 16).is_null);
try t.expectEqual(null, row.get(?[]const u8, 17)); try t.expectEqual(true, row.iterator([]const u8, 18).is_null);
try t.expectEqual(null, row.get(?[]u8, 19)); try t.expectEqual(true, row.iterator([]const u8, 20).is_null);
try t.expectEqual(null, row.get(?[]u8, 21)); try t.expectEqual(null, row.get(?f64, 21));
try t.expectEqual(null, row.get(?i64, 23)); try t.expectEqual(true, row.iterator(i64, 24).is_null);
try t.expectEqual(null, row.get(?[]u8, 25)); try t.expectEqual(true, row.iterator([]const u8, 26).is_null);
try t.expectEqual(null, row.get(?[]u8, 27)); try t.expectEqual(true, row.iterator([]const u8, 28).is_null);
try t.expectEqual(null, row.get(?u8, 29)); try t.expectEqual(true, row.iterator(u8, 30).is_null);
try t.expectEqual(null, row.get(?u8, 31)); try t.expectEqual(true, row.iterator(u8, 32).is_null);
try t.expectEqual(null, try result.next());}
test "PG: query column names" { var c = try t.connect(.{}); defer c.deinit(); { var result = try c.query("select 1 as id, 'leto' as name", .{}); try t.expectEqual(0, result.column_names.len); try result.drain(); result.deinit(); }
{ var result = try c.queryOpts("select 1 as id, 'leto' as name", .{}, .{ .column_names = true }); defer result.deinit(); try t.expectEqual(2, result.column_names.len); try t.expectString("id", result.column_names[0]); try t.expectString("name", result.column_names[1]); }}
test "PG: JSON struct" { var c = try t.connect(.{}); defer c.deinit();
{ const result = c.exec( \\ \\ insert into all_types (id, col_json, col_jsonb) \\ values ($1, $2, $3) , .{ 4, DummyStruct{ .id = 1, .name = "Leto" }, &DummyStruct{ .id = 2, .name = "Ghanima" } });
if (result) |affected| { try t.expectEqual(1, affected); } else |err| { try t.fail(c, err); } }
var result = try c.query("select col_json, col_jsonb from all_types where id = $1", .{4}); defer result.deinit();
const row = (try result.nextUnsafe()) orelse unreachable; try t.expectString("{\"id\":1,\"name\":\"Leto\"}", row.get([]u8, 0)); try t.expectString("{\"id\": 2, \"name\": \"Ghanima\"}", row.get(?[]const u8, 1).?);}
test "Conn: prepare" { var c = try t.connect(.{}); defer c.deinit();
var stmt = try c.prepare("select $1::int where $2"); try stmt.bind(938); try stmt.bind(true);
var result = try stmt.execute(); defer result.deinit();
var row = (try result.next()) orelse unreachable; try t.expectEqual(938, row.get(i32, 0));
try t.expectEqual(null, try result.next());}
test "PG: row" { var c = try t.connect(.{}); defer c.deinit();
const r1 = try c.row("select 1 where $1", .{false}); try t.expectEqual(null, r1);
var r2 = (try c.rowUnsafe("select 2 where $1", .{true})) orelse unreachable; try t.expectEqual(2, r2.get(i32, 0)); try r2.deinit();
// make sure the conn is still valid after a successful row var r3 = (try c.rowUnsafe("select $1::int where $2", .{ 3, true })) orelse unreachable; try t.expectEqual(3, r3.get(i32, 0)); try r3.deinit();
// make sure the conn is still valid after MoreThanOneRow error var r4 = (try c.rowUnsafe("select $1::text where $2", .{ "hi", true })) orelse unreachable; try t.expectString("hi", r4.get([]u8, 0)); try r4.deinit();}
test "PG: begin/commit" { var c = try t.connect(.{}); defer c.deinit();
try c.begin(); _ = try c.exec("delete from simple_table", .{}); _ = try c.exec("insert into simple_table values ($1)", .{"begin_commit"}); try c.commit();
var row = (try c.rowUnsafe("select value from simple_table", .{})).?; defer row.deinit() catch {};
try t.expectString("begin_commit", row.get([]u8, 0));}
test "PG: begin/rollback" { var c = try t.connect(.{}); defer c.deinit();
_ = try c.exec("delete from simple_table", .{}); try c.begin(); _ = try c.exec("insert into simple_table values ($1)", .{"begin_commit"}); try c.rollback();
const row = try c.row("select value from simple_table", .{}); try t.expectEqual(null, row);}
test "PG: bind enums" { var c = try t.connect(.{}); defer c.deinit();
_ = try c.exec( \\ insert into all_types (id, col_enum, col_enum_arr, col_text, col_text_arr) \\ values (5, $1, $2, $3, $4) , .{ DummyEnum.val1, &[_]DummyEnum{ DummyEnum.val1, DummyEnum.val2 }, DummyEnum.val2, [_]DummyEnum{ DummyEnum.val2, DummyEnum.val1 } });
var row = (try c.rowUnsafe( \\ select col_enum, col_text, col_enum_arr, col_text_arr \\ from all_types \\ where id = 5 , .{})) orelse unreachable; defer row.deinit() catch {};
try t.expectString("val1", row.get([]u8, 0)); try t.expectString("val2", row.get([]u8, 1));
var arena = std.heap.ArenaAllocator.init(t.allocator); defer arena.deinit(); const aa = arena.allocator();
{ const arr = try row.iterator([]const u8, 2).alloc(aa); try t.expectEqual(2, arr.len); try t.expectString("val1", arr[0]); try t.expectString("val2", arr[1]); }
{ const arr = try row.iterator([]const u8, 3).alloc(aa); try t.expectEqual(2, arr.len); try t.expectString("val2", arr[0]); try t.expectString("val1", arr[1]); }}
test "PG: numeric" { defer t.reset();
var c = try t.connect(.{}); defer c.deinit();
{ // read var row = (try c.rowUnsafe( \\ select 'nan'::numeric, '+Inf'::numeric, '-Inf'::numeric, \\ 0::numeric, 0.0::numeric, -0.00009::numeric, -999999.888880::numeric, \\ 0.000008, 999999.888807::numeric, 123456.78901234::numeric(14, 8) , .{})).?; defer row.deinit() catch {};
try t.expectEqual(true, std.math.isNan(row.get(f64, 0))); try t.expectEqual(true, std.math.isInf(row.get(f64, 1))); try t.expectEqual(true, std.math.isNegativeInf(row.get(f64, 2))); try t.expectEqual(0, row.get(f64, 3)); try t.expectEqual(0, row.get(f64, 4)); try t.expectEqual(-0.00009, row.get(f64, 5)); try t.expectEqual(-999999.888880, row.get(f64, 6)); try t.expectEqual(0.000008, row.get(f64, 7)); try t.expectEqual(999999.888807, row.get(f64, 8)); try t.expectEqual(123456.78901234, row.get(f64, 9)); }
{ // write + write var row = (try c.rowUnsafe( \\ select \\ $1::numeric, $2::numeric, $3::numeric, \\ $4::numeric, $5::numeric, $6::numeric, \\ $7::numeric, $8::numeric, $9::numeric, \\ $10::numeric, $11::numeric, $12::numeric, \\ $13::numeric, $14::numeric, $15::numeric, \\ $16::numeric, $17::numeric, $18::numeric, \\ $19::numeric, $20::numeric, $21::numeric, \\ $22::numeric, $23::numeric, $24::numeric, \\ $25::numeric, $26::numeric, $27::numeric[] , .{ -0.00089891, 939293122.0001101, "-123.4560991", std.math.nan(f64), std.math.inf(f64), -std.math.inf(f64), std.math.nan(f32), std.math.inf(f32), -std.math.inf(f32), 1.1, 12.98, 123.987, 1234.9876, 12345.98765, 123456.987654, 1234567.9876543, 12345678.98765432, 123456789.987654321, @as(f64, 0), @as(f64, 1), 0, 1, 999999999.9999999, @as(f64, 999999999.9999999), -999999999.9999999, @as(f64, -999999999.9999999), &[_][]const u8{ "1.1", "-0.0034" } })).?; defer row.deinit() catch {};
{ // test the pg.Numeric fields const numeric = row.get(types.Numeric, 1); try t.expectEqual(939293122.0001101, numeric.toFloat()); try t.expectEqual(2, numeric.weight); try t.expectEqual(.positive, numeric.sign); try t.expectEqual(7, numeric.scale); try t.expectSlice(u8, &.{ 0, 9, 15, 89, 12, 50, 0, 1, 3, 242 }, numeric.digits); }
try expectNumeric(row.get(types.Numeric, 0), "-0.00089891"); try expectNumeric(row.get(types.Numeric, 1), "939293122.0001101"); try expectNumeric(row.get(types.Numeric, 2), "-123.4560991");
try expectNumeric(row.get(types.Numeric, 3), "nan"); try expectNumeric(row.get(types.Numeric, 4), "inf"); try expectNumeric(row.get(types.Numeric, 5), "-inf");
try expectNumeric(row.get(types.Numeric, 6), "nan"); try expectNumeric(row.get(types.Numeric, 7), "inf"); try expectNumeric(row.get(types.Numeric, 8), "-inf");
try expectNumeric(row.get(types.Numeric, 9), "1.1"); try expectNumeric(row.get(types.Numeric, 10), "12.98"); try expectNumeric(row.get(types.Numeric, 11), "123.987"); try expectNumeric(row.get(types.Numeric, 12), "1234.9876"); try expectNumeric(row.get(types.Numeric, 13), "12345.98765"); try expectNumeric(row.get(types.Numeric, 14), "123456.987654"); try expectNumeric(row.get(types.Numeric, 15), "1234567.9876543"); try expectNumeric(row.get(types.Numeric, 16), "12345678.98765432"); try expectNumeric(row.get(types.Numeric, 17), "123456789.987654321"); try expectNumeric(row.get(types.Numeric, 18), "0.0"); try expectNumeric(row.get(types.Numeric, 19), "1.0"); try expectNumeric(row.get(types.Numeric, 20), "0.0"); try expectNumeric(row.get(types.Numeric, 21), "1.0"); try expectNumeric(row.get(types.Numeric, 22), "999999999.9999999"); try expectNumeric(row.get(types.Numeric, 23), "999999999.9999999"); try expectNumeric(row.get(types.Numeric, 24), "-999999999.9999999"); try expectNumeric(row.get(types.Numeric, 25), "-999999999.9999999");
const arr = try row.iterator(types.Numeric, 26).alloc(t.arena.allocator()); try t.expectEqual(2, arr.len); try t.expectEqual(1.1, arr[0].toFloat()); try t.expectDelta(-0.0034, arr[1].toFloat(), 0.00000001); }}
// char array encoding is a little special, so let's test variantstest "PG: char" { defer t.reset();
var c = try t.connect(.{}); defer c.deinit();
// read var row = (try c.rowUnsafe( \\ select $1::char[], $2::char[], $3::char[], $4::char[] , .{ &[_]u8{','}, &[_]u8{ ',', '"' }, &[_]u8{ '\\', 'a', ' ' }, &[_]u8{ 'z', '@' } })).?; defer row.deinit() catch {};
// used for our arrays const aa = t.arena.allocator();
try t.expectSlice(u8, &.{','}, try row.iterator(u8, 0).alloc(aa)); try t.expectSlice(u8, &.{ ',', '"' }, try row.iterator(u8, 1).alloc(aa)); try t.expectSlice(u8, &.{ '\\', 'a', ' ' }, try row.iterator(u8, 2).alloc(aa)); try t.expectSlice(u8, &.{ 'z', '@' }, try row.iterator(u8, 3).alloc(aa));}
test "PG: bind []const u8" { defer t.reset();
var c = try t.connect(.{}); defer c.deinit(); const value: []const u8 = "hello";
{ const result = c.exec("insert into all_types (id, col_text) values ($1, $2)", .{ 6, value }); if (result) |affected| { try t.expectEqual(1, affected); } else |err| { try t.fail(c, err); } }
var result = try c.query("select id, col_text from all_types where id = $1", .{6}); defer result.deinit();
const row = (try result.nextUnsafe()) orelse unreachable; try t.expectEqual(6, row.get(i32, 0)); try t.expectString("hello", row.get([]u8, 1));}
test "PG: bind []?i64" { defer t.reset();
var c = try t.connect(.{}); defer c.deinit(); const values = [_]?i64{ 1, null, 3 };
{ const result = c.exec("insert into all_types (id, col_int8_arr) values ($1, $2)", .{ 7, values }); if (result) |affected| { try t.expectEqual(1, affected); } else |err| { try t.fail(c, err); } }
var result = try c.query("select id, col_int8_arr from all_types where id = $1", .{7}); defer result.deinit();
const row = (try result.nextUnsafe()) orelse unreachable; try t.expectEqual(7, row.get(i32, 0)); { const arr = try row.iterator(?i64, 1).alloc(t.arena.allocator()); try t.expectEqual(3, arr.len); try t.expectEqual(1, arr[0]); try t.expectEqual(null, arr[1]); try t.expectEqual(3, arr[2]); }}
test "PG: bind []?f64" { defer t.reset();
var c = try t.connect(.{}); defer c.deinit(); const values = [_]?f64{ null, null, 0.2, null };
{ const result = c.exec("insert into all_types (id, col_float8_arr) values ($1, $2)", .{ 8, values }); if (result) |affected| { try t.expectEqual(1, affected); } else |err| { try t.fail(c, err); } }
var result = try c.query("select id, col_float8_arr from all_types where id = $1", .{8}); defer result.deinit();
const row = (try result.nextUnsafe()) orelse unreachable; try t.expectEqual(8, row.get(i32, 0)); { const arr = try row.iterator(?f64, 1).alloc(t.arena.allocator()); try t.expectEqual(4, arr.len); try t.expectEqual(null, arr[0]); try t.expectEqual(null, arr[1]); try t.expectEqual(0.2, arr[2]); try t.expectEqual(null, arr[3]); }}
test "PG: bind []?bool" { defer t.reset();
var c = try t.connect(.{}); defer c.deinit(); const values = [_]?bool{ null, true, false, null };
{ const result = c.exec("insert into all_types (id, col_bool_arr) values ($1, $2)", .{ 9, values }); if (result) |affected| { try t.expectEqual(1, affected); } else |err| { try t.fail(c, err); } }
var result = try c.query("select id, col_bool_arr from all_types where id = $1", .{9}); defer result.deinit();
const row = (try result.nextUnsafe()) orelse unreachable; try t.expectEqual(9, row.get(i32, 0)); { const arr = try row.iterator(?bool, 1).alloc(t.arena.allocator()); try t.expectEqual(4, arr.len); try t.expectEqual(null, arr[0]); try t.expectEqual(true, arr[1]); try t.expectEqual(false, arr[2]); try t.expectEqual(null, arr[3]); }}
test "PG: bind []?[]const u8" { defer t.reset();
var c = try t.connect(.{}); defer c.deinit(); const values = [_]?[]const u8{ "hello", null, null };
{ const result = c.exec("insert into all_types (id, col_text_arr) values ($1, $2)", .{ 10, values }); if (result) |affected| { try t.expectEqual(1, affected); } else |err| { try t.fail(c, err); } }
var result = try c.query("select id, col_text_arr from all_types where id = $1", .{10}); defer result.deinit();
const row = (try result.nextUnsafe()) orelse unreachable; try t.expectEqual(10, row.get(i32, 0)); { const arr = try row.iterator(?[]const u8, 1).alloc(t.arena.allocator()); try t.expectEqual(3, arr.len); try t.expectString("hello", arr[0].?); try t.expectEqual(null, arr[1]); try t.expectEqual(null, arr[2]); }}
test "PG: binary wrapper" { defer t.reset();
var c = try t.connect(.{}); defer c.deinit();
_ = try c.exec( \\ create extension if not exists postgis; \\ create table if not exists places ( \\ id int not null, \\ location geography not null \\ ); , .{});
const data = lib.Binary{ .data = &.{ 1, 1, 0, 0, 32, 230, 16, 0, 0, 43, 107, 238, 243, 22, 122, 82, 192, 60, 20, 204, 226, 238, 89, 68, 64 }, }; var row = (try c.rowUnsafe("select $1::geography", .{data})).?; defer row.deinit() catch {}; try t.expectString(data.data, row.get([]const u8, 0));}
test "PG: isUnique" { defer t.reset();
var c = try t.connect(.{}); defer c.deinit();
{ try t.expectError(error.PG, c.exec("insert into all_types (id, id) values ($1)", .{ 999, null })); try t.expectEqual(false, c.err.?.isUnique()); }
{ _ = try c.exec("insert into all_types (id) values ($1)", .{999}); _ = try t.expectError(error.PG, c.exec("insert into all_types (id) values ($1)", .{999})); try t.expectEqual(true, c.err.?.isUnique()); }
{ // can still use the connection after the error _ = try t.expectError(error.PG, c.row("insert into all_types (id) values ($1) returning id", .{999})); try t.expectEqual(true, c.err.?.isUnique()); }}
test "PG: large read" { var c = try t.connect(.{ .read_buffer = 500 }); defer c.deinit();
{ // want this to be larger than our read_buffer var rows = try c.query("select $1::text", .{"!" ** 1000}); defer rows.deinit();
const row = (try rows.nextUnsafe()).?; try t.expectString("!" ** 1000, row.get([]u8, 0)); try t.expectEqual(null, try rows.next()); }
{ // with a row var row = (try c.rowUnsafe("select $1::text", .{"z" ** 1000})).?; defer row.deinit() catch {}; try t.expectString("z" ** 1000, row.get([]u8, 0)); }}
test "Conn: dynamic buffer freed on error" { var c = try t.connect(.{ .read_buffer = 100 }); defer c.deinit();
var rows = try c.query("select $1::text", .{"!" ** 200}); defer rows.deinit();
const row = (try rows.nextUnsafe()).?; try t.expectString("!" ** 200, row.get([]u8, 0));
// we end here, simulating the app returning an error. This causes // rows.deinit() and c.deinit() to be called prematurely (from // the point of view of our internal state). Specifically, conn.reader.endFlow // isn't called.}
test "PG: Record" { var c = try t.connect(.{}); defer c.deinit();
{ var row = (try c.rowUnsafe("select row(9001, 'hello'::text)", .{})).?; defer row.deinit() catch {};
var record = row.record(0); try t.expectEqual(2, record.number_of_columns); try t.expectEqual(9001, record.next(i32)); try t.expectString("hello", record.next([]const u8)); }
{ var row = (try c.rowUnsafe("select row(null)", .{})).?; defer row.deinit() catch {};
var record = row.record(0); try t.expectEqual(1, record.number_of_columns); try t.expectEqual(null, record.next(?i32)); }}
test "Conn: application_name" { var conn = try Conn.open(t.io, t.allocator, .{}); defer conn.deinit(); try conn.auth(.{ .username = "pgz_user_clear", .password = "pgz_user_clear_pw", .database = "postgres", .application_name = "pg_zig_test", });
var row = (try conn.rowUnsafe("show application_name", .{})) orelse unreachable; defer row.deinit() catch {};
try t.expectString("pg_zig_test", row.get([]const u8, 0));}
test "PG: bind strictness" { var c = try t.connect(.{}); defer c.deinit(); try t.expectError(error.BindWrongType, c.row("select $1", .{100})); try t.expectError(error.BindWrongType, c.row("select $1", .{10.2})); try t.expectError(error.BindWrongType, c.row("select $1", .{true}));
try t.expectError(error.BindWrongType, c.row("select $1", .{@as(i32, 100)})); try t.expectError(error.BindWrongType, c.row("select $1", .{@as(f32, 10.2)}));
// conn is still usable try t.expectEqual(4, t.scalar(&c, "select 4"));}
test "PG: eager error" { var c = try t.connect(.{}); defer c.deinit();
{ // Some errors happen when the prepared statement is executed try t.expectError(error.PG, c.query("select * from invalid", .{})); try t.expectString("relation \"invalid\" does not exist", c.err.?.message); }
{ // some errors only happen when the result is read try c.begin(); defer c.rollback() catch {}; const sql = "create temp table test1 (id int) on commit drop"; _ = try c.exec(sql, .{}); try t.expectError(error.PG, c.query(sql, .{})); }}
// https://github.com/karlseguin/pg.zig/issues/44test "PG: eager error conn state" { var pool = try lib.Pool.init(t.io, t.allocator, .{ .size = 1, .auth = t.authOpts(.{}) }); defer pool.deinit();
{ var c = try pool.acquire(); defer c.release();
// duplicate it _ = try c.exec("insert into all_types (id) values ($1)", .{2000}); try t.expectError(error.PG, c.exec("insert into all_types (id) values ($1)", .{2000})); }
{ // only 1 connection in our pool, so the fact that the above fails and // this one succeeds, means we're properly handling the failure var c = try pool.acquire(); defer c.release(); _ = try c.exec("insert into all_types (id) values ($1)", .{2001}); }}
// https://github.com/karlseguin/pg.zig/issues/45test "PG: rollback during error" { var pool = try lib.Pool.init(t.io, t.allocator, .{ .size = 1, .auth = t.authOpts(.{}) }); defer pool.deinit();
_ = try pool.exec("truncate table all_types", .{});
{ var c = try pool.acquire(); defer c.release();
try c.begin(); // duplicate it _ = try c.exec("insert into all_types (id) values ($1)", .{3000}); try t.expectError(error.PG, c.exec("insert into all_types (id) values ($1)", .{3000})); try c.rollback(); }
{ // only 1 connection in our pool, so the fact that the above fails and // this one succeeds, means we're properly handling the failure var c = try pool.acquire(); defer c.release(); _ = try c.exec("insert into all_types (id) values ($1)", .{3001}); }
var result = try pool.query("select id from all_types order by id", .{}); defer result.deinit();
try t.expectEqual(3001, (try result.next()).?.get(i32, 0)); try t.expectEqual(null, (try result.next()));}
test "open URI" { const uri = try std.Uri.parse("postgresql://postgres:postgres@127.0.0.1:5432/postgres?tcp_user_timeout=5000"); var conn = try Conn.openAndAuthUri(t.io, t.allocator, uri); conn.deinit();}
test "Conn: TLS required" { { var conn = try Conn.open(t.io, t.allocator, .{ .tls = .off }); defer conn.deinit(); try t.expectError(error.PG, conn.auth(.{ .username = "pgz_user_ssl" })); try t.expectEqual(true, std.mem.find(u8, conn.err.?.message, "no encryption") != null); }
{ var conn = try t.connect(.{ .tls = Conn.Opts.TLS.require, .username = "pgz_user_ssl", .password = "pgz_user_ssl_pw" }); defer conn.deinit(); }}
test "Conn: TLS verify-full" { try t.expectError(error.SSLCertificationVerificationError, Conn.open(t.io, t.allocator, .{ .tls = .{ .verify_full = null } }));
{ var conn = try t.connect(.{ .tls = Conn.Opts.TLS{ .verify_full = "tests/root.crt" }, .username = "pgz_user_ssl", .password = "pgz_user_ssl_pw" }); defer conn.deinit(); }}
test "Conn: query is cancelable" { const S = struct { fn sleepQuery(c: *Conn) !void { var result = try c.query("select pg_sleep(3)", .{}); result.deinit(); } };
var conn = try t.connect(.{}); defer conn.deinit();
// Run the query concurrently, let it reach its blocking read, then cancel. var future = try t.io.concurrent(S.sleepQuery, .{&conn}); try t.io.sleep(.fromMilliseconds(50), .awake);
const start = std.Io.Clock.Timestamp.now(t.io, .awake); const result = future.cancel(t.io); const elapsed_ms = start.untilNow(t.io).raw.toMilliseconds();
try t.expectError(error.Canceled, result); try t.expectEqual(true, elapsed_ms < 1500); // prompt, not blocked until pg_sleep ends try t.expectEqual(Conn.State.fail, conn._state); try t.expectError(error.ConnectionBusy, conn.exec("select 1", .{}));}
test "PG: cached query" { var c = try t.connect(.{}); defer c.deinit();
{ var result = try c.queryOpts("select $1::int as id, $2::text as name", .{ 1, "leto" }, .{ .cache_name = "c1" }); try t.expectEqual(0, result.column_names.len); const row = (try result.nextUnsafe()) orelse unreachable; try t.expectEqual(1, row.get(i32, 0)); try t.expectString("leto", row.get([]u8, 1));
try t.expectEqual(null, try result.next()); result.deinit(); }
{ var result = try c.queryOpts("slc", .{ 2, "ghanima" }, .{ .cache_name = "c1" }); try t.expectEqual(0, result.column_names.len); const row = (try result.nextUnsafe()) orelse unreachable; try t.expectEqual(2, row.get(i32, 0)); try t.expectString("ghanima", row.get([]u8, 1));
try t.expectEqual(null, try result.next()); result.deinit(); }
try c.deallocate("c1");
{ try t.expectError(error.PG, c.queryOpts("slc", .{ 2, "ghanima" }, .{ .cache_name = "c1" })); try t.expectEqual(true, std.mem.find(u8, c.err.?.message, "syntax error at or near \"slc\"") != null); }}
test "PG: cached query with column names" { var c = try t.connect(.{}); defer c.deinit();
{ var result = try c.queryOpts("select $1::int as id, $2::text as name", .{ 1, "leto" }, .{ .cache_name = "c2", .column_names = true }); try t.expectEqual(2, result.column_names.len); try t.expectString("id", result.column_names[0]); try t.expectString("name", result.column_names[1]);
const row = (try result.nextUnsafe()) orelse unreachable; try t.expectEqual(1, row.get(i32, 0)); try t.expectString("leto", row.get([]u8, 1));
try t.expectEqual(null, try result.next()); result.deinit(); }
{ var result = try c.queryOpts("", .{ 2, "ghanima" }, .{ .cache_name = "c2", .column_names = true }); try t.expectEqual(2, result.column_names.len); try t.expectString("id", result.column_names[0]); try t.expectString("name", result.column_names[1]);
const row = (try result.nextUnsafe()) orelse unreachable; try t.expectEqual(2, row.get(i32, 0)); try t.expectString("ghanima", row.get([]u8, 1));
try t.expectEqual(null, try result.next()); result.deinit(); }}
fn expectNumeric(numeric: types.Numeric, expected: []const u8) !void { var str_buf: [50]u8 = undefined; try t.expectString(expected, try numeric.toString(&str_buf));
const a = try t.allocator.alloc(u8, numeric.estimatedStringLen()); defer t.allocator.free(a); try t.expectString(expected, try numeric.toString(a));
if (std.mem.eql(u8, expected, "nan")) { try t.expectEqual(true, std.math.isNan(numeric.toFloat())); } else if (std.mem.eql(u8, expected, "inf")) { try t.expectEqual(true, std.math.isInf(numeric.toFloat())); } else if (std.mem.eql(u8, expected, "-inf")) { try t.expectEqual(true, std.math.isNegativeInf(numeric.toFloat())); } else { try t.expectDelta(try std.fmt.parseFloat(f64, expected), numeric.toFloat(), 0.000001); }}
const DummyStruct = struct { id: i32, name: []const u8,};
const DummyEnum = enum { val1, val2,};