Know what to test using these recipes: Node service that calls a database
If you've ever gone through a JavaScript or Node unit testing tutorial, it can be difficult to find anything that shows you what you should be testing - what scenarios to cover, what to write tests for, etc.
It's easy to find tutorials that take you through very basic examples - things like how to write tests for addition functions or tests for checking object properties - but more difficult to find ones that go beyond the basics as well as cover real-world scenarios.
As a developer you know you "should" be writing tests if you want to be considered a "good developer". But if you don't know the kinds of test scenarios you should be looking out for, it can be hard to write them in the first place. And if you're completely new to writing tests, it's even more frustrating.
When you've got a feature you need to implement at work, deadlines are looming, and you're stuck when it comes to the tests, usually those tests don't get written at all.
Application of testing recipes
When I was learning how to write tests for my code, I faced this frustration too. But I learned what things to cover by a couple different means:
- getting bug reports for scenarios my tests should have covered
- reading through lots of tests for existing codebases, both work codebases as well as open source ones
I eventually noticed that a lot of tests covered a similar set of scenarios. Depending on what part of an application you're working on, there are things to look out for to make sure your tests - and by extension, code - cover so you can be sure any new changes introduced into the application later on will catch anything if it breaks.
These scenarios are distilled into a set of recipes, of which this post addresses one real-world bit of coding you'll likely have come across since it's very common - a Node service that queries a database and does something with that data.
Expect more of these posts to come, covering things like calling a 3rd party API, testing your own API, etc. There are only so many scenarios that can be digested within a single post so I'm planning on expanding to others in the future.
Ultimately, not only will you know what tests to write, the tests will also help inform the design/implementation of your code.
Before we get into it, a couple notes
Recipes won't get you all the way there - after all, every application and business domain is unique - but will give you a good base to work from. Just like you don't always end up following a cooking recipe 100%, these will give you a solid base to work from as well as serve as guidelines for things to keep in mind when testing.
Also, while these posts will mostly target testing things at the unit level, since they will be covering scenarios first and foremost, they will inevitably cover integration tests and end-to-end tests when called for.
Lastly, we'll be writing tests against a real database rather than a completely mocked one (although we will mock errors). Often the community is split on what the right approach is - real database vs mocks - but discussing this is out of scope of this post. Regardless, the scenarios worth covering are still the same.
And if you want this recipe as a quick and easy to reference cheatsheet, you can download it by signing up to my newsletter below:
Subscribe for the cheatsheet
Subscribe for the cheatsheet
No spam ever. Unsubscribe any time.
Initial setup
We'll be using a PostgreSQL database and rather than use an ORM here, we'll be querying the database through the node-postgres database driver. Even if you are using a different database, the scenarios covered in this recipe should be almost identical.
- Install Postgres (homebrew)
brew install postgres - Start Postgres
pg_ctl -D /usr/local/var/postgres start - Create database
createdb autoparts
Now that PostgreSQL is running and we've created a new database, let's get started with our first scenario.
To give you a preview, our directory structure will look like this in the end:
The complete code in final form can be downloaded here, but I recommend following along as you will see the code progress with each test. Sometimes we'll have to come back to change an implementation of a function as we add more tests and cover more scenarios.
Queries
Scenarios: --Do your tests cover the basic queries you'll perform on your database?-- --Does the correct data come back?--
We'll be adding the following functions to call our database:
createTable()dropTable()insert()select()selectOne()
Let's start with createTable().
In db-utils.js:
const { Client } = require('pg')
const getConnection = () => {
return {
host: 'localhost',
database: 'autoparts',
password: null,
port: 5432
}
}
const createTable = async function (tableName) {
const client = new Client(getConnection())
await client.connect()
return await client.query(`DROP TABLE IF EXISTS ${tableName};
CREATE TABLE ${tableName} (id SERIAL PRIMARY KEY, name VARCHAR(40) not null, price DECIMAL(10, 2));`)
}
module.exports = {
createTable
}
First we import pg, which is the npm package for node-postgres, and grab the Client from the package.
Next we add a getConnection() function where we specify the PostgreSQL database we want to use, where it's running and what port it's on.
Then we add the createTable() function that establishes the connection to the database and creates a table.
What test should we write for this? Remember that the scenarios we're testing here are that the queries perform the actions they are supposed to, given our functions. So we'll want to make sure the table was actually created in the database.
In db-utils.test.js:
const { createTable } = require('../src/db-utils')
describe('Database Utils', () => {
describe('createTable', () => {
it('should create the table in the database', async () => {
const res = await createTable('items')
// because we just created the table, no rows should exist
// the first res is actually the result from the DROP TABLE, so we take the second
expect(res[1].rowCount).to.be.null
})
})
})
In the test, we used createTable() to create the table, then confirmed it was added to the database, which covers our scenario for this function for now.
Now that we have the table, we'll want to make sure we can add data to it, which corresponds to an INSERT INTO query:
const insert = async function (tableName, itemName, price) {
const client = new Client(getConnection())
await client.connect()
return await client.query(`INSERT INTO ${tableName} (name, price) VALUES ('${itemName}', '${price}');`)
}
And the test, covering our scenario of creating the data in the table:
describe('insert', () => {
it('should insert an item into the table', async () => {
const res = await insert('items', 'steering wheel', 62.59)
expect(res.rowCount).to.equal(1)
})
})
Lastly, we'll want to make sure that we can actually fetch the data (this corresponds to a SELECT query):
const select = async function (tableName, limit = 'ALL', columns = '*') {
const client = new Client(getConnection())
await client.connect()
return await client.query(`SELECT ${columns} FROM ${tableName} LIMIT ${limit}`)
}
And the test, which relies on querying the data already created in the table with the insert() test:
describe('select', () => {
it('should select items from the table', async () => {
const res = await select('items')
expect(res.rows).to.deep.equal([ { id: 1, name: 'steering wheel', price: '62.59' } ])
})
})
Closing database connections
Scenarios: --When you establish a client connection to the database, does it close that connection after the query?--
Before we move on, if you've been following along by writing the code and running the tests on your machine you've probably noticed that the tests aren't exiting, they just hang there after they pass. This leads us to our next scenario to cover: making sure the connection to the database is closed!
I inherited a codebase once that was not closing connections to the database after executing queries, and when we ran it through performance testing it hit a memory leak in about 2 minutes of running. So always make sure you close your connections.
This is something that itself is kind of hard to write test code for, but the fact that the tests hang after running in and of itself serves as the test. Now that we know the connections aren't closing, let's fix that:
const createTable = async function (tableName) {
const client = new Client(getConnection())
await client.connect()
const res = await client.query(`DROP TABLE IF EXISTS ${tableName};
CREATE TABLE ${tableName} (id SERIAL PRIMARY KEY, name VARCHAR(40) not null, price DECIMAL(10, 2));`)
// end connection!
await client.end()
return res
}
const insert = async function (tableName, itemName, price) {
const client = new Client(getConnection())
await client.connect()
const res = await client.query(`INSERT INTO ${tableName} (name, price) VALUES ('${itemName}', '${price}');`)
// end connection!
await client.end()
return res
}
const select = async function (tableName, limit = 'ALL', columns = '*') {
const client = new Client(getConnection())
await client.connect()
const res = await client.query(`SELECT ${columns} FROM ${tableName} LIMIT ${limit}`)
// end connection!
await client.end()
return res
}
As you can see, calling await client.end() ends the connection. Now when you run the tests, Mocha finishes and returns to the command prompt.
Test cleanup
Scenario: --Making sure to clean up your database or table after tests run--
Our next scenario to cover in writing a real-world Node->DB service is not a code scenario but a test scenario. Because we're writing tests against an actual database rather than using mocks, we need to make sure we clean up after the tests run.
You can either drop the table or drop the database altogether, but for simplicity we'll just drop the table. Let's add a function for that to db-utils.js:
const dropTable = async function (tableName) {
const client = new Client(getConnection())
await client.connect()
await client.query(`DROP TABLE IF EXISTS ${tableName};`)
await client.end()
}
In our db-utils.test.js we'll add dropTable() to the after hook which is a good place to put cleanup actions since it runs after the tests for that describe block run:
after(async () => {
await dropTable('items')
})
You might have noticed that createTable() itself always drops the table if it exists before creating it, so even if we didn't have the dropTable() function we would be fine.
But remember, this post is addressing the scenarios you should be looking out for - and having a testing cleanup function like dropTable() is good practice to include for safety purposes.
On to the Node service itself
Now that we've got some database querying utils, it's time to work on the actual Node service, which will be an Item Service.
The theoretical application of this service is for an auto parts store (a store that sells things like steering wheels, oil, windshield wipers, etc.). So we likely need functionality for fetching all items (for creating a catalog), fetching item names (for advertising purposes), and getting a price for an item (for purchase). Remember, our items table has the structure of:
id SERIAL PRIMARY KEY, name VARCHAR(40) not null, price DECIMAL(10, 2)
Note: these functions are fairly oversimplified for demonstration purposes. The idea here is just to gain an understanding of the scenarios you'd want to cover.
So let's add the first Item Service function, fetching all items:
const { select } = require('./db-utils')
const fetchAllItems = async function () {
// this is a 'select all' because we aren't passing in a limit argument
// the db util select function defaults to LIMIT ALL if not limit arg is passed in
return await select('items')
}
As I added in the code comment, this is a fetch all because we aren't passing in a LIMIT amount.
For the test, we'll want to make sure it does, indeed, fetch all items in the table. For this, I recommend adding a small number of items to the table, so you can more easily check verify all were returned in the test. If you added anything more than a few items, it would be annoying to check that in the test. In the test below, we add two items in the before hook, then check that those two items are returned in the test.
const { createTable, insert, dropTable } = require('../src/db-utils')
const { fetchAllItems } = require('../src/item-service')
describe('Item Service', () => {
before(async () => {
// here we're doing some table setup stuff so that we can perform assertions later
// this is basically like running a fixture
await createTable('items')
await insert('items', 'steering wheel', 62.59)
await insert('items', 'windshield wiper', 23.39)
})
after(async () => {
await dropTable('items')
})
describe('fetchAllItems', () => {
it('should fetch all items from items table', async () => {
const items = await fetchAllItems()
expect(items.rows).to.deep.equal([
{id: 1, name: 'steering wheel', price: '62.59'},
{id: 2, name: 'windshield wiper', price: '23.39'}
])
})
})
Database down
Scenario: --What if the database is down?--
Before we move on from here, we need to address another very important scenario that you should look out for - what happens if the database is down? This is often called the "unhappy path", and let's address it now.
We need to wrap our call to the database in a try/catch block in order to catch this:
const fetchAllItems = async function () {
// this is a 'select all' because we aren't passing in a limit argument
// the db util select function defaults to LIMIT ALL if not limit arg is passed in
try {
return await select('items')
} catch(err) {
throw err
}
}
The test code for this is a bit trickier. You could test it manually by killing the connection to the database while the tests run, but then your tests that should pass under normal connection circumstances would fail, not to mention getting the timing exactly right would be mostly a matter of luck.
Instead, we'll change getConnection() in db-utils.js to be able to create a connection with bad data, thus "mocking" a situation in which the database would be down. In this case the database is up, but creating a bad connection simulates this.
Note: you could use Sinon, rewire, mockery, etc. for this but it's a bit trickier than the way I've done it here. And again, the most important thing is to understand the concept and the scenario in which you'd want to cover.
getConnection() in db-utils.js will now look like this:
const getConnection = () => {
if (process.env.UNHAPPY === 'true') {
return {
database: 'something that will throw bad connection',
password: 'this will result in unhappy path',
port: 3211
}
} else {
return {
host: 'localhost',
database: 'autoparts',
password: null,
port: 5432
}
}
}
And the Item Service tests will look like so:
describe('Item Service', () => {
before(async () => {
// here we're doing some table setup stuff so that we can perform assertions later
// this is basically like running a fixture
await createTable('items')
await insert('items', 'steering wheel', 62.59)
await insert('items', 'windshield wiper', 23.39)
})
// this resets our environment variable so the next test doesn't fail due to bad db connection
afterEach(() => {
process.env.UNHAPPY = false
})
after(async () => {
await dropTable('items')
})
describe('fetchAllItems', () => {
it('should fetch all items from items table', async () => {
const items = await fetchAllItems()
expect(items.rows).to.deep.equal([
{id: 1, name: 'steering wheel', price: '62.59'},
{id: 2, name: 'windshield wiper', price: '23.39'}
])
})
// this tests the error path
it('should catch error if database is down', () => {
process.env.UNHAPPY = 'true'
await expect(fetchAllItems()).to.be.rejected
})
})
})
Notice the 'should catch error if database is down' test. We trigger a database down scenario by setting the UNHAPPY environment variable to true, which causes getConnection() to use bad data, and then we reset the environment variable after each test so the next test doesn't fail due to a bad connection.
The line await expect(fetchAllItems()).to.be.rejected is what checks to make sure the try/catch block within the fetchAllItems() function itself is actually hitting the catch path, which throws the error.
Note: if we wanted to check the error message we could use await expect(fetchAllItems()).to.be.rejectedWith('Error message here'), but in this case the since we're faking an error, the error message will not necessarily be the one we would see if the database was down, so we just want to check that it handles the error.
rejectedWith comes from the npm package chai-as-promised.
Filtering returned data
Scenario: --Is appropriate data from query result filtered out?--
When you're working at the service level, the purpose it serves is to not only call the database, but to return that data in a way that makes sense for the client that will be calling that service. So imagine you have a table with lots of different columns, but the client only needs a few of them. In the service you'd want to filter the resulting querying down, which is our next common test scenario we need to cover.
In item-service.js, let's add the fetchItemNames() function.
const fetchItemNames = async function () {
try {
const items = await select('items')
return items.rows.map(({name}) => name.toUpperCase())
} catch(err) {
throw err
}
}
Here we only need the item names from the autoparts table, so we return an array with just the names.
describe('fetchItemNames', () => {
it('should return item names in upper case from items table', async () => {
const items = await fetchItemNames()
expect(items).to.deep.equal([
'STEERING WHEEL',
'WINDSHIELD WIPER'
])
})
it('should catch error if database is down', () => {
process.env.UNHAPPY = 'true'
await expect(fetchItemNames()).to.be.rejected
})
})
Another scenario to cover, which I skipped here just for simplicity, would be making sure that the data is formatted how you need it to be. In this case the names are upper-cased, but you might also construct an object, tacking on some properties to that object, or even perform some other business logic to get the data in a more usable form for the client.
Bad query
Scenarios: --What if no data is returned for the query?-- --What if the item does not exist?--
Lastly, let's add one more function to the Item Service - a getPrice() function:
const getPrice = async function (itemName) {
try {
const items = await selectOne('items', itemName)
if (items.rows.length) {
return items.rows.map(({price}) => price).pop()
} else {
throw Error('Either no items, or item name was wrong/does not exist')
}
} catch(err) {
throw err
}
}
For this function, what if we pass in an item name that does not exist? How should the service handle that? What if no data is returned for the query? We can cover those scenarios in the tests:
describe('getPrice', () => {
it('should return price for one item', async () => {
const price = await getPrice('windshield wiper')
expect(price).to.equal('23.39')
})
it('should catch error if item does not exist', async () => {
await expect(getPrice('oil')).to.be.rejectedWith(
'Either no items, or item name was wrong/does not exist'
)
})
it('should catch error if database is down', async () => {
process.env.UNHAPPY = 'true'
await expect(getPrice()).to.be.rejected
})
})
The first test - 'should return price for one item' - is our "happy path".
The next - 'should catch error if item does not exist' - as the name states, is our scenario if the item does not exist. Here, rather than just check to see if the Promise is reject like we did with the "database down" scenario, we check explicitly for the error message.
Now, you have a decision to make here when implementing the code for the function itself. You can either swallow the error and return an empty data structure, or you can throw the error and let the client handle it. How you approach it will depend on the needs of your application. The client might want to be aware of errors, or it might not want to be. It might just need nothing returned in that case.
Regardless, the test scenario will remain the same, only the function code (and by extension, the test code will change a bit).
Wrapping up
We covered several different scenarios you can use to help guide your unit tests. Next time you are writing a Node service that interacts with a database, use this "recipe" as a reference guide to help you if you aren't sure what to test. Of course, you may find more scenarios that are relevant to your application, but this recipe will cover a lot of the most common ones (and ones that are sometimes overlooked).
Here's the link to the complete code again.
And in order to make it even quicker for you the next time you're writing tests, I made a cheatsheet that covers these scenarios and when you'd want to use them, for easy reference. I also added a couple other scenarios like if you are using a connection pool rather than a single connection client, handling other unhappy paths, etc. Sign up below to receive it and you will also get the next posts in this series as I write them!
Subscribe for the cheatsheet
No spam ever. Unsubscribe any time.