Here’s an example MySQL library in Node.js using promises and Jest tests to create a table, populate it, and read it back:
First, make sure to install the mysql and jest packages:
1 2 3 4 5 6 | $ mkdir node-mysql-jest $ cd node-mysql-jest $ git init $ npm init $ npm install mysql $ npm install jest |
Next create a file named mysql.js and add the following code
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 | const mysql = require('mysql'); // Create a connection pool const pool = mysql.createPool({ host: 'localhost', user: 'root', password: 'password', database: 'testdb', }); function closeDatabase() { pool.end(); } // Create a function to execute SQL queries function query(sql, args) { return new Promise((resolve, reject) => { pool.query(sql, args, (err, rows) => { if (err) return reject(err); resolve(rows); }); }); } async function dropTable() { const sql = ` DROP TABLE IF EXISTS users `; await query(sql); } // Create a function to create a table async function createTable() { const sql = ` CREATE TABLE IF NOT EXISTS users ( id INT(11) NOT NULL AUTO_INCREMENT, name VARCHAR(255) NOT NULL, email VARCHAR(255) NOT NULL, PRIMARY KEY (id) ) `; await query(sql); } // Create a function to populate the table async function populateTable() { const sql = ` INSERT INTO users (name, email) VALUES ('John Doe', 'john.doe@example.com'), ('Jane Doe', 'jane.doe@example.com') `; await query(sql); } // Create a function to read the table async function readTable() { const sql = ` SELECT * FROM users `; return await query(sql); } // Export the functions for use in other modules module.exports = { dropTable, createTable, populateTable, readTable, closeDatabase }; |
This code exports three functions: dropTable, createTable, populateTable, readTable and closeDatabase which respectively create a new table, populate the table with sample data, and read the contents of the table.
Next, create a Jest test file named mysql.test.js and add the following code:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | const mysql = require('./mysql'); describe('MySQL library', () => { beforeAll(async () => { // Create the table and populate it with sample data await mysql.createTable(); await mysql.populateTable(); }); afterAll(async () => { // Delete the table after all tests have run await mysql.dropTable(); mysql.closeDatabase(); }); test('Reads the table', async () => { const rows = await mysql.readTable(); expect(rows).toHaveLength(2); expect(rows[0].name).toBe('John Doe'); expect(rows[0].email).toBe('john.doe@example.com'); expect(rows[1].name).toBe('Jane Doe'); expect(rows[1].email).toBe('jane.doe@example.com'); }); }); |
This test file imports the mysql module, creates the table and populates it with sample data before all tests, and deletes the table after all tests have run. It then tests that the readTable function returns the correct data.
To run jest make sure you have added the command to run jest in the scripts section of you package.json file. This is created automatically for you when you run npm init
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | { "name": "njs-mysql-jest", "version": "1.0.0", "description": "Example Node MySQL and Jest", "main": "app.js", "scripts": { "test": "jest" }, "author": "Keith", "license": "ISC", "dependencies": { "jest": "^29.5.0", "mysql": "^2.18.1" } } |
To run the tests, run the following command:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | $ npm test > njs-mysql-jest@1.0.0 test > jest PASS ./mysql.test.js MySQL library ✓ Reads the table (2 ms) Test Suites: 1 passed, 1 total Tests: 1 passed, 1 total Snapshots: 0 total Time: 0.243 s, estimated 1 s Ran all test suites. ➜ njs-mysql-jest git:(main) ✗ |
There you have a simple but effective starter for 10 creating a node.js MySQL library for your javascript app.