TypeORM upsert - create if not exist
TypescriptTypeormTypescript Problem Overview
Does TypeORM include some functionnality to avoid this :
let contraption = await thingRepository.findOne({ name : "Contraption" });
if(!contraption) // Create if not exist
{
let newThing = new Thing();
newThing.name = "Contraption"
await thingRepository.save(newThing);
contraption = newThing;
}
Something like :
let contraption = await thingRepository.upsert({ name : "Contraption" });
Typescript Solutions
Solution 1 - Typescript
As pointed out by Tomer Amir, there is currently a partial solution for real upsert and a feature request is ATM opened on TypeORM's repository :
TypeORM upsert feature request
Partial solution :
await connection.createQueryBuilder()
.insert()
.into(Post)
.values(post2)
.onConflict(`("id") DO NOTHING`)
.execute();
await connection.createQueryBuilder()
.insert()
.into(Post)
.values(post2)
.onConflict(`("id") DO UPDATE SET "title" = :title`)
.setParameter("title", post2.title)
.execute();
Old answer actually points to the "update" way of doing what OP was asking for :
There's already a method for it : Repository<T>.save()
, of which documentation says :
> Saves all given entities in the database. If entities do not exist in > the database then inserts, otherwise updates.
But if you do not specify the id or unique set of fields, the save method can't know you're refering to an existing database object.
So upserting with typeORM is :
let contraption = await thingRepository.save({id: 1, name : "New Contraption Name !"});
Solution 2 - Typescript
For anyone finding this in 2021, Typeorm's Repository.save()
method will update or insert if it finds a match with a primary key. This works in sqlite too.
From the documentation:
/**
* Saves all given entities in the database.
* If entities do not exist in the database then inserts, otherwise updates.
*/
Solution 3 - Typescript
Use INSERT IGNORE to ignore duplicates on MySQL and Postgres:
await connection.createQueryBuilder()
.insert()
.into(Post)
.values(post)
.orIgnore()
.execute();
Solution 4 - Typescript
For anyone looking for a way to upsert multiple records and is using Postgres and TypeORM, you're able to access the row you're attempting to update/insert via the excluded keyword.
const posts = [{ id: 1, title: "First Post" }, { id: 2, title: "Second Post" }];
await connection.createQueryBuilder()
.insert()
.into(Post)
.values(posts)
.onConflict(`("id") DO UPDATE SET "title" = excluded."title"`)
.execute();
Solution 5 - Typescript
this might help when ONCONFLICT
doesn't work with MySQL. From Github
await getConnection()
.createQueryBuilder()
.insert()
.into(GroupEntity)
.values(updatedGroups)
.orUpdate({ conflict_target: ['id'], overwrite: ['name', 'parentId', 'web', 'avatar', 'description'] })
.execute();
Solution 6 - Typescript
2021 answer: v0.2.40 added native upsert support to TypeORM
Solution 7 - Typescript
There is now a library that plugs into TypeORM to help do this.
Solution 8 - Typescript
For those who struggle with unique constraint conflicts, like two unique fields, do this.
So first add a name to constraint in your entity,
@Entity()
@Unique('constraint_name', ['col_one', 'col_two'])
Then, you can use onConflict with ON CONSTRAINT
this.createQueryBuilder()
.insert()
.into(YourEntity)
.values(yourValues)
.onConflict(`ON CONSTRAINT constraint_name DO UPDATE SET whatever = 1`)
.execute()
);
Solution 9 - Typescript
You might want to checkout the "preload" method from the Repository class: https://typeorm.delightful.studio/classes/repository_repository.repository.html#preload
> Creates a new entity from the given plan javascript object. If entity already exist in the database, then it loads it (and everything related to it), replaces all values with the new ones from the given object and returns this new entity. This new entity is actually a loaded from the db entity with all properties replaced from the new object. Note that given entity-like object must have an entity id / primary key to find entity by. Returns undefined if entity with given id was not found.
As said above, the limitation is that you need to search by ID.
Solution 10 - Typescript
Note in your entity as user here
@OneToMany(type => Post, post => post.user, {
cascade: true
})
posts: Post[];
export const saveAllPosts = async (req: Request, res: Response) => {
const userRepository = getManager().getRepository(User);
const postRepository = getManager().getRepository(Post);
let i;
let newUsers:any = [];
let newUser:any = {};
let newPost:any = {};
for(i=1; i<=6; i ++) {
newUser = await userRepository.findOne({
where: { id: i}
});
if(typeof newUser == "undefined") {
newUser = new User();
console.log("insert");
} else {
console.log("update");
}
newUser.name = "naval pankaj test"+i;
newPost = await postRepository.findOne({
where: { userId: i}
});
if(typeof newPost == "undefined") {
newPost = new Post();
console.log("post insert");
} else {
console.log("post update");
}
newPost.title = "naval pankaj add post title "+i;
newUser.posts = [newPost];
newUsers.push(newUser);
}
await userRepository.save(newUsers);
res.send("complete");
};