Introduction

I recently started a new project for a client. The stack for this project included the framework Loopback 4 with a PostgresQL database. I ran into some issues setting up the foreign keys. In this article, we'll set up a quick project with a few models and foreign keys to get started properly. That might help some of you if you ever need to do the same thing.

Setting up our project

Loopback allows developers to use a command line tool. To install it globally, you can run: npm i -g @loopback/cli.

Once this is done, we can create a new project by running the command lb4 app. You'll have a complete a few fields like shown on the following image.

I added every single function in the project. Now, the CLI tool will install the needed dependencies for you. When it's done, you should see:

Mine is in French, but it just tells you that the project has been created and how to run it.

Great! Now we can create our models.

Creating our models

The models we create with Loopback will be used to create the database's tables. Loopback gives us a way to quickly create a model with the command line tool we installed earlier.

But what kind of models will we create? We'll have 3:

Customer model will have an id, a name and a platformId.
Order model will have an id and a customerId.
Platform model will have an id and a name.

We keep things simple, the point is not to have complex models, but to show how to setup everything properly.

First, let's create the customer model. To create a model, we can run lb4 model.

The command line will ask you some questions about the model. Answer it like the image below.

Good, next up, the Order model:

Finally, the Platform model:

We know have our three models. Next up, we need to create a datasource. In other words, tell our application where to find our database.

The Loopback datasource

As mentioned previously, we want to use a PostgresQL database. To link a database to Loopback, we can run the command lb4 datasource:

You'll have to use the arrow keys to find the PostgresQL connector in the list. By default, PostgresQL runs on port 5432. Choose the username/password combination that you want.

Note: Loopback may install the loopback-connector-postgresql package if it's not present. This connector is used by Loopback to talk to a PostgresQL database.

Create the database

Good! Now, the problem that we have is that we need to actually create the database test_postgres, which is the name I chose here. If you do not have PostgresQL installed, you can follow this tutorial.

Once you have it installed, you should see the PostgresQL version when you run psql --version.

If that's the case, you should be able to connect to a PostgresQL instance:

The -U flag is followed by the user name you chose when you created your datasource with Loopback. You'll then be prompted to enter the password you entered earlier. If everything went smoothly, you should see postgres=# in the command line, meaning that the connection was successfull.

To create the database, we'll run the command CREATE DATABASE <Database_Name>;

Then we can connect to our new database by running \c test_postgres;

Great! Next up, we need to create the repositories.

Create the repositories

A repository is adding behavior to a model. This is different from Loopback 3 where the model was also providing the CRUD behaviors. Here, we can run lb4 repository to create our repositories.

You first select our datasource, then choose all three models ( use space to select ).

Now, we can run our Loopback application with npm start. In our psql instance, I can see the current tables by running \dt;

Create our controllers

Finally, we'll create our controllers. Controllers are created by running lb4 controller

This will create the basic CRUD endpoints for each model.

Where the issue starts

So far, everything is going fine. Let's see where it starts to go bad. Loopback 4 gives you a command to auto-migrate your database models that you can run with npm run migrate.

Ok, this comes out of nowhere. Let me explain. Remember when we created our models earlier, we gave the id field a type string. I also said that this field would be generated automatically. Indeed, I want to use the PostgresQL UUID type, something in the form of a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11.

Now, if I said that the id type was number, it would be quite easy for Loopback. It starts at 1, and increments by 1 every time a new entity is created in the database. But when the type is string, Loopback doesn't know how to autogenerate this, we have to tell him. That's what the error is all about.

Giving our id fields the UUID type

We need first to go into our models and explicitly say how to generate the id field. In the files src/models/customer.model.ts, src/models/order.model.ts and src/models/platform.model.ts, change the code:

  @property({
    type: 'string',
    id: true,
    generated: true,
  })
  id?: string;

  @property({
    type: 'string',
    id: true,
    generated: true,
    useDefaultIdType: false,
    postgresql: {
      dataType: 'uuid',
    },
  })
  id?: string;

That's how we explicitly tell Loopback how to use the UUID type for our id fields. We are going to run npm run migrate -- --rebuild to drop the existing schemas. The warnings are gone!

