Actions

The PostgreSQL connector is distributed across two libraries:

• ballerinax/postgresql
• ballerinax/postgresql.driver

Available clients:

Client	Purpose
Client	Provides standard SQL operations against a PostgreSQL database: query, single-row query, execute (DML/DDL), batch execute, stored procedure call, and connection management.

For event-driven integration, see the Trigger Reference.

---

Client

Provides standard SQL operations against a PostgreSQL database: query, single-row query, execute (DML/DDL), batch execute, stored procedure call, and connection management.

Configuration

Field	Type	Default	Description
host	string	"localhost"	PostgreSQL server hostname or IP address.
username	string?	"postgres"	Database username.
password	string?	()	Database password.
database	string?	()	Name of the database to connect to.
port	int	5432	PostgreSQL server port.
options	Options?	()	Advanced PostgreSQL-specific connection options (SSL, timeouts, prepared statement caching, and more).
connectionPool	sql:ConnectionPool?	()	Connection pool configuration. If not provided, the global shared pool is used.

Initializing the client

import ballerinax/postgresql;
import ballerinax/postgresql.driver as _;

configurable string host = ?;
configurable string username = ?;
configurable string password = ?;
configurable string database = ?;
configurable int port = 5432;

postgresql:Client dbClient = check new (
    host = host,
    username = username,
    password = password,
    database = database,
    port = port
);

Operations

Query operations

query

Executes a SQL SELECT query and returns multiple results as a stream of records.

Parameters:

Name	Type	Required	Description
sqlQuery	sql:ParameterizedQuery	Yes	SQL query with optional parameters (for example, `SELECT * FROM customers WHERE country = ${country}`).
rowType	typedesc<record {}>	No	Record type to map query results to.

Returns: stream<rowType, sql:Error?>

Sample code:

type Customer record {|
    int customerId;
    string firstName;
    string lastName;
    int registrationId;
    float creditLimit;
    string country;
|};

stream<Customer, sql:Error?> resultStream = dbClient->query(`SELECT * FROM Customers`);

Sample response:

{"customerId": 1, "firstName": "Peter", "lastName": "Stuart", "registrationId": 1, "creditLimit": 5000.75, "country": "USA"}
{"customerId": 2, "firstName": "Dan", "lastName": "Brown", "registrationId": 2, "creditLimit": 10000.00, "country": "UK"}

queryRow

Executes a SQL query expected to return a single row or a scalar value. Returns sql:NoRowsError if no results are found.

Parameters:

Name	Type	Required	Description
sqlQuery	sql:ParameterizedQuery	Yes	SQL query expected to return one row or value.
returnType	typedesc<anydata>	No	Expected return type: a record for a full row, or a primitive type for a scalar value.

Returns: returnType|sql:Error

Sample code:

type Customer record {|
    int customerId;
    string firstName;
    string lastName;
    int registrationId;
    float creditLimit;
    string country;
|};

int customerId = 1;
Customer customer = check dbClient->queryRow(
    `SELECT * FROM Customers WHERE customerId = ${customerId}`
);

Sample response:

{"customerId": 1, "firstName": "Peter", "lastName": "Stuart", "registrationId": 1, "creditLimit": 5000.75, "country": "USA"}

Execute operations

execute

Executes a SQL DDL or DML statement (CREATE, INSERT, UPDATE, DELETE) and returns execution metadata.

Parameters:

Name	Type	Required	Description
sqlQuery	sql:ParameterizedQuery	Yes	SQL statement with optional parameters.

Returns: sql:ExecutionResult|sql:Error

Sample code:

sql:ExecutionResult result = check dbClient->execute(
    `INSERT INTO Customers (firstName, lastName, registrationId, creditLimit, country)
     VALUES (${"Peter"}, ${"Stuart"}, ${1}, ${5000.75}, ${"USA"})`
);

Sample response:

{"affectedRowCount": 1, "lastInsertId": 1}

batchExecute

Executes a batch of parameterized SQL statements in a single call, useful for bulk inserts or updates.

Parameters:

Name	Type	Required	Description
sqlQueries	sql:ParameterizedQuery	Yes	Array of parameterized SQL statements to execute as a batch.

Returns: sql:ExecutionResult[]|sql:Error

note

Passing an empty array returns an sql:ApplicationError. Filter or guard upstream if your input may be empty.

Sample code:

var customers = [
    {firstName: "Peter", lastName: "Stuart", country: "USA"},
    {firstName: "Dan", lastName: "Brown", country: "UK"},
    {firstName: "Anna", lastName: "Lee", country: "SG"}
];

