CODEWITHSUNDEEP
javascriptdatabaseknex.js
Barton Young
Barton Young

Reputation: 81

How can I insert only unique data into Postgres using Knex?

I am creating a simple 'attendance' database where a student will only be allowed to 'check in' once per day. Each checkin will include an user_id, name, time, and DATE. Using Knex, how can I only allow one checkin per student per day.

I have attempted several variations of 'whereNotExists' from this thread but nothing seems to work.

Currently this is my insert statement:

db('checkins').insert(db
.select(attrs.user_id, attrs.user_name, attrs.date, attrs.created_at)
.whereNotExists(db('checkins').where('user_name', attrs.user_name).andWhere('date', attrs.date).then(() => {
  console.log('successful insert')
}))

I then receive this error ('alice_id' is a test value I used in 'attrs.user_id'):

Unhandled rejection error: column "alice_id" does not exist

Upvotes: 1

Views: 765

Answers (1)

Fazal Rasel
Fazal Rasel

Reputation: 4526

you should validate before insert i.e.

db('checkins')
.where({
  user_id: attrs.user_id,
  date: attrs.date
})
.first() // getting the first value
.then((found) => {
   if (found){
     res.json('already present');
   }else{
     // now insert data
      db('checkins')
        .insert({
          // your data
        });
   }
});

Upvotes: 3

Related Questions