sharp-db


Classes for running SQL and building select queries for MySQL in Node

Installation

``bash
npm install sharp-db
`

Table of Contents

* Db
* Connection
* Instantiation
* SSH Tunneling
* Basic Use
* Bindings
* Methods
* Useful Query Options
* Solutions to Common Problems
* Select
* Select.parse()
* Building the Query
* Fetching Data
* Counting Results
* Set the Db instance
* Dependent Data
* Other Methods
* Select.parse() Limitations
* DataBroker
* Use in Integration Tests
* Insertions
* Deletions
* SqlBuilder
* Function List
* QueryLogger
* Logging queries
* How to Contribute
* ISC License

Db

$3

Connection can be configured with environmental variables or in the constructor.

| Option | ENV name | Default |
|---|---|----------------|
| host | DB_HOST | 127.0.0.1 |
| port | DB_PORT | 3306 |
| user | DB_USER | root |
| password | DB_PASSWORD | _empty string_ |
| database | DB_DATABASE | undefined |
| charset | DB_CHARSET| utf8mb4 |

See node's mysqljs for other options.

$3

Connect to MySQL server

`js
const { Db } = require('sharp-db');
// read options from ENV
const db1 = Db.factory();

// specify options in constructor
const db2 = new Db({
host: '127.0.0.1',
user: 'root',
password: '',
port: 3306,
});

// instance that was last created
const db2Again = Db.factory();

// Don't forget to close the connection when done
db1.end();
db2.end();
`

#### Auto factory and end

You can use await Db.withInstance(db => / do stuff with db /)
to get an instance, do something, and then close the connection.

`js
const { Db } = require('sharp-db');

// read options from ENV, instantiate and call db.end() automatically
const { error, domain } = await Db.withInstance(async db => {
const sql = 'SELECT email FROM users WHERE id = 5';
const { results: email } = await db.selectValue(sql);
return {
domain: email.split('@').pop(),
};
});
`

Db.withInstance() will return one of the following:

1. { error } where error is an object from mysql2. Full docs. Summary:
- error.sqlMessage The textual description of the error
- error.code The string error code such as PROTOCOL_CONNECTION_LOST
- error.errno The associated number code
- error.fatal True if the error caused the connection to close
- error.sql The full SQL of the failed query
- error.sqlState The five-character SQLSTATE code

2. Whatever value is returned from the handler. We suggest always returning an object, expecting the caller to check .error.

WARNING: Your handler function must return a promise that resolves AFTER
your query has returned a result. Failing to do so will result in db.end()
being called before your query is run. You may see an Error similar to the
following:

`[ERROR] -1 (N/A): Can't add new command when connection is in closed state`

For example:
`js
// WILL FAIL:
const { error } = Db.withInstance(db => {
db.insertInto('users', user);
});

// WILL SUCCEED:
const { error, results } = Db.withInstance(db => {
return db.insertInto('users', user);
});
// ALSO OK:
const { error, newUserId } = Db.withInstance(async db => {
const { insertId } = await db.insertInto('users', user);
return {
newUserId: insertId,
};
});
`

$3

Connect to MySQL through an SSH tunnel

`js
const { Db } = require('sharp-db');
const db = Db.factory({
// MySQL connection as first argument
host: '127.0.0.1',
port: 3306,
user: 'root',
password: '',
}, {
// SSH connection as second argument
host: 'example.com',
port: 22,
user: 'ubuntu',
privateKey: '~/.ssh/example.com.pem',
});
`

SSH Tunnel Options

| Option | ENV name | Default |
|--------------|--------------------|-------------|
| host | DB_SSH_HOST | "localhost" |
| port | DB_SSH_PORT | 22 |
| user | DB_SSH_USER | _none_ |
| password | DB_SSH_PASSWORD | _none_ |
| privateKey | DB_SSH_PRIVATE_KEY | _none_ |
| localPort | DB_SSH_LOCAL_PORT | 12346 |

See all options in ssh2's npm package.

$3

All code examples below assume the Db instance has been stored in db.

