Sqlite error: no such table keyword

[MyWay]

Hi, fresh docker-compose install, I'm getting:

Sequelize CLI [Node: 20.11.0, CLI: 6.6.2, ORM: 6.36.0]

Loaded configuration file "database/config.js".
Using environment "production".
== 1707068556345-add-new-keyword-fields: migrating =======

ERROR: SQLITE_ERROR: no such table: keyword

docker-compose.yml

version: '3.8' 

services:
  serpbear:
    image: towfiqi/serpbear
    container_name: serpbear 
    restart: always
    volumes: 
      - serpbear_data:/app/data
    ports:
      - 6000:3000
    environment:
      USER: "${USER}"
      PASSWORD: "${PASSWORD}"
      SECRET: "${SECRET}"
      APIKEY: "${APIKEY}"
      NEXT_PUBLIC_APP_URL: "https://myurl"
      SEARCH_CONSOLE_CLIENT_EMAIL: "${SEARCH_CONSOLE_CLIENT_EMAIL}"
      SEARCH_CONSOLE_PRIVATE_KEY: "${SEARCH_CONSOLE_PRIVATE_KEY}"

[towfiqi]

this happens when your app initiates before the volume mounts. Not sure how to fix it as I have limited knowledge of docker-compose. One way to fix this is to log in to the container that stopped with the above error, and then run this command:

npx sequelize-cli db:migrate --env production

[MyWay]

I'm not sure that's the issue. I have tried connecting, re-running and I got the same error. Maybe missing table creation before something?

[drissbri]

you have to delete the existing migration files at database/migrations and create these new ones : create_keyword :

'use strict';

module.exports = {
  up: async (queryInterface, Sequelize) => {
    await queryInterface.createTable('keyword', {
      ID: {
        allowNull: false,
        autoIncrement: true,
        primaryKey: true,
        type: Sequelize.INTEGER
      },
      keyword: {
        type: Sequelize.STRING,
        allowNull: false
      },
      device: {
        type: Sequelize.STRING,
        allowNull: true,
        defaultValue: 'desktop'
      },
      country: {
        type: Sequelize.STRING,
        allowNull: true,
        defaultValue: 'US'
      },
      city: {
        type: Sequelize.STRING,
        allowNull: true,
        defaultValue: ''
      },
      latlong: {
        type: Sequelize.STRING,
        allowNull: true,
        defaultValue: ''
      },
      domain: {
        type: Sequelize.STRING,
        allowNull: false,
        defaultValue: '{}'
      },
      lastUpdated: {
        type: Sequelize.STRING,
        allowNull: true
      },
      added: {
        type: Sequelize.STRING,
        allowNull: true
      },
      position: {
        type: Sequelize.INTEGER,
        allowNull: false,
        defaultValue: 0
      },
      history: {
        type: Sequelize.STRING,
        allowNull: true,
        defaultValue: JSON.stringify([])
      },
      url: {
        type: Sequelize.STRING,
        allowNull: true,
        defaultValue: JSON.stringify([])
      },
      tags: {
        type: Sequelize.STRING,
        allowNull: true,
        defaultValue: JSON.stringify([])
      },
      lastResult: {
        type: Sequelize.STRING,
        allowNull: true,
        defaultValue: JSON.stringify([])
      },
      sticky: {
        type: Sequelize.BOOLEAN,
        allowNull: true,
        defaultValue: true
      },
      updating: {
        type: Sequelize.BOOLEAN,
        allowNull: true,
        defaultValue: false
      },
      lastUpdateError: {
        type: Sequelize.STRING,
        allowNull: true,
        defaultValue: 'false'
      },
      settings: {
        type: Sequelize.STRING,
        allowNull: true
      }
    });
  },

  down: async (queryInterface, Sequelize) => {
    await queryInterface.dropTable('keyword');
  }
};`

create_domain :
`'use strict';

module.exports = {
  up: async (queryInterface, Sequelize) => {
    await queryInterface.createTable('domain', {
      ID: {
        allowNull: false,
        autoIncrement: true,
        primaryKey: true,
        type: Sequelize.INTEGER
      },
      domain: {
        type: Sequelize.STRING,
        allowNull: false,
        unique: true
      },
      slug: {
        type: Sequelize.STRING,
        allowNull: false,
        unique: true
      },
      keywordCount: {
        type: Sequelize.INTEGER,
        allowNull: false,
        defaultValue: 0
      },
      lastUpdated: {
        type: Sequelize.STRING,
        allowNull: true
      },
      added: {
        type: Sequelize.STRING,
        allowNull: true
      },
      tags: {
        type: Sequelize.STRING,
        allowNull: true,
        defaultValue: JSON.stringify([])
      },
      notification: {
        type: Sequelize.BOOLEAN,
        allowNull: true,
        defaultValue: true
      },
      notification_interval: {
        type: Sequelize.STRING,
        allowNull: true,
        defaultValue: 'daily'
      },
      notification_emails: {
        type: Sequelize.STRING,
        allowNull: true,
        defaultValue: ''
      },
      search_console: {
        type: Sequelize.STRING,
        allowNull: true
      }
    });
  },

  down: async (queryInterface, Sequelize) => {
    await queryInterface.dropTable('domain');
  }
};

[Yo-shad]

@drissbri Nice, it works

[towfiqi]

That's a hacky way and might cause issues in the future though. Are you guys having this issue when you are trying to create a new serpbear instance with a new volume or updating an existing instance? Are you using docker-compose?

What happens when you run the npx sequelize-cli db:migrate --env production inside the container that I provided as a fix for this issue in the comment aboe?

[Yo-shad]

I'm run it locally. First I tried to pull the latest version but got an error Fatal: Not A Git Repository” (Or Any Of The Parent Directories... while I was in serpbear directory. Then tried initialize the repository and try again. I decided to delete everything and start from scratch. Everything worked well but when hit npm run prestart and start:all I got :

Sequelize CLI [Node: 20.11.0, CLI: 6.6.2, ORM: 6.34.0] [start] [start] Loaded configuration file "database\config.js". [start] Using environment "production". [start] == 1707068556345-add-new-keyword-fields: migrating ======= [start] [start] ERROR: SQLITE_ERROR: no such table: keyword [start]

Didnt try your fix yet, what to do now? What kind of issue @drissbri fix can cause?

[MyWay]

That's a hacky way and might cause issues in the future though. Are you guys having this issue when you are trying to create a new serpbear instance with a new volume or updating an existing instance? Are you using docker-compose?

What happens when you run the npx sequelize-cli db:migrate --env production inside the container that I provided as a fix for this issue in the comment aboe?

The issue seems to be only on a new instance with a new volume. Updating from the old db worked for me.

[vexersa]

Same issue here.

I have tried to run npx sequelize-cli db:migrate --env production which produces:

/app $ npx sequelize-cli db:migrate --env production

Sequelize CLI [Node: 20.11.0, CLI: 6.6.2, ORM: 6.36.0]

Loaded configuration file "database/config.js".
Using environment "production".
== 1707068556345-add-new-keyword-fields: migrating =======

ERROR: SQLITE_ERROR: no such table: keyword

Note that this is using the default docker-compose.yaml file with no changes.