Skip to content

Core API

Jump to bottom
Eugene Kabanov edited this page Jun 30, 2016 · 15 revisions
  1. Connection functions.
  1. Pool functions.
  1. Command execution functions.
  1. Result functions.
  1. Asynchronous notify functions.
  1. Utility functions.

Connection functions

proc connect*(connection: string): Future[apgConnection] =

Makes a new connection to the database server.

Arguments

  • connection - string can contain one or more parameter settings separated by whitespace. You can find more information on format of this string in PostgreSQL documentation.

Return value

Returns Future[apgConnection] which completes, when connection will be established. Future holds value of apgConnection, which can be used to execute SQL queries.

proc reset*(conn: apgConnection): Future[void] =

Resets the communication channel to the server.

This function will close the connection to the server and attempt to reestablish a new connection to the same server, using all the same parameters previously used. This may be useful for error recovery if a working connection is lost.

Arguments

  • conn - connection, which must be reset.

Return value

Returns Future[void] which becomes completed, when reset operation completes.

proc close*(conn: apgConnection) =

Closes the connection to the server.

Arguments

  • conn - connection to be closed.

Pool functions

proc newPool*(size = 10): apgPool =

Creates new pool of connections to PostgreSQL server.

Arguments

  • size - number of connections in pool, default value is 10 connections.

Return value

Returns apgPool object.

proc connect*(pool: apgPool, connection: string): Future[void] {.async.} =

Makes a new connection for whole pool to the database server.

Arguments

  • pool - pool object.
  • connection - connection string, which will be used to connect pool's connections to PostgreSQL server. You can find more information on format of this string in PostgreSQL documentation.

Return value

Function returns future Future[void], which completes, when all pool connections will connected to PostgreSQL server.

proc close*(pool: apgPool) =

Closes all pool connections

Arguments

  • pool - pool object.

template withConnection*(pool: apgPool, conn, body: untyped) =

This template allow to use one connection from pool to perform multiple operations. Inside withConnection block, you can work with one particulare apgConnection object conn.

withConnection(pool, conn) do:
  await setClientEncoding(conn, "WIN1252")
  var res = await exec("SELECT * FROM foo")

Arguments

  • pool - pool object
  • conn - name of variable, which receives connection from pool.

Command execution functions

macro exec*(conn: apgConnection|apgPool, statement: string, params: varargs[typed]): Future[apgResult] =

Executes SQL statement on connection or pool. Many SQL statements supported.

Arguments

  • conn - can be apgConnection object or apgPool object.
  • statement - string representing SQL statement to execute.
  • params - variate number of parameters for SQL query. Supported types described on Types conversion wiki page.

Return value

Function returns Future[apgResult] which will be available, when execution of SQL query will be completed.

proc copyTo*(conn: apgConnection, buffer: pointer, nbytes: int32): Future[apgResult] =

Sends data to the server during COPY. Be sure to execute COPY ... FROM STDOUT; SQL statement via exec(), before using this function.

Arguments

  • conn - connection object.
  • buffer - data buffer to be sent to PostgreSQL server. nil value of this argument causes to send EOF to server. See Examples.
  • nbytes - size of data buffer buffer.

Return value

Function returns Future[apgResult] which will be available, when data transfer process will be completed.

proc copyFromInto*(conn: apgConnection, buffer: pointer, nbytes: int): Future[int] =

Receives data from server during COPY. Be sure to execute COPY ... TO STDOUT SQL statement via exec(), before using this function. Data is always returned one data row at a time; if only a partial row is available, it is not returned. Be sure buffer can hold whole dataset row in text representation. See Examples.

Arguments

  • conn - connection object
  • buffer - data buffer which receives COPY data.
  • nbytes - size of data buffer buffer.

Return value

Function returns number of bytes copied to buffer, 0 value is equal to EOF.

Result functions

proc close*(apgres: apgResult) =

Closes result dataset. Also closes all results inside of apgres object in case of multiple SQL statements was executed.

Arguments

  • apgres - result object.

proc len*(apgres: apgResult): int =

Returns number of SQL query's results stored inside apgres object.

Arguments

  • apgres - result object.

Return value

