Skip to content

feat: query-builder: Upsert in InsertQueryBuilder (next)#7076

Closed
nebkat wants to merge 37 commits intotypeorm:masterfrom
nebkat:upsert-next
Closed

feat: query-builder: Upsert in InsertQueryBuilder (next)#7076
nebkat wants to merge 37 commits intotypeorm:masterfrom
nebkat:upsert-next

Conversation

@nebkat
Copy link
Copy Markdown
Contributor

@nebkat nebkat commented Nov 18, 2020

Depends on #7058 (refactor: Query builders and entity/column/relation metadata)

Necessary due to large amount of duplicate code before refactor.

Only last commit is relevant to this PR! See #7058 for rest.

Description of change

class InsertQueryBuilder ... {
    // ON CONFLICT target
    onConflict(statement: string): this; // Raw conflict expression e.g. ON CONSTRAINT constraint name, or (index_expression)
    onConflict(columns: string[]): this; // Array of property paths that get mapped to database names, works with embedded

    // INSERT IGNORE or INSERT ... ON CONFLICT DO NOTHING
    orIgnore(
        ignore: boolean = true, // Now only accepts boolean, statement previously accepted was never used
        conflict?: string|string[] // Same as onConflict(), optional but useful in Postgres/Sqlite
    ): this;

    // INSERT ... ON DUPLICATE KEY UPDATE or INSERT ... ON CONFLICT conflict_target DO UPDATE
    orUpdate(
        overwrite: boolean|string[]|undefined = true, // Overwrite all with inserted values (true), or only property paths (string[])
        values?: ObjectLiteral, // Specific values to set on conflict, removes corresponding from overwrite
        conflict?: string|string[] // Same as onConflict(), defaults to all primary columns
    ): this;
}

class EntityManager {
    async upsert<Entity>(
        target: EntityTarget<Entity>,
        entity: QueryDeepPartialEntity<Entity>|(QueryDeepPartialEntity<Entity>[]),
        overwrite: boolean|string[] = true,
        values?: QueryDeepPartialEntity<Entity>,
        conflict?: string|string[]
    ): Promise<InsertResult>;
}

Breaks existing API so has to be released in next. Much easier to use than before, but more importantly provides efficient upsert in EntityManager that doesn't use save.

Example 1

class BigData {
    @PrimaryGeneratedColumn() id: number;
    @Column({name: "unique_email", unique: true}) email: string;
    @Column() a: string;
    @Column() b: string;
    @Column() c: string;
    @Column({name: "notActuallyD"}) d: string;
    @Column() e: string;
    @Column() whatsOurNamingScheme?: string;
    @Column(() => Embedded) embedded?: Embedded;
}

/**
 * NEW
 */
getConnection().createQueryBuilder(BigData, 'data')
        .insert()
        .values({email: "test@test.com", a: "a", b: "b", ..., embedded: { ... }})
        .orUpdate(true) // Overwrite all existing columns
        .onConflict(["email"]) // Correctly mapped to unique_email
        .execute();

/**
 * OLD
 */
getConnection().createQueryBuilder(BigData, 'data')
        .insert()
        .values({email: "test@test.com", a: "a", b: "b", ..., embedded: { ... }})
        .orUpdate({
                // Dangerous dealing in raw column database names
                overwrite: ["a", "b", ... "notActuallyD", ..., "whats_our_naming_scheme", "embedded_columns"],
                conflict_target: ["unique_email"] // Same here, treated as a raw column database name
         })
        .execute();

Example 2

class RefreshToken {
    @PrimaryColumn() series: number;
    @Column() token: string;
    @Column({default: 0}) uses: number;
}

/**
 * NEW
 */
for (let i = 0; i < 100; i++) {
    getConnection().createQueryBuilder(RefreshToken, 'token')
        .insert()
        .values({series: 1234, token: randomToken()})
        .orUpdate(["token"], {
                uses: () => "uses + 1"
        })
        .execute();
}

/**
 * OLD
 */
for (let i = 0; i < 100; i++) {
    getConnection().createQueryBuilder(RefreshToken, 'token')
        .insert()
        .values({series: 1234, token: randomToken()})
        .orUpdate({
                overwrite: ["token"],
                columns: ["uses"]
        })
        .setParameter("uses", () => "uses + 1") // Unclear where "uses" param came from
        .execute();
}

Improvement of #7029.

Fixes #1090.

Pull-Request Checklist

  • Code is up-to-date with the master branch
  • npm run lint passes with this change
  • npm run test passes with this change
  • This pull request links relevant issues as Fixes #0000
  • There are new or updated unit tests validating the change
  • Documentation has been updated to reflect this change
  • The new commits follow conventions explained in CONTRIBUTING.md

@nebkat nebkat force-pushed the upsert-next branch 3 times, most recently from 058ab5b to 1972b85 Compare November 18, 2020 03:24
@nebkat nebkat mentioned this pull request Jan 16, 2021
7 tasks
@nebkat nebkat force-pushed the upsert-next branch 4 times, most recently from 7f7e1dd to e96fdaa Compare January 21, 2021 01:04
@nebkat nebkat marked this pull request as ready for review January 21, 2021 01:32
Moves QueryBuilder and its subclasses to their own directory.
…oftDelete

* DeleteQueryBuilder, UpdateQueryBuilder and SoftDeleteQueryBuilder abstracted to ModificationQueryBuilder
* SoftDeleteQueryBuilder extends UpdateQueryBuilder
…er events

* All transaction code abstracted back to QueryBuilder.execute(), new abstract method executeInsideTransaction() added
* All before/after events abstracted back to QueryBuilder.execute(), new abstract methods execute[Before/After]QueryBroadcast()
…pression()

