Standard library · Database

db/postgres

A PostgreSQL client written in Salam: connection strings and URIs, TLS, queries, transactions, pools and migrations.

import db.postgres

On this page

Tutorial: a bookshop in eleven steps
1. Connect
2. Connection strings, URIs and TLS
3. Create the tables
4. Insert rows and get the new ids
5. Read rows into your own structs
6. Values from users, safely
7. NULL values
8. Transactions
9. Errors
10. A pool, and one connection per thread
11. PostgreSQL features
More
Guide: schema migrations
Guide: settings from the environment
Guide: nested results as JSON
Guide: which function for which job
Types
Functions

This package talks to PostgreSQL directly: it implements the Postgres wire protocol in Salam itself, with password, MD5 and SCRAM-SHA-256 logins and optional TLS, so it needs no libpq or any other outside library. Every row of a result is fetched from the server at once, so RowCount is known before you read any of them. A Database also fits the shared Connection interface of db, which gives you engine neutral helpers, connection pools and migrations. This page starts with a tutorial; the full reference of every function and type comes after it.

Tutorial: a bookshop in eleven steps

The steps below build the database of a small bookshop: books, stock and orders. Each step is a complete program, and the output under it is what it printed against a real PostgreSQL 17. Run them in order, because each step uses the data the earlier ones left behind.

First start a server. With Docker that is one command; it creates the bookshop database and the postgres user with the password secret:

docker run -d --name bookshop-db -p 5432:5432 -e POSTGRES_PASSWORD=secret -e POSTGRES_DB=bookshop postgres:17-alpine

Without Docker, install PostgreSQL from your system's packages, then run createdb bookshop and give the postgres user that password. The programs connect to 127.0.0.1 on port 5432. A real application logs in with its own user rather than postgres, as step 2 shows.

1. Connect

Open takes a connection string of key=value pairs, the same format psql and libpq use. It always returns a Database, even when the login failed, so check Ok before anything else; Error then holds the server's message. Ping sends a tiny query to make sure the connection still works, which is a good health check for a long running service.

db-postgres-connect.salam

import db.postgres

func main:
    d := postgres.Open("host=127.0.0.1 port=5432 user=postgres password=secret dbname=bookshop")
    defer postgres.Close(d)
    if not postgres.Ok(d):
        print "could not connect: " + postgres.Error(d)
        ret 1
    end
    println "connected to", postgres.DatabaseName(d), "as", postgres.UserName(d)
    println "server", postgres.HostName(d) + ":" + postgres.Port(d), "version", postgres.ServerVersion(d)
    println postgres.QueryText(d, "SELECT split_part(version(), ' on ', 1)")
    println "alive:", postgres.Ping(d)

    bad := postgres.Open("host=127.0.0.1 user=postgres password=wrong dbname=bookshop")
    defer postgres.Close(bad)
    println "with a wrong password:", postgres.Ok(bad)
    print postgres.Error(bad)

    off := postgres.Open("host=127.0.0.1 port=5499 user=postgres password=secret connect_timeout=3")
    defer postgres.Close(off)
    println "with no server on the port:", postgres.Ok(off)
    print postgres.Error(off)
end

Outputconnected to bookshop as postgres server 127.0.0.1:5432 version 170011 PostgreSQL 17.11 alive: true with a wrong password: false FATAL: password authentication failed for user "postgres" with no server on the port: false connection to server at "127.0.0.1", port 5499 failed

The server's own messages keep their PostgreSQL wording and start with the severity: FATAL for a refused login, ERROR for a failed statement. An error message ends with a newline, which is why the examples print it with print rather than println. With nothing listening on the port, Open fails at once. connect_timeout, in seconds, limits how long the login waits for each answer from the server, 30 seconds when you leave it out.

2. Connection strings, URIs and TLS

The same settings can be written three ways: as key=value pairs, as a postgresql://user:password@host:port/dbname?key=value URI, or as the five arguments of OpenFull. This step also creates a login role for the shop, with a password that has a space and an @ in it, to show how such a value is written in each form.

db-postgres-options.salam

import db.postgres

func main:
    admin := postgres.OpenFull("127.0.0.1", 5432, "postgres", "secret", "bookshop")
    defer postgres.Close(admin)
    println "OpenFull:", postgres.Ok(admin)
    _ := postgres.Exec(admin, "DROP ROLE IF EXISTS clerk")
    _ := postgres.Exec(admin, "CREATE ROLE clerk LOGIN PASSWORD 'p@ss word'")

    a := postgres.Open("host=127.0.0.1 user=clerk password='p@ss word' dbname=bookshop")
    defer postgres.Close(a)
    println "quoted value:", postgres.Ok(a), postgres.UserName(a)

    b := postgres.Open("postgresql://clerk:p%40ss%20word@127.0.0.1:5432/bookshop?connect_timeout=5")
    defer postgres.Close(b)
    println "uri:", postgres.Ok(b), postgres.UserName(b), postgres.DatabaseName(b)

    c := postgres.Open("host=127.0.0.1 user=clerk password='p@ss word' dbname=bookshop sslmode=disable")
    defer postgres.Close(c)
    println "sslmode=disable:", postgres.Ok(c), postgres.QueryText(c, "SHOW ssl")

    t := postgres.Open("host=127.0.0.1 user=clerk password='p@ss word' dbname=bookshop sslmode=require")
    defer postgres.Close(t)
    println "sslmode=require:", postgres.Ok(t)
    print postgres.Error(t)
end

OutputOpenFull: true quoted value: true clerk uri: true clerk bookshop sslmode=disable: true off sslmode=require: false server does not support SSL, but SSL was required

In the key=value form, a value with spaces goes in single quotes. In a URI, characters with a meaning of their own are percent encoded: %40 for @ and %20 for a space. OpenFull joins its arguments without quoting them, so use it only for values without spaces.

The keys this package reads are host (or hostaddr), port, user, password, dbname, sslmode, sslrootcert and connect_timeout; other keys are ignored. sslmode works as in libpq: prefer (the default) uses TLS when the server offers it, require refuses to go on without it, verify-ca and verify-full also check the server's certificate (against the CA file in sslrootcert when you give one), and disable and allow never ask for TLS. Without one of the verify modes the certificate is not checked. The test server here has TLS turned off, so require fails. Unix domain sockets are not supported; connect over TCP, even on the same machine.

3. Create the tables

