Documentation

For a quick usage guide visit any of the following node-db drivers:

Database class

A connection is created by instantiating the Database class. Its constructor takes an object with connection options. Once the instance is created, calling its connect() method will perform the connection, and as a result emit the appropriate event and call the optional callback.

Options specified to the Database constructor may vary from driver to driver:

db-drizzle
  • mysql: Since db-drizzle uses the MySQL protocol to talk to database servers, this boolean setting specifies if the server is an actual MySQL server, or a Drizze server.
  • hostname: Hostname to connect to. Should be omitted if using socket. Optional, defaults to localhost.
  • port: Port to connect to. Should be omitted if using socket. Optional, defaults to 3306.
  • user: Database user.
  • password: Database password.
  • database: Database name.
db-mysql
  • hostname: Hostname to connect to. Should be omitted if using socket. Optional, defaults to localhost.
  • port: Port to connect to. Should be omitted if using socket. Optional, defaults to 3306.
  • socket: Unix socket to use when connecting. Should be omitted if using hostname and port. Optional.
  • user: Database user.
  • password: Database password.
  • database: Database name.
  • charset: The name of the character set to use as the default character set. Optional. See option MYSQL_SET_CHARSET_NAME in mysql_options().
  • compress: If true, use the compressed client/server protocol. Optional, defaults to false. See option MYSQL_OPT_COMPRESS in mysql_options().
  • initCommand: SQL statement to execute when connecting to the MySQL server. Optional. See option MYSQL_INIT_COMMAND in mysql_options().
  • readTimeout: If greater than 0, set the timeout in seconds for attempts to read from the server. Optional, defaults to 0. See option MYSQL_OPT_READ_TIMEOUT in mysql_options().
  • reconnect: Enable or disable automatic reconnection to the server if the connection is found to have been lost. Optional, defaults to true. See option MYSQL_OPT_RECONNECT in mysql_options().
  • sslVerifyServer: Enable or disable verification of the server’s Common Name value in its certificate against the host name used when connecting to the server. Optional, defaults to false. See option MYSQL_OPT_SSL_VERIFY_SERVER_CERT in mysql_options().
  • timeout: If greater than 0, set the timeout in seconds for attempts to connect to the server. Optional, defaults to 0. See option MYSQL_OPT_CONNECT_TIMEOUT in mysql_options().
  • writeTimeout: If greater than 0, set the timeout in seconds for attempts to write to the server. Optional, defaults to 0. See option MYSQL_OPT_WRITE_TIMEOUT in mysql_options().

Events

ready

Emitted when the connection has been established and is ready to use. Arguments:

  • server: an object containing server details:
    • hostname: Hostname connected to.
    • user: User utilized for establishing the connection.
    • database: Database to which the connection was established.
    • version: Server software version.
error

Emitted when the connection attempt was unsuccessful.

Arguments:

  • error: Error.

connect([options], [callback])

Performs the connection to the database, and as a result emits an event.

Arguments:

  • options: Connection options:
    • async: If set to false, the connection will be performed synchronously. Optional, defaults to true.
  • callback: Callback to trigger when the connection attempt finished. The first argument to this callback is an error (null if no error found.) The rest of the arguments match those defined in the ready event.

disconnect()

Disconnect from the database.

isConnected()

Tells if the connection is established and alive by pinging the server.

escape(string)

Returns the given argument escaped for safe usage in a SQL statement.

Arguments:

  • string: String to escape.

name(string)

Returns the given argument escaped for usage as a table/field name in an SQL statement.

Arguments:

  • string: String to escape.

query()

Creates an instance of the Query class to perform queries. Takes any number of arguments accepted by execute() method of the Query class.

Constants

The Database class defines the following constants:

  • COLUMN_TYPE_STRING: Identifies the type of a column as a string.
  • COLUMN_TYPE_BOOL: Identifies the type of a column as a boolean.
  • COLUMN_TYPE_INT: Identifies the type of a column as an integer.
  • COLUMN_TYPE_NUMBER: Identifies the type of a column as a number.
  • COLUMN_TYPE_DATE: Identifies the type of a column as a date.
  • COLUMN_TYPE_TIME: Identifies the type of a column as a time.
  • COLUMN_TYPE_DATETIME: Identifies the type of a column as a datetime.
  • COLUMN_TYPE_TEXT: Identifies the type of a column as a text.
  • COLUMN_TYPE_SET: Identifies the type of a column as a set.

