LoopBack 4 TodoList Application Tutorial - Running on Relational DatabasesIf you are running this example using a relational database, there are extrasteps in order to set up constraints in the database. We’re using PostgreSQL forillustration, but it would work for other relational databases in a similar way.

Create a new datasource

We’re going to update the DataSource db to connect to the PostgreSQL databaseinstead of the in-memory database.

First, remove the existing db, run:

rm src/datasources/db.datasource.*

Then, create the datasource with the same name with the lb4 datasource commandand select PostgreSQL as the connector.

$ lb4 datasource
? Datasource name: db
? Select the connector for db:
  Redis key-value connector (supported by StrongLoop)
  MongoDB (supported by StrongLoop)
  MySQL (supported by StrongLoop)
❯ PostgreSQL (supported by StrongLoop)
  Oracle (supported by StrongLoop)
  Microsoft SQL (supported by StrongLoop)
  REST services (supported by StrongLoop)
  ...

Specify the foreign key constraint in the models

Based on the model relations we’ve specified in the 3 models, the expecteddatabase tables look like below:

Note:

There is ongoing work on supporting strong relations with referential integrity. For details, please see epic #2231.

Specify the foreign key constraints in Todo model

We will use the foreignKeys attribute to determine the constraints in thedatabase table.

In src/models/todo.model.ts, add the settings options in the @modeldecorator.

@model({
  settings: {
    foreignKeys: {
      fk_todo_todoListId: {
        name: 'fk_todo_todoListId',
        entity: 'TodoList',
        entityKey: 'id',
        foreignKey: 'todolistid',
      },
    },
  },
})
export class Todo extends Entity {
  //etc.
}

Check out the details forauto-migrate and auto-updatefor PostgreSQL connector. For other connectors, go to the correspondingconnector from theConnectors Referencesection.

Specify the foreign key constraints in TodoListImage model

Similar to the Todo model, we will specify the constraints in the settings.

@model({
  settings: {
    foreignKeys: {
      fk_todoListImage_todoListId: {
        name: 'fk_todoListImage_todoListId',
        entity: 'TodoList',
        entityKey: 'id',
        foreignKey: 'todolistid',
      },
    },
  },
})
export class TodoListImage extends Entity {
  //etc.
}

Database migration using npm run migrate command

The order of table creation is important. We are going to migrate TodoListmodel before the Todo and TodoListImage models.

In src/migrate.ts, modify this line:

await app.migrateSchema({existingSchema});

to:

await app.migrateSchema({
  existingSchema,
  // The order of table creation is important.
  // A referenced table must exist before creating a
  // foreign key constraint.
  // For PostgreSQL connector, it does not create tables in the
  // right order.  Therefore, this change is needed.
  models: ['TodoList', 'Todo', 'TodoListImage'],
});

Run the following commands:

$ npm run build
$ npm run migrate

The todolist, todo and todolistimage tables are created.

For details, see the Database migrations documentationpage.