Exec runs SQL that returns no rows and gives back 0 on success and -1 on failure. It accepts several statements separated by semicolons, which suits a schema. Use the PostgreSQL types that fit the data: bigint GENERATED ALWAYS AS IDENTITY for ids, numeric for money, text[] for a list of tags and jsonb for free form details.

db-postgres-schema.salam

import db.postgres

func main:
    d := postgres.Open("host=127.0.0.1 user=postgres password=secret dbname=bookshop")
    defer postgres.Close(d)

    schema := `
        DROP TABLE IF EXISTS orders, books;
        CREATE TABLE books (
            id       bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
            title    text NOT NULL UNIQUE,
            author   text NOT NULL,
            year     int CHECK (year > 0),
            price    numeric(6, 2) NOT NULL CHECK (price >= 0),
            stock    int NOT NULL DEFAULT 0 CHECK (stock >= 0),
            isbn     text,
            tags     text[] NOT NULL DEFAULT '{}',
            info     jsonb NOT NULL DEFAULT '{}'
        );
        CREATE TABLE orders (
            id       bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
            customer text NOT NULL,
            book_id  bigint NOT NULL REFERENCES books (id),
            qty      int NOT NULL CHECK (qty > 0),
            total    numeric(8, 2) NOT NULL
        );`
    if postgres.Exec(d, schema) != 0:
        print postgres.Error(d)
        ret 1
    end

    r := postgres.Query(d, `SELECT table_name, column_name, data_type, is_nullable
        FROM information_schema.columns
        WHERE table_name IN ('books', 'orders') ORDER BY table_name, ordinal_position`)
    defer r.Close()
    until r.Next():
        println r.Text(0) + "." + r.Text(1), r.Text(2), "null:", r.Text(3)
    end
end

Outputbooks.id bigint null: NO books.title text null: NO books.author text null: NO books.year integer null: YES books.price numeric null: NO books.stock integer null: NO books.isbn text null: YES books.tags ARRAY null: NO books.info jsonb null: NO orders.id bigint null: NO orders.customer text null: NO orders.book_id bigint null: NO orders.qty integer null: NO orders.total numeric null: NO

Constraints such as UNIQUE, CHECK (stock >= 0) and REFERENCES let the database refuse bad data even when a bug in a program tries to write it. Step 9 shows what such a refusal looks like.

4. Insert rows and get the new ids

An INSERT ... RETURNING id is a query that returns the generated id, so run it with Query and read the one row. Text that comes from outside the program goes through Quote, which returns it as a quoted, escaped SQL literal: the apostrophe in the second title is doubled and cannot end the string early. ExecCount runs one statement and returns how many rows it inserted, updated or deleted.

db-postgres-insert.salam

import db.postgres
import str

func addBook(d: postgres.Database, title: str, author: str, year: int, price: str, stock: int): i64:
    sql := "INSERT INTO books (title, author, year, price, stock) VALUES (" + postgres.Quote(d, title) + ", " + postgres.Quote(d, author) + ", " + str.FromInt(year) + ", " + price + ", " + str.FromInt(stock) + ") RETURNING id"
    r := postgres.Query(d, sql)
    defer r.Close()
    if not r.Next():
        print postgres.Error(d)
        ret 0
    end
    ret r.Int64(0)
end

func main:
    d := postgres.Open("host=127.0.0.1 user=postgres password=secret dbname=bookshop")
    defer postgres.Close(d)

    println postgres.Quote(d, "The Hitchhiker's Guide to the Galaxy")
    println "Dune got id", addBook(d, "Dune", "Frank Herbert", 1965, "12.50", 4)
    println "Hitchhiker got id", addBook(d, "The Hitchhiker's Guide to the Galaxy", "Douglas Adams", 1979, "9.99", 2)

    n := postgres.ExecCount(d, `INSERT INTO books (title, author, year, price, stock, isbn, tags) VALUES
        ('Persuasion', 'Jane Austen', 1817, 7.25, 3, '978-0141439686', '{novel,classic}'),
        ('The Little Prince', 'Antoine de Saint-Exupéry', 1943, 6.00, 10, '978-0156012195', '{fable,classic}'),
        ('Neuromancer', 'William Gibson', 1984, 11.00, 1, NULL, '{scifi}')`)
    println "inserted", n, "more, last id", d.LastInsertId()

    r := postgres.Query(d, "UPDATE books SET tags = '{scifi,classic}' WHERE title = 'Dune' RETURNING id, title, tags")
    until r.Next():
        println "updated", r.Int(0), r.Text(1), r.Text(2)
    end
    r.Close()

    println "price rise on", postgres.ExecCount(d, "UPDATE books SET price = price + 1 WHERE year < 1900"), "old book"
end

Output'The Hitchhiker''s Guide to the Galaxy' Dune got id 1 Hitchhiker got id 2 inserted 3 more, last id 5 updated 1 Dune {scifi,classic} price rise on 1 old book

LastInsertId asks the server for lastval(), the last value any sequence or identity column produced in this session. After the three row insert that is the id of the last row; RETURNING is the clearer choice when you need every id. RETURNING also works on UPDATE and DELETE, as the tag change shows.

d.Changes() is the count of rows the last d.Exec(...) changed. It is only kept when you call the method on the value; the package function postgres.Exec(d, ...) works on a copy, so d.Changes() stays 0 after it. ExecCount avoids the question.

5. Read rows into your own structs

Query returns a Rows. Next moves to the next row and returns false when there is none, so until r.Next(): visits every row; inside the loop Text, Int, Int64 and Float read one column by its position, starting at 0. Wrap the loop in a function that fills a Vector of your own struct and the rest of the program never sees SQL rows.

db-postgres-structs.salam

import db
import db.postgres
import str

struct Book:
    pub id: i64 = 0
    pub title: str = ""
    pub author: str = ""
    pub year: int = 0
    pub cents: int = 0
    pub stock: int = 0
end

func money(cents: int): str:
    c := cents % 100
    if c < 10:
        ret str.FromInt(cents / 100) + ".0" + str.FromInt(c)
    end
    ret str.FromInt(cents / 100) + "." + str.FromInt(c)
end

func loadBooks(d: postgres.Database, where: str): Vector<Book>:
    mut out := Vector {} as Vector<Book>
    r := postgres.Query(d, "SELECT id, title, author, year, (price * 100)::int AS cents, stock FROM books " + where)
    defer r.Close()
    until r.Next():
        out.push(Book { id = r.Int64(0), title = r.Text(1), author = r.Text(2), year = r.Int(3), cents = r.Int(4), stock = r.Int(5) })
    end
    ret out