sql:ParameterizedQuery[] batch = from var c in customers
    select `INSERT INTO Customers (firstName, lastName, country)
            VALUES (${c.firstName}, ${c.lastName}, ${c.country})`;

sql:ExecutionResult[] results = check dbClient->batchExecute(batch);

Sample response:

[{"affectedRowCount": 1, "lastInsertId": 1}, {"affectedRowCount": 1, "lastInsertId": 2}, {"affectedRowCount": 1, "lastInsertId": 3}]

Stored procedures

call

Calls a stored procedure or function and returns result sets and output parameter values.

Parameters:

Name	Type	Required	Description
sqlQuery	sql:ParameterizedCallQuery	Yes	Stored procedure call query (for example, `CALL InsertStudent(${id}, ${name}, ${age})`).
rowTypes	typedesc<record {}>[]	No	Array of record types for mapping result sets returned by the procedure.

Returns: sql:ProcedureCallResult|sql:Error

Sample code:

int personId = 1;
string personName = "Alice";
int personAge = 25;

sql:ProcedureCallResult result = check dbClient->call(
    `CALL InsertStudent(${personId}, ${personName}, ${personAge})`
);
result.close();

Sample response:

{"executionResult": {"affectedRowCount": 1}, "queryResult": null}

Connection management

close

Closes the client and releases the associated connection pool (if not shared by other clients). Call this only at the end of the application lifetime.

Returns: sql:Error?

Sample code:

check dbClient.close();

---

Supporting types

Options

Advanced PostgreSQL connection options. Passed to Client.init through the options parameter.

Field	Type	Default	Description
ssl	SecureSocket?	()	SSL/TLS security settings.
connectTimeout	decimal	0	Connection timeout in seconds. 0 means the JDBC driver default (typically no timeout).
socketTimeout	decimal	0	Socket read/write timeout in seconds (0 means no timeout).
loginTimeout	decimal	0	Time, in seconds, allowed to complete the database login (0 means no timeout).
rowFetchSize	int?	()	Number of rows to fetch in a single round trip when streaming results.
cachedMetadataFieldsCount	int?	()	Number of fields cached per connection.
cachedMetadataFieldSize	int?	()	Size, in bytes, of metadata fields cached per connection.
preparedStatementThreshold	int?	()	Number of PreparedStatement executions required before switching to a server-side prepared statement.
preparedStatementCacheQueries	int?	()	Maximum number of prepared statements cached per connection.
preparedStatementCacheSize	int?	()	Maximum size, in mebibytes, of the prepared statement cache per connection.
cancelSignalTimeout	decimal	10	Time, in seconds, by which the cancel command is sent out of band over its own connection.
keepAliveTcpProbe	boolean?	()	Enable TCP keep-alive probes on the socket.
binaryTransfer	boolean?	()	Use the binary protocol for sending and receiving data when supported.
currentSchema	string?	()	The schema to be used by the client.

SecureSocket

Field	Type	Default	Description
mode	SSLMode	PREFER	SSL mode (see below).
rootcert	string?	()	File name of the SSL root certificate. Defaults to ${user.home}/.postgresql/root.crt on Unix or %appdata%/postgresql/root.crt on Windows.
key	crypto:KeyStore|CertKey?	()	Client certificate configuration. Use a crypto:KeyStore for JKS or PKCS#12 keystores, or a CertKey record for PEM files.

CertKey

Represents a client certificate, private key, and optional key password loaded from PEM files. Use as an alternative to crypto:KeyStore in SecureSocket.key.

Field	Type	Default	Description
certFile	string	Required	Path to the file containing the client certificate.
keyFile	string	Required	Path to the file containing the client private key.
keyPassword	string?	()	Password for the private key, if it is encrypted.

SSLMode

The supported SSL connection modes:

Constant	Value	Description
PREFER	"PREFER"	Try an encrypted connection first and fall back to unencrypted if the server does not support it.
REQUIRE	"REQUIRE"	Require an encrypted connection. Does not verify the server certificate.
DISABLE	"DISABLE"	Establish an unencrypted connection.
ALLOW	"ALLOW"	Try an unencrypted connection first and fall back to an encrypted one if the server requires it.
VERIFY_CA	"VERIFY-CA"	Require an encrypted connection and verify the server CA certificate.
VERIFY_FULL	"VERIFY-FULL"	Require an encrypted connection, verify the CA, and verify the server hostname.

sql:ConnectionPool

Connection pool configuration is provided by the parent ballerina/sql library. See the sql:ConnectionPool API reference for the full field list and pool-handling semantics.