#### Plain select queries

`js
const { Db } = require('sharp-db');
const db = Db.factory();
const { query, results, fields } = await db.select('SELECT * FROM users');
// query is the final query executed after value binding
// results is an Array of objects representing the query results
// fields is an Array of objects representing the columns that were returned

// Don't forget to close the connection when done
db.destroy();
`

Relevant properties of each fields item:

| Item | Description | Example |
|---|---|---|
| characterSet | Character set constant | 45 |
| encoding | Character set name | utf8 |
| name | Name of column | my_column |
| columnLength | Number of bytes of field | 400 |
| columnType | Data type constant | 253 |
| flags | Field flag constant | 33 |

$3

Question-mark and colon-prefixed bindings are supported.

#### Binding with Question Marks

`js
const sql = 'SELECT * FROM users WHERE is_active = ? AND department_id = ?';
const { results: users } = await db.select(sql, true, 5);
`

#### Named Bindings

`js
const sql = 'SELECT * FROM users WHERE is_active = :isActive AND department_id = :departmentId';
const { results: users } = await db.select(sql, {
isActive: true,
departmentId: 5,
});
`

#### Binding data types

`js
const { results: users } = await db.select(sql, {
isActive: true, // Boolean
departmentId: 5, // Number
createdAt: '2020-02-14', // Strings
statusCode: [1, 2, 3], // Arrays e.g. IN(1, 2, 3)
deletedAt: null, // null e.g. NULL
});
`

$3

#### selectFirst(sql, ...bindValues)

Get only the first row.

`js
const { results: row } = await db.selectFirst(sql);
`

Example results: { id: 1, name: "John" }

#### selectValue(sql, ...bindValues)

Get only the first column of the first row.

`js
const { results: value } = await db.selectValue(sql);
`

Example results: "John"

#### selectHash(sql, ...bindValues)

Get an Object with column-value pairs.

`js
const { results: hash } = await db.selectHash(sql);
`

Example results: { "1": "John", "2": "Jane" }

#### selectList(sql, ...bindValues)

Get an Array of values for the first column of the first row.

`js
const { results: list } = await db.selectList(sql);
`

Example results: ["John", "Jane"]

#### selectExists(sql, ...bindValues)

Return true if query returns any rows.

`js
const { results: doesExist } = await db.selectExists(sql);
`

Example results: true

#### selectIndexed(indexColumn, sql, ...bindValues)

Return an Object where every result row is indexed by the given field.

`js
const { results: usersById } = await db.selectIndexed('id', sql);
`

Example results:
`js
results = {
"1": { id: 1, name: "John" },
"2": { id: 2, name: "Jane" },
}
`

#### selectGrouped(groupColumn, sql, ...bindValues)

Return an Object where every result row is indexed by the given field.

`js
const { results: usersGroupedByOrg } = await db.selectGrouped('org', sql);
`

Example results:
`js
results = {
"Marketing": [
{ id: 1, name: "John", org: "Marketing" },
{ id: 2, name: "Jane", org: "Marketing" },
],
"Finance": [
{ id: 3, name: "Jose", org: "Finance" },
],
}
`

#### selectOrCreate(table, criteria[, newValues])

Select a record or create a new record. Good when normalizing data that is frequently referenced.

For example, say I have a table hits with a column url_id and a table urls with columns id and url.

I want to add a new hit record with a given URL. You might write this:

`js
const newHit = {
date: '2021-10-15 17:43:24',
url: 'https://example.com',
}

const { results } = await db.selectOrCreate('urls', { url: newHit.url });

await db.insert('hits', {
date: newHit.date,
url_id: results.id,
});
`

$3

SQL can actually be an Object with options.

`js
const options = {
sql:
SELECT users., avatars.
FROM users
INNER JOIN avatars ON avatars.user_id = users.id
WHERE users.is_active = ?
,
// kill query if not completed within 30 seconds
timeout: 30000,
// return records with keys users and avatars with their own fields nested underneath
nestTables: true,
// you can also bind values here using question marks
values: [true],
};
const { results } = await db.select(options);
`