end

func main:
    d := postgres.Open("host=127.0.0.1 user=postgres password=secret dbname=bookshop")
    defer postgres.Close(d)

    books := loadBooks(d, "ORDER BY year")
    println books.len(), "books"
    each b in books:
        println b.id, b.title, "by", b.author, b.year, money(b.cents), "stock", b.stock
    end

    cheap := loadBooks(d, "WHERE price < 10 ORDER BY price")
    println "under 10:", cheap.len()

    r := postgres.Query(d, "SELECT title, price, stock, isbn FROM books WHERE id = 3")
    defer r.Close()
    r.Next()
    repeat r.ColumnCount() in i:
        kind := r.ColumnType(i)
        mut name := "text"
        if kind == db.TYPE_INT: name = "int" end
        if kind == db.TYPE_FLOAT: name = "float" end
        println r.ColumnName(i), "=", r.Text(i), "as", name
    end
end

Output5 books 3 Persuasion by Jane Austen 1817 8.25 stock 3 4 The Little Prince by Antoine de Saint-Exupéry 1943 6.00 stock 10 1 Dune by Frank Herbert 1965 12.50 stock 4 2 The Hitchhiker's Guide to the Galaxy by Douglas Adams 1979 9.99 stock 2 5 Neuromancer by William Gibson 1984 11.00 stock 1 under 10: 3 title = Persuasion as text price = 8.25 as float stock = 3 as int isbn = 978-0141439686 as text

The prices are read as whole cents ((price * 100)::int in the SQL) rather than with Float, because a binary float cannot hold most decimal amounts exactly. Text gives the exact numeric value as the server wrote it, such as 8.25, which is the other safe choice. ColumnName and ColumnType describe the columns: integers and booleans report db.TYPE_INT, real, double precision and numeric report db.TYPE_FLOAT, bytea reports db.TYPE_BLOB, and everything else, dates, arrays and jsonb included, is db.TYPE_TEXT. Always close a Rows when you are done; defer r.Close() right after the query is the easy way.

6. Values from users, safely

This package sends SQL as text and has no placeholders, so every value that comes from a user must go through Quote before it is joined into a statement. A quoted value can contain anything, quotes and semicolons included, and it stays one string. Numbers do not need quoting, but they must really be numbers: convert the input to an integer and check it.

db-postgres-safe.salam

import db.postgres
import conv
import str

func likePattern(s: str): str:
    ret "%" + str.Replace(str.Replace(str.Replace(s, "\\", "\\\\"), "%", "\\%"), "_", "\\_") + "%"
end

func search(d: postgres.Database, words: str): int:
    sql := "SELECT title FROM books WHERE title ILIKE " + postgres.Quote(d, likePattern(words)) + " ORDER BY title"
    r := postgres.Query(d, sql)
    defer r.Close()
    println "search", postgres.Quote(d, words), "found", r.RowCount()
    until r.Next():
        println "  " + r.Text(0)
    end
    ret r.RowCount()
end

func main:
    d := postgres.Open("host=127.0.0.1 user=postgres password=secret dbname=bookshop")
    defer postgres.Close(d)

    _ := search(d, "the")
    _ := search(d, "x' OR '1'='1")
    _ := search(d, "'; DROP TABLE books; --")
    _ := search(d, "100%")
    println "books still there:", postgres.QueryInt(d, "SELECT count(*) FROM books")

    typed := "3 OR 1=1"
    id := conv.ParseInt(typed)
    if conv.FormatInt(id) != typed:
        println "not a book id:", typed
    end
    println postgres.Quote(d, `C:\books\new`)
end

Outputsearch 'the' found 2 The Hitchhiker's Guide to the Galaxy The Little Prince search 'x'' OR ''1''=''1' found 0 search '''; DROP TABLE books; --' found 0 search '100%' found 0 books still there: 5 not a book id: 3 OR 1=1 E'C:\\books\\new'

Both attacks only searched for odd titles, and the table is still there. Inside a LIKE or ILIKE pattern, % and _ are wildcards, so the search escapes them with a backslash first; otherwise a search for 100% would match every title that starts with 100. When the text has a backslash, Quote writes it in PostgreSQL's E'...' form with a leading space, which is still a single literal.

Never join raw input into a table or column name. If the user picks a sort column, compare their choice with a fixed list of names in your program and use your own copy of the name.

7. NULL values

A column can hold no value at all: SQL's NULL. Text returns an empty string for it and Int returns 0, which cannot be told apart from a real empty string or a real 0, so call IsNull whenever the difference matters. To write a NULL, put the word NULL into the SQL instead of a quoted value; a small helper keeps that tidy.

db-postgres-null.salam

import db.postgres

func orNull(d: postgres.Database, s: str): str:
    if s == "":
        ret "NULL"
    end
    ret postgres.Quote(d, s)
end

func main:
    d := postgres.Open("host=127.0.0.1 user=postgres password=secret dbname=bookshop")
    defer postgres.Close(d)

    _ := postgres.Exec(d, "UPDATE books SET isbn = " + orNull(d, "978-0441172719") + " WHERE title = 'Dune'")
    _ := postgres.Exec(d, "UPDATE books SET isbn = " + orNull(d, "") + " WHERE title LIKE 'The Hitch%'")

    r := postgres.Query(d, "SELECT title, isbn FROM books ORDER BY id")
    defer r.Close()
    until r.Next():
        if r.IsNull(1):
            println r.Text(0), "has no ISBN, Text gives", "[" + r.Text(1) + "]"
        else:
            println r.Text(0), r.Text(1)
        end
    end

    println "missing:", postgres.QueryInt(d, "SELECT count(*) FROM books WHERE isbn IS NULL")
    println "= NULL matches", postgres.QueryInt(d, "SELECT count(*) FROM books WHERE isbn = NULL")
    newest := postgres.Query(d, "SELECT max(year) FROM books WHERE year > 2100")
    defer newest.Close()
    newest.Next()
    println "max over no rows:", newest.Int(0), newest.IsNull(0)
    println postgres.QueryText(d, "SELECT string_agg(coalesce(isbn, 'none'), ', ' ORDER BY id) FROM books")
end

OutputDune 978-0441172719 The Hitchhiker's Guide to the Galaxy has no ISBN, Text gives [] Persuasion 978-0141439686 The Little Prince 978-0156012195 Neuromancer has no ISBN, Text gives [] missing: 2 = NULL matches 0 max over no rows: 0 true 978-0441172719, none, 978-0141439686, 978-0156012195, none

