Using Postgresql with LoopBack4 JWT Authentication Extension

3 min readDec 11, 2020

https://www.pexels.com/photo/two-person-standing-under-lot-of-bullet-cctv-camera-374103/

This article is a continuation of my previous post on using LoopBack 4 JWT authentication extension@loopback/jwt-authentication . The full example can be found https://github.com/strongloop/loopback-next/tree/master/examples/todo-jwt. You can download the example using this command:

lb4 example todo-jwt

The default implementation of the @loopback/jwt-authentication is to store the User and UserCredentials data in a datasource. Therefore, say we define a DataSource DsDataSource for the user data, we can add the line below in constructor of the application class in src/application.ts .

this.dataSource(DsDataSource, UserServiceBindings.DATASOURCE_NAME);

What do I need to set up if I’m using a SQL database like Postgres?

If you’re using a noSQL database like Mongodb, after you bind the datasource of the UserService to the datasource, you’re good to go.

If you’re using a SQL database like Postgres, you still need to create the tables. The database migrate script (i.e. by running npm run migrate) only create the table schema for the models you defined in the app, and it does not include the User and UserCredentials ones.

There are a few options to do that.

Note: There might be a better way, if you know of them, please let me know!

Option 1: Inspect the models and construct the SQL command accordingly

The models are defined in https://github.com/strongloop/loopback-next/tree/master/extensions/authentication-jwt/src/models, so you can create the table with the same model name and columns with the same property names.

Keep that in mind that all the table and column names should be lower case for Postgres.

Option 2: Recreate the User and UserCredentials model and then run the migrate script

The idea is to be able to use the database migration script to create those table schemas. Here are the steps:

copy and paste the User and UserCredentials models from https://github.com/strongloop/loopback-next/tree/master/extensions/authentication-jwt/src/models
create the repository classes that bind the models with the datasource
run npm run migrate command

UPDATED (Thanks for the feedback)
If you are using this option, remember to remove the model and repository classes after running the database migration script. The User has a hasOne relationship with UserCredentials, so simply creating the repository classes using the lb4 repository command would not be sufficient. By deleting those files, the corresponding ones in the jwt-authentication will be used.

Option 3: Use the scripts I generated from Option 2

I used the steps in Option 2 to create the table schema, and then use pg_dump to get the DDL. Hence, this option is only applicable if you’re using Postgres, but the DDL might be similar for other databases.

User table

CREATE TABLE public."user" (
    id text NOT NULL,
    realm text,
    username text,
    email text NOT NULL,
    emailverified boolean,
    verificationtoken text
);ALTER TABLE ONLY public."user"
    ADD CONSTRAINT user_pkey PRIMARY KEY (id);CREATE UNIQUE INDEX user_email_idx ON public."user" USING btree (email);

UserCredentials table

CREATE TABLE public.usercredentials (
    id text NOT NULL,
    password text NOT NULL,
    userid text NOT NULL
);ALTER TABLE ONLY public.usercredentials
    ADD CONSTRAINT usercredentials_pkey PRIMARY KEY (id);

Hope it helps!

Using Postgresql with LoopBack4 JWT Authentication Extension

Written by Diana Lau