TypeORM查询SQLite的user_account表返回空数组问题排查

基础环境说明 #

已创建SQLite数据库，包含user_account表，表内预置2条记录，表结构定义如下：

CREATE TABLE user_account 
(
    id INTEGER PRIMARY KEY NOT NULL,
    username TEXT UNIQUE NOT NULL, 
    email TEXT, 
    login TEXT, 
    session_id TEXT NULL
);

现有代码 #

DataSource配置 #

import { DataSource } from "typeorm";
import { UserAccount } from "../entities/UserAccount";
import * as SQLite from 'sqlite3'

export const AppDataSource = new DataSource({
    type: "sqlite",
    database: "./inventory.db",
    //driver: SQLite,
    logging: false,
    synchronize: false,
    entities: [UserAccount]
})

AppDataSource.initialize()
    .then(() => {
        console.log("Data Source has been initialized!")
    })
    .catch((err) => {
        console.error("Error during Data Source initialization", err)
    })

UserAccount实体类 #

import { Entity, PrimaryGeneratedColumn, Column, BaseEntity } from "typeorm";

@Entity({name: "user_account"})
export class UserAccount extends BaseEntity{
    @PrimaryGeneratedColumn()
    public id: number=0;

    @Column()
    username: string = "";

    @Column()
    email: string = "";

    @Column()
    session_id: string = "";
}

LoginController控制器 #

import { Response, Request, response } from 'express';
import { ErrorResponse } from '../utils/errorResponse';
import { AppDataSource } from '../db/data-source';
import { UserAccount } from '../entities/UserAccount';

const login = async (req:Request,res:Response,next:any) => {
    const {username,password} = req.body;
    
    const userRepository = AppDataSource.getRepository(UserAccount);
    const meAndBearsPhoto = await userRepository.find();
    
    if (!username || !password){
        return next(new ErrorResponse("Please provide a valid username and Password", 400));
    };

    res.send(meAndBearsPhoto);
}
module.exports = {login}

index.ts入口文件 #

import "reflect-metadata";
import express, { Express } from 'express';
import dotenv from 'dotenv';
import cookieParser from 'cookie-parser';

dotenv.config();

const app: Express = express();
const port = process.env.PORT || 3500;

app.use(express.urlencoded({extended:false}));

//Built-in middleware for json
app.use(express.json());

//middleware for cookies
app.use(cookieParser());

//Route Middlewares
app.use("/api/auth", require("./src/routes/login"));

app.listen(port, () => {
  console.log(`⚡️[server]: SAT is running at http://localhost:${port}`);
});

异常表现 #

• 接口调用find方法预期返回2条表记录，实际返回空数组
• 测试findOneBy等其他查询方法，返回结果同样为空
• TypeORM自动生成的查询SQL如下：

SELECT "UserAccount"."id" AS "UserAccount_id", "UserAccount"."username" AS "UserAccount_username", "UserAccount"."email" AS "UserAccount_email", "UserAccount"."session_id" AS "UserAccount_session_id" FROM "user_account" "UserAccount"

• 手动在数据库客户端执行上述SQL语句，可以正常查询到预置的2条数据

根因定位 #

核心问题是数据库路径配置使用了相对路径./inventory.db：Node服务启动时的工作目录，和存放预置数据的inventory.db文件所在目录不一致，导致TypeORM实际连接的是服务启动目录下自动新建的空inventory.db文件，而非提前插入了2条记录的目标数据库。手动执行SQL时连接的是正确路径的存量数据库，因此能正常查到数据。
另外实体类配置和表结构存在不一致：表中存在login字段未在实体中映射，email、session_id字段的非空约束和表定义不匹配，不会直接导致本次查询为空，但会引发后续字段操作异常。

修复步骤 #

1. 修改数据库路径为绝对路径，避免相对路径受启动工作目录影响，示例配置如下，路径层级需根据自身项目目录结构调整：

import path from 'path';
// 其他原有导入保持不变

export const AppDataSource = new DataSource({
    type: "sqlite",
    // 基于当前配置文件位置拼接数据库文件的绝对路径
    database: path.resolve(__dirname, "../../inventory.db"),
    logging: true, // 排查阶段可开启SQL日志，确认执行逻辑
    synchronize: false,
    entities: [UserAccount]
})

2. 修正实体类配置，和表结构完全对齐：

import { Entity, PrimaryGeneratedColumn, Column, BaseEntity } from "typeorm";

@Entity({name: "user_account"})
export class UserAccount extends BaseEntity{
    @PrimaryGeneratedColumn()
    public id: number=0;

    @Column({ unique: true })
    username: string = "";

    @Column({ nullable: true })
    email: string = "";

    @Column({ nullable: true })
    login: string = "";

    @Column({ nullable: true })
    session_id: string = "";
}

3. 重启服务后调用接口，即可正常返回预期的2条用户记录。

内容的提问来源于stack exchange，提问作者Amando dos Santos Neto