DEV Community: Hernan Reyes

Keeping track of database schema changes

Hernan Reyes — Mon, 20 Jun 2022 15:04:18 +0000

Overview

Have you ever wonder how do developers handle changes in the database schema of a project?, what do they do when a new table/column/index is added, or when a table is dropped, a column, an index and that. How they ensure that everyone involved in the project have all the changes applied to their database?. Well, for this, they keep track of a history of every change made to the database schema.

This was something I didn't know until I first work with other people and I read the README of the project and said that I have to run the “migrations” to have my database schema up-to-date. That made me ask my self: how it didn’t cross my mind handling something that obvious before?, well, was because I never change off computer or reformatted my pc, so it made me reinstall the hole project from zero to realize that I haven’t a way to restore my database without a backup.

My goal in this article is to show you how this database migrations works, so you know what to do when new changes are applied to the database, whether you’re working solo or with other people.

How it works?

The way it works is simple, you have to do the same you do when you keep a history of the project’s code, so we’ll need a version control system like GIT, then we can ensure that everyone involved in the project can have the database’s changes history up-to-date. Here I’ll show you two ways to handle the database migrations, but first let's see what they have in common:

You’ll write files that contain the SQL with a name format like: {version}_{description}.{up/down}.{ext}

the description can be a short explanation of what the migration file does, ex:
1. create_users_table
2. adds_birthdate_column_to_users_table
3. drops_country_column_from_users_table
The SQL to apply changes to the schema (known as up migration) and a SQL to reverse those changes (known as down migration)

That’s why in the previous point, the name format indicates whether is an up or down migration
ℹ️ Some libraries manage one file and with comments indicate the `up` SQL and `down` SQL
A way to run the migrations in order

For this, we make use of the version of the filename format, you can put a sequence number or the timestamp of when you created the file, so when we run the migrations we execute them in ascending order. This is needed because you may add migrations that depend on other migrations to be executed first.
Ensure we don’t run a migration twice

Libraries may create a table to keep a log of the executed migrations, so they don’t do it twice.
The extension of the file depends on the method you choose to handle the migrations

It can be a .sql file or the extension of your programming language

Now that we know what they have in common, let's see each method in action.

Pure SQL’s migrations

Here you’ll have to create a directory to store the .sql files, which will contain the changes made to the database schema and a way to reverse them. Following the format described in the previous section, you’ll have files like these:

Then in your migration files, just write the changes you want to make, for example the 000001_create_users_table.up.sql migration in the above image would have something like this:

CREATE TABLE users
(
    id         SERIAL       NOT NULL,
    first_name VARCHAR(100) NOT NULL,
    last_name  VARCHAR(100),
    email      VARCHAR(100) NOT NULL,
    password   VARCHAR(256) NOT NULL
);

and for the migration 000001_create_users_table.down.sql, the needed SQL to reverse the changes made by the up migration file:

DROP TABLE IF EXISTS users;

ℹ️ Some libraries can read the SQL files from sources like github, gitlab, aws s3, etc

For this method, you can use the next libraries:

Migrations with your programming language

Here applied the same conventions as in the Pure SQL’s migrations, but instead of having .sql migrations, you’ll have migrations with the extension of your programming language, in my case, I’ll be using Golang, so the previous structure of files I showed you earlier will look like this with a timestamp versioning instead of a sequence:

As you can see, unlike the previous method, here we only have 3 files, why is that?, it is because of the library I’m using, we indicate the up and down migration in the same file, here is the content for the users table:

func init() {
    goose.AddMigration(upCreateUsersTable, downCreateUsersTable)
}

func upCreateUsersTable(tx *sql.Tx) error {
    _, err := tx.Exec(`
        CREATE TABLE users
        (
            id         SERIAL       NOT NULL,
            first_name VARCHAR(100) NOT NULL,
            last_name  VARCHAR(100),
            email      VARCHAR(100) NOT NULL,
            password   VARCHAR(256) NOT NULL
        );`)
    if err != nil {
        return err
    }
    return nil
}

func downCreateUsersTable(tx *sql.Tx) error {
    _, err := tx.Exec("DROP TABLE IF EXISTS users;")
    if err != nil {
        return err
    }
    return nil
}

