Understanding the SQLite Error: no such table: story

Introduction

In this article, we will delve into the details of a common error that occurs when working with Sequelize and SQLite databases. The error “SQLITE_ERROR: no such table: story” can be puzzling at first glance, but once understood, it is relatively easy to resolve.

Setting Up the Environment

Before we begin, let’s set up our environment to replicate the issue. We will use the following dependencies:

• Node.js 16.17.0
• npm 6.14.13
• Sequelize 5.22.5
• SQLite 3.36.0

We will create a new project with npm and install the required dependencies.

npm init -y
npm install sequelize sqlite3

Next, we will create a config directory and add our database configuration file (config.json) with the following content:

{
  "development": {
    "database": "localDB",
    "dialect": "sqlite",
    "redBotStoryStateUrl": "http://localhost:5000/storystate?",
    "address": "http://localhost:3001",
    "refreshStateAllMs": 10000,
    "listenOnPort": 3001,
    "checkStoryState": false
  },
  "production": {
    // production configuration goes here...
  }
}

Creating the Migrations File

We will create a new file (20210622124450-modify_story_add_storyNumber.js) in the db/migrations directory with the following content:

'use strict';

module.exports = {
  up: (queryInterface, Sequelize) => {
    return queryInterface.addColumn('story', 'storyNumber', {
      type: Sequelize.STRING,
      allowNull: true
    })
  },

  down: (queryInterface, Sequelize) => {
    return queryInterface.removeColumn('story', 'storyNumber')
  }
};

Running the Migrations

We will use sequelize-cli to run the migrations.

npx sequelize-cli db:migrate

This should create a new table named “story” with a column named “storyNumber”.

Understanding the Error

The error “SQLITE_ERROR: no such table: story” occurs when Sequelize tries to add a column to a non-existent table. In this case, the story table does not exist in our database.

Solution

To fix this issue, we need to create the story table before running the migrations.

We can do this by adding the following code to our migration file:

'use strict';

module.exports = {
  up: (queryInterface, Sequelize) => {
    return queryInterface.createTable('story', {
      id: {
        type: Sequelize.INTEGER,
        autoIncrement: true,
        primaryKey: true
      },
      storyNumber: {
        type: Sequelize.STRING,
        allowNull: true
      }
    })
  },

  down: (queryInterface, Sequelize) => {
    return queryInterface.removeColumn('story', 'storyNumber')
  }
};

Alternatively, we can use the createTable method to create the table before running the migrations.

npx sequelize-cli db:migrate --create-table story

Conclusion

In this article, we discussed a common error that occurs when working with Sequelize and SQLite databases. We also went through the steps of setting up our environment, creating the necessary migration files, and running the migrations. Finally, we provided solutions to fix the issue by either creating the table manually or using the createTable method.

Last modified on 2024-01-21