Executing statements

This most basic functionality is still very often useful. Prego provides automated statement preparation to make it fast and easy, and provides transparent connection pooling.

Connections

By default, prego runs over a single, default connection prego.db, that auto-configures as explained in installation. Sometimes you can make use of several connections, say, you can create a new connection per server request to make them run in parallel. It is fairly good approach especially when using db partitioning and mutli-host/mutli-disc postgres installation. Again, you might operate several databases too at once using different connections. To obtain new connection, use

conn = new prego.Connection connectionString # New connection to anywhere

dupe = conn.clone # points to same db but works in parallel, isnt'it nice? ;)

defaultDupe = prego.db.clone # just clone default connection

Still, in most cases, using default connection might be appropriate (except with transactions where connection duplication is maintained by prego).

From this point, we will use default connection, but all descrived below could be applied to any Connection object

Parameterless and bulk queries

Most often you needn't just a connection, but performing SQL statements over it. To just execute an parameterless SQL statement, or a series of such statements, spearated by ';' use:

prego.db.query "drop table some_data; drop_table another_data;", (err, result) ->
    # check err and use result

This is the only way to execute several statements in one call. Err and result is what PG module returns (https://github.com/brianc/node-postgres).

Parametric queries

Usually you need to parametrize the connection, e.g. queries with 'vairables'. Such queries are 'prepared' firts time and then executed very effectively. For example:

prego.db.execute "SELECT * FROM users WHERE last_name ILIKE $1", [ pattenrn + '%'], (err, result) ->
    # if !err result.rows are all matching rows

Prego takes care of naming, preparing and caching statements for you. See PG module documentation for more details on using parameters and values in prepared statements.

If you need only one row:

prego.db.executeRow "SELECT COUNT(*) FROM users WHERE last_name ILIKE $1", [ pattern + '%'], (err,res) -> return err if err console.log 'Found users:', res.count

Fetching big result sets

When working with a big result sets, it is not always desirable to fetch all the result set in memory as execute does, the ploper approach is to get it row by row:

prego.db.executeEach "SELECT * FROM users WHERE last_name ILIKE $1", [ pattenrn + '%'], (err, row) ->
    return err if err
    if row
         console.log 'Got a row:', row
    else
         console.log 'No more rows'

As the example shows, callback would be called once per any row in result set and then once with row == null on end