Compare with IS NULL, never = NULL, which is never true. Aggregates such as max and sum over no rows return NULL, not 0; coalesce(x, fallback) replaces a NULL in the SQL itself.

8. Transactions

An order changes two tables: the stock of the book goes down and a row is added to orders. Either both happen or neither. Begin starts a transaction and returns a Tx whose Exec and Query run inside it; Commit keeps the changes and Rollback throws them away.

db-postgres-tx.salam

import db.postgres
import str

func placeOrder(d: postgres.Database, customer: str, book: int, qty: int): bool:
    tx := d.Begin()
    if not tx.Ok():
        ret false
    end
    r := tx.Query("UPDATE books SET stock = stock - " + str.FromInt(qty) + " WHERE id = " + str.FromInt(book) + " AND stock >= " + str.FromInt(qty) + " RETURNING price * " + str.FromInt(qty))
    defer r.Close()
    if not r.Next():
        tx.Rollback()
        println customer, "wants", qty, "of book " + str.FromInt(book) + ": not enough stock, rolled back"
        ret false
    end
    total := r.Text(0)
    if tx.Exec("INSERT INTO orders (customer, book_id, qty, total) VALUES (" + postgres.Quote(d, customer) + ", " + str.FromInt(book) + ", " + str.FromInt(qty) + ", " + total + ")") != 0:
        tx.Rollback()
        ret false
    end
    if tx.Commit() != 0:
        ret false
    end
    println customer, "bought", qty, "of book", book, "for", total
    ret true
end

func main:
    d := postgres.Open("host=127.0.0.1 user=postgres password=secret dbname=bookshop")
    defer postgres.Close(d)

    _ := placeOrder(d, "Sara", 1, 2)
    _ := placeOrder(d, "Omid", 5, 1)
    _ := placeOrder(d, "Lena", 5, 1)
    _ := placeOrder(d, "Sara", 4, 3)

    println "orders:", postgres.QueryInt(d, "SELECT count(*) FROM orders"), "takings:", postgres.QueryText(d, "SELECT sum(total) FROM orders")
    println "Dune left:", postgres.QueryInt(d, "SELECT stock FROM books WHERE id = 1"), "Neuromancer left:", postgres.QueryInt(d, "SELECT stock FROM books WHERE id = 5")
end

OutputSara bought 2 of book 1 for 25.00 Omid bought 1 of book 5 for 11.00 Lena wants 1 of book 5: not enough stock, rolled back Sara bought 3 of book 4 for 18.00 orders: 3 takings: 54.00 Dune left: 2 Neuromancer left: 0

UPDATE ... WHERE stock >= qty RETURNING ... checks and takes the stock in one statement, so two customers ordering the last copy at the same time cannot both get it: the second one waits for the first transaction to finish and then finds the stock is 0.

Check every statement inside a transaction. After a failed statement PostgreSQL ignores the rest of the transaction, and a later Commit rolls back instead. Commit then returns -1, so a 0 from it means your changes really were saved.

9. Errors

Every call reports failure the same way. Exec returns -1, ExecCount returns -1, and Query returns a Rows whose Ok is false and whose Next returns false at once. Error then has the server's message, with the DETAIL line when there is one; it is cleared when the next statement starts.

db-postgres-errors.salam

import db.postgres
import str

func attempt(d: postgres.Database, sql: str):
    if postgres.Exec(d, sql) != 0:
        print postgres.Error(d)
    end
end

func main:
    d := postgres.Open("host=127.0.0.1 user=postgres password=secret dbname=bookshop")
    defer postgres.Close(d)

    attempt(d, "INSERT INTO books (title, author, price) VALUES ('Dune', 'Someone', 5)")
    attempt(d, "UPDATE books SET stock = stock - 50 WHERE id = 1")
    attempt(d, "INSERT INTO orders (customer, book_id, qty, total) VALUES ('Omid', 99, 1, 1)")
    attempt(d, "SELEC title FROM books")

    r := postgres.Query(d, "SELECT nope FROM books")
    println "query ok:", r.Ok(), "rows:", r.RowCount(), "next:", r.Next()
    println "ExecCount on error:", postgres.ExecCount(d, "DELETE FROM nowhere")
    print postgres.Error(d)

    tx := d.Begin()
    _ := tx.Exec("UPDATE books SET price = 0 WHERE id = 1")
    _ := tx.Exec("INSERT INTO books (title) VALUES ('Untitled')")
    print postgres.Error(d)
    _ := tx.Exec("UPDATE books SET price = 1 WHERE id = 2")
    print postgres.Error(d)
    tx.Rollback()
    println "Dune still costs", postgres.QueryText(d, "SELECT price FROM books WHERE id = 1")

    killer := postgres.Open("host=127.0.0.1 user=postgres password=secret dbname=bookshop")
    defer postgres.Close(killer)
    pid := postgres.QueryInt(d, "SELECT pg_backend_pid()")
    _ := postgres.Exec(killer, "SELECT pg_terminate_backend(" + str.FromInt(pid) + ")")
    println "after the server dropped us, ping:", postgres.Ping(d)
    postgres.Reset(d)
    println "after Reset, ping:", postgres.Ping(d), postgres.QueryInt(d, "SELECT count(*) FROM books")
end

OutputERROR: duplicate key value violates unique constraint "books_title_key" DETAIL: Key (title)=(Dune) already exists. ERROR: new row for relation "books" violates check constraint "books_stock_check" DETAIL: Failing row contains (1, Dune, Frank Herbert, 1965, 12.50, -48, 978-0441172719, {scifi,classic}, {}). ERROR: insert or update on table "orders" violates foreign key constraint "orders_book_id_fkey" DETAIL: Key (book_id)=(99) is not present in table "books". ERROR: syntax error at or near "SELEC" query ok: false rows: 0 next: false ExecCount on error: -1 ERROR: relation "nowhere" does not exist ERROR: null value in column "author" of relation "books" violates not-null constraint DETAIL: Failing row contains (7, Untitled, null, null, null, 0, null, {}, {}). ERROR: current transaction is aborted, commands ignored until end of transaction block Dune still costs 12.50 after the server dropped us, ping: false after Reset, ping: true 5

