X-Press Publishing Issue table

Hello everyone!!!

I´m got a new issue in the X-Press Publishing problem is when I try to build my Issue database. It appears in my SQL but when I want to run the program send me a message saying not found column.

I will aprecciete if someone could help me.

Thank you so much.


migration.js

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

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

    db.run('CREATE TABLE IF NOT EXISTS `Series` ( ' +
    '`id` INTEGER NOT NULL, ' +
    '`name` TEXT NOT NULL, ' +
    '`description` TEXT NOT NULL, '  +
    'PRIMARY KEY(`id`) )');

    db.run('CREATE TABLE IF NOT EXISTS `Issue` ( ' +
    '`id` INTEGER NOT NULL, ' +
    '`name` TEXT NOT NULL, ' +
    '`issue_number` INTEGER NOT NULL, ' +
    '`publication_date` TEXT NOT NULL, ' +
    '`artist_id` INTEGER NOT NULL, ' +
    '`series_id` INTEGER NOT NULL, ' +
    'PRIMARY KEY(`id`), '+
    'FOREIGN KEY(`artist_id`) REFERENCES `Artist`(`id`),'+
    'FOREIGN KEY(`series_id`) REFERENCES `Series`(`id`) ) ');
});

series.js

const express = require('express');
const seriesRouter = express.Router();

const sqlite3= require('sqlite3');
const db = new sqlite3.Database(process.env.TEST_DATABASE || './database.sqlite');



seriesRouter.param('seriesId', (req, res, next, seriesId) =>{
    const sql= 'SELECT * FROM Series WHERE Series.id = $seriesId';
    const values = {$seriesId : seriesId};
    db.get(sql, values, (error, series)=>{
        if(error){
            next(error);
        } else if (series){
           req.series = series;
           next();
        } else{
            res.sendStatus(404);
        }
    })
})

seriesRouter.get('/', (req, res, next) => {
    db.all('SELECT * FROM Series', (error, series)=>{
        if(error){
            next(error);
        } else {
            res.status(200).json({series: series});
        }
    });
})

seriesRouter.get('/:seriesId', (req, res, next)=>{
    res.status(200).json({series: req.series});    
});

seriesRouter.post('/', (req, res, next) =>{

    const name = req.body.series.name;
    const description = req.body.series.description;

    if(!name || !description){
        return res.sendStatus(400);
    }
    const sql = 'INSERT INTO Series (name, description) VALUES ($name, $description)';
    const values ={
            $name: name,
            $description: description
        };
    db.run(sql, values, function(error){
        if(error){
            next(error);
        }else{
            db.get(`SELECT * FROM Series WHERE id = ${this.lastID}`,
            (error, series) =>{
                res.status(201).json({series : series});
            })
            }
 
            
    });
    
})

seriesRouter.put('/:seriesId', (req, res, next)=>{
    const name = req.body.series.name;
    const description = req.body.series.description;

    if(!name || !description){
        return res.sendStatus(400);
    } 
    const sql = 'UPDATE Series SET name = $name, description = $description' +
     'WHERE Series.id= $seriesId ';
    const values = {
        $name : name,
        $description : description,
        $seriesId : req.params.seriesId
    };
    db.run(sql, values, (error) =>{
        if(error){
            next(error);
        } else {
            db.get(`SELECT * FROM Series WHERE Series.id = ${req.params.seriesId}`,
            (error, series)=>{
                res.status(200).json({series : series});
            })
        }
    })
})

module.exports = seriesRouter;

It is possible that you have created the Issue table (without all the columns) while working on the project and it would therefore not make a new Issue table when running migration.js again as it is now written. I would try to manually delete the database-file (database.sqlite) and then run node migration.js again as it will recreate that file.

You could also add error-logging to the console to help debugging. For example:

db.run(`CREATE TABLE IF NOT EXISTS Issue (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    issue_number INTEGER NOT NULL,
    publication_date TEXT NOT NULL,
    artist_id INTEGER NOT NULL,
    series_id INTEGER NOT NULL,
    FOREIGN KEY(artist_id) REFERENCES Artist(id),
    FOREIGN KEY(series_id) REFERENCES Series(id)
    )`,
    function(err) {
        if (err) {
            console.log(err);
        }
    });

1 Like

Thank you for your advice. I did manually and I solve the problem.
:slight_smile: