Security News
tea.xyz Spam Plagues npm and RubyGems Package Registries
Tea.xyz, a crypto project aimed at rewarding open source contributions, is once again facing backlash due to an influx of spam packages flooding public package registries.
mysql-chassis
Advanced tools
Readme
A promise-based API for mysqljs (mysqljs
is formerly called node-mysql
). It provides easy SQL methods including:
It also provides a middleware layer for db.onBeforeResults and db.onResults.
Key features:
SELECT ... LIMIT BY 1
(see middleware)npm install --save mysql-chassis
Quickstart Example:
// Non-ES6
// var MySQl = require('mysql-chassis').default;
// ES6
import MySQL from 'mysql-chassis';
const db = new MySQL({
database: 'databasename',
user: 'username'
});
Note that if you don't provide
password
orhost
options, MySQL Chassis will pass an empty string as the password andmysqljs
already passeslocalhost
by default.
The options passed in are a blend of MySQL Chassis and mysqljs
options. Any options that mysqljs
createConnection() or createPool() can receive will be passed through. Access to the mysqljs
underlying connection (pooled or un-pooled) is given as follows:
const db = new MySQL({
database: 'databasename',
user: 'username'
});
// mysqljs' connection object
db.connection
As stated before, the options passed into MySQL Chassis are a blend of MySQL Chassis options and the underlying mysqljs
library. See their connection options for more details. For MySQL Chassis, here are the options:
password
: Even though this is a mysqljs
option, we just want to note that if this is omitted, then null
is sent to mysqljs
.sqlPath
: A filepath where SQL files can be found. This is used by selectFile()
and queryFile()
. If no value is passed. Default value is ./sql
retryLimit
: How many times should MySQL Chassis try to re-connect if a connection is not made initially, or if a connection is lost. Default value is Infinity
transforms
: An object for transform settings. See Transforms below.Here's a more elaborate example of what your connection to MySQL Chassis might look like
import MySQL from 'mysql-chassis';
import path from 'path';
const db = new MySQL({
host: process.env.DB_HOST,
database: process.env.DB_NAME,
user: process.env.DB_USER,
password: process.env.DB_PASS,
sqlPath: path.join(process.cwd(), './sql')
});
db.on('connectionAttempt', tries => {
console.log(`MySQL Chassis: Trying to connect. Try: ${tries}`)
});
db.on('connectionSuccess', tries => {
console.log(`MySQL Chassis: Connection Success. Try: ${tries}`)
});
db.on('connectionError', err => {
console.error('MySQL Chassis: Could not establish connection. Code:', err.code)
});
db.on('connectionLost', err => {
console.error('MySQL Chassis: Connection was lost. Code:', err.code)
});
db.on('connectionTriesLimitReached', tries => {
console.error(`MySQL Chassis: Quit trying to connect after ${tries} tries`)
});
db.on('sqlError', err => {
console.error(`MySQL Chassis: SQL Error`, { SQL: err.sql, Code: err.code })
});
// SELECT a user
const userId = 1
db.selectFile('SELECT * FROM user WHERE user_id = :userId LIMIT 1', { userId })
.then(row => console.log(row))
.catch(err => console.error(err));
export default db;
See the .queryFile()
and .selectFile()
method below for an example of how to use SQL statements as files instead of inline strings.
All query execution methods return a promise with the results of the SQL:
db.query('SELECT * FROM user').then(function(results) {
console.log(results)
}).catch(err => {
console.log(err)
})
When a SELECT
statement is passed in, the results
will contain the following properties:
rows
: The same data that mysqljs
would give youfields
: The same data that mysqljs
would give yousql
: The SQL which was executed.For non-SELECT
statements, the results
returned will be the same as mysqljs
results which can contain any of the following properties depending on what type of SQL was performed:
affectedRows
insertId
changedRows
fieldCount
serverStatus
warningCount
message
In addition to these, there will also be a sql
property returned in the results
. The purpose of returning the SQL statement in the results is mostly for debugging. Many of the methods below allow you to do PDO-Style bound parameters, and some methods will even write your SQL for you if you choose to use those features. So it's nice to know what eventual SQL was executed.
If there's an error, the promise .catch(err)
will give you:
err
: The error provided by mysqljs
sql
: The SQL which was executedquery()
can be used to execute any type of SQL. If the query is an SELECT
statement, you can access the rows returned via result.rows
:
db.query('SELECT * FROM user')
.then(result => console.log(result.rows));
If you need to pass dynamic values into your query, use the bindValues
option which will properly escape the values with mysqljs
's connection.escape()
method.
const bindValues = { id: 1 };
db.query('SELECT * FROM user WHERE user_id = :id', bindValues);
When you use the bindValues
option, you'll also use placeholders in your SQL (such as :id
) to map where the values should be placed.
Works just like query()
except it allows you to pass a filename instead of SQL directly. The filename will be appended to your sqlPath
settings (configured at the time of connection, see above)
As an example:
const bindValues = { id: 1 };
db.queryFile('somefile', bindValues);
This assumes somefile.sql
exists in the sqlPath
folder and looks like this:
SELECT * FROM user WHERE user_id = :id
If the file exists, .queryFile()
will behave exactly like .query()
Also, if you want to organize your SQL files into sub folders of the sqlPath
, you can access those files as:
const bindValues = { id: 1 };
db.queryFile('path/to/somefile', bindValues)
path/to
in this case is relative to the path supplied by sqlPath
.
Note that the filename can written with or without the
.sql
extension. If no extension is provided, then.sql
will be added to your filename.
Works just like .query()
except it will return only the rows
from the promise instead of a results
object that contains rows
with other meta data. Also it's only meant to be used on SELECT
statements.
Use .select()
over .query()
if
SELECT
statement, and.query()
As a proof of concept, these two method calls would output the same data for rows
:
db.select('SELECT * FROM user')
.then(rows => console.log(rows))
db.query('SELECT * FROM user')
.then(results => console.log(results.rows))
Works just like .queryFile()
in the sense that you can pass a filename in, but works like .select()
in the sense of how it returns rows
instead of results
.
Creates a SELECT ${fields} FROM ${table} ${where}
statement and runs it through .select()
fields
: Can be a comma delimited string or an array of strings. Either way, the fields will be normalized with backticks (in case you have any MySQL reserved words) and trimmed of whitespace.table
: Must be a string.where
: (optional) Uses .sqlWhere()
to build a WHERE
. See docs below for more on .sqlWhere()
.This method will write your INSERT
statement for you and then return the results of .query()
. Here's how we can execute an INSERT
statement for a user with name
and email
fields:
const insertValues = {name: 'Brad', email: 'brad@foobar.com'};
db.insert('user', insertValues)
.then(results => console.log(results.insertId));
The INSERT
statement executed would be:
INSERT INTO user
SET `name` = 'Brad', `email` = 'brad@foobar.com'
Allows multiple inserts to be performed in one SQL statement (better for performance than looping and creating individual inserts when many are needed). Similar to:
INSERT INTO `some_table`
(col1, col2, col3)
VALUES
(1,2,3),
(4,5,6),
(7,8,9)
table
: Must be a string.values
: Must be an array of objects, where each object represents the column name and values to be inserted into a new row. Note that the order of the properties in values
does not determine their placement in the SQL statement. The order that is used in the SQL statement depends on columnTemplate
and the algorithm will map the keys of the values
objects to the columnTemplate
.columnTemplate
: (optional) Must be an array of unique column names. This will be used as the "columns" section of the SQL statement. If not provided, this method will use the fields of the first object in values
to make the columnTemplate
Here's an example:
const insertValues = [
{ datetime_added: 'NOW()', first_name: 'Brad' },
{ first_name: 'Dave', last_name: 'Smith', datetime_added: 'NOW()' }
];
db.insertMultiple('user', insertValues, ['first_name', 'last_name', 'datetime_added'])
.then(response => console.log(response))
.catch(err => console.log(err));
As you can see, the "uniformity" of the two objects for values
do not match. That is okay in this case because we provided the last argument, an array indicating which columns we want. However, without that array, we would not get the desired result since the first object's keys datetime_added
and first_name
would be used as the column template, and therefore leaving the first_name
out of the second insert. So always provide the third columnTemplate
argument if you can.
This method will write your UPDATE
statement for you and then return the results of .query()
. Here's how we can execute an UPDATE
statement for a user to update name
and email
fields:
const updateValues = { name: 'Brad', email: 'brad@foobar.com' };
const whereClause = { user_id: 1, active: true };
db.update('user', updateValues, whereClause).then(function(results) {
console.log(results.changedRows)
});
The UPDATE
statement executed would be:
UPDATE user
SET `name` = 'Brad', `email` = 'brad@foobar.com'
WHERE `user_id` = 1
AND `active` = true
See more on .where()
below.
Same as .insert()
, but if they primary key already exists, then the INSERT
will be ignored. This will not produce an error or warning.
This relies on MySQL's INSERT IGNORE
feature.
Attempt an INSERT
statement, but of the primary key already exists and therefore the record cannot be inserted, then switch to an UPDATE
statement.
This relies on MySQL's ON DUPLICATE KEY UPDATE
feature.
This method will write your DELETE
statement for you and returns the same promise as query()
.
const whereClause = { user_id: 1, active: true };
db.delete('user', whereClause)
.then(results => console.log(results.affectedRows));
The DELETE
statement executed would be:
DELETE FROM user
WHERE `user_id` = 1
AND `active` = true
See more on .sqlWhere()
below.
This method is normally used by other API methods, such as .update()
, and .delete()
. You can also use it directly:
console.log(db.sqlWhere({
user_id: 1
active: true
})) // outputs: WHERE `user_id` = 1 AND `active` = true
Values passed in will be escaped using mysqljs
's connection.escape()
method.
If a string is passed in, the string will be returned without changes and without escaping. This allows you to write custom "where-clauses" as needed:
db.update('user', updateValues, 'WHERE user.datetime_added < NOW()');
For custom functionality, you can add middleware to be ran before or after queries are executed:
db.onBeforeQuery((sql, bindValues) => {
// Here you can modify the SQL before it is ran
return sql;
});
db.onResults((sql, results) => {
// Here you can modify the results before they are returned
return results;
});
Example 1: mysqljs
always returns an array of rows regardless of how many rows are returned. But if you wanted to modify the results such that when the SELECT
statement has a LIMIT 1
at the end, then it will just return an object for the one row, then this is how that could be done:
db.onResults((sql, results) => {
if (results.length !== 1) return results;
return /^SELECT\s(.|\n)+LIMIT 1$/g.test(sql.trim()) ? results[0] : results;
});
Example 2: If you feel inclined to treat your SQL files as templates which can be dynamic depending on the bindValues
, you can use middleware with ejs templates
db.onBeforeQuery((sql, bindValues) => {
sql = ejs.compile(sql)(bindValues);
return sql;
});
Now, your SQL statements can be written as follows:
# file.sql
SELECT *
FROM user
WHERE user_id = :id
<% if (active) { %>
AND active = true
<% } %>
Now, running the following will result in the AND active = true
part of the SQL running
db.select('file', { id: 1, active: true });
Transforms are a means of helping .insert()
and .update()
methods know what to do when then encounter values that won't go well with MySQL. The transforms object is a map of values that .insert()
and .update()
could encounter, and what real values we'd rather use in the creation of the SQL statement.
The default transforms are:
transforms: {
undefined: 'NULL',
'': 'NULL',
'NOW()': 'NOW()',
'CURTIME()': 'CURTIME()'
};
As an example, let's say we pass undefined
or an empty string into our .insert()
method:
db.insert('user', { name: '', email: undefined });
Ideally for MySQL, you would want those values transformed to MySQL's NULL
# With Transforms (ideal)
INSERT INTO user
SET `name` = NULL, `email` = NULL
# Without Transforms (not ideal)
INSERT INTO user
SET `name` = '', `email` = ''
Note that escaping does not occur on transformed values. The value of the transform is placed directly inside the SQL statement whenever a bindValues
value matches a transform key.
As another example, let's say you want to be able to pass the NOW()
or CURTIME()
MySQL functions as values into your .insert()
or .update()
methods. As you can see, the transforms allow you to write:
db.insert('user', {name: 'Brad', datetime_added: 'NOW()'});
Which will result in:
# With Transforms (ideal)
INSERT INTO user
SET `name` = 'Brad', `datetime_added` = NOW()
# Without Transforms (will cause an error if datetime_added expects a valid date)
INSERT INTO user
SET `name` = 'Brad', `datetime_added` = 'NOW()'
You can modify the default transforms or add your own at connection time:
const db = new MySQL({
database: 'databasename',
user: 'username',
transforms: {
'lookForThisValue': 'replaceWithThisValue', // Custom
'': 'Empty Value' // Override default
}
});
FAQs
Wrapper code for node-mysql
The npm package mysql-chassis receives a total of 22 weekly downloads. As such, mysql-chassis popularity was classified as not popular.
We found that mysql-chassis demonstrated a not healthy version release cadence and project activity because the last version was released a year ago. It has 2 open source maintainers collaborating on the project.
Did you know?
Socket for GitHub automatically highlights issues in each pull request and monitors the health of all your open source dependencies. Discover the contents of your packages and block harmful activity before you install or update your dependencies.
Security News
Tea.xyz, a crypto project aimed at rewarding open source contributions, is once again facing backlash due to an influx of spam packages flooding public package registries.
Security News
As cyber threats become more autonomous, AI-powered defenses are crucial for businesses to stay ahead of attackers who can exploit software vulnerabilities at scale.
Security News
UnitedHealth Group disclosed that the ransomware attack on Change Healthcare compromised protected health information for millions in the U.S., with estimated costs to the company expected to reach $1 billion.