使用sequelize-typescript时遇SQLITE_ERROR: no such table: Users问题求助

在依赖注入风格的Express.js REST API中使用sequelize-typescript时，出现错误：

Error occured due to SQLITE_ERROR: no such table: Users

以下是相关代码：

实体文件 user.ts #

export class User extends Model {
@PrimaryKey
@Column({
    type: DataType.INTEGER,
    autoIncrement: true
})
declare userId: number

@Column({
    type: DataType.STRING
})
declare firstName : string;

@Column({
    type: DataType.STRING
})
declare lastName: string;

@Column({
    type: DataType.STRING
})
declare email: string;

@Column({
    type: DataType.STRING
})
declare phone: string;
}

数据库中间件代码 #

export default class DatabaseMiddleware {
private databaseInstance : Sequelize;

constructor() {
    this.databaseInstance = new Sequelize({
        database: process.env.MAMLAKHA_DATABASE_NAME,
        dialect: "sqlite",
        host: process.env.MAMLAKHA_DATABASE_HOST,
        username: process.env.MAMLAKHA_DATABASE_USERNAME,
        password: process.env.MAMLAKHA_DATABASE_PASSWORD,
        // storage: process.env.MAMLAKHA_DATABASE_STORAGE_PATH,
        logging(sql, timing) {
            console.log("The SQL statement from Sequelize executed is", sql, timing);
        },
        models: [User],
        repositoryMode: true
    });
}

public async connectToDatabase() {
    try {
        await this.databaseInstance.authenticate();
        if(process.env.ENVIRONMENT_PROFILE === "development") {
            await this.databaseInstance.sync({ alter: true });
        }

        console.log("Connection to database has been established successfully");
    } catch(error) {
        console.error("Unable to connect to database due to", error);
    }
}

public getDatabaseInstance() {
    return this.databaseInstance;
}
}

REST API启动主代码 #

class App {
private baseDirectory : string;
private expressApp : Express

// Middleware Instances
private databaseMiddleware : DatabaseMiddleware;

constructor() {
    dotenv.config({ path: "./.env.development" });
    this.baseDirectory = __dirname;
    this.expressApp = express();
    routingContainer(Container);

    useExpressServer(this.expressApp, {
        routePrefix: process.env.MAMLAKHA_ROUTE_PREFIX,
        defaultErrorHandler: false,
        controllers: [this.baseDirectory + `/**/controllers/*.ts`]
    });

    this.expressApp.use(bodyParser.urlencoded({ extended: false }));
    this.expressApp.use(bodyParser.json());

    this.databaseMiddleware = new DatabaseMiddleware();
}

run() {
    this.expressApp.listen(process.env.MAMLAKHA_REST_API_PORT, () => {
        console.info(`Hello there, it's Mamlakha app and it's running on port ${process.env.MAMLAKHA_REST_API_PORT} 🤘`);
    });

    this.databaseMiddleware.connectToDatabase();
}

get expressAppInstance() : Express {
    return this.expressApp;
}
}
const main : App = new App(); main.run();

解决方案 #

1. 给实体类添加@Table装饰器 #

sequelize-typescript需要通过@Table装饰器识别实体对应的数据库表，否则sync操作不会创建表。修改user.ts：

import { Model, Table, PrimaryKey, Column, DataType } from 'sequelize-typescript';

@Table
export class User extends Model {
  // ... 原有字段定义
}

2. 启用SQLite存储路径配置 #

SQLite默认使用内存数据库，重启后数据和表会丢失。取消storage配置的注释，并确保.env文件中配置了正确的存储路径（比如./database.sqlite）：

this.databaseInstance = new Sequelize({
    // ... 其他配置
    storage: process.env.MAMLAKHA_DATABASE_STORAGE_PATH,
    // ... 其他配置
});

3. 确保数据库初始化完成后再启动服务 #

当前代码先启动Express服务再初始化数据库，可能导致控制器在表创建前发起查询。修改App的run方法：

async run() {
    try {
        await this.databaseMiddleware.connectToDatabase();
        this.expressApp.listen(process.env.MAMLAKHA_REST_API_PORT, () => {
            console.info(`Hello there, it's Mamlakha app and it's running on port ${process.env.MAMLAKHA_REST_API_PORT} 🤘`);
        });
    } catch (error) {
        console.error("Failed to start app due to database connection issue:", error);
    }
}

4. 检查环境变量配置 #

确认.env.development中的ENVIRONMENT_PROFILE值为development，否则不会执行sync({ alter: true })创建表。

5. 手动指定表名（可选） #

如果sequelize自动生成的表名不符合预期，可以在@Table装饰器中明确指定：

@Table({ tableName: 'Users' })
export class User extends Model {
  // ... 原有字段定义
}

内容的提问来源于stack exchange，提问作者Adrian Joseph