Primary/Foreign keys are not set on device using ActiveRecord

[patrickschmelter]

Hey,

I have build and tested an app in chrome browser, where it runs smoothly. The problem is that keys won't be set on real devices. Tested using iOS 11.2.6 and Android 7.1.1.

Sample Code: City Entity

import {Entity, PrimaryGeneratedColumn, Column, OneToMany, BaseEntity} from 'typeorm';
import {Tour} from "./tour";

@Entity("City")
export class City extends BaseEntity {

    @PrimaryGeneratedColumn()
    public id: number;
    @Column()
    public name: string;

    @OneToMany(type => Tour, tour => tour.city)
    public tours: Tour[];

}

Tour Entity

import {Entity, PrimaryGeneratedColumn, Column, ManyToOne, OneToMany, BaseEntity} from 'typeorm';
import {City} from "./city";

@Entity("Tour")
export class Tour extends BaseEntity {

    @PrimaryGeneratedColumn()
    public id: number;
    @Column()
    public name: string;

    @ManyToOne(type => City, city => city.tours)
    public city: City;

}

Tables are correctly generated:

console.log: creating a new table: City
console.info: query: CREATE TABLE "City" ("id" integer PRIMARY KEY AUTOINCREMENT NOT NULL, "name" varchar(10000) NOT NULL)
console.log: creating a new table: Tour
console.info: query: CREATE TABLE "Tour" ("id" integer PRIMARY KEY AUTOINCREMENT NOT NULL, "name" varchar(10000) NOT NULL, "cityId" integer)
console.log: creating a foreign keys: FK_6975824c36f19794cee06158b73 on table "Tour"
console.info: query: CREATE TABLE "temporary_Tour" ("id" integer PRIMARY KEY AUTOINCREMENT NOT NULL, "name" varchar(10000) NOT NULL, "cityId" integer, CONSTRAINT "FK_6975824c36f19794cee06158b73" FOREIGN KEY ("cityId") REFERENCES "City" ("id"))
console.info: query: INSERT INTO "temporary_Tour"("id", "name", "cityId") SELECT "id", "name", "cityId" FROM "Tour"
console.info: query: DROP TABLE "Tour"
console.info: query: ALTER TABLE "temporary_Tour" RENAME TO "Tour"

Connecting and inserting into the database:

if (platform.is('cordova')) {
            // Running on device or emulator
            await createConnection({
                type: 'cordova',
                database: 'db',
                location: 'default',
                logging: ['error', 'query', 'schema'],
                synchronize: true,
                entities: [
                    City,
                    Tour
                ]
            });
        }
        else {
            // Running app in browser
            await createConnection({
                type: 'sqljs',
                autoSave: true,
                location: 'browser',
                logging: ['error', 'query', 'schema'],
                synchronize: true,
                entities: [
                    City,
                    Tour
                ]
            });
        }

        let city = new City();
        city.name = "City";
        await city.save();
        console.log("city: " + JSON.stringify(city));

        let tour = new Tour();
        tour.name = "Tour";
        tour.city = city;
        await tour.save();
        console.log("tour: " + JSON.stringify(tour));

In the insert statements notice the NULL at the point where the foreign key should be, also the city should have an id at this point:

console.info: query: BEGIN TRANSACTION
console.info: query: INSERT INTO "City"("name") VALUES (?) -- PARAMETERS: ["City"]
console.info: query: COMMIT
console.log: city: {"name":"City"}
console.info: query: BEGIN TRANSACTION
console.info: query: INSERT INTO "Tour"("name", "cityId") VALUES (?, NULL) -- PARAMETERS: ["Tour"]
console.info: query: COMMIT
console.log: tour: {"name":"Tour", "city":{"name":"City"}}

Loading the relation obviously fails:

cities = await City.find({ relations: ["tours"] }) as City[];
console.log("cities loaded:" + JSON.stringify(this.cities));

or alternatively

 city.tours = await getConnection()
            .createQueryBuilder()
            .relation("City", "tours")
            .of(city)
            .loadMany();

The output is: console.log: cities loaded:[{"id":1,"name":"City","tours":[]}] Interestingly the city has an id here.

Manually adding the cityId as Column to the tour entity and setting it does not work either:

        let tour = new Tour();
        tour.name = "Tour";
        tour.city = city;
        tour.cityId = city.id;
        console.log("tour: " + JSON.stringify(tour));
        await tour.save();

Device output:

console.log: tour: {"name":"Tour","city":{"name":"City"}}
console.info: query: BEGIN TRANSACTION
console.info: query: INSERT INTO "Tour"("name", "cityId") VALUES (?, NULL) -- PARAMETERS: ["Tour"]
console.error: query failed: INSERT INTO "Tour"("name", "cityId") VALUES (?, NULL) -- PARAMETERS: ["Tour"]
console.error: error: [object Object]
console.info: query: ROLLBACK

Whereas the browser output looks as follows. On device the SELECT last_insert_rowid() is not called:

query:  BEGIN TRANSACTION
query:  INSERT INTO "City"("name") VALUES (?) -- PARAMETERS: ["City"]
query:  SELECT last_insert_rowid()
query:  COMMIT
city: {"name":"City","id":1}
query:  BEGIN TRANSACTION
query:  INSERT INTO "Tour"("name", "cityId") VALUES (?, ?) -- PARAMETERS: ["Tour",1]
query:  SELECT last_insert_rowid()
query:  COMMIT
tour: {"name":"Tour","city":{"name":"City","id":1},"cityId":1,"id":1}

Seems to be a problem with creating a primary key, as everything else is working as expected when I set it manually:

        let city = new City();
        console.log("city: " + JSON.stringify(city));
        city.id = 1;
        console.log("city: " + JSON.stringify(city));
        city.name = "City";
        await city.save();
        console.log("city: " + JSON.stringify(city));

Device Output:

console.log: city: {}
console.log: city: {"id":1}
console.info: query: SELECT "City"."id" AS "City_id", "City"."name" AS "City_name" FROM "City" "City" WHERE ("City"."id" = ?) -- PARAMETERS: [1]
console.info: query: BEGIN TRANSACTION
console.info: query: INSERT INTO "City"("name") VALUES (?, ?) -- PARAMETERS: ["City"]
console.info: query: COMMIT
console.log: city: {"id":1,"name":"City"}

[daniel-lang]

What version of TypeORM are you using?

If you are using next where is a known and fixed bug that occurs when inserting columns with the cordova driver. This issue has been fixed in this pull request. But as far as I know, no new alpha release was made since then.

[patrickschmelter]

Thats good news, I'm on 0.2.0-alpha.28 currently

[daniel-lang]

I just checked, that's the latest version, which was released before the fix. This means, that you will have to wait for the next alpha release

[daniel-lang]

0.2.0-alpha.29 should be available including the fix for your issue.

[daniel-lang]

@patrickschmelter if you still encounter this bug, feel free to reopen the issue