#### nestTables Example

Given a query of:

`sql
SELECT users., avatars.
FROM users
INNER JOIN avatars ON avatars.user_id = users.id
WHERE users.is_active = ?
`

nesting tables will return a data structure such as:

`js
results = [
{
users: {
id: 1,
name: 'John Doe',
is_active: true,
},
avatars: {
id: 101,
user_id: 1,
url: 'http://example.com/john.png'
}
},
{
users: {
id: 2,
name: 'Jane Doe',
is_active: true,
},
avatars: {
id: 102,
user_id: 2,
url: 'http://example.com/jane.png'
}
}
]
`

#### selectFrom(table, fields, values)

Build and run a simple select statement.

`js
const { results } = await db.selectFrom('users', ['fname','lname'], {
'id >': 5,
is_active: true,
department_id: [1,2],
});
`

#### insert(sql, ...bindVars)

Run an insert statement; return the id of the new record if applicable.

`js
const { insertId } = await db.insert("INSERT INTO users SET name='John', email='john@example.com'");
`

#### insertInto(table, values)

Build and run an insert statement; return the id of the new record if applicable.

`js
const { insertId } = await db.insertInto('users', {
name: 'John',
email: 'john@example.com',
});
`

#### insertExtended(table, rows)

Build and run an extended insert statement; return the id of the last record if applicable.

`js
const { insertId } = await db.insertExtended('users', [
{ name: 'John', email: 'john@example.com' },
{ name: 'Jane', email: 'jane@example.com' },
]);
`

#### insertIntoOnDuplicateKeyUpdate(table, insert, update)

Build and run an insert statement; return the id of the new record if applicable.

`js
const { insertId, affectedRows } = await db.insertIntoOnDuplicateKeyUpdate(
'users',
{
sso_ref: 'A123456',
name: 'Jane Doe',
created_at: '2020-02-02',
},
{
name: 'Jane Doe Carter',
modified_at: '2020-02-02',
}
);
`

#### update(sql, ...bindValues)

Run an update statement; return the number of affected rows.

`js
const { affectedRows } = await db.update(
"UPDATE users SET name = ? WHERE id = ?",
'Jane Doe Carter',
5
);
`

#### updateTable(table, set, where)

Build and run an update statement; return the number of affected rows.

`js
const { affectedRows } = await db.updateTable(
'users',
{ name: 'Jane Doe Carter' },
{ id: 5 }
);
`

#### delete(sql, ...bindValues)

Run a delete statement; return the number of affected rows.

`js
const { affectedRows } = await db.delete(
"DELETE FROM users WHERE id = ? LIMIT 1",
5
);
`

#### deleteFrom(table, where, limit)

Build and run a delete statement; return the number of affected rows.

`js
const { affectedRows } = await db.deleteFrom('users', { id: 5 }, 1);
`

#### query(sql, ...bindValues)

Run any type of statement.

`js
const { query, results, fields } = await db.query(
'SELECT * FROM users'
);
`

#### multiQuery(sql, ...bindValues)

Run multiple statements delimited by semicolon.

`js
const { query, results, fields } = await db.query(
'SELECT FROM users; SELECT FROM tags'
);
`

$3

#### Connection is in closed state

Error: Can't add new command when connection is in closed state

Make sure you use await your results before closing your connection.

#### ECONNRESET or error event

Error: read ECONNRESET or Emitted 'error' event on Client instance

Your SSH connection may have timed out. To keep connection alive, you
can send keepalive packets.

`js
const sshConfig = {
// ...
// How often (in milliseconds) to send SSH-level keepalive packets to the server (in a similar way as OpenSSH's ServerAliveInterval config option). Set to 0 to disable. Default: 0
keepaliveInterval: 30,
// How many consecutive, unanswered SSH-level keepalive packets that can be sent to the server before disconnection (similar to OpenSSH's ServerAliveCountMax config option). Default: 3
keepaliveCountMax: 120,
}
const db = new Db(mysqlConfig, sshConfig);
`

