X-Press Publishing / Error: SQLITE_CONSTRAINT:

Hi there!

I am working on the project X-press publishing: https://www.codecademy.com/paths/web-development/tracks/building-a-persistent-api/modules/persistent-api-cumulative-projects/projects/x-press-publishing

I’ve got two annoing SQLite errors on PUT and DELETE. Despite the test is passed there are two errors “Error: SQLITE_CONSTRAINT: NOT NULL constraint failed: Artist.name”:

 PUT /api/artists/:id
Error: SQLITE_CONSTRAINT: NOT NULL constraint failed: Artist.name
PUT /api/artists/1 200 8.203 ms - 135
    √ should update the artist with the given ID
PUT /api/artists/1 200 6.183 ms - 135
    √ should return a 200 status code after artist update
PUT /api/artists/1 200 6.273 ms - 135
    √ should return the updated artist after artist update
PUT /api/artists/1 400 0.680 ms - -
    √ should return a 400 status code for invalid artist updates

  DELETE /api/artists/:id
Error: SQLITE_CONSTRAINT: NOT NULL constraint failed: Artist.name
DELETE /api/artists/1 200 6.769 ms - 122
    √ should set the artist with the given ID as not currently-employed
DELETE /api/artists/1 200 6.405 ms - 122
    √ should return a 200 status code after artist delete
DELETE /api/artists/1 200 5.953 ms - 122
    √ should return the deleted artist after artist delete

My PUT and DELETE look like that:

artistsRouter.put('/:artistId', (req, res, next) => {
  const name = req.body.artist.name;
  const dateOfBirth = req.body.artist.dateOfBirth;
  const biography = req.body.artist.biography;
  const isCurrentlyEmployed = req.body.artist.isCurrentlyEmployed === 0 ? 0 : 1;

  if (!name || !dateOfBirth || !biography) {
    res.status(400).send();
  };

  db.run(`UPDATE Artist 
          SET name = $name,
              date_of_birth = $dateOfBirth,
              biography = $biography,
              is_currently_employed = $isCurrentlyEmployed
          WHERE id = $id`, 
          {$name: name,
            $dateOfBirth: dateOfBirth,
            $biography: biography,
            $isCurrentlyEmployed: isCurrentlyEmployed,
            $id: req.params.artistId}, 
            (err) => {
              if (err) {
                next(err);
              } else {
                db.get(`SELECT * FROM Artist WHERE id = ${req.params.artistId}`, (err, artist) => {
                  res.status(200).send({artist: artist});
                }); 
              };
            });
});


artistsRouter.delete('/:artistId', (req, res, next) => {
  db.run(`UPDATE Artist
          SET is_currently_employed = 0
          WHERE id = ${req.params.artistId}`,
          (err) => {
            if (err) {
              next(err);
            } else {
              db.get(`SELECT * FROM Artist WHERE id = ${req.params.artistId}`, (err, artist) => {
                res.status(200).send({artist: artist});
              });
            };
          });
});

My migration.js looks like that:

const sqlite3 = require('sqlite3');
const db = new sqlite3.Database('./database.sqlite');

db.serialize(() => {
  db.run(`DROP TABLE IF EXISTS Artist`);
  db.run(`CREATE TABLE IF NOT EXISTS Artist (
    id INTEGER PRIMARY KEY NOT NULL,
    name TEXT NOT NULL,
    date_of_birth TEXT NOT NULL,
    biography TEXT NOT NULL,
    is_currently_employed INTEGER DEFAULT 1
  )`);
});

The application works properly. Do you have any ideas why this SQLITE_CONSTRAINT occurs?

i have also got the same issue but in series route instead of artist route

In my case i missed “return” here:

2 Likes

thank you, it also worked for me.
how did you figure it out?

1 Like

The “return” in that case should prevent the programm from executing db.run if any of provided data is invalid. If we miss the “return” the programm goes futher and tries to execute db.run with invalid data and causes error thereby.

Hi, I’m getting this error, even with adding return. What does Artist.name even mean? I’m trying to find where name doesn’t exist or is NULL.



artistRouter.put('/:artistId', (req,res,next)=>{

    const id = req.params.artistId
    const name = req.body.artist.name
    const dateOfBirth = req.body.artist.dateOfBirth
    const biography = req.body.artist.biography
    let is_currently_employed =req.body.artist.isCurrentlyEmployed === 0 ? 0 : 1;

    console.log(name)    
    
    if( !name || !dateOfBirth || !biography ){
        return res.status(400).send();
    }else{
        console.log('Update')
        db.run(
            `UPDATE Artist 
            SET name = $name, 
                date_of_birth = $dateOfBirth, 
                biography = $biography, 
                is_currently_employed = $is_currently_employed
            WHERE id = $id`, {
                $id : id,
                $name : name,
                $dateOfBirth : dateOfBirth,
                $biography : biography, 
                $is_currently_employed : is_currently_employed

            }, function(err){
            if(err){
                console.log(err)
                next(err)
            }else{
                db.get(`SELECT * FROM Artist WHERE id = ${id}`, function(err,row){
                console.log(row)
                res.status(200).send({artist:row})
            })
        }

        })}

})