How to create an ExpressJS restful API with UUID and Postgres pg-promise library

Question

I am trying to create CRUD for user in my web App. I am using uuid for my id (also the primary key as shown in the user table below). I am using the pg-promise library to connect to Postgres. The Route code to create users and get all users are working. But I am having challenge on how to reference the uuid when creating the id const for getting one user, deleting user or updating user i.e const user_id = parseInt(req.params.id);

Please see my sample codes below. I will also appreciate any online resource with example.

MY USER TABLE

CREATE TABLE m_user (
id uuid DEFAULT uuid_generate_v4() NOT NULL,
username text NOT NULL UNIQUE,
password text NOT NULL,
confirm_password text NOT NULL,
created_at timestamp NOT NULL default now(),
last_login timestamp NOT NULL default now(),
PRIMARY KEY (id)
);

CRUD TO GET ONE USER

function getSingleUser(req, res, next) {
  const showoneuserquery = 'SELECT * FROM user where id = $1'
  const user_id = parseInt(req.params.id);
  db.one(showoneuserquery, user_id)
    .then(function (data) {
      res.status(200)
        .json({
          status: 'success',
          data: data,
          message: 'Retrieved ONE user'
        });
    })

    .catch(function (err) {
      return next(err);
    });
}

CRUD TO EDIT USER

function editUser(req, res, next) {
  const edituserquery = 'update user set username=$1, password=$2, confirm_password=$3  where id=$4'
  db.none(edituserquery, [req.body.username, req.body.password, req.body.confirm_password, (req.params.id)])
    .then(function () {
      res.status(200)
        .json({
          status: 'success',
          message: 'Updated user'
        });
    })
    .catch(function (err) {
      return next(err);
    });
}

@Khauri I separate the router business logic from the route in a controller file. The codes I work on is the business logic as indicated in my question. But maybe I don't understand the code you are referring to. — Jetro Olowole, Dec 14 '19 at 13:07
Please update your question with errors/situations you face after running the named actions. — , Dec 14 '19 at 13:14
You said you're having problems referencing the id with `req.params.id`? Usually you'd specify that value in your router like so: `router.get('/:id', getSingleUser)`. If that wasn't what you meant, can you be more specific about the error you're getting? — Khauri, Dec 14 '19 at 13:15
@Khauri I did have the route properly set via ```router.get('/:id', getSingleUser).``` The part I'm confudes with is the place where I declare a constant for user_id ```const user_id = parseInt(req.params.id);``` SInce I am using uuid, I think I should use something other than the **parseInt function.** — Jetro Olowole, Dec 14 '19 at 17:32
A couple notes. First a disclaimer I don't know ExpressJS. But ... You claim you are using uuid for your table's primary. However that is not how it is defined at the database table level. The table DDL you posted defines id as BIGSERIAL. That definition makes it a 64bit integer. However, uuid is a 32byte hex value. Additionally, **user** is a Postgres and SQL standard [reserved word](https://www.postgresql.org/docs/current/sql-keywords-appendix.html). While you can get away with using it for now that could go away if Postgres development team decides to adhere closer to the SQL standard. — Belayer, Dec 14 '19 at 21:54
Belayer beat me by a second... Indeed, the question has a contradiction, seeking info about UUID usage, while showing use of 64-bit integer in the table (`BIGSERIAL`). UUID-s are just strings, and they are used as such everywhere, while 64-bit integers have some specifics, like the ones [explained here](https://stackoverflow.com/questions/39168501/pg-promise-returns-integers-as-strings). — vitaly-t, Dec 14 '19 at 21:56
@Belayer the id has been corrected to uuid. It was a mistake but my question still remains. Also, I prefix the user table name in my project. — Jetro Olowole, Dec 15 '19 at 02:06

score 1 · Answer 1 · answered Dec 15 '19 at 03:51

OK a couple notes on UUID. @vitaly_t not quite correct uuid are not just strings. While visually they appear as strings and you can process them as strings they are well UUID. Having unique properties strings lack most notable they have be several representations formats. Below a small demo showing this. A string cannot do this functionally.

create table uuid_demo(uuid_col uuid,  description text);

-- insert a generated systen function generated uuid
-- requires pgcrypto extension
insert into uuid_demo(uuid_col, description)
     values (gen_random_uuid(),'Systen Generated uuid') ;

-- show generated
select * from uuid_demo;
/*
uuid_col                            ;description
d41c9d09-0e21-4acf-b82c-41f009c84aeb;Systen Generated uuid
*/

-- query demo with generated uuid, both with and without formatting
select uuid_demo.*,'#1. Standard Form'  note       from uuid_demo where uuid_col = 'd41c9d09-0e21-4acf-b82c-41f009c84aeb'
union all
select uuid_demo.*,'#2. Standard, cast'            from uuid_demo where uuid_col = 'd41c9d09-0e21-4acf-b82c-41f009c84aeb'::uuid
union all
select uuid_demo.*,'#3 Non Standard Upper case'    from uuid_demo where uuid_col = 'D41C9D09-0E21-4ACF-B82C-41F009C84AEB'
union all
select uuid_demo.*,'#4 Non Standard Unformatted'   from uuid_demo where uuid_col = 'd41c9d090e214acfb82c41f009c84aeb'
union all
select uuid_demo.*,'#5 Non Std. Upper Unformatted' from uuid_demo where uuid_col = 'D41C9D090E214ACFB82C41F009C84AEB'
union all
select uuid_demo.*,'#7 Non Standard bracket'       from uuid_demo where uuid_col = '{d41c9d090e214acfb82c41f009c84aeb}';

/*
uuid_col                            ;description          ;note
d41c9d09-0e21-4acf-b82c-41f009c84aeb;Systen Generated uuid;#1. Standard Form
d41c9d09-0e21-4acf-b82c-41f009c84aeb;Systen Generated uuid;#2. Standard, cast
d41c9d09-0e21-4acf-b82c-41f009c84aeb;Systen Generated uuid;#3. Non Standard Upper case
d41c9d09-0e21-4acf-b82c-41f009c84aeb;Systen Generated uuid;#4. Non Standard Unformatted
d41c9d09-0e21-4acf-b82c-41f009c84aeb;Systen Generated uuid;#5. Non Std. Upper Unformatted
d41c9d09-0e21-4acf-b82c-41f009c84aeb;Systen Generated uuid;#6. Non Standard bracket

Now for your direct issue: How should you reference it. Here vitaly_t is correct in that in your code just process it as a string.

Try the Standard for (#1). That is the form Postgres will always provide as output.
Try the Postgres cast operator (::) (#2).
Try the Postgres CAST directive (not shown above).

I'll try putting each of these into ExpressJS format. But I make no guarantee (I just copied and modified from your post). Again see my earlier disclaimer.

const showoneuserquery = 'SELECT * FROM user where id = $1'
const showoneuserquery = 'SELECT * FROM user where id = $1::uuid'
const showoneuserquery = 'SELECT * FROM user where id = CAST ($1 as UUID)'

For update, delete operations follow the same format.

If none of those work you have another option. Since you have a unique constraint on username just use it for accessing the users table, then just use the UUID when joining to users table, which should be UUID column name to UUID column name. Nothing special needed.

Thanks@Belayer, I have tested using the username and it works before your answer came in. I'll follow your suggestion and use the uuid when joining table. I also have some aspects of the app where I'll still use uuid. — Jetro Olowole, Dec 15 '19 at 04:58

How to create an ExpressJS restful API with UUID and Postgres pg-promise library

1 Answers1