Note: You may have to install the package uuid-ossp that provides the function for the UUID generation in psql. If that the case, inside your psql instance, run create extension if not exists "uuid-ossp";.

Testing our progress

I want to make sure that our ID field is indeed using the UUID type. Loopback gives us the Explorer API to easily interact with the database and the models. You can find the Explorer at http://localhost:3000/explorer. You can find here our controllers we created earlier and the basic endpoints. Let's quickly create a new Platform. Find the PlatformController and click on the POST /platforms item. Then, click on the Try it out! button on the right. You should see the following screen.

We can create a new Platform entity by typing the platform name ( where there is "string". I'm calling my new platform "Best Platform Ever". Then click on Execute. You should see this below.

As you can see, the id has been generated automatically and it has the UUID type we wanted!

Foreign keys

Last but not least, we will configure our foreign keys. In our models, we need to add some settings. First, in our Customer model, we need to configure the foreign key for the Platform model. In src/models/customer.model.ts, above the class definition, you have a @model() decorator. Replace it with:

@model({
  settings: {
    foreignKeys: {
      fkCustomerPlatformId: {
        name: 'fk_customer_platformId',
        entity: 'Platform',
        entityKey: 'id',
        foreignKey: 'platformid',
      },
    },
  },
})

As you can see, our foreign key as a name, and entity, entity key and foreign key. The entity represents the model it references, here, Platform. The entity key is the field we use for the reference, here, id. Finally, the foreign key is the name of the field in our Customer table, here platformid.

Good, so we should be good for the Customer table right? Let's find out. Run npm run build, then npm run migrate -- --rebuild.

ERROR!

It says that the foreign key can't be implemented. If you look for details in the error log, it says that the type uuid and text are not compatible. Which makes sense, our Platform model has a field id of type uuid. The platformid field in our Customer model is of type string. That can't work. Go back in the Customer model and change the platformid field from

@property({
    type: 'string',
    required: true,
  })
  platformId: string;

 @property({
    type: 'string',
    required: true,
    postgresql: {
      dataType: 'uuid',
    },
  })
  platformId: string;

Run npm run build and npm run migrate (no need to rebuild). The error is gone. But let's make sure the foreign key has been implemented in the database. Move to psql and run \d+ customer; to get the constraints from the Customer table.

As you can see, the foreign key constraint has successfully been added to the table!

We can now do the same thing for the Order model. We'll add a little difficulty here. We have to add a Platform reference in our Order model. So in addition to the customer.id foreign key, we need to add a platform.id foreign key. Don't worry, we don't need much 😉

In our src/models/order.model.ts, the new class should look like this:

// Adding our foreign keys configuration
@model({
  settings: {
    foreignKeys: {
      fkOrderPlatformId: {
        name: 'fk_order_platformId',
        entity: 'Platform',
        entityKey: 'id',
        foreignKey: 'platformid',
      },
      fkOrderCustomerId: {
        name: 'fk_order_customerId',
        entity: 'Customer',
        entityKey: 'id',
        foreignKey: 'customerid',
      },
    },
  },

})
export class Order extends Entity {
  @property({
    type: 'string',
    id: true,
    generated: true,
    useDefaultIdType: false,
    postgresql: {
      dataType: 'uuid',
    },
  })
  id?: string;

  @property({
    type: 'string',
    required: true,
    postgresql: {
      dataType: 'uuid',
    },
  })
  customerId: string;

// This is new
  @property({
    type: 'string',
    required: true,
    postgresql: {
      dataType: 'uuid',
    },
  })
  platformId: string;

  constructor(data?: Partial<Order>) {
    super(data);
  }
}

We added the new platformId property definition. We also added the dataType for the customerId and platformId, to make sure the field types are compatible. Finally, we added the configuration for our foreign keys in the model decorator.

Let's run npm run build and npm run migrate again. This time, in psql, run \d+ order; to get the constraints from the Order table:

There we have it! Our 2 foreign keys are properly configured, just like we expected! Congratulations!

Conclusion

I spent a lot of time debugging this issue. I hope I managed to make it clear for you if you run into those issues. The Loopback 4 documentation can be a bit difficult to navigate sometimes, or even non-existent.

Have fun ❤️

Using foreign keys with Loopback 4 and PostgresQL