Changing the structure of the MySQL database without removing it (Dockerized Node.js app with Sequelize)

Viewed 88

I have a Node.js application connected with the MySQL database, both placed on docker containers. I use the Sequelize package to communicate with the database from Node.js.

utils/database.js

const Sequelize = require('sequelize')

const { SQL_DB, SQL_USER, SQL_PASSWORD, SQL_HOST } = process.env
const sequelize = new Sequelize(
    SQL_DB, SQL_USER, SQL_PASSWORD, {
        dialect: 'mysql',
        host: SQL_HOST,
        logging: false,
        operatorAliases: false
    }
)

module.exports = sequelize

Everything works fine, as long as I don't change the properties of my Sequelize model in Node.js.

models/song.js

const Sequelize = require('sequelize')
const sequelize = require('../utils/database')

const User = require('./user')

const Song = sequelize.define('song', {
    id: {
        type: Sequelize.INTEGER,
        primaryKey: true,
        allowNull: false,
        autoIncrement: true,
        unique: true
    },
    title: {
        type: Sequelize.STRING,
        allowNull: false,
        unique: true,
    },
    lyrics: {
        type: Sequelize.STRING,
        allowNull: false,
    },
    link: {
        type: Sequelize.STRING,
        allowNull: false,
        unique: true,
    },
    recordedAt: {
        type: Sequelize.DATEONLY,
        allowNull: false,  
    }
})
Song.belongsTo(User, { 
    foreignKey: {
        name:  'userId',
        allowNull: false
    }
})

module.exports = Song

Then I got the following error from the backend (in this case I'm trying to add a new field 'recordedAt' to the song model):

{
    "error": {
        "name": "SequelizeDatabaseError",
        "parent": {
            "code": "ER_BAD_FIELD_ERROR",
            "errno": 1054,
            "sqlState": "42S22",
            "sqlMessage": "Unknown column 'recordedAt' in 'field list'",
            "sql": "SELECT `id`, `title`, `lyrics`, `link`, `recordedAt`, `createdAt`, `updatedAt`, `userId` FROM `songs` AS `song`;"
        },
        "original": {
            "code": "ER_BAD_FIELD_ERROR",
            "errno": 1054,
            "sqlState": "42S22",
            "sqlMessage": "Unknown column 'recordedAt' in 'field list'",
            "sql": "SELECT `id`, `title`, `lyrics`, `link`, `recordedAt`, `createdAt`, `updatedAt`, `userId` FROM `songs` AS `song`;"
        },
        "sql": "SELECT `id`, `title`, `lyrics`, `link`, `recordedAt`, `createdAt`, `updatedAt`, `userId` FROM `songs` AS `song`;"
    }
}

I know that changes in database structure should be kept as migrations. I learned that in my case the sequelize-cli package will help me. I looked into the documentation (https://sequelize.org/master/manual/migrations.html) and successfully installed it in my Node.js project. However, I have a few problems:

  1. However, I don't understand how to replace my current database schema with the one given in config.js (development, test, production) when building a docker container.

  2. Documentation advises me to create models manually before migration, the following command (example): bash npx sequelize-cli model: generate --name User --attributes firstName: string, lastName: string, email: string. I don't want to create models manually, I would like them to be taken from my models folder, e.g. from models/song.js

  3. In the documentation, I found the following command to start the migration, although I decided to try it: bash npx sequelize-cli db: migrate. Running it locally get an error: bash ERROR: getaddrinfo EAI_AGAIN db db: 3306 while on the container from Node.js I got a few errors, I'm sure they are due to a bad combination of sequelize and sequelize-cli configurations.

Could someone explain to me how to implement this, and thus allow me to add fields to the database on the fly instead of removing it every time? In the future, this would be unacceptable in a production environment and I would like to configure it well.

0 Answers
Related