The second part shows the transaction rule from step 8 in action: after the failed insert, the next statement is refused with current transaction is aborted until Rollback. The last part shows a dropped connection, here cut by the server with pg_terminate_backend, as also happens when the server restarts. Ping notices it, and Reset connects again with the same settings.

A Query must return rows. An UPDATE or DELETE without RETURNING run through Query does its work, but the Rows it gives back is not Ok; use Exec or ExecCount for those.

10. A pool, and one connection per thread

A connection handles one statement at a time. A server that answers many requests keeps a few connections open and lends one to each request. db.Pool from the db package does the bookkeeping: Add puts connections in, Acquire lends one out as a lease number, Conn gives the connection for that lease and Release returns it. postgres.Conn turns a Database into the shared Connection the pool stores.

db-postgres-pool.salam

import db
import db.postgres
import mem
import thread

const DSN := "host=127.0.0.1 user=postgres password=secret dbname=bookshop"

struct Job:
    pub author: str = ""
    pub sold: int = 0
end

func countSold(j: Job*):
    d := postgres.Open(DSN)
    defer postgres.Close(d)
    j[0].sold = postgres.QueryInt(d, "SELECT coalesce(sum(o.qty), 0) FROM orders o JOIN books b ON b.id = o.book_id WHERE b.author = " + postgres.Quote(d, j[0].author))
end

func main:
    mut pool := db.NewPool(3)
    defer db.Free(pool)
    repeat 3:
        _ := db.Add(pool, postgres.Conn(postgres.Open(DSN)))
    end
    println "pool:", db.Size(pool), "connections,", db.Idle(pool), "idle"

    a := db.Acquire(pool)
    b := db.Acquire(pool)
    println "two requests in flight:", db.InUse(pool), "in use"
    println "books:", db.QueryInt(db.Conn(pool, a), "SELECT count(*) FROM books"), "orders:", db.QueryInt(db.Conn(pool, b), "SELECT count(*) FROM orders")
    db.Release(pool, a)
    db.Release(pool, b)

    mut jobs := Vector {} as Vector<Job*>
    defer jobs.free()
    mut group := Vector {} as Vector<thread.Thread>
    defer group.free()
    each name in ["Frank Herbert", "William Gibson", "Antoine de Saint-Exupéry", "Jane Austen"]:
        j := mem.Allocate(sizeof(Job)) as Job*
        j[0] = Job { author = name }
        jobs.push(j)
        group = thread.Collect(group, spawn(countSold, j))
    end
    thread.JoinAll(group)
    each j in jobs:
        println j[0].author, "sold", j[0].sold
        mem.Free(j as void*)
    end
end

Outputpool: 3 connections, 3 idle two requests in flight: 2 in use books: 5 orders: 3 Frank Herbert sold 2 William Gibson sold 1 Antoine de Saint-Exupéry sold 3 Jane Austen sold 0

The pool itself is not locked, so lease and release from one thread. For work spread over threads, the simplest safe rule is the one the second half follows: every thread opens its own connection, uses it and closes it. Never use one Database from two threads at the same time.

11. PostgreSQL features

Everything PostgreSQL can do is one SQL string away. This step uses a few features that save a lot of program code: ON CONFLICT ... DO UPDATE inserts a row or updates the one that is already there, jsonb operators read and search inside JSON documents, unnest turns an array column into rows, full text search finds words in any form, and a window function ranks the authors by takings.

db-postgres-features.salam

import db.postgres

func show(d: postgres.Database, title: str, sql: str):
    r := postgres.Query(d, sql)
    defer r.Close()
    println title
    until r.Next():
        mut line := " "
        repeat r.ColumnCount() in i:
            line = line + " " + r.Text(i)
        end
        println line
    end
end

func main:
    d := postgres.Open("host=127.0.0.1 user=postgres password=secret dbname=bookshop")
    defer postgres.Close(d)

    _ := postgres.Exec(d, `INSERT INTO books (title, author, year, price, stock) VALUES
        ('Dune', 'Frank Herbert', 1965, 12.50, 5),
        ('Foundation', 'Isaac Asimov', 1951, 8.75, 4)
        ON CONFLICT (title) DO UPDATE SET stock = books.stock + EXCLUDED.stock`)
    show(d, "restock, insert or add:", "SELECT title, stock FROM books WHERE title IN ('Dune', 'Foundation') ORDER BY title")

    _ := postgres.Exec(d, `UPDATE books SET info = '{"pages": 412, "series": "Dune Chronicles", "awards": ["Hugo", "Nebula"]}' WHERE title = 'Dune';
        UPDATE books SET info = '{"pages": 255, "series": "Foundation"}', tags = '{scifi,classic}' WHERE title = 'Foundation'`)
    show(d, "jsonb fields:", "SELECT title, info->>'series', (info->>'pages')::int FROM books WHERE info ? 'series' ORDER BY title")
    show(d, "won a Hugo:", `SELECT title FROM books WHERE info->'awards' @> '["Hugo"]'`)

    show(d, "arrays:", "SELECT tag, count(*) FROM books, unnest(tags) AS tag GROUP BY tag ORDER BY count(*) DESC, tag")
    show(d, "full text:", "SELECT title FROM books WHERE to_tsvector('english', title) @@ plainto_tsquery('english', 'guides galaxy')")
    show(d, "best sellers:", `SELECT b.author, sum(o.total), rank() OVER (ORDER BY sum(o.total) DESC)
        FROM orders o JOIN books b ON b.id = o.book_id GROUP BY b.author`)
end

Outputrestock, insert or add: Dune 7 Foundation 4 jsonb fields: Dune Dune Chronicles 412 Foundation Foundation 255 won a Hugo: Dune arrays: classic 4 scifi 3 fable 1 novel 1 full text: The Hitchhiker's Guide to the Galaxy best sellers: Frank Herbert 25.00 1 Antoine de Saint-Exupéry 18.00 2 William Gibson 11.00 3

Every value arrives as text in PostgreSQL's own format: arrays as {scifi,classic}, jsonb as JSON text and booleans as t or f (Int reads those as 1 and 0). When a result has structure, let the SQL build JSON and decode it in Salam, as the JSON guide below shows.

Bulk loading: COPY FROM STDIN is not supported. Insert many rows with one multi row INSERT per few hundred rows, inside a transaction.
QueryInt and QueryText read the first column of the first row in one call, for counts, sums and settings.
Code written against db.Connection runs unchanged on SQLite and MySQL; db.QueryInt, db.RunAll and db.InTx work on all of them.
ServerVersion returns the major version times 10000 plus the minor version, so 170011 is 17.11; compare it with a number to use features of newer servers.
Results are held in memory in full. For a very large table, read it in pages with ORDER BY id LIMIT 1000 and WHERE id > last.