Select

A Select object represents a SQL SELECT query and allows dynamically adding
clauses including JOIN, WHERE, ORDER BY, LIMIT, OFFSET.

$3

The easiest way to define a base query is to use Select.parse(sql) and then
add criteria as needed.

`js
const { Select } = require('sharp-db');
const query = Select.parse(
SELECT u.id, u.fname, u.lname, u.email, p.phone
FROM users
LEFT JOIN phone_numbers p ON p.user_id = u.id
AND p.type = 'main'
WHERE u.is_active = 1
);
if (email) {
query.where('u.email', email);
}
if (areaCode) {
query.where('p.phone', 'LIKE ?%', areaCode);
}
query.sort(sortField);
query.limit(limitTo);
`

You can also define binding in the base query itself.

`js
const query = Select.parse(
SELECT u.id, u.fname, u.lname, u.email, a.city, a.zip
FROM users
LEFT JOIN addresses a ON a.user_id = u.id
WHERE a.state = :state
);
query.bind('state', state);
`

And you can bind multiple values at once.

`js
const query = Select.parse(
SELECT u.id, u.fname, u.lname, u.email, a.city, a.zip
FROM users
LEFT JOIN addresses a ON a.user_id = u.id
WHERE a.state = :state
AND a.city IN (:city)
);
query.bind({ state, city });
`

$3

The following are the most common methods for building queries.

- query.columns(columnNames) - Add column names to fetch
- query.column(columnName) - Add a column name to fetch
- query.table(tableName) - Specify the table in the FROM clause
- query.from(tableName) - Same as above
- query.innerJoin(expression) - Add an INNER JOIN expression
- query.leftJoin(expression) - Add a LEFT JOIN expression
- query.fullJoin(expression) - Add a FULL JOIN expression
- query.rightJoin(expression) - Add a RIGHT JOIN expression
- query.crossJoin(expression) - Add a CROSS JOIN expression
- query.leftOuterJoin(expression) - Add a LEFT OUTER JOIN expression
- query.fullOuterJoin(expression) - Add a FULL OUTER JOIN expression
- query.rightOuterJoin(expression) - Add a RIGHT OUTER JOIN expression
- query.groupBy(column) - Group by a column or expression
- query.where(column, operator, value) - Require column satisfy operator
- query.where(column, value) - Require column equal a value
- query.where(expression) - Add an arbitrary WHERE expression
- query.where(columnValuePairs) - Add multiple conditions
- query.whereBetween(column, twoValueArray) - Require value BETWEEN, < or >
- query.orWhere(conditions) - Specify multiple where()s joined by OR
- query.having(column, operator, value) - Having column satisfy operator
- query.having(column, value) - Having column equal value
- query.having(column, value) - Having column equal value
- query.having(expression) - Having an arbitrary expression
- query.orHaving(expressions) - Multiple having()s joined by OR
- query.orderBy(column) - Add ORDER BY clause
- query.sortField(column, mapNames) - Add ORDER BY clause with mapNames
- query.limit(num) - Limit by the given number
- query.offset(num) - Specify an offset
- query.page(num) - Automatically calculate offset based on limit and page

$3

The methods to fetch data mirror those of Db.

- query.fetch() - equivalent to db.select()
- query.fetchFirst() - equivalent to db.selectFirst()
- query.fetchHashed() - equivalent to db.selectHashed()
- query.fetchList() - equivalent to db.selectList()
- query.fetchValue() - equivalent to db.selectValue()
- query.fetchIndexed(byField) - equivalent to db.selectIndexed(byField)
- query.fetchGrouped(byField) - equivalent to db.selectGrouped(byField)

$3

One powerful feature of Select is that it can construct a count query to fetch
the number of results that would have been returned if there were no LIMIT.

`js
const query = Select.parse('SELECT id, name FROM users LIMIT 5');
const { results: users } = await query.fetch();
const { results: count } = await query.foundRows();
// will run the following query:
// SELECT COUNT(*) AS foundRows FROM users
`

