PostgreSQL Client

The PostgreSQL client provides a simple interface to interact with PostgreSQL databases through HeroScript.

Configuration

The PostgreSQL client can be configured using HeroScript. Configuration settings are stored on the filesystem for future use.

Basic Configuration Example

#!/usr/bin/env -S v -n -w -gc none  -cc tcc -d use_openssl -enable-globals run

import incubaid.herolib.core
import incubaid.herolib.clients.postgresql_client


// Configure PostgreSQL client
heroscript := "
!!postgresql_client.configure 
 name:'test'
 user: 'postgres'
 port: 5432
 host: 'localhost'
 password: '1234'
 dbname: 'postgres'
"

// Process the heroscript configuration
postgresql_client.play(heroscript: heroscript)!

// Get the configured client
mut db_client := postgresql_client.get(name: "test")!

// Check if test database exists, create if not
if !db_client.db_exists('test')! {
 println('Creating database test...')
 db_client.db_create('test')!
}

// Switch to test database
db_client.dbname = 'test'

// Create table if not exists
create_table_sql := "CREATE TABLE IF NOT EXISTS users (
 id SERIAL PRIMARY KEY,
 name VARCHAR(100) NOT NULL,
 email VARCHAR(255) UNIQUE NOT NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)"

println('Creating table users if not exists...')
db_client.exec(create_table_sql)!

println('Database and table setup completed successfully!')

Configuration Parameters

Database Operations

Check Connection

// Check if connection is working
db_client.check()!

Database Management

// Check if database exists
exists := db_client.db_exists('mydb')!

// Create database
db_client.db_create('mydb')!

// Delete database
db_client.db_delete('mydb')!

// List all databases
db_names := db_client.db_names()!

Query Execution

// Execute a query
rows := db_client.exec('SELECT * FROM mytable;')!

// Query without semicolon is automatically appended
rows := db_client.exec('SELECT * FROM mytable')!

Backup Functionality

The client provides functionality to backup databases:

// Backup a specific database
db_client.backup(dbname: 'mydb', dest: '/path/to/backup/dir')!

// Backup all databases
db_client.backup(dest: '/path/to/backup/dir')!

Backups are created in custom PostgreSQL format (.bak files) which can be restored using pg_restore.

OS supporting

OSX

## supporting

brew install postgresql@17
brew services start postgresql@17

#if only the client is needed
brew install libpq
brew link --force libpq
export PATH="/usr/local/opt/libpq/bin:$PATH"

fn delete(args ArgsGet) !

fn exists(args ArgsGet) !bool

does the config exists?

fn get(args ArgsGet) !&PostgresqlClient

fn heroscript_dumps(obj PostgresqlClient) !string

///////////NORMALLY NO NEED TO TOUCH

fn heroscript_loads(heroscript string) !PostgresqlClient

fn list(args ArgsList) ![]&PostgresqlClient

if fromdb set: load from filesystem, and not from mem, will also reset what is in mem

fn new(args ArgsGet) !&PostgresqlClient

fn play(mut plbook PlayBook) !

fn set(o PostgresqlClient) !

register the config for the future

fn switch(name string)

switch instance to be used for postgresql_client

struct ArgsGet {
pub mut:
	name   string = 'default'
	fromdb bool // will load from filesystem
	create bool // default will not create if not exist
}

///////FACTORY

struct ArgsList {
pub mut:
	fromdb bool // will load from filesystem
}

struct BackupParams {
pub mut:
	dbname string
	dest   string
}

struct PostgresqlClient {
mut:
	db_ ?pg.DB @[skip]
pub mut:
	name     string = 'default'
	user     string = 'root'
	port     int    = 5432
	host     string = 'localhost'
	password string = ''
	dbname   string = 'postgres'
}

fn (mut self PostgresqlClient) backup(args BackupParams) !

fn (mut self PostgresqlClient) check() !

fn (mut self PostgresqlClient) db() !pg.DB

fn (mut self PostgresqlClient) db_create(name_ string) !

fn (mut self PostgresqlClient) db_delete(name_ string) !

fn (mut self PostgresqlClient) db_exists(name_ string) !bool

fn (mut self PostgresqlClient) db_names() ![]string

fn (mut self PostgresqlClient) exec(c_ string) ![]pg.Row