Query class

Handles construction and execution of queries. Once you have established a connection to a database through the Database class, call its query() method to create an instance of the Query class. You should never instantiate Query directly.

Once the Query object is created, you can build and run an SQL query using the chain methods and calling execute(), or specifying the query directly to execute(). As a result of the query execution, the appropriate events will be emitted and any given callbacks will be executed as needed.

Events

success

Emitted when the query was executed successfully. Its arguments depend on the type of query executed. If it was a query that produces results (such as a SELECT), the arguments are:

  • rows: The result rows as an array, where each element is an object which keys correspond to column names, and values to column values.
  • columns: The columns that are part of the result. Each column is an object with the following properties:
    • name: Column name.
    • type: Column type, identified by any of the appropriate Database constants.

If the query executed produced no results (such as INSERT), the arguments are:

  • result: Object with the following properties:
    • id: If the query was an INSERT that produced a generated ID, this is the generated ID.
    • affected: Number of affected rows.
    • warning: Number of rows that triggered a warning.
error

Emitted when the query execution was unsuccessful.

Arguments:

  • error: Error.
each

Emitted for each row found as a result of the query execution.

Arguments:

  • row: an object which keys correspond to column names, and values to column values.
  • index: 0-based index of the row.
  • last: boolean indicating if this is the last row.

execute([query], [values], [callback], [options])

Execute the given query, or the query built through the Query chain methods. As a result of this execution, the appropriate events will be emitted and the callback triggered.

Arguments:

  • query: If specified, the SQL query to execute that may contain placeholders.
  • values: If the query argument is set and contains value placeholders, this argument specifies the values to use when replacing. Refer to the where() method for information about value placeholders. Optional.
  • callback: Callback to trigger when the query executed. The first argument to this callback is an error (null if no error found.) The rest of the arguments match those defined in the success event.
  • options: an object containing the following properties. Optional:
    • async: If set to false the query will be executed synchronously. Optional, defaults to true.
    • cast: If set to true, the result values will be converted to their Javascript native type. Optional, defaults to true.
    • bufferText: If set to true, all TEXT fields will be converted to a node Buffer. Otherwise only binary TEXT fields will be wrapped in Buffer. Optional, and only applicable if cast is set to true. Optional, defaults to false.
    • start: A callback to be executed whenever a query is about to start. This callback takes one argument (the SQL query), and if it returns anything it’ll be treated as a modified version of the query, and it will be used instead. If the callback returns false, the query execution will be aborted. Optional.
    • finish: A callback to be executed after a query was run, regardless of it succeeded or not. No arguments are provided to the callback. Optional.

select(fields)

Query chain method to start a SELECT statement.

Arguments:

  • fields: Can be any of:
    • String to use. Example: '`id`, `user`, `email`, CONCAT(`first_name`, ' ', `last_name`) AS `name`'.
    • Array of field names. Each field name can be a string, or an object to specify aliasing. Example: ['id', 'user', {'total': 'COUNT(*)'}].

from(tables, [escape])

Query chain method to append a FROM clause.

Arguments:

  • tables: Can be any of:
    • String to use. Example: 'users, profiles'.
    • Array of table names. Each table name can be a string, or an object to specify aliasing. Example: ['users', 'profiles', {'m': 'messages'}].
  • escape: boolean indicating if table names should be escaped. Optional, defaults to true.

join(options, [values])

Query chain method to append a JOIN clause.

Arguments:

  • options: an object containing the following properties:
    • type: Type of join. Optional, defaults to INNER.
    • table: Table to join with.
    • alias: Alias to use for the table. Optional.
    • conditions: Conditions to use when joining. Optional.
    • escape: Boolean indicating if table and alias name should be escaped. Optional, defaults to true.
  • values: If the conditions property in the options argument contains value placeholders, this argument specifies the values to use when replacing. Refer to the where() method for information about value placeholders. Optional.

