Automatically deleting PostgreSQL databases matching a pattern.

This is a follow-up to Setting up database connections with Knex and Jest.

In the previous article, we worked on setting up database connections with Knex and Jest. Basically, we created an environment that spins up a new database at the start of each Jest worker, truncates it after each test, and destroys it after all of the tests have been run.

The problem with this approach is sometimes tests fail, or you cancel the execution of the suite. When this happens, sometimes the suite isn’t cleaned up properly and that database lingers around. This is annoying because now whenever you login to your database manager to query for some juicy data, you’ve got all of these extra databases sitting around wasting valuable screen space.

So, let’s get rid of them!

Since the script we wrote just generates UUIDs as the names of the test databases, we should be able to just delete all databases where their name matches a UUID regex. So all we need is a script to query PostgreSQL for the current list of databases, filter that list based on the UUID regex, and then delete each database.

And here is that lovely script:

#!/usr/bin/env zsh

PATTERN=${2:='%-%-%-%-%'}

psql -c "copy (select datname from pg_database where datname like '$PATTERN') to stdout" | while read line; do
  echo "$line"
  dropdb "$line"
done

And that’s it! It’s a pretty simple little script.

First, we define the pattern (this will use the like operator inside PostgreSQL) to look for a string with 4 groups of characters, separated by a dash. That will definitely match any valid UUID.

We then run a query to list the database names from PostgreSQL that match the pattern, and then call the dropdb command on them. This command comes with any standard PostgreSQL installation, so luckily we don’t need to write any extra query magic!

Before dropping the database, I just echo the name of the database so I’m aware of what I’m dropping.

But there’s a problem. I probably want to be able to approve the names of the databases that are being dropped before performing the operation, so I don’t accidentally remove something that I don’t mean to. So let’s update the script to accomplish this:

psql -c "copy (select datname from pg_database where datname like '$PATTERN') to stdout" | while read line; do
  echo "$line"
  if [ "$1" = "yes" ]; then    dropdb "$line"  fidone

Now, the script only actually drops the databases whenever you call it with “yes” after the name of the command. This means you can call it by itself first, verify the list of databases it’s going to delete, and then call it again with the “yes” argument.

Okay! So take that fancy script, and put it somewhere on your machine where you’ll be able to remember where to call it. For me, I put it in /usr/local/bin/cleandbs.

Make sure to also give it execution permissions!

chmod a+x /usr/local/bin/cleandbs

Then, to get the list of databases the script will delete (but not actually delete them), just run cleandbs in your terminal. Review the list, make sure it’s not going to delete anything crazy, and then run cleandbs yes to actually delete them.

If you notice that the script is trying to delete databases you’re wanting to keep, try adjusting the PATTERN variable inside the script to be something more appropriate.