Before Starting a Full Stack Project, Consider this About Your Database

Photo by Iiris L on Unsplash

You’ve heard about databases. A central place to keep all of your llamas (or data for normal folk). Maybe you chose one, be it sqlite, PostgreSQL, or Microsoft SQL. Even better is to include an ORM or sql meta-compiler so that you can switch to another database easily if your project requirements change.

Wait, switch to another database? Why would you do that?

Lots of reasons. It’s common for projects to start of strong on a NoSQL database, think they’re flying high by not defining a schema, and then have the devs pull out their hair when they find it’s not as efficient as they had intended.

Or maybe you’re within the throes of needing to switch to a database and you’re hyperventilating right now.

This article is for people not yet to that spot, though if you are hyperventilating, take a breather, then come back and read this article. It may still be helpful.

So, what’s one thing you need to incorporate into your full-stack application related to the database?

Migrations!

Why are Migrations needed?

If your project is like a lot of projects in its infancy, you probably didn’t think too hard about the database. You probably saw the raw connection library and thought it would be handy to write a bunch of database code on the initial app connection.

For example, consider the following snippet:

So far so good. This is exactly what the customer wanted, so we hit the big ol’ DEPLOY button and go home for the day.

But then say you want to update the product. Perhaps customers want a color palette associated with their account (not just a single color), and they want to say what’s their favorite.

So now we need to make 2 modifications to the original statement:

Beautiful! Just Beautiful!

Or maybe not? Because remember, we’re executing this startup code on the initial run, which means that the tables will be created each time. That’s all well and good for the color table, but what about the user table, specifically the favoriteColor column?

That’s not ever going to get created.

So then we have 3 options (well, including a 4th, which this article is about):

1. Include code to DROP the tables, then recreate, meaning all data will be lost. This is alright for initial development, but remember, this code is already deployed in production.
2. Inform other developers that they need to include the code UPDATE User ADD COLUMN favoriteColor CHAR(10) . But then you also need to inform the customers/users as well. And you created this app so users won’t have to run SQL code.
3. Include the code from #3 in the initial sql code. This is the best option, since it’s resilient against the table already existing and users won’t have to run the sql code.

But there’s still a problem. What if the program has restarted? We run into a logic problem:

• The table users already exists.
• The column favoriteColor already exists.
• Therefore, if we try to modify users again to add the favoriteColor column, the query will fail.
• And that means any other SQL we’ve included won’t execute.

As it stands there’s only 1 solution to this problem:

Instead of including the SQL query in 1 big long string, break it up into an array. Then execute one statement at a time, ignoring any errors that come up. THIS IS A BAD IDEA! Because we’re assuming that the only error we’ll get in a column conflict. We could have a syntax error, or be missing a CHAR length.

As a general rule of thumb, never swallow errors.

Instead, let’s keep track of the changes.

Introduction to Migrations

The bad news is that there’s no solution in the database to do this. The power of databases is in its ability to quickly index and retrieve data, not keep track of database changes.

In addition, we may decide that SQLite isn’t powerful enough (it usually isn’t), and we may decide to switch to PostgreSQL. The syntax is significantly different that we can’t just replace the SQLite connector with a PostgreSQL connector and run the same SQL code.

This packages typically employ an ORM, or Object Relational Mapper to allow for easy schema creation. If you are not yet familiar with ORMs, if you do a duckduckgo.com search for your language + “ORM” you should be able to come up with an ORM for your programming language compatible with your SQL database (most try to cover 90% of use cases like SQLite, MySQL, PostgreSQL, Microsoft SQL, and even Mongo!)

Many ORM (object relational model) packages include migration infrastructure (or you may find a migration package meant for specific architectures). Here is an example of a migration in Python:

In this case you can upgrade/downgrade the database with the alembic command.

But how does Alembic know what migration to apply?

Notice the revision and down_revision at the top of the file? Those are read by alembic to construct a directed graph of revisions. The state of the current migration is recorded in the database in its own table, so that the migrator can apply the upgrade or downgrade commands effectively.

Pretty neat, huh?

Include A Migration Strategy!

So, before you embark on your project development journey, spend a little time looking for a good migration strategy for your project.

The good news is that you’re not the first to use the tech stack you’re using. Someone else has done it before and have gone through the annoying bits. Hence people have created a solution to make it easier for you and me!

I hope you found this article helpful. Please share with someone you think will find value from it.

And if your team is looking for a team lead or senior developer, please reach out to me. You can schedule a free consultation at https://damngood.tech.