SQLite OneToMany ManyToOne synchronize: true Error: SQLITE_CONSTRAINT #2576
Description
Issue type:
[x] question
[x] bug report
[ ] feature request
[ ] documentation issue
Database system/driver:
[ ] cordova
[ ] mongodb
[ ] mssql
[ ] mysql / mariadb
[ ] oracle
[ ] postgres
[x] sqlite
[ ] sqljs
[ ] react-native
[ ] expo
TypeORM version:
[x] latest
[x] @next
[ ] 0.x.x (or put your version here)
Steps to reproduce or a small repository showing the problem:
First, this is my entity.
User.ts
import { Entity, Generated, Column, OneToMany, CreateDateColumn, UpdateDateColumn } from 'typeorm'
import Mutation from './Mutation'
@Entity('user')
export class User {
@Column({
unique: true,
primary: true
})
@Generated('uuid')
id: string
@Column()
name: string
@Column({
nullable: true
})
class: string
@Column({
nullable: true
})
nik: string
@Column({
nullable: true
})
address: string
@Column({
default: false
})
isDeleted: boolean
@Column({
default: 0
})
balance: number
@OneToMany(type => Mutation, mutation => mutation.user)
mutations: Mutation[]
@CreateDateColumn()
createDate: string
@UpdateDateColumn()
updateDate: string
}
export default UserMutation.ts
import { Entity, Generated, Column, ManyToOne, CreateDateColumn, UpdateDateColumn } from 'typeorm'
import User from './User'
@Entity('mutation')
export class Mutation {
@Column({
unique: true,
primary: true
})
@Generated('uuid')
id: string
@Column({
enum: ['ADD', 'SUBSTRACT']
})
type: string
@Column()
balance: number
@ManyToOne(type => User, user => user.mutations)
user: User
@CreateDateColumn()
createDate: string
@UpdateDateColumn()
updateDate: string
}
export default Mutationormconfig.js
module.exports = {
type: 'sqlite',
database: dbPath,
autoSchemaSync: true,
synchronize: true, /* I think this is cause the problem */
autoSave: true,
logging: true,
entities: ['./app/database/entity/*.js']
}So everything works fine. Insert new user, CRUD user, CRUD mutation.
But after close my app, and reopen my app, everything looks error.
On the console I see
query: BEGIN TRANSACTION
query: SELECT * FROM "sqlite_master" WHERE "type" = 'table' AND "name" IN ('mutation', 'user')
query: SELECT * FROM "sqlite_master" WHERE "type" = 'index' AND "tbl_name" IN ('mutation', 'user')
query: PRAGMA table_info("user")
query: PRAGMA index_list("user")
query: PRAGMA foreign_key_list("user")
query: PRAGMA table_info("mutation")
query: PRAGMA index_list("mutation")
query: PRAGMA foreign_key_list("mutation")
query: CREATE TABLE "temporary_mutation" ("id" varchar PRIMARY KEY NOT NULL, "type" varchar NOT NULL, "balance" integer NOT NULL, "createDate" datetime NOT NULL DEFAULT (datetime('now')), "updateDate" datetime NOT NULL DEFAULT (datetime('now')), "userId" varchar, CONSTRAINT "FK_81b6138f2087ac02096ff1ed441" FOREIGN KEY ("userId") REFERENCES "user" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION)
query: INSERT INTO "temporary_mutation"("id", "type", "balance", "createDate", "updateDate", "userId") SELECT "id", "type", "balance", "createDate", "updateDate", "userId" FROM "mutation"
query: DROP TABLE "mutation"
query: ALTER TABLE "temporary_mutation" RENAME TO "mutation"
query: CREATE TABLE "temporary_user" ("id" varchar PRIMARY KEY NOT NULL, "name" varchar NOT NULL, "class" varchar, "nik" varchar, "address" varchar, "isDeleted" boolean NOT NULL DEFAULT (0), "balance" integer NOT NULL DEFAULT (0), "createDate" datetime NOT NULL DEFAULT (datetime('now')), "updateDate" datetime NOT NULL DEFAULT (datetime('now')))
query: INSERT INTO "temporary_user"("id", "name", "class", "nik", "address", "isDeleted", "balance", "createDate", "updateDate") SELECT "id", "name", "class", "nik", "address", "isDeleted", "balance", "createDate", "updateDate" FROM "user"
query: DROP TABLE "user"
query failed: DROP TABLE "user"
error: { Error: SQLITE_CONSTRAINT: FOREIGN KEY constraint failed errno: 19, code: 'SQLITE_CONSTRAINT' }
query: ROLLBACK
Cannot make connection
Actually I can solve the error by drop all the data in mutation table, but I don't want to always delete all my data when I need to run the app.
After googling I found this #1530 (comment)
I think that's look legit , but I'm not sure how to fix correctly.
I've tried typeorm@next and @latest
Any answer will be help