MySQL Promise Extension

This module decorates the objects: connection and pool, from the mysql module, with more functionality and support to ES6 promises. It was written with the goal to define queries and transactions with less effort.

---

Installation

It's available through the NPM package:

npm install --save mysql (peer dependency)
npm install --save mysql-promise-extension

---

Usage

This module exports two factory functions. One for connections: createConnection(options), and another for the pool: pool(options). The options are the same as the options from the mysql module.

connection

The connection object returned by the factory function createConnection(options) is the same as the connection object from the mysql module, but with the extended functionality provided by this module. So, you have access to all original properties and functions from the mysql module in the case you need it.

The principal extended functions are:

• execute(query)
• executeTransaction(queryFunctions)

These functions: execute and executeTransaction, provide a simple way to perform queries in the database with less verbose code like: establish and terminate connections or handle the transactions commit/rollback.

The query object is the same as the used in the mysql module.

Also, provides the promisified version of the core functions:

• connectP(): Wrap function for connection.connect()
• endP(): Wrap function for connection.end()
• queryP(query): Wrap function for connection.query(query)
• beginTransactionP(): Wrap function for connection.beginTransaction()
• commitTransactionP(): Wrap function for connection.commit()
• rollbackP(): Wrap function for connection.rollback()

Examples with async/await

First of all, lets see how the execute and executeTransaction are used:

1const createConnection = require('mysql-promise-extension').createConnection
2const options = { ... }
3
4const getHobbiesAndUsers = (async () => {
5	const queryHobbies = 'select name from HOBBY'
6	const queryUsers = 'select name from USER'
7	const [hobbies, users] = await createConnection(options).execute([queryHobbies, queryUsers])
8	return { hobbies, users }
9})
10
11const getHobbiesFromUser = (async () => {
12	const queryHobbies = {
13		sql: 'select hobby_name as name from USER_HOBBY where user_id=?',
14		values: [1]
15	}
16	const hobbies = await createConnection(options).execute(queryHobbies)
17	return hobbies
18})
19
20const createUserAndHobby = (async () => {
21	const queryCreateUser = () => ({
22		sql: 'insert into USER (name) values(?);',
23		values: ['bob']
24	})
25
26	const queryCreateAssociationWithHobby = previousQueryResult => ({
27		sql: 'insert into USER_HOBBY (user_id, hobby_name) values(?,?);',
28		values: [previousQueryResult.insertId, 'soccer']
29	})
30
31	const result = await createConnection(options).executeTransaction([queryCreateUser, queryCreateAssociationWithHobby])
32	return result.affectedRows
33})

With the execute function, we only need to define the queries to pass as a argument and can be more than one.

The executeTransaction function is slightly different. As we can see, it receives an array of functions. Those functions can receive one argument, which is the result of the previous query. It's useful for cases where we need the result of the previous query. The functions return a query object identical to the object used in the execute function. The executeTransaction uses the waterfall implementation approach to preserve the sequential order. If any error is thrown during the transaction, then rollback will be done automatically.

Now, lets see with the promisified functions:

1const createConnection = require('mysql-promise-extension').createConnection
2const options = { ... }
3
4const getHobbiesAndUsers = (async () => {
5	const connection = createConnection(options)
6	try {
7		await connection.connectP()
8		const [hobbies, users] = await Promise.all([connection.queryP('select name from HOBBY'), connection.queryP('select name from USER')])
9		return { hobbies, users }
10	}
11	finally {
12		await connection.endP()
13	}
14
15	return null
16})
17		
18const getHobbiesFromUser = (async () => {
19	const connection = createConnection(options)
20	try {
21		await connection.connectP()
22		const hobbies = await connection.queryP('select hobby_name as name from USER_HOBBY where user_id=1')
23		return hobbies
24	}
25	finally {
26		await connection.endP()
27	}
28
29	return null
30})
31
32const createUserAndHobby = (async () => {
33	const connection = createConnection(options)
34	await connection.connectP()
35	try {
36		await connection.beginTransactionP()
37		
38		const createUser = await connection.queryP({
39			sql: 'insert into USER (name) values(?);',
40			values: ['bob']
41		})
42
43		const createHobby = await connection.queryP({
44			sql: 'insert into USER_HOBBY (user_id, hobby_name) values(?,?);',
45			values: [createUser.insertId, 'soccer']
46		})
47	
48		await connection.commitTransactionP()
49
50		return createHoby.affectedRows
51	}
52	catch(err) {
53		await connection.rollbackP()
54	}
55	finally {
56		await connection.endP()
57	}
58
59	return 0
60})