$3

There are three ways to specify the Db instance to fetch data with:

1. query = Select.parse(sql, db)
1. query = new Select(db)
1. query.db = db

If no instance is specified, Db.factory() is used.

$3

A Select object can splice in sibling or child data for each row.

#### withSiblingData(propertyName, siblingSql)

Example:

`js
const query = Select.parse('SELECT id, name FROM users');
query.withSiblingData(
'homeAddress',
Select.parse(
SELECT * FROM addresses
WHERE addresses.user_id IN(:id)
AND addresses.type = 'home'
AND addresses.deleted_at IS NULL
),
);
query.withSiblingData(
'workAddress',
Select.parse(
SELECT * FROM addresses
WHERE addresses.user_id IN(:id)
AND addresses.type = 'work'
AND addresses.deleted_at IS NULL
),
);
const { results } = await query.fetch();
`

...and results for example may equal:

`js
results = [
{
id: 1,
name: 'John',
homeAddress: {
id: 11,
type: 'home',
is_active: 1,
user_id: 1,
street: '123 Any St.',
city: 'Any Town',
state: 'CA'
},
workAddress: {
id: 12,
type: 'work',
is_active: 1,
user_id: 1,
street: '123 Commerce Dr.',
city: 'Any Town',
state: 'CA',
},
},
{
id: 2,
name: 'Jane',
// rows without sibling data will be null
homeAddress: null,
workAddress: {
id: 12,
type: 'work',
is_active: 1,
user_id: 2,
street: '123 Tower Blvd.',
city: 'Any Town',
state: 'CA',
},
}
]
`

#### withChildData(propertyName, childSql)

Example:

`js
const query = Select.parse('SELECT id, headline, published_by FROM posts');
query.withChildData(
'theComments',
Select.parse('SELECT * FROM comments WHERE comments.post_id IN(:id)')
);
query.withChildData(
'theTags',
Select.parse(
SELECT posts_tags.post_id, tags.* FROM tags
INNER JOIN posts_tags ON posts_tags.tag_id = tags.id
WHERE posts_tags.post_id IN(:id)
)
);
query.withSiblingData(
'thePublisher',
Select.parse('SELECT id, name FROM users WHERE user_id IN(:published_by)')
);
const { results } = await query.fetch();
`

...and results for example may equal:

`js
results = [
{
id: 1,
headline: 'Turmoil in China',
published_by: 1001,
theComments: [
{
id: 11,
post_id: 1,
user_id: 101,
text: 'Sad to hear',
},
{
id: 12,
post_id: 1,
user_id: 102,
text: 'Hope it works out',
},
],
theTags: [
{
id: 101,
post_id: 1,
name: 'China',
},
{
id: 102,
post_id: 1,
name: 'Crisis',
},
],
thePublisher: {
id: 1001,
name: 'John',
},
},
{
id: 2,
headline: 'Syria at War',
// records with missing child data will hae empty arrays
theComments: [],
theTags: [],
thePublisher: null,
}
]
`

$3

Select has a few other useful methods.

- query.getClone() - Get an exact copy of this query object
- query.unjoin(table) - Remove a join expression
- query.escape(value) - Escape a raw value
- query.escapeQuoteless(value) - Escape a value but avoid wrapping in quotes
- query.toString() - Get prettified SQL
- query.normalized() - Get raw SQL (all whitespace is spaces)
- query.toBoundSql() - Get raw SQL with bindings replaced
- query.reset(field) - Reset a single aspect of the query (e.g. where, having)
- query.reset() - Reset query to an empty state

$3

Select.parse() uses regular expressions and is not a true parser. The intent
is to be fast and useful for 99% of situations.

Below are some limitations illustrated by example.

#### Nested Subqueries

Most subqueries can be parsed but sub-subqueries don't work.

