provide option to Repository.save don't set null value for nullable column

[umegaya]

Issue type:

[ ] question [ ] bug report [x] feature request [ ] documentation issue

Database system/driver:

[ ] cordova [ ] mongodb [ ] mssql [x] mysql / mariadb [ ] oracle [ ] postgres [ ] cockroachdb [ ] sqlite [ ] sqljs [ ] react-native [ ] expo

TypeORM version:

[ ] latest [ ] @next [x] 0.2.17

Steps to reproduce or a small repository showing the problem:

• code

// Record.ts
export class Record {

    @PrimaryGeneratedColumn()
    id?: number;

    @Column({ nullable: true })
    text?: string;

    @Column({ default: "foo" })
    text2?: string;

    @Column({ type: "varbinary", nullable: true })
    bytes?: Buffer;

    @Column()
    numeric: number;

    @UpdateDateColumn()
    last_update?: Date;

    @CreateDateColumn()
    created?: Date;
}

// test.ts
import {createConnection} from "typeorm";
import {Record} from "./record";

async function main() {
    const conn = await createConnection({
        type: "mysql",
        host: "localhost",
        port: 3306,
        username: "root",
        password: "admin",
        database: "testdb",
        entities: [ Record ],
        synchronize: true,
        logging: ["query"]
    });

    const repo = conn.getRepository(Record);
    const r: Record = {
        numeric: 1,
        text: "hoge",
        bytes: Buffer.from("abcd")
    }
    await repo.save(r);

    console.log(r);

    const r2: Record = { 
        id: r.id,
        numeric: 2,
        text: "fuga"
    };

    await repo.save(r2);

    console.log(r2);
}

main().then(() => {
    console.log("ok");
    process.exit(0);
}).catch((e) => {
    console.log("ng", e, e.stack);
    process.exit(1);
}); 

• result

// migration SQLs (omitted)...
query: START TRANSACTION
query: INSERT INTO `record`(`id`, `text`, `text2`, `bytes`, `numeric`, `last_update`, `created`) VALUES (DEFAULT, ?, DEFAULT, ?, ?, DEFAULT, DEFAULT) -- PARAMETERS: ["hoge",{"type":"Buffer","data":[97,98,99,100]},1]
query: SELECT `Record`.`id` AS `Record_id`, `Record`.`text2` AS `Record_text2`, `Record`.`last_update` AS `Record_last_update`, `Record`.`created` AS `Record_created` FROM `record` `Record` WHERE `Record`.`id` = ? -- PARAMETERS: [12]
query: COMMIT
{ numeric: 1,
  text: 'hoge',
  bytes: <Buffer 61 62 63 64>,
  id: 12,
  text2: 'foo',
  last_update: 2019-05-19T19:07:10.217Z,
  created: 2019-05-19T19:07:10.217Z }
query: SELECT `Record`.`id` AS `Record_id`, `Record`.`text` AS `Record_text`, `Record`.`text2` AS `Record_text2`, `Record`.`bytes` AS `Record_bytes`, `Record`.`numeric` AS `Record_numeric`, `Record`.`last_update` AS `Record_last_update`, `Record`.`created` AS `Record_created` FROM `record` `Record` WHERE `Record`.`id` IN (?) -- PARAMETERS: [12]
query: START TRANSACTION
query: UPDATE `record` SET `text` = ?, `numeric` = ?, `last_update` = CURRENT_TIMESTAMP WHERE `id` IN (?) -- PARAMETERS: ["fuga",2,12]
query: SELECT `Record`.`id` AS `Record_id`, `Record`.`last_update` AS `Record_last_update` FROM `record` `Record` WHERE `Record`.`id` = ? -- PARAMETERS: [12]
query: COMMIT
{ id: 12,
  numeric: 2,
  text: 'fuga',
  bytes: null,
  last_update: 2019-05-19T19:07:10.000Z }
ok

@pleerock I saw https://github.com/typeorm/typeorm/issues/1617 and understand this behavior is intended.

but we want to treat updated partial entity by Repository.save as which and how columns changed with save operation. to efficiently send update result to client. (suppose there is table which huge number of column, including auto updating columns like UpdateColumnDate, and a few of them are updated according to different type of API)

it seems that except nullable column problem, Repository.save works as we intended and we can know which and how columns changed with save operation via updated partial entity after Repository.save finished.

so, can you add the option which omits this behavior?

actually I don't fully get your https://github.com/typeorm/typeorm/issues/1617#issuecomment-366985111 and https://github.com/typeorm/typeorm/issues/1617#issuecomment-367062762 . because returned deep partial entity only have null value if database value is actually null, otherwise contains actual value right? but as long as testing with above example, Record.bytes have null value even if value is not null in database.

[Kononnable]

Just to make it clear: You expect r2 to have no bytes property after saving, right?

[umegaya]

@Kononnable yes.

as I put in result, r2 has null value for bytes column after saving.

{ id: 12,
  numeric: 2,
  text: 'fuga',
  bytes: null,
  last_update: 2019-05-19T19:07:10.000Z }

it would be better if r2 become

{ id: 12,
  numeric: 2,
  text: 'fuga',
  last_update: 2019-05-19T19:07:10.000Z }

so that only changed columns to be retrieved, with some option for saving.

[Kononnable]

bytes is not null in db, so returning null here is definitely a bug.

[duwejeferson]

I'm having the same problem. After a partial update (using .save()) nullable columns that weren't provided are set to null, even when they have value in DB, so if I call a save later, after some processing, they are wiped out.

[JotFX]

I am having the same problem. I am not specifying a nullable column, and the value gets wiped to NULL.

[tbener]

I'm having the same problem. Any updates on this? Any workarounds?

[Ginden]

Any workarounds?

Load entity before saving it.

[lucas-labs]

Any workarounds?

Load entity before saving it.

This is still dangerous, as an update could've happend between loading and .save()

[alcalyn]

Having the issue with:

    @Column({ type: 'char', length: 60, nullable: true, select: false })
    password?: string;

I don't select it by default, except for authentication.

When I use repository.save() on an entity having user as relation, user.password first switch from undefined to null. When I presist again, it deletes the user password