upsert with return functionality
Problem
proposing the following functionality (essentially `insert... on conflict.. returning..`): [code block] at least in postgres, this is possible in a single operation, but it's not currently possible in a single operation with TypeORM (example not tested, just for showing a current rough solution..): [code block]
Unverified for your environment
Select your OS to check compatibility.
1 Fix
Implement Upsert with Return Functionality in TypeORM
TypeORM does not currently support the PostgreSQL 'INSERT ... ON CONFLICT ... RETURNING ...' syntax in a single operation, which leads to the inability to retrieve the inserted or updated record directly after an upsert operation.
Awaiting Verification
Be the first to verify this fix
- 1
Create a Custom Repository Method
Define a custom repository method that utilizes raw SQL to perform the upsert operation with returning functionality. This allows you to bypass TypeORM's limitations.
typescriptasync upsertWithReturn(entity: YourEntity): Promise<YourEntity> { const result = await this.entityManager.query(`INSERT INTO your_table (columns) VALUES ($1, $2) ON CONFLICT (unique_column) DO UPDATE SET column = $3 RETURNING *`, [value1, value2, updatedValue]); return result[0]; } - 2
Update Entity Manager Configuration
Ensure that your EntityManager is properly configured to allow the execution of raw SQL queries. This may involve checking database connection settings and permissions.
typescript// Ensure your connection options allow raw queries const connection = await createConnection({ type: 'postgres', host: 'localhost', port: 5432, username: 'user', password: 'password', database: 'database', synchronize: true, logging: false }); - 3
Test the Upsert Method
Write unit tests to verify that the new upsert method works as expected. Ensure that it correctly inserts new records and updates existing ones while returning the correct data.
typescriptit('should upsert and return the entity', async () => { const entity = await repository.upsertWithReturn({/* entity data */}); expect(entity).toHaveProperty('id'); }); - 4
Document the New Functionality
Update the project documentation to include the new upsert method, its usage, and any limitations or considerations for developers using this functionality.
typescript// Documentation example /** * Upserts an entity and returns the inserted or updated entity. * @param entity - The entity to upsert. * @returns The upserted entity. */
Validation
Confirm that the upsert method correctly inserts new records and updates existing ones by running tests and checking the returned data against expected results.
Sign in to verify this fix
Environment
Submitted by
Alex Chen
2450 rep