Functions returns number of results stored inside `apgres object.

proc `[]`*(apgres: apgResult, index: int): PPGresult =

Returns result PPGResult by index index from apgResult object.

Arguments

  • apgres - result object.
  • index - index.

Return value

Returns PPGResult object.

proc getValue*(pgres: PPGresult): string =

Get single result value as string.

Arguments

  • pgres - result object.

Return value

Functions returns result value as string.

proc getRow*(pgres: PPGresult): Row =

Get one row from result.

Arguments

  • pgres - result object.

Return value

Function returns Row object, which encapsulates result's row.

proc getRows*(pgres: PPGresult, rows: int): seq[Row] =

Get number of rows from result.

Arguments

  • pgres - result object.
  • rows - number of rows to return. If rows == -1 all rows from result's dataset will be returned.

Return value

Function returns sequence of rows count rows from result dataset as seq[Row].

template getAllRows*(pgres: PPGresult): seq[Row] =

Get all rows from result dataset. This template is alias for getRows(pgres, -1).

Arguments

  • pgres - result object.

Return value

Function returns all rows from result dataset as seq[Row].

iterator rows*(pgres: PPGresult): Row =

Iterates over result dataset rows.

Arguments

  • pgres - result object.

Return value

Iterator returns Row object for all rows in result dataset.

proc getAffectedRows*(pgres: PPGresult): int64 =

Get number of rows affected by SQL query.

Arguments

  • pgres - result object

Result value

Function returns number of rows affected by SQL query.

Asynchronous notify functions

proc listenNotify*(conn: apgConnection|apgPool, channel: string): Future[void] =

Start listening for a notification on channel channel. This function executes LISTEN "channel" SQL query on connection or pool. You can get more information about in PostgreSQL documentation.

Arguments

  • conn - established connection apgConnection or pool object apgPool.
  • channel - name of channel (any identifier).

Return value

Functions returns Future[void] which completes, when operation will be completed.

proc unlistenNotify*(conn: apgConnection|apgPool, channel: string): Future[void] =

Stop listening for a notification on channel channel. This function executes UNLISTEN "channel" SQL query on connection or pool. You can get more information about in PostgreSQL documentation.

Arguments

  • conn - established connection apgConnection or pool object apgPool.
  • channel - name of channel (any identifier).

Return value

Functions returns Future[void] which completes, when operation will be completed.

proc sendNotify*(conn: apgConnection|apgPool, channel: string, payload: string): Future[void] =

Function generates notification on channel channel with payload payload. Size of payload is limited to 8000 bytes because of PostgreSQL limitation. More information you can find in PostgreSQL documentation.

Arguments

  • conn - established connection apgConnection or pool object apgPool.
  • channel - name of channel (any identifier).
  • payload - string can be sent along with the notification. Size of string is limited to 8000 bytes.

Return value

Functions returns Future[void] which completes, when operation will be completed.

proc notify*(conn: apgConnection|apgPool, channel: string): Future[apgNotify] =

Make future for PostgreSQL asynchronous notify mechanism. Future will be completed immediately when notify on channel channel will be received. Be sure to start listening for notifies on channel channel via call to listenNotify().

Arguments

  • conn - established connection apgConnection or pool object apgPool.
  • channel - name of channel (any identifier).

Return value

Functions returns Future[apgNotify] which will be completed when asynchronous notify arrives. You can setup your own complete callback on this future.

Utility functions

proc setClientEncoding*(conn: apgConnection, encoding: string): Future[void] {.async.} =

Functions sets default character set encoding on connnection conn using encoding. You can see available character sets in PostgreSQL documentation.

Arguments

  • conn - established connection apgConnection.
  • encoding name of character set encoding.

Return value
Functions returns Future[void, which completes when operation will be completed.

proc getClientEncoding*(conn: apgConnection): string =

Get current character set encoding for connection.

Arguments

  • conn - established connection apgConnection.

Return value

Function returns current character set encoding.

proc getVersion*(): int =

Get version of LibPQ library.

Return value

Function returns LibPQ library version as integer.

proc getServerVersion*(conn: apgConnection): int =

Get PosgreSQL server version.

Arguments

  • conn - established connection to PostgreSQL server.

Return value

Function returns PostgreSQL server version as integer.

proc getProtocolVersion*(conn: apgConnection): int =

Get PostgreSQL protocol version.

Arguments

  • conn - established connection to PostgreSQL server.

Return value

Function returns PostgreSQL protocol version as integer.

proc escapeString*(conn: apgConnection, str: string): string =

Escapes a string for use within an SQL command according to rules of connection. Do not use escaping if you are using parameters params in exec macro.

var name = "'; SELECT 1;"
# Escaping is not needed
await exec(conn, "SELECT * FROM people WHERE name=$1", name)
# Escaping needed
await exec(conn, "SELECT * FROM people WHERE name='" & escapeString(conn, name) & "';")

Arguments

  • conn - established connection to PostgreSQL server.

Return value

Function returns escaped string which can be used in SQL command.

proc escapeBytea*(conn: apgConnection, buf: pointer, size: int): string =

Converts binary data to PostgreSQL's BYTEA string representation. You don't need to convert binary data to string, if you are using params in exec macro.

var bindata = array['\x00', '\x01', '\x02']

# Conversion not needed.
var query = "INSERT INTO binData VALUES ($1)"
var res = await exec(conn, query, bindata)

# Conversion needed.
var query = "INSERT INTO binData VALUES (" & escapeBytea(conn, addr(bindata[0]), len(bindata) & ");"
var res = await exec(conn, query)

Arguments

  • conn - established connection to PostgreSQL server.
  • buf - pointer to binary data buffer.
  • size - length of binary data buffer in bytes.

Return value

Function returns string representation of PostgreSQL's BYTEA type.

proc unescapeBytea*(str: string): seq[char] =

Converts PostgreSQL's BYTEA string representation back to seq[char].

Arguments

  • str - BYTEA string representation.

Return value

Function returns seq[char] representation of PostgreSQL's BYTEA string.

Clone this wiki locally