Drivers must explicitly return "NULL" string because Array.join() ignores null values
unlike string concatenation that was used previously.
More efficient method of finding the columns that are contained in a given entity object.

Previously the object was recursively scanned for keys and all were converted to property paths,
then for every property path the columns were searched to find a matching one. Now the columns are
selected as the object is scanned, and since ownColumns is searched instead of all columns it is
more efficient.
…dering

For certain drivers where the native parameters are not indexed (e.g. MySQL, "?")
the parameters which are found at the start of a query - specifically in UPDATE
SET param = ? - must be placed at the start of the native parameters objects. For
drivers where the parameters are indexed, the order must match the order of param
variable creation (e.g. Postgres, ":1").

This allows each driver to specify whether it cares about parameter order, allowing
UpdateQueryBuilder to reorder params if necessary.
…ryRunners

While SELECT queries tend to result in objects containing columns and their values,
DELETE/INSERT/UPDATE queries all have different outputs depending on the driver. This
moves the processing of those outputs and their mapping to the [Delete/Insert/Update]Result
objects to the driver QueryRunners so that it can be altered without changing the QueryBuilders.
Greatly simplifies getEntityValue, setEntityValue and related functions. Previously embedded objects
were explored using recursive functions and array shifting, which is now replaced with simple for of
loops. Improves readability and possibly performance.
* New QueryBuilder.createColumnValuePersistExpression() that converts (column, value) to expression string
    * Previously mostly duplicated between InsertQueryBuilder and UpdateQueryBuilder
    * SoftDeleteQueryBuilder now even closer to UpdateQueryBuilder

* Entity column and raw object key based expression generation also combined
    * Previously some duplicated code, e.g. function values always treated as raw SQL

* InsertQueryBuilder.getInsertedColumnsOrKeys() now returns string[] if using raw objects
    * Now supports extracting columns from multiple raw objects, without predefined insert columns

* Static ReturningResultsEntityUpdator.generateUUIDParameterName() for extracting generated UUID
    * Ensures that InsertQueryBuilder uses correct parameter name
Replaces string concatenation with joins in all query builders. No longer need to keep track
of whether a particular element of an expression should have a space before or after it.
Does not seem to do anything differently to Object.assign().

Introduce Mutable util to access readonly properties of classes when initializing.
…vers

Some drivers require functions such as ST_AsGeoJSON when persisting or selecting columns, this
moves the wrapping of the persist/select expressions from the query builder to the driver.
SAP HANA doesn't support null values as parameters so they are converted to the
raw NULL value. This moves that conversion from query builder to SapDriver.
Makes Driver.parametrizeValue() an optional function, currently implemented
only by SqlServer. Other drivers can now also add additional information to
their parameters if necessary.
…yBuilder

Moves all functionality from SoftDeleteQueryBuilder to UpdateQueryBuilder except the
"from()" function used to specific "soft delete FROM table", because a similar method
does not currently exist for UpdateQueryBuilder.
In preparation for "virtual"/computed columns, call internally generated columns "internal"
rather than "virtual" to avoid confusion.
…ction)

All options in @column() define a real column except for embedded. This adds an additional
@Embedded decorator for more explicitly defining embeddeds while still maintaining the
option in @column().
* Introduces new type to describe the fields parameter of an @Index or @unique.
* Allows (type) => [type.column] syntax inside embedded
…yMetadatas()

* Removes unnecessary array pushing/slicing
* Matches buildMigrations()/buildSubscribers() more closely
…elete

* Rename ModificationQueryBuilder to AbstractModifyQueryBuilder (UPDATE/DELETE)

* New AbstractPersistQueryBuilder for INSERT/UPDATE/DELETE
    * Common code for RETURNING expressions
    * Common code for INSERT/UPDATE value expressions

* Move UpdateDateColumn, VersionDateColumn, etc to value expression calculation
    * Matches InsertQueryBuilder behavior
refactor: driver: Move database specific EntityManagers to driver

Accidentally ammended during rebase
…lags and generators

Introduces `DriverConfig` and `DriverQueryGenerators` interfaces that include various
flags that drivers can use to customize behavior of the built in functionality. This
decouples these built in features from the drivers themselves, moving towards the
possibility of having each driver in a separate package.
* InsertQueryBuilder.orUpdate() now takes 3 parameters, overwrite columns, column values and conflict condition
    * Overwrite either specific property paths in array or `true` to overwrite all
    * Values same as UPDATE query values, replaces overwritten columns
    * Conflict see onConflict()

* InsertQueryBuilder.orIgnore() now takes 2 parameters, ignore and conflict condition
    * Ignore boolean whether to add IGNORE / DO NOTHING, no longer accepts statement
    * Conflict see onConflict()

* InsertQueryBuilder.onConflict() now accepts either raw expression or property paths array
    * For Postgres/Sqlite ON CONFLICT ...

* EntityManager.upsert(), Repository.upsert(), BaseEntity.upsert()

Fixes: typeorm#1090
@Pablo1107
Copy link
Copy Markdown

Why this PR was closed?

@nebkat
Copy link
Copy Markdown
Contributor Author

nebkat commented Apr 29, 2021

@Pablo1107 Maintainers have stated that they do not intend on merging the refactoring (#7058) necessary for this change and will instead focus on a new version of TypeORM (https://github.com/typeorm/typeorm/tree/typed-repository). Change otherwise works perfectly but will now require refactoring.

@nebkat nebkat mentioned this pull request Apr 29, 2021
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Labels

None yet

Development

Successfully merging this pull request may close these issues.

upsert with return functionality

2 participants