Expo SDK46中TypeORM操作SQLite报错：表已存在

错误信息 #

QueryFailedError: table "temporary_media_base" already exists (code 1 SQLITE_ERROR): , while compiling: CREATE TABLE "temporary_media_base" ("id" varchar PRIMARY KEY NOT NULL, "createdAt" datetime NOT NULL DEFAULT (datetime(' now')), "apiId" varchar, "updatedAt" datetime NOT NULL DEFAULT (datetime('now')), "deletedAt" datetime, "label" varchar, "base64" varchar NOT NULL, "type" varchar NOT NULL, "noteId" varchar, CONSTRAINT "UQ_3aa2a1f345b42d934c4a87e7b00" UNIQUE ("apiId"), CONSTRAINT "FK_01c465d2205772621fd3986d004" FOREIGN KEY ("noteId") REFERENCES "block_note" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION)

相关代码 #

MatiereEntity实体代码 #

import { Column, Entity, OneToMany } from "typeorm/browser";
import { BaseEntity } from "./base.entity";
import { EmploiTempsItemEntity } from "./emploi-temps-item";
import { SectionMatiereEntity } from "./section-matiere.entity";

@Entity('matiere')
export class MatiereEntity extends BaseEntity{
  @Column() label:string

  @Column() prof:string

  @OneToMany(
    type=>SectionMatiereEntity,
    (matiere)=>matiere.matiere,
    {
      nullable:true,
      cascade:true
    }
  )
  sectionMatiere:MatiereEntity[]

  @OneToMany(
    type=>EmploiTempsItemEntity,
    (matiere)=>matiere.matiere,
    {
      nullable:true,
      cascade:true
    }
  )
  emploiTemps:EmploiTempsItemEntity[]
}

package.json配置 #

{
  "name": "matrive-v2",
  "version": "1.0.0",
  "main": "node_modules/expo/AppEntry.js",
  "scripts": {
    "start": "expo start",
    "android": "expo start --android",
    "ios": "expo start --ios",
    "web": "expo start --web",
    "typeorm": "npx typeorm-ts-node-commonjs --dataSource dataBase/data-source.ts"
  },
  "dependencies": {
    "@babel/preset-typescript": "^7.18.6",
    "@expo/vector-icons": "^13.0.0",
    "@react-native-community/blur": "^4.2.0",
    "@react-native-community/datetimepicker": "^6.2.0",
    "@react-native-community/masked-view": "^0.1.11",
    "@react-native-community/netinfo": "^9.3.0",
    "@react-navigation/drawer": "^6.4.4",
    "@react-navigation/native": "^6.0.12",
    "@react-navigation/native-stack": "^6.8.0",
    "@react-navigation/stack": "^6.2.3",
    "@shopify/flash-list": "1.1.0",
    "babel-plugin-transform-typescript-metadata": "^0.3.2",
    "class-transformer": "^0.5.1",
    "class-validator": "^0.13.2",
    "date-fns": "^2.29.3",
    "expo": "~46.0.9",
    "expo-dev-client": "~1.2.1",
    "expo-device": "^4.3.0",
    "expo-document-picker": "^10.3.0",
    "expo-file-system": "^14.1.0",
    "expo-image-picker": "^13.3.1",
    "expo-media-library": "^14.2.0",
    "expo-notifications": "~0.16.1",
    "expo-splash-screen": "^0.16.2",
    "expo-sqlite": "^10.3.0",
    "expo-status-bar": "~1.4.0",
    "expo-system-ui": "^1.3.0",
    "expo-updates": "~0.14.6",
    "install": "^0.13.0",
    "moment": "^2.29.4",
    "native-base": "^3.4.13",
    "npm": "^8.19.2",
    "npx": "^10.2.2",
    "react": "18.0.0",
    "react-class-validator": "^1.4.0",
    "react-dom": "18.0.0",
    "react-native": "0.69.5",
    "react-native-anchor-carousel": "^4.0.1",
    "react-native-drawer": "^2.5.1",
    "react-native-dropdown-picker": "^5.4.2",
    "react-native-fab": "^1.0.18",
    "react-native-flash-message": "^0.3.1",
    "react-native-floating-action": "^1.22.0",
    "react-native-gesture-handler": "~2.5.0",
    "react-native-get-random-values": "^1.8.0",
    "react-native-modal": "^13.0.1",
    "react-native-modalize": "^2.1.1",
    "react-native-multi-selectbox": "^1.5.0",
    "react-native-multi-selectbox-typescript": "^0.1.2",
    "react-native-pager-view": "^5.4.24",
    "react-native-portalize": "^1.0.7",
    "react-native-reanimated": "~2.9.1",
    "react-native-safe-area-context": "4.3.1",
    "react-native-screens": "~3.15.0",
    "react-native-svg": "12.3.0",
    "react-native-swiper": "^1.6.0",
    "react-native-tab-view": "^3.1.1",
    "react-native-web": "~0.18.7",
    "react-redux": "^8.0.2",
    "redux": "^4.2.0",
    "reflect-metadata": "^0.1.13",
    "typeorm": "^0.3.9",
    "uninstall": "^0.0.0",
    "uuid": "^8.3.2"
  },
  "devDependencies": {
    "@babel/core": "^7.12.9",
    "@babel/plugin-proposal-export-namespace-from": "^7.18.9",
    "@types/node": "^18.7.14",
    "@types/react": "~18.0.14",
    "@types/react-native": "~0.69.1",
    "ts-node": "^10.9.1",
    "typescript": "~4.3.5"
  },
  "private": true
}

