Sequelize can create tables, but I can’t insert or select anything

I am creating an express app with basic MVC functionality using Sequelize.

Right now I am implementing a route to insert one row of student data into the database. When the server starts, sequelize force sync tables and everything looks fine. I can confirm that the DB exists and Sequelize just created the Students table in it.

Then I insert manually one row using the CL.

mysql> insert into students values(1,'Alan','Johnson');

Now:

  • the server is listening,
  • the database was created,
  • Sequelize was able to see the modules and then create the corresponding table.
  • The table contains one row which was seeded manually.

My problem is this:

  • I have a feeling that the connection.js is not finding the .env file.
  • But, how it that possible if sequelize.sync was able to create a table in the db? Can you please help me find what is wrong in:
    • http://localhost:3001/api/insertStudent
    • http://localhost:3001/api/Allstudents

NOTE:

I am using that route just to get to the Student.create() and Student.findAll() command.

app.js

const express = require('express');
const sequelize = require('./config/connection');
const path = require('path');
const router = require('./routes/index');
const PORT = process.env.PORT || 3001;
const app = express();

app.use(express.json());
app.use(router);


sequelize.sync({force: true}).then(
    app.listen(PORT, () => {
        console.log('Sever running on port: %j', PORT);
        console.log('http://localhost:%j/', PORT);
        console.log('http://localhost:%j/api/', PORT);
        console.log('http://localhost:%j/api/Allstudents', PORT);
        console.log('http://localhost:%j/api/insertStudent', PORT);
    })
);

package.json

{
  "name": "Project-2-connection-test",
  "version": "1.0.0",
  "description": "",
  "main": "app.js",
  "dependencies": {
    "dotenv": "^10.0.0",
    "express": "^4.17.1",
    "find-config": "^1.0.0",
    "mysql2": "^2.3.0",
    "sequelize": "^6.6.5",
    "sequelize-cli": "^6.2.0"
  },
  "devDependencies": {},
  "scripts": {
    "start": "node app.js"
  },
  "keywords": [],
  "author": "",
  "license": "ISC"
}

.env

DB_NAME="student_behavior_db"
DB_USER="root"
DB_PASSWORD=

routes/index.js

const router = require('express').Router();
const student_routes = require('./myApi/studentRoutes');
router.use('/api',student_routes);

router.get('/',(req,res)=>{
    res.status(200).send('<h1>Home root</h1>');
})

module.exports = router;

routes/Myapi/index.js

const router = require('express').Router();
const {Student} = require('../../models/index');

/* ----------------------------------------------------------------------NOT WORKING */
router.get('/allStudents', (req, res) => {
    try {
        const students = Student.findAll();
        console.log("---> students :" + JSON.stringify(students));
        return res.status(200).json(students);
    } catch (e) {
        return res.status(500).send(e.message);
    }
});
/* ----------------------------------------------------------------------NOT WORKING */
router.get('/insertStudent', (req, res) => {
     console.log("---> insertStudent :" );
    const  studentInsert  = Student.create({id:2,firstName:"John",lastName:"Stevens"})
     console.log("---> studentInsert :" + studentInsert );
        res.status(200).json(studentInsert);
})

router.get('/', (req, res) => {
    res.status(200).send('<h1>Root on student-routes</h1>');
})

module.exports = router;

models/student.js

const {Model, DataTypes} = require('sequelize');
const sequelize = require('../config/connection');


class Student extends Model {
    /**
     * Helper method for defining associations.
     * This method is not a part of Sequelize lifecycle.
     * The `models/index` file will call this method automatically.
     */
    static associate(models) {
        // define association here
    }
};
Student.init({
    id: {type: DataTypes.INTEGER, primaryKey: true},
    firstName: {type: DataTypes.STRING, allowNull: false},
    lastName: {type: DataTypes.STRING, allowNull: false}
}, {
    sequelize,
    timestamps: false,
    modelName: 'Student',
});

module.exports = Student;

models/index.js

const Student = require('./Student');

module.exports = {Student};

db/schema.sql

DROP DATABASE IF EXISTS student_behavior_db;
CREATE DATABASE student_behavior_db;

config/connections.js

 const Sequelize = require('sequelize');
require('dotenv').config();

const sequelize = new Sequelize(
    process.env.DB_NAME,
    process.env.DB_USER,
    process.env.DB_PASSWORD,
    {
        host: 'localhost',
        dialect: 'mysql',
        port: 3306,
    }
);

module.exports = sequelize;

config/config.json

{
  "development": {
    "host": "127.0.0.1",
    "dialect": "mysql",
    "port": 3306
  },
  "test": {
    "host": "127.0.0.1",
    "dialect": "mysql",
    "port": 3306
  },
  "production": {
    "host": "127.0.0.1",
    "dialect": "mysql",
    "port": 3306
  }
}

Your support is greatly appreciated.

Answer

The findAll returns a promise and you’re not resolving that promise. https://sequelize.org/master/class/lib/model.js~Model.html#static-method-findAll

Either invoke .then on Student.findAll() or use async/await on your middleware.