Here you have an up and down function which are passed to goose.AddMigration(), this will obviously be different depending on your programming language and library you’re using.
For this method, you can use the next libraries:

What if I’m mid-project and don’t have migrations?

For this, you can create a backup of the database schema, so you can add it as an initial migration, but you have to make sure that the library you use does not execute it in your local database (because you already have those changes), so you have to dig into the library to see how they keep track of the migrations that have been already run, the ones I know creates a table in your database, so you can start there.

Then you can start keeping track of the database changes. You want a new table?, add a migration, you want to drop a column?, add a migration, you want an index, add a migration, remember, every change to your database schema must have a migration.

Conclusion

Here I gave you an overview of database migrations, with two common approaches on how to handle them. Personally, I like to use the first method because I just have to worry on writing the SQL and not writing code to execute the migration with my programming language, I’ve been using this method for over 3 years now and never had the need to use the second one.

Hope you've learned something new today and start using these migrations if you’re not already, so when you join a project you are prepared to use them or teach to other developers how they work if they don’t know, as I didn’t 3 years ago.

If you want to know how to create and run the file migrations automatically refer to the libraries I mentioned, there they explain how to use them.

Thank you for reading and see you in the next article 👋

How the N+1 query problem can burn your database

Hernan Reyes — Wed, 15 Jun 2022 11:38:34 +0000

Overview

Have you ever watched a movie/series or experience your self going to a hotel where they have room service?, let's put that you go to one of these hotels, where the hotel’s restaurant is at the first floor, and you’re hosteling at the 10th floor. Now, when you are there, you decide to order something to eat for lunch, and imaging that the waitress instead of bringing you all the food with the compliments (drinks, desserts, etc.) at once, bring you every meal, drink, dessert and that, one by one, that will be so inefficient, because the waitress will have to do many runs before bringing you everything you asked for, going back and forth from floor 1 to 10. This is what the N+1 problem is, getting everything you request in many runs.

The ideal will be to carry what you ordered in a bussing cart like this, so the waitress can bring all at once:

In this article will see how this problem look in code and some solutions you can do to avoid this and ensure the best performance for your application.

Time to see some code

To show how the N+1 looks in your code, I’m going to build a simple console application that prints the available menus to order from a restaurant. For this, we’re going to have a database with a meals and drinks table, in the menu, each meal will come with a drink. Let's see the models for these tables:


// Meal model a record of meals table
type Meal struct {
    ID          uint
    Name        string
    Description string
    DrinkID     uint
    Drink       Drink
}

type Meals []Meal

// Drink model a record of drinks table
type Drink struct {
    ID          uint
    Name        string
    Description string
}

type Drinks []Drink

Now let's see the N+1 in action, here we have some methods to query data from the database (you can find the full code on my repo)

type Waitress struct {
    db *sql.DB
}

// getMeals gets 20 meals from the db
func (b Waitress) getMeals() (Meals, error) {
    // this method has the logic to query from the db, if you want to see
    // the actual code, you can go the repo

    // SQL used in this method
    // `SELECT id, name, description, drink_id FROM meals LIMIT 20`

    return meals, nil
}

// getDrinkByID gets a drink from the db by an id
func (b Waitress) getDrinkByID(ID uint) (Drink, error) {
    // this method has the logic to query from the db, if you want to see
    // the actual code, you can go the repo

    // SQL used in this method
  // `SELECT id, name, description FROM drinks WHERE id = $1`

    return Drink{}, nil
}

And finally the method with the N+1 query problem

func (b Waitress) ListMenu() (Meals, error) {
    // the `1` of our `N+1`
    meals, err := b.getMeals()
    if err != nil {
        return nil, err
    }

    // here is our waitress going back and forth 
    // from floor 1 to 10, this will be the `N` of our `N+1`
    for i, meal := range meals {
        drink, err := b.getDrinkByID(meal.DrinkID)
        if err != nil {
            return nil, err
        }

        meals[i].Drink = drink
    }

    return meals, nil
}

Yes, this simple logic can burn your database, because you are going back and forth to add the drinks to each meal, which is not efficient.

The more records you have to query or the most users you have, the most this N+1 problem we’ll affect your application because the time complexity is O(N)/Linear time.

ℹ️ Here I gave you an example in the backend, but this problem can also be found in your frontend, where instead of calling your database directly, you’ll be calling an endpoint of your backend which at the same time would call the database.

Solution

Now let's see two solutions to our problem.

Join the authors in the SQL query

This maybe the easier solution, in here you’ll just have to write a query like the following:

SELECT m.id, m.name, m.description, d.name, d.description
FROM meals AS m
    INNER JOIN drinks AS d ON d.id = m.drink_id

With this query, our code will look the following

type Solution1 struct {
    db *sql.DB
}

func (s Solution1) ListMenu() (Meals, error) {
    // this method will execute the previous SQL
    // SELECT
    // m.id,
    //  m.name,
    //  m.description,
    //  d.name,
    //  d.description
    // FROM meals AS m
    // INNER JOIN drinks AS d ON d.id = m.drink_id

    // if you want to see the full code, refer to my repo

    return meals, nil
}

With this query, now we only have you query our database once and that’s it.

Get the meals and then join the drinks with your programming language

No, we won’t do the same as the example where we saw the N+1 problem, here, instead of querying the meals and then querying the drinks one by one, we’ll just do two queries to our database, let see how:


func (s Solution2) ListMenu() (Meals, error) {
    meals, err := s.getMeals()
    if err != nil {
        return nil, err
    }

    // this is our waitress bringing everything in a bussing cart 
    drinks, err := s.getDrinksByIDsIn(meals.GetUniqueDrinkIDs())
    if err != nil {
        return nil, err
    }

    meals.JoinDrinks(drinks)

    return meals, nil
}

func (s Solution2) getMeals() (Meals, error) {
    // this method has the logic to query from the db, if you want to see
    // what's in these methods, you can go the repo

    // sql used in this method
    // `SELECT id, name, description, drink_id FROM meals LIMIT 20`

    return Meals{}, nil
}

func (s Solution2) getDrinksByIDsIn(ids []uint) (Drinks, error) {
    // this method has the logic to query from the db, if you want to see
    // what's in these methods, you can go the repo

    // sql used in this method
    // `SELECT id, name, description FROM drinks WHERE id IN (1, 2, 3)`

    return Drinks{}, nil
}

As you can see, we only have two queries to our database: s.getMeals() and s.getDrinksByIDsIn, and if you read the ListMenu method, you’ve noticed that we introduced to more methods, let see what they do and why we need them:

// to query the drinks, we need to know their IDs, for that
// we add this method to our `Meals` slice
func (m Meals) GetUniqueDrinkIDs() []uint {
    var ids []uint
    drinks := make(map[uint]struct{}, 0)

    for _, v := range m {
        _, ok := drinks[v.DrinkID]
        if ok {
            continue
        }

        drinks[v.DrinkID] = struct{}{}
        ids = append(ids, v.DrinkID)
    }

    return ids
}

// once we've query our meals and drinks, we proceed to join the 
// corresponding drink to our meals
func (m Meals) JoinDrinks(drink Drinks) {
    for i, meal := range m {
        m[i].Drink = drink.GetByID(meal.DrinkID)
    }
}

// this methos is needed by the `JoinDrinks()` method
// so it can find the drink for a meal 
func (d Drinks) GetByID(ID uint) Drink {
    for _, drink := range d {
        if drink.ID == ID {
            return drink
        }
    }

    return Drink{}
}

Now, you can see we don't query the database for every drink, instead, in one query we get all the meals, and in the other we query the drinks and then join them to the corresponding meals.

When to use one solution or the other?

Well, in this app, every meal includes just one drink, but what happened if a meal includes more than one drink?, in that scenario, the first solution can't help us, because the SQL query is going to repeat the record for every drink in a meal, so what we want to do, is use the second solution, when we first query the meals, and then get the drinks to join them to the corresponding meals

Personal experience

At work, we have a microservice that is in charge to cache a lot of data about the products we have, twice a day or at demand, and it used to take about 1 minute to cache all the data because of this problem, after we remove the N+1s, it went from 1 minute to 2 seconds!.