`js
// WILL NOT WORK
const query = Select.parse(
SELECT * FROM categories_posts WHERE category_id IN(
SELECT id FROM categories WHERE client_id IN(
SELECT client_id FROM affiliations WHERE name LIKE :name
)
));
// WILL WORK
const subquery = Select.parse(SELECT id FROM categories WHERE client_id IN(
SELECT client_id FROM affiliations WHERE name LIKE :name
));
subquery.bind({ name: 'DogeCoin' });
const query = Select.parse(SELECT * FROM categories_posts WHERE);
query.where(category_id IN(${subquery}));
`

#### Keywords in Strings

If you need to use keywords in strings, use bindings.

`sql
-- WILL NOT WORK
SELECT id, CONCAT('WHERE ', expr) FROM users WHERE name = :name;
-- WILL WORK
SELECT id, CONCAT(:binding, expr) FROM users WHERE name = :name;
`

#### Nested OR and AND Clauses

Nested logic can't be parsed properly.

`sql
-- WILL NOT WORK
SELECT * FROM users
WHERE (
fname = :fname AND (
lname LIKE '%john' OR lname LIKE 'john%'
) OR (
id > 0 AND is_active IS NOT NULL
)
)
`

`js
// WILL WORK
const query = Select.parse(SELECT * FROM users);
query.orWhere([
"fname = :fname AND (lname LIKE '%john' OR lname LIKE 'john%')",
'id > 0 AND is_active IS NOT NULL',
]);
`

DataBroker

DataBroker is useful for inserting and deleting data that will needs to be
removed and restored.

$3

With integration tests, it may be useful to insert test data, run assertions
and then clean up the test data.

$3

Use the .insert() method to add records and then call .cleanup() to remove
those records.

Example:

`js
const { DataBroker, Db } = require('sharp-db');
const broker = new DataBroker(Db.factory(config));
const userId = await broker.insert('users', {
name: 'John',
is_active: true,
});
// the new user ID is also available at broker.ids
expect(broker.ids.users[0]).toBe(userId);
// ... integration test using userId ...
// then clean up all data
await broker.cleanup();
`

Example with composite key:

`js
const { DataBroker, Db } = require('sharp-db');
const broker = new DataBroker(Db.factory(config));
const userId = await broker.insert('posts_images', {
post_id: 1,
image_id: 2,
sort: 1,
}, { compositeKey: ['post_id', 'image_id'] });
// the new user ID is also available at broker.ids
expect(broker.ids.posts_images[0]).toEqual({
post_id: 1,
image_id: 2,
});
// ... integration test using userId ...
// then clean up all data
await broker.cleanup();
`

$3

Example:

`js
const { DataBroker, Db } = require('sharp-db');
const broker = new DataBroker(Db.factory(config));
// affectedRows will be the count of records deleted
const affectedRows = await broker.delete('users', { status_id: 5 });
// the deleted records are available at broker.deleted
expect(broker.deleted).toHaveLength(affectedRows);
// ... integration test ...
// then restore all the deleted all data
await broker.cleanup();
`

SqlBuilder

$3

The SqlBuilder objects builds SQL for Db methods such as selectFrom().
Below is a full list of methods if you want to build SQL outside of Db.

- SqlBuilder.quote(identifier)
- SqlBuilder.escape(value)
- SqlBuilder.selectFrom(table, fields, criteria, extra)
- SqlBuilder.selectBy(table, column, value)
- SqlBuilder.insertInto(table, row)
- SqlBuilder.insertIntoOnDuplicateKeyUpdate(table, insert, update)
- SqlBuilder.insertExtended(table, rows)
- SqlBuilder.updateTable(table, set, where)
- SqlBuilder.deleteFrom(table, where, limit)
- SqlBuilder.exportRows(table, rows, options)
- SqlBuilder.buildWhere(field, value)
- SqlBuilder.buildWheres(wheres)

QueryLogger

$3

Example:

`js
const { Db, QueryLogger } = require('sharp-db');
const logger = new QueryLogger();
const db = Db.factory();
logger.watch(db);
// ... run queries
logger.getLastQuery(); // last query
logger.getQueries(); // all queries
logger.clear(); // clear all logs
logger.unwatch(db); // stop capturing logs
``