Check in with your database

In most web projects, work is thought of in terms of being frontend or backend. Depending on the size of the team you might have people dedicated to one of these parts, or it might be the same people doing both.

Very rarely, in smaller teams, will the database be looked after by a dedicated team. It is normally owned by the backend developers.

If you are part of a team that has dedicated database people, then this article isn’t for you. But if you are part of a team where the database is owned by developers, then read on!

Your database probably isn’t perfect

Creating and maintaining a database is hard, if you are a developer, you will almost always be thinking of the database in ways that it can help your code.

I know lots of developers who never look at the database, it is a complete black box to them. I include myself in this for many projects. I get onboarded, I run whatever setup is needed to create a database locally, and so long as it works, why would I care what is happening under the hood? (Spoiler alert, I’m going to share why I should care!).

A mixture of good database design being hard and developers needing to worry about, well, developing, leads to less than perfect databases.

Perfect isn’t always the goal

Before I continue, it is worth noting that often “works” is actually good enough, if you have a small system, or very little time to dedicate to the project, there is nothing wrong with “works”.

Where I’ve seen this end up biting teams is when a poor database decision ends up causing performance bottlenecks either to the end user or to a team that now need the database to do something different and their previous decisions have painted them into a corner. The rest of this article is going to address some of these issues.

How to check in with your database

When I say check in, I mean take a look at what makes up your database and make sure you are getting the most out of it. Here are some questions you can ask, and why you might want to ask them.

These aren’t in any particular order, and how easy/hard these are to think about will depend on your individual setup.

Is your database up to date?

Are the versions of the database you run on production, staging, during CI, and development the same? And are they are running a well supported version of the database?

If you are running different versions, you might be open to some very confusing times in the future if you introduce something that only works in newer versions of your database of choice. As a rule, I would advise picking the newest version you know to work and enforcing that everything uses this.

If your database isn’t running a supported version, you could well be missing out on security updates, and you will certainly be missing out on feature and performance improvements.

Does your database reflect your domain?

If you had to open up your database and understand what type of work your company did, could you? Would the table names and column names reflect the names your business uses? If they don’t then it will make it harder to reason about the database long term. I would consider starting a body of work to make naming consistent.

Are you enforcing consistency in the database?

It is common for your application code to validate certain properties of an object, a User needs to have an email and it needs to be unique. A ShopItem needs to have a quantity greater than or equal to zero. Things like this.

Your application code can protect your application from adding a ShopItem with quantity set to -10, but if your database doesn’t have the same check, there is a chance someone or something could make a ShopItem with an invalid quantity. This will manifest in different issues in your application, not least because if you have made the assumption a negative quantity is impossible, you probably won’t account for it in application logic.

The rule of thumb should be, if your application expects data to be a certain way, and it is possible to enforce at a database level, you should.

Are you enforcing consistency in the application?

The opposite of what I just talked about is also true, if you have a database table that has a required field, you should make sure your application also checks and enforces the restriction. It might seem obvious that a BlogPost needs a title, but if your database enforces a title but your code doesn’t, then you will eventually get confusing errors to dig into.

How easy is it to add or change your database tables?

Does your framework of choice make adding or changing tables easy? Or is there a lot of conversation and concern anytime the database is changed?

If your deployment set up doesn’t make it easy to edit the database schema without requiring downtime or additional steps, then it might be worth while spending some time understanding what could be changed to make this easier. Not only will you save time in the long term, you will benefit from all systems having the same database schema without needing developer intervention each time.

Are you using appropriate indexes?

Indexes allow your database to look up certain columns or groups of columns faster. Depending on the size of your database tables sometimes this can be orders of magnitude faster.

When creating tables many developers will consider this, if you have a User you know you will look up by email address, you might add an index at this point.

When the system changes, as systems like to do, you may forget to add an index on, for example, username when you build you a basic search page which only allows Users to be searched by username.

A lack of indexes often doesn’t show itself in development, or even staging, where the database table size is often smaller and the database isn’t under as much pressure as it is on production.

How easy is it to restore your database?

If you’ve got this far, I’m going to guess you’re a developer who is invested in having your database working as well as it could be. I’m going to further guess that it might be your job to restore the database should your production database ever become corrupt or go offline for some reason.

If you don’t know how your database backups work, or don’t know what the restore procedure might be for your database, I would suggest finding out and writing it down somewhere people in your business can see. It would also be good to test doing a restore on a staging environment just to make sure you can do this with as little stress as possible.

Tools to help

The tools you can use will very much depend on your application framework and database of choice. If you use Ruby on Rails, you will find the database consistency gem to be invaluable.

There may be similar for your chosen framework, and there are many tools that can help show when queries are slow, or opportunities to add indexes.