where(conditions, [values])

Query chain method to append a WHERE clause.

Arguments:

  • conditions: SQL conditional statement that may include value placeholders.
  • values: If the conditions argument includes value placeholders, this argument specifies the values to use when replacing. Optional.
Value placeholders

A value placeholder is indicated through the question mark (?) character. For each value placeholder, a respective value must exist in the values argument. If you wish to include an actual question mark in the expression without it being treated as a value placeholder, you must first escape it with the backslash, like so: \?, or the question mark should be included in a string, like so: 'This is my question?'

Examples
where('id = ?', [ 1 ]);
// Generates: WHERE id = 1
where('approved = ? AND created > ?', [ true, new Date(2011, 4, 21, 12, 0, 0) ]);
// Generates: WHERE approved = 1 AND created > '2011-05-21 12:00:00'
where("content LIKE '%?%' AND group = ? AND role IN ?", [ 'site', [ 'admin', 'user' ]]);
// Generates: WHERE content LIKE '%?%' AND group = 'site' AND role IN ('admin','user')

and(conditions, [values])

Query chain method to append an AND clause.

Arguments:

  • conditions: SQL conditional statement that may include value placeholders.
  • values: If the conditions argument contains value placeholders, this argument specifies the values to use when replacing. Refer to the where() method for information about value placeholders. Optional.

or(conditions, [values])

Query chain method to append an OR clause.

Arguments:

  • conditions: SQL conditional statement that may include value placeholders.
  • values: If the conditions argument contains value placeholders, this argument specifies the values to use when replacing. Refer to the where() method for information about value placeholders. Optional.

order(fields, [escape])

Query chain method to append an ORDER BY clause.

Arguments:

  • fields: An object where each key is the field / expression to order by, and the value the order to apply. The value can be a string, a boolean (where true means ascending order, and false descending), or an object with the following properties:
    • escape: Boolean indicating if the value should be escaped. Optional, defaults to true.
    • order: Either a boolean (true for ascending, false for descending), or a string.
  • escape: boolean indicating if the field names should be escaped. Optional, defaults to true.

limit([offset], rows)

Query chain method to append a LIMIT clause.

Arguments:

  • offset: Offset to start at. Optional.
  • rows: Number of rows.

add(expression)

Query chain method to append a generic clause.

Arguments:

  • expression: Expression to add to the query. Can be any of:
    • String containing expression to add. Example: ' UNION '.
    • Another Query instance to add as subquery. Example: add(this.query().select('*').from('users'))

insert(table, [fields], values, [escape])

Query chain method to start an INSERT clause, or build a whole INSERT statement.

  • table: Table name.
  • fields: Array of field names. Follows the same format as the fields argument of the select() method. Optional.
  • values: Array of values (or array of array of values for multiple records), where each value should be a Javascript native type that can be mapped to SQL. If set to false, the VALUES section will be omitted (ideal for chaining with a select() method). If a value is a javascript object, it can contain the following properties:
    • escape: Boolean indicating if the value should be escaped. Optional, defaults to true.
    • value: Actual value to use.
    • precision: Number indicating the precision when dealing with decimal numbers. Optional, defaults to V8 Javascript standard’s precision.
  • escape: boolean indicating if the table name should be escaped. Optional, defaults to true.

update(tables, [escape])

Query chain method to start an UPDATE statement.

Arguments:

  • tables: Table names. Follows the same format as the tables argument of the from() method.
  • escape: boolean indicating if table names should be escaped. Optional, defaults to true.

set(values, [escape])

Query chain method to append a SET clause (to be used with the update() method).

Arguments:

  • values: An object where each key is a field name, and each value a field value. Each value should follow the same format of each individual value in the values argument of the insert() method.
  • escape: boolean indicating if field names should be escaped. Optional, defaults to true.

delete([tables], [escape])

Query chain method to start a DELETE statement.

Arguments:

  • tables: Table names. Follows the same format as the tables argument of the from() method. Optional.
  • escape: boolean indicating if table names should be escaped. Optional, defaults to true.

sql()

Returns the current SQL statement as built by the chain methods.