🐘 forge-postgres

Are you a hackooor? Do you use Forge for stuff it's not meant for? Then this plugin is for you!

Presenting — forge-postgres. A postgres driver (?) for Forge scripts.

This plugin lets you connect to a postgres db directly via Forge, execute queries and read values into native types — all in beautiful, idiomatic Solidity.

📋 Requirements

This plugin requires you to run your scripts with ffi turned on
Some basic configuration is required (see instructions below)
The sed utility must be installed
python must be installed
Postgres not included

⚙️ Installation

It's forge!

$ forge install tudmotu/forge-postgres

You do need one more thing and that's creating a .forge/tmp/ directory for temporary files. Create the directory, then add write permissions in your foundry.toml:

fs_permissions = [
    { access = "read-write", path = "./.forge/logs/"}
]

📝 Usage

The API is somewhat similar to bindings from other languages such as Java JDBC API. It supports placeholders and reading values into native types.

Connections

First, create a connection object:

import { Connection, Postgres } from 'forge-postgres/Postgres.sol';

Connection memory conn = Postgres.connect({
    user: 'postgres',
    passwd: 'password',
    host: '127.0.0.1:5432',
    database: 'postgres'
});

Queries

A simple query can be executed directly with a string literal:

import { Record } from 'forge-postgres/Postgres.sol';

conn.execute('create table abc (id decimal, name text)');
Record[] memory records = conn.execute('select * from abc');

Statements

A parameterized query can be executed using a Statement object:

import { Statement } from 'forge-postgres/Postgres.sol';

conn.execute('create table abc (id decimal, name text)');

Statement memory statement = conn.createStatement(
    'insert into abc (id, name) values ($1, $2)'
);

statement.addDecimalParam(6969);
statement.addTextParam('tudmotu');

conn.execute(statement);

You can also invoke .prepare() to see the generated query.

Records

Queries return an array of Record elements. Each Record object represents a row in the result set. You can read the values in each column using the .readX methods. See the full API below.

Record[] memory records = conn.execute('select id, name from abc');
string memory name = records[0].readString(1);

Structs

You can even kinda read/write structs using the bytea type in postgres:

struct Profile {
    uint[] friendIds;
    uint balance;
}

conn.execute('create table users (username text, profile bytea)');

Profile memory profile;
uint[] memory ids = new uint[](2);
ids[0] = 420;
ids[1] = 1337;
profile.friendIds = ids;
profile.balance = 10 ether;

bytes memory profileBytes = abi.encode(profile);

Statement memory statement = conn.createStatement(
    'insert into users (username, profile) values ($1, $2)'
);

statement.addTextParam('tudmotu');
statement.addByteaParam(profileBytes);
conn.execute(statement);

Record[] memory records = conn.execute('select * from users');

Profile memory dbProfile = abi.decode(
    records[0].readBytes(1),
    (Profile)
);

🌐 API

Postgres

function connect (
    string memory username,
    string memory passwd,
    string memory host,
    string memory database
) returns (Connection memory);

Connection

function execute (
    string memory query
) returns (Record[] memory);

function execute (
    Statement memory statement
) public returns (Record[] memory);

function createStatement (
    string memory query
) returns (Statement memory);

Statement

function addDecimalParam (uint value);
function addBigIntParam (uint64 value);
function addIntParam (uint32 value);
function addTextParam (string memory value);
function addByteaParam (bytes memory value);
function addBooleanParam (bool value);
function prepare ();

Record

function readString (uint columnIndex);
function readBytes (uint columnIndex);
function readAddress (uint columnIndex);
function readUint (uint columnIndex);
function readInt (uint columnIndex);
function readBytes32 (uint columnIndex);
function readBool (uint columnIndex);

Tudmotu/forge-postgres