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.

Getting connection

prego = require 'prego'

prego.connection (err, connection) ->
    # err || connection 

Connection is the pg's connection, see https://github.com/brianc/node-postgres for dtails on what could be done with it. See Intsallation on how to configure connection.

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.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.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.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.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