Conclusion

Don’t overestimate a simple logic like the N+1’s, you can fall into this problem easily, but also you can fix it easily, but if you don’t do it in time, your application performance will let you know overtime.

Something I didn’t mention is, the N+1 in ORMs like Gorm because I don’t have experience with these, but I will recommend if you are using ORMs, that you dig into the underlying code to see if you have this problem.

⚠️ The way I structure the code in this article is not meant to be a guide on how you should structure your code, it is made as simple as possible to focus on the problem and solution code.

Homework

If you are working on a project, or you already have projects in production, go check them out to get rid of any N+1 you find.

References

N+1 in Laravel: Here you can see the N+1 in Eloquent (an ORM for Laravel)
Figma Jam and IconDuck: To make the illustrations
Article repository: You can find the examples in my GitHub

How the Strategy Pattern works?

Hernan Reyes — Thu, 09 Jun 2022 01:05:01 +0000

Overview

Have you ever entered to an e-commerce and buy a product?, If you have, then you may have been show different ways to pay (PayPal, Credit Card, etc.)

This purchase process is a way to see the Strategy Pattern. Strategy is a Behavioral design pattern that lets you define different strategies and switch between them to reach a goal. In the above image, you have a goal (buy a product) and different strategies to accomplish it (pay with PayPal, Credit Card, etc.).

Let’s see how we would implement this purchase process.

Structure of Strategy Pattern

First, let's understand the structure of the pattern. It is defined by 3 parts:

Context Class

This is our goal
Contract Interface

These are the rules to be followed by the Strategies
Strategy Classes

Refers to the different strategies to reach my goal, are the implementation of the Contract Interface

Here is a Class Diagram for the Strategy structure

Will also need a Client who trigger the purchase process. Will see this in the code examples.

Code time, yay!

Now, let’s take the Class Diagram into code to see how it is done. I’ll be using Golang because it is the language I’m comfortable with:

Define the Contract Interface
Define the Context
Implement the Strategies
For this, will have 3 different classes (PayPal, CreditCard and Bank)
Client

In this example, I’ll be using the console to execute the purchase process.

When we run our program, this is what happen:

ℹ️ Note: This pattern is a way to implement the Open/Closed Principle of SOLID.

What if we want to add another payment method?

This would be easy, you just have to create another class that implements the PaymentMethodStrategy interface:

and after that, you just have to register it in your context class

The conventional way

How would it be to implement this process without the Strategy pattern?

Well, instead of having 3 classes (Context, Contract and Strategies), we’ll only have 1 class that implements all the logic to pay with PayPal, CreditCard, Bank, etc.

This will be our Purchase Class that implements all the logic
Our client

When we run our program, this is what happen:

This could be fine when you first build your app, or when you know that you’ll rarely will add or modify existent logic. But if you know that the business logic will grow, things like this may happen in the future:

It will be difficult to navigate throughout the code
Highly chance you modify something you’re not supposed to
Git conflicts when different people working on the same class
A lot of conditionals to switch between the different payment methods

From the business perspective it may be ok because it works, but from the technical perspective is not because it will not be maintainable in the future.

ℹ️ Note: Putting all the logic in one class, for this example, will go against the Single Responsibility Principle of SOLID.

Practice

Here I showed what the Strategy Pattern is, how it is structured, and when you should use this pattern instead of putting all the logic in one class.

I invite you to implement this pattern to apply a discount to an order and share the code in the comments. Hope this blog helped you understand the Strategy pattern. See you in the next Design Pattern article of this series.

References

Dive into Design Patterns: This article is made by the notes I took from this book
Peek: Tool to record my terminal
Figma Jam and IconDuck: To make the illustrations
Ray.so: For the code images
Article repository: You can find the examples in my GitHub

Hello, World!

Hernan Reyes — Sat, 28 May 2022 12:07:09 +0000

Hello everybody, welcome to my Behind The Scenes.

Here, I'll be posting the new things I learn while working or just studying from a Backend Developer perspective, this is the first time for me writing on a platform like this, so I'm excited to join this world as a writer and share my knowledge with the community.

See you soon!