Guide: schema migrations

A real application changes its schema over time, and every copy of the database has to follow. db.Migrator from the db package keeps numbered steps, each with SQL to apply it and SQL to undo it, and records the applied ones in a schema_migrations table. Each step runs in its own transaction, which in PostgreSQL covers CREATE TABLE and CREATE INDEX too, so a failed step leaves no half made change behind.

db-postgres-guide-migrate.salam

import db
import db.postgres

func main:
    d := postgres.Open("host=127.0.0.1 user=postgres password=secret dbname=bookshop")
    defer postgres.Close(d)
    c := postgres.Conn(d)

    mut m := db.NewMigrator()
    defer db.FreeMigrator(m)
    db.AddMigration(m, 1, "reviews", `CREATE TABLE reviews (
            id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
            book_id bigint NOT NULL REFERENCES books (id),
            stars int NOT NULL CHECK (stars BETWEEN 1 AND 5),
            body text)`, "DROP TABLE reviews")
    db.AddMigration(m, 2, "orders by customer", "CREATE INDEX orders_customer ON orders (customer)", "DROP INDEX orders_customer")

    println "schema version", db.SchemaVersion(m, c), "pending", db.PendingCount(m, c)
    println "applied", db.MigrateUp(m, c)
    println "schema version", db.SchemaVersion(m, c), "pending", db.PendingCount(m, c)
    println "applied", db.MigrateUp(m, c), "on a second run"

    println "rollback:", db.Rollback(m, c), "now at", db.SchemaVersion(m, c)
    println "index exists:", postgres.QueryInt(d, "SELECT count(*) FROM pg_indexes WHERE indexname = 'orders_customer'")
    println "migrate to 2:", db.MigrateTo(m, c, 2)
    println postgres.QueryText(d, "SELECT string_agg(version || ' ' || name, ', ' ORDER BY version) FROM schema_migrations")
end

Outputschema version 0 pending 2 applied 2 schema version 2 pending 0 applied 0 on a second run rollback: true now at 1 index exists: 0 migrate to 2: 1 1 reviews, 2 orders by customer

Run MigrateUp when the program starts: it applies only the steps the database has not seen, so running it twice is safe. Rollback undoes the newest step and MigrateTo moves to a chosen version.

Guide: settings from the environment

Passwords do not belong in source code. Like libpq, Open starts from the environment variables PGHOST, PGPORT, PGUSER, PGPASSWORD, PGDATABASE, PGSSLMODE and PGSSLROOTCERT, then applies whatever the connection string sets. So Open("") connects with the environment alone, and the program below works with either a single DATABASE_URL or the PG... variables.

db-postgres-guide-env.salam

import db.postgres
import os

func main:
    d := postgres.Open(os.Env("DATABASE_URL"))
    defer postgres.Close(d)
    if not postgres.Ok(d):
        print "database: " + postgres.Error(d)
        ret 1
    end
    println "database", postgres.DatabaseName(d), "on", postgres.HostName(d), "as", postgres.UserName(d)
    println postgres.QueryInt(d, "SELECT count(*) FROM books"), "books"
end

Outputdatabase bookshop on 127.0.0.1 as postgres 6 books

The output above came from running it with the PG... variables set:

PGHOST=127.0.0.1 PGUSER=postgres PGPASSWORD=secret PGDATABASE=bookshop salam run app.salam
DATABASE_URL=postgresql://postgres:secret@127.0.0.1:5432/bookshop salam run app.salam

When no user is given anywhere, the USER environment variable is used, and when no database is given, the database with the same name as the user.

Guide: nested results as JSON

A list of books, each with its orders, would take one query per book, or one join whose rows you regroup by hand. PostgreSQL can build the whole nested document in one query with json_agg and json_build_object, and encoding/json reads it straight into Salam structs whose field names match the JSON keys.

db-postgres-guide-json.salam

import db.postgres
import encoding.json
import encoding.encerr

struct Order:
    pub customer: str = ""
    pub qty: int = 0
end

struct Book:
    pub title: str = ""
    pub price: f64 = 0.0
    pub orders: Vector<Order> = Vector {}
end

struct Shelf:
    pub books: Vector<Book> = Vector {}
end

func main:
    d := postgres.Open("host=127.0.0.1 user=postgres password=secret dbname=bookshop")
    defer postgres.Close(d)

    text := postgres.QueryText(d, `SELECT json_build_object('books', coalesce(json_agg(b ORDER BY b.title), '[]'))
        FROM (SELECT bk.title, bk.price,
                     coalesce((SELECT json_agg(json_build_object('customer', o.customer, 'qty', o.qty) ORDER BY o.id)
                               FROM orders o WHERE o.book_id = bk.id), '[]') AS orders
              FROM books bk WHERE bk.id IN (SELECT book_id FROM orders)) b`)

    mut shelf := Shelf {}
    mut e := encerr.NoError()
    if not json.UnmarshalLenient(text, shelf, e):
        println "bad json:", e.message
        ret 1
    end
    each b in shelf.books:
        println b.title, b.price, "orders:", b.orders.len()
        each o in b.orders:
            println "  " + o.customer, "x", o.qty
        end
    end
end

OutputDune 12.5 orders: 1 Sara x 2 Neuromancer 11 orders: 1 Omid x 1 The Little Prince 6 orders: 1 Sara x 3

coalesce(..., '[]') turns "no orders" into an empty list instead of NULL. The same pattern works the other way: send a JSON document as one quoted string and unpack it on the server with jsonb_to_recordset.

Guide: which function for which job

You want to	Use
Connect	`Open` with `key=value` pairs or a `postgresql://` URI, then `Ok`
Run DDL, or a write whose count you do not need	`Exec` (0 is success)
Know how many rows a write changed	`ExecCount`
Get generated ids or changed rows back	`Query` with `INSERT/UPDATE/DELETE ... RETURNING`
Read many rows	`Query`, then `until r.Next():` and `r.Close()`
Read one number or one string	`QueryInt`, `QueryText`
Put user text into SQL	`Quote`, always
Tell NULL from empty	`r.IsNull(col)`
Change several things at once or not at all	`d.Begin()`, then `Commit` or `Rollback`
Recover after the server dropped the connection	`Ping`, then `Reset`
Write code that works on several engines	`Conn` and the db helpers