pool

If you want to use a pool of connections, you can get it through the factory function pool(options). The factory function returns an object identical to the MySql module's pool object, but, like the connection, is extended with more functionality.

The extended functions are:

• getConnectionP(): Wrap function for pool.getConnection()
• queryP(query): Wrap function for pool.query(query)
• execute(query)
• executeTransaction(queryFunctions)

Where the functions with suffix "P" are the promisified functions, and the last two functions: execute and executeTransaction, provide the same functionality as the functions, with same names, from the connection object.

Examples

With the execute and executeTransaction functions (the same use as in the connection):

1const options = { ... }
2const pool = require('mysql-promise-extension').pool(options)
3
4const getHobbiesAndUsers = (async () => {
5	const queryHobbies = 'select name from HOBBY'
6	const queryUsers = 'select name from USER'
7	const [hobbies, users] = await pool.execute([queryHobbies, queryUsers])
8	return { hobbies, users }
9})
10
11const getHobbiesFromUser = (async () => {
12	const queryHobbies = {
13		sql: 'select hobby_name as name from USER_HOBBY where user_id=?',
14		values: [1]
15	}
16
17	const hobbies = await pool.execute(queryHobbies)
18	return hobbies
19})
20
21const createUserAndHobby = (async () => {
22	const queryCreateUser = () => ({
23		sql: 'insert into USER (name) values(?);',
24		values: ['bob']
25	})
26
27	const queryCreateAssociationWithHobby = previousQueryResult => ({
28		sql: 'insert into USER_HOBBY (user_id, hobby_name) values(?,?);',
29		values: [previousQueryResult.insertId, 'soccer']
30	})
31
32	const result = await pool.executeTransaction([queryCreateUser, queryCreateAssociationWithHobby])
33	return result.affectedRows
34})

With the promisified functions:

1const options = { ... }
2const pool = require('mysql-promise-extension').pool(options)
3
4const getHobbiesAndUsers = (async () => {
5	const [hobbies, users] = await pool.queryP('select name from HOBBY; select name from USER;')
6	return { hobbies, users }
7})
8
9const getHobbiesFromUser = (async () => {
10	// Use the connection directly from the pool
11	const connection = await pool.getConnectionP()
12	try {
13  	const hobbies = await connection.queryP('select hobby_name as name from USER_HOBBY where user_id=1')
14		return hobbies
15	}
16	finally {
17		// Don't forget to release it
18		connection.release()
19	}
20
21	return null
22})
23
24const createUserAndHobby = (async () => {
25	const connection = await pool.getConnectionP()
26	try {
27		await connection.beginTransactionP()
28		
29		const createUser = await connection.queryP({
30			sql: 'insert into USER (name) values(?);',
31			values: ['bob']
32		})
33
34		const createHobby = await connection.queryP({
35			sql: 'insert into USER_HOBBY (user_id, hobby_name) values(?,?);',
36			values: [createUser.insertId, 'soccer']
37		})
38
39		await connection.commitTransactionP()
40
41		return createHobby.affectedRows
42	}
43	catch(err) {
44		await connection.rollbackP()
45	}
46	finally {
47		connection.release()
48	}
49
50	return 0
51})

---

Bugs/Requests

GitHub issues

Todo List:

• Create the proper documentation.
• Able to extend the PoolCluster functionality.