TypeORM数据源配置 #

import * as SQLite from "expo-sqlite";
import { DataSourceOptions } from "typeorm/browser";
import { BloackNoteEntity } from "./entities/block-note.entity";
import { ConfigEntity } from "./entities/config.entity";
import { EmploiTempsItemEntity } from "./entities/emploi-temps-item";
import { EmploiTempsEntity } from "./entities/emploi-temps.entity";
import { EventEntity } from "./entities/envent.entity";
import { EventNotificationEntity } from "./entities/event-notifiaction.entity";
import { EventTypeEntity } from "./entities/event-type.entity";
import { MatiereEntity } from "./entities/matiere.entity";
import { MediaEntity } from "./entities/media.entity";
import { NoteEntity } from "./entities/note.entity";
import { SchoolYearEntity } from "./entities/school-year.entity";
import { SectionMatiereEntity } from "./entities/section-matiere.entity";
import { sectionEntity } from "./entities/section.entity";
import { TypeNoteEntity } from "./entities/type-note.entity";
import { UserEntity } from "./entities/user.enity";

const config:DataSourceOptions = {
  database: "mydatabase",
  driver: SQLite,
  entities: [
    ConfigEntity,TypeNoteEntity,NoteEntity,
    UserEntity,BloackNoteEntity,EmploiTempsEntity,EmploiTempsItemEntity,
    SectionMatiereEntity,EventEntity,EventTypeEntity,EventNotificationEntity,
    MatiereEntity, sectionEntity,MediaEntity, SchoolYearEntity
  ],
  type: "expo",
  synchronize:true,
  entitySkipConstructor:true
};

export default config;

解决方案 #

1. 排查MediaEntity与BaseEntity的继承关系 #

错误中提到的temporary_media_base表和MediaEntity强相关，先检查MediaEntity的代码：

• 确认MediaEntity是否使用了@TableInheritance装饰器，TypeORM处理表继承时会创建临时表，若继承配置有误（比如BaseEntity被错误标记为@Entity而非抽象类），会导致重复创建临时表。
• 确保BaseEntity是抽象类，且未添加@Entity()装饰器，仅作为实体基类提供通用字段（如id、createdAt等）。

2. 清除现有数据库（临时解决） #

synchronize: true在开发环境中，若数据库结构变更未被正确同步，会导致表冲突：

• 在Expo应用中，通过清除应用数据（Android：设置-应用-你的应用-存储-清除数据；iOS：卸载重装应用）重置数据库。
• 或者临时修改TypeORM配置中的database名称（比如改成mydatabase_new），测试是否能正常创建新数据库。

3. 关闭synchronize，改用手动迁移 #

synchronize: true仅适合快速原型开发，生产环境禁用，且易引发表同步冲突：

1. 将TypeORM配置中的synchronize改为false。
2. 使用TypeORM迁移工具生成迁移文件：

   npm run typeorm migration:generate -- -n InitDatabase
   

3. 执行迁移文件，手动同步数据库结构：

   npm run typeorm migration:run
   

4. 检查实体注册重复 #

确认TypeORM配置的entities数组中，没有重复注册MediaEntity或相关实体，重复注册会导致TypeORM多次尝试创建同一张表（包括临时表）。

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