v2 / vlib / db / pg / README.md
214 lines · 158 sloc · 6.46 KB · 726559c029ba7e9171e3f7cb5dcfd204ee42f7e3
Raw

Description

pg is a wrapper for the PostgreSQL client library. It provides access to a PostgreSQL database server.

Before you can use this module, you must first have PostgreSQL installed on your system. To do this, find your OS and perform the actions listed.

Note These instructions are meant only as a convenience. If your OS is not listed or you need extra help, go here.

Fedora 31

sudo dnf install postgresql-server postgresql-contrib
sudo systemctl enable postgresql # to autostart on startup
sudo systemctl start  postgresql

Ubuntu/Debian

sudo apt install postgresql postgresql-client
sudo systemctl enable postgresql # to autostart on startup
sudo systemctl start  postgresql

MacOSX (Homebrew)

brew install postgresql
brew services start postgresql

On newer Homebrew setups the formula and service name may be versioned instead, for example postgresql@18. Use the exact name reported by brew info postgresql.

MacOSX (MacPorts)

gem install pg -- --with-pg-config=/opt/local/lib/postgresql[version number]/bin/pg_config

Installing libpq-dev or its equivalent for your OS

Ubuntu/Debian: sudo apt install libpq-dev

Red Hat Linux (RHEL): yum install postgresql-devel

OpenSuse: zypper in postgresql-devel

ArchLinux: pacman -S postgresql-libs

FreeBSD: pkg install postgresql18-client

OpenBSD: pkg_add postgresql-client

Windows:

The directory structure of the @VEXEROOT/thirdparty/pg folder will look like the following after following all instructions. Create the pg folder yourself if it does not exist yet.

@VEXEROOT/thirdparty/pg
├───libpq
│       libpq-fe.h
│       pg_config.h
│       postgres_ext.h
│
└───win64
    └───msvc
            libpq.lib

Installation instructions are as follows

Download the latest PostgreSQL version from the official website
(currently https://www.enterprisedb.com/downloads/postgres-postgresql-downloads)

In one of the steps in the installer, tick the following boxes:
[X] PostgreSQL Server
[ ] pgAdmin 4
[ ] Stack Builder
[X] Command Line Tools

We need PostgreSQL Server because it brings with it the C header files
needed for building programs that link to `libpq.dll`.

After finishing installation, add the folder `C:/Program Files/PostgreSQL/<version>/bin` to PATH.
Any program that wants to use postgres client functionality require these DLLs found in `/bin`:
- libcrypto-3-x64.dll
- libiconv-2.dll
- libintl-9.dll
- libpq.dll
- libssl-3-x64.dll
- libwinpthread-1.dll

If you want to compile with MSVC, you will need to copy `C:/Program Files/PostgreSQL/<version>/bin/libpq.lib`
into the `@VEXEROOT/thirdparty/pg/win64/msvc` directory.

Navigate to `C:/Program Files/PostgreSQL/<version>/include`. There you will find the files:
- `libpq-fe.h`
- `pg_config.h`
- `postgres_ext.h`

Copy the header files into `@VEXEROOT/thirdparty/pg/libpq`. You can now compile programs using the `db.pg` module.

---

After building an executable that uses `db.pg`, you may want to distribute it to others
who might not have the postgres DLLs installed on their machine. All you need to do is to
make sure a copy of all the required DLLs are in the same folder as your executable.

Getting Started with PostgreSQL

Read this section to learn how to install and connect to PostgreSQL Windows; Linux; macOS.

When you use pg.connect(pg.Config{ ... }), empty Config fields are omitted from the generated libpq connection string. That lets libpq defaults, PGPASSWORD, and .pgpass apply when you do not set those fields in code.

Using Parameterized Queries

Parameterized queries (exec_param, etc.) in V require the use of the following syntax: ($n).

The number following the $ specifies which parameter from the argument array to use.

db.exec_param_many('INSERT INTO users (username, password) VALUES ($1, $2)', ['tom', 'securePassword'])!
db.exec_param('SELECT * FROM users WHERE username = ($1) limit 1', 'tom')!

Using LISTEN/NOTIFY

PostgreSQL's LISTEN/NOTIFY mechanism allows you to build event-driven applications. One connection can send notifications on a channel, and all connections listening on that channel will receive them.

Basic Usage

import db.pg

fn main() {
    db := pg.connect(pg.Config{ user: 'postgres', password: 'password', dbname: 'mydb' })!
    defer { db.close() or {} }

    // Start listening on a channel
    db.listen('my_channel')!

    // From another connection or session, send a notification
    db.notify('my_channel', 'Hello, World!')!

    // Process incoming data from the server
    db.consume_input()!

    // Check for notifications
    if notification := db.get_notification() {
        println('Received notification on channel: ${notification.channel}')
        println('Payload: ${notification.payload}')
        println('From server process: ${notification.pid}')
    }

    // Stop listening
    db.unlisten('my_channel')!
    // Or unlisten from all channels
    db.unlisten_all()!
}

Event Loop with Polling

For real-time applications, you can use the socket file descriptor with select/poll:

import db.pg
import time

fn main() {
    db := pg.connect(pg.Config{ user: 'postgres', password: 'password', dbname: 'mydb' })!
    defer { db.close() or {} }

    db.listen('events')!

    // Get socket fd for polling (useful with select/epoll)
    socket_fd := db.socket()
    println('Socket FD: ${socket_fd}')

    // Simple polling loop
    for {
        db.consume_input()!
        for {
            notification := db.get_notification() or { break }
            println('Event: ${notification.channel} - ${notification.payload}')
        }
        time.sleep(100 * time.millisecond)
    }
}

Available Methods