Types

`Database`

struct Database

One PostgreSQL connection.

Name	Type	Description
`handle`	`void*`	The connection handle; null when there is none.
`affected`	`i64 = 0`	The number of rows changed by the last statement.

db-postgres-Client.salam

import db
import db.postgres

func main:
    if not postgres.Available():
        println "the Postgres client is not available"
        ret
    end
    println postgres.ClientVersion()
    d := postgres.Open("host=127.0.0.1 port=5432 dbname=shop user=app password=s3cret")
    if not postgres.Ok(d):
        println postgres.Error(d)
        ret
    end
    println postgres.ServerVersion(d), postgres.DatabaseName(d), postgres.UserName(d), postgres.HostName(d), postgres.Port(d)
    println postgres.Ping(d)
    _ := postgres.Exec(d, "CREATE TABLE IF NOT EXISTS items (id SERIAL PRIMARY KEY, name TEXT, price NUMERIC)")
    _ := postgres.Exec(d, "INSERT INTO items (name, price) VALUES (" + postgres.Quote(d, "tea") + ", 12.5)")
    println postgres.ExecCount(d, "UPDATE items SET price = price * 1.1")

    rows := postgres.Query(d, "SELECT id, name, price FROM items ORDER BY id")
    println rows.Ok(), rows.ColumnCount(), rows.RowCount(), rows.ColumnName(1)
    until rows.Next():
        println rows.Int(0), rows.Text(1), rows.Float(2), rows.Int64(0), rows.IsNull(2), rows.ColumnType(1)
    end
    _ := rows.Close()
    println postgres.QueryInt(d, "SELECT COUNT(*) FROM items"), postgres.QueryText(d, "SELECT version()")

    c := postgres.Conn(d)
    tx := c.Begin()
    _ := tx.Exec("DELETE FROM items")
    _ := tx.Rollback()
    println db.QueryInt(c, "SELECT COUNT(*) FROM items"), d.Kind()
    postgres.Reset(d)
    _ := postgres.Close(d)
    _ := postgres.OpenFull("db.example.com", 5432, "app", "s3cret", "shop")
end

`Kind`

func Kind(): str

The name of the database engine.

The example for this item is shown with the ones above: db-postgres-Client.salam

`Ok`

func Ok(): bool

The connection is up.

The example for this item is shown with the ones above: db-postgres-Client.salam

`Ping`

func Ping(): bool

The connection is alive.

The example for this item is shown with the ones above: db-postgres-Client.salam

`Exec`

func Exec(sql: str): int

Runs SQL that returns no rows, one or several statements separated by semicolons; 0 means success and -1 failure. The method form keeps the changed row count for Changes.

The example for this item is shown with the ones above: db-postgres-Client.salam

`Query`

func Query(sql: str): dyn db.Rows

Runs a query and returns the rows; Ok on the result is false when it failed or returned no rows set.

The example for this item is shown with the ones above: db-postgres-Client.salam

`Begin`

func Begin(): dyn db.Tx

Starts a transaction with BEGIN; the transaction is not Ok when that failed.

The example for this item is shown with the ones above: db-postgres-Client.salam

`LastInsertId`

func LastInsertId(): i64

The last value a sequence or identity column produced in this session (SELECT lastval()); 0 when there is none.

The example for this item is shown with the ones above: db-postgres-Client.salam

`Changes`

func Changes(): i64

The number of rows the last Exec called on this value changed.

The example for this item is shown with the ones above: db-postgres-Client.salam

`Error`

func Error(): str

The last error message from the server or the connection, ending in a newline; empty after a statement that worked.

The example for this item is shown with the ones above: db-postgres-Client.salam

`Quote`

func Quote(s: str): str

Returns the string as a quoted, escaped SQL literal, safe to join into a statement; with a backslash in it, in the E'...' form.

The example for this item is shown with the ones above: db-postgres-Client.salam

`Close`

func Close(): int

Closes and frees it.

The example for this item is shown with the ones above: db-postgres-Client.salam

`Rows`

struct Rows

The result of one query.

Name	Type	Description
`handle`	`void*`	The result handle; null when the query failed.
`row`	`int = -1`	The index of the current row; -1 before the first `Next`.
`count`	`int = 0`	The number of rows.

The example for this item is shown with the ones above: db-postgres-Client.salam

`Ok`

func Ok(): bool

The query worked and returned rows.

The example for this item is shown with the ones above: db-postgres-Client.salam

`Next`

func Next(): bool

Moves to the next row; false when no row is left.

The example for this item is shown with the ones above: db-postgres-Client.salam

`ColumnCount`

func ColumnCount(): int

The number of columns.

The example for this item is shown with the ones above: db-postgres-Client.salam

`RowCount`

func RowCount(): int

The number of rows in the result.

The example for this item is shown with the ones above: db-postgres-Client.salam

`ColumnName`

func ColumnName(col: int): str

The column name.

The example for this item is shown with the ones above: db-postgres-Client.salam

`ColumnType`

func ColumnType(col: int): int

The type of the column value in the current row, one of the TYPE_... constants in db: integers and booleans are TYPE_INT, real, double precision and numeric are TYPE_FLOAT, bytea is TYPE_BLOB, NULL is TYPE_NULL and the rest is TYPE_TEXT.

The example for this item is shown with the ones above: db-postgres-Client.salam

`IsNull`

func IsNull(col: int): bool

The column value is null.

The example for this item is shown with the ones above: db-postgres-Client.salam

`Text`

func Text(col: int): str

The column value as text, in PostgreSQL's own format; empty for NULL.

The example for this item is shown with the ones above: db-postgres-Client.salam

`Int`

func Int(col: int): int

The column value as an integer; t and f read as 1 and 0, NULL as 0.

The example for this item is shown with the ones above: db-postgres-Client.salam

`Int64`

func Int64(col: int): i64

The column value as a 64 bit integer; t and f read as 1 and 0, NULL as 0.

The example for this item is shown with the ones above: db-postgres-Client.salam

`Float`

func Float(col: int): f64

The column value as a float; 0 for NULL.

The example for this item is shown with the ones above: db-postgres-Client.salam

`Close`

func Close(): int

Closes and frees it.

The example for this item is shown with the ones above: db-postgres-Client.salam

`Txn`

struct Txn

One transaction.

Name	Type	Description
`handle`	`void*`	The connection pointer.

The example for this item is shown with the ones above: db-postgres-Client.salam

`Ok`

func Ok(): bool

BEGIN worked.

The example for this item is shown with the ones above: db-postgres-Client.salam

`Exec`

func Exec(sql: str): int

Runs one statement; zero means success.

The example for this item is shown with the ones above: db-postgres-Client.salam

`Query`

func Query(sql: str): dyn db.Rows

Runs a query and returns the rows.

The example for this item is shown with the ones above: db-postgres-Client.salam

`Commit`

func Commit(): int

Commits the transaction and returns 0 when the changes were saved. It returns -1 when the commit failed, or when PostgreSQL rolled back instead because a statement inside the transaction had failed.

The example for this item is shown with the ones above: db-postgres-Client.salam

`Rollback`

func Rollback(): int

Rolls the transaction back.

The example for this item is shown with the ones above: db-postgres-Client.salam

Functions

`Available`

func Available(): bool

The Postgres client is available; since it is inside Salam itself, this is always true.

The example for this item is shown with the ones above: db-postgres-Client.salam

`ClientVersion`

func ClientVersion(): int

The protocol version this client matches, as a number: always 180000.

The example for this item is shown with the ones above: db-postgres-Client.salam

`ServerVersion`

func ServerVersion(d: Database): int

The server version as major times 10000 plus minor, such as 170011 for 17.11; 0 without a connection.

The example for this item is shown with the ones above: db-postgres-Client.salam

`Open`

func Open(conninfo: str): Database

Connects with a connection string, such as host=... port=... user=... password=... dbname=..., or a postgresql:// URI. Unset values come from PGHOST, PGPORT, PGUSER, PGPASSWORD, PGDATABASE and PGSSLMODE. It always returns a Database; check Ok.

The example for this item is shown with the ones above: db-postgres-Client.salam

`OpenFull`

func OpenFull(host: str, port: int, user: str, pass: str, dbname: str): Database

Connects with a host, port, user, password and database name. The values are not quoted, so use Open for a value with spaces.

The example for this item is shown with the ones above: db-postgres-Client.salam

`Ok`

func Ok(d: Database): bool

The connection is up.

The example for this item is shown with the ones above: db-postgres-Client.salam

`Ping`

func Ping(d: Database): bool

The connection is alive.

The example for this item is shown with the ones above: db-postgres-Client.salam

`Reset`

func Reset(d: Database)

Closes the connection and opens it again with the same settings, for example after the server dropped it.

The example for this item is shown with the ones above: db-postgres-Client.salam

`Close`

func Close(d: Database): int

Closes it.

The example for this item is shown with the ones above: db-postgres-Client.salam

`Error`

func Error(d: Database): str

The last error message.

The example for this item is shown with the ones above: db-postgres-Client.salam

`Exec`

func Exec(d: Database, sql: str): int

Runs SQL that returns no rows; 0 means success and -1 failure.

The example for this item is shown with the ones above: db-postgres-Client.salam

`ExecCount`

func ExecCount(d: Database, sql: str): i64

Runs one statement and returns the number of rows it inserted, updated or deleted; -1 on failure.

The example for this item is shown with the ones above: db-postgres-Client.salam

`Query`

func Query(d: Database, sql: str): Rows

Runs a query and returns its rows; Ok on the result is false when it failed or returned no rows set.

The example for this item is shown with the ones above: db-postgres-Client.salam

`Quote`

func Quote(d: Database, s: str): str

Returns the string as a quoted, escaped SQL literal, safe to join into a statement.

The example for this item is shown with the ones above: db-postgres-Client.salam

`DatabaseName`

func DatabaseName(d: Database): str

The database name.

The example for this item is shown with the ones above: db-postgres-Client.salam

`UserName`

func UserName(d: Database): str

The user name.

The example for this item is shown with the ones above: db-postgres-Client.salam

`HostName`

func HostName(d: Database): str

The host it connected to.

The example for this item is shown with the ones above: db-postgres-Client.salam

`Port`

func Port(d: Database): str

The port, as text.

The example for this item is shown with the ones above: db-postgres-Client.salam

`QueryInt`

func QueryInt(d: Database, sql: str): int

The first column of the first row as an integer; 0 when the query failed or returned nothing.

The example for this item is shown with the ones above: db-postgres-Client.salam

`QueryText`

func QueryText(d: Database, sql: str): str

The first column of the first row as text; empty when the query failed or returned nothing.

The example for this item is shown with the ones above: db-postgres-Client.salam

`Conn`

func Conn(d: Database): dyn db.Connection

Returns the connection as a shared Connection.

The example for this item is shown with the ones above: db-postgres-Client.salam

db/postgres

Tutorial: a bookshop in eleven steps

1. Connect

2. Connection strings, URIs and TLS

3. Create the tables

4. Insert rows and get the new ids

5. Read rows into your own structs

6. Values from users, safely

7. NULL values

8. Transactions

9. Errors

10. A pool, and one connection per thread

11. PostgreSQL features

More

Guide: schema migrations

Guide: settings from the environment

Guide: nested results as JSON

Guide: which function for which job

Types

Database

Kind

Ok

Ping

Exec

Query

Begin

LastInsertId

Changes

Error

Quote

Close

Rows

Ok

Next

ColumnCount

RowCount

ColumnName

ColumnType

IsNull

Text

Int

Int64

Float

Close

Txn

Ok

Exec

Query

Commit

Rollback

Functions

Available

ClientVersion

ServerVersion

Open

OpenFull

Ok

Ping

Reset

Close

Error

Exec

ExecCount

Query

Quote

DatabaseName

UserName

HostName

Port

QueryInt

QueryText

Conn

`Database`

`Kind`

`Ok`

`Ping`

`Exec`

`Query`

`Begin`

`LastInsertId`

`Changes`

`Error`

`Quote`

`Close`

`Rows`

`Ok`

`Next`

`ColumnCount`

`RowCount`

`ColumnName`

`ColumnType`

`IsNull`

`Text`

`Int`

`Int64`

`Float`

`Close`

`Txn`

`Ok`

`Exec`

`Query`

`Commit`

`Rollback`

`Available`

`ClientVersion`

`ServerVersion`

`Open`

`OpenFull`

`Ok`

`Ping`

`Reset`

`Close`

`Error`

`Exec`

`ExecCount`

`Query`

`Quote`

`DatabaseName`

`UserName`

`HostName`

`Port`

`QueryInt`

`QueryText`

`Conn`