Bulk save to PostgreSQL with TypeORM

Question

I wanted to do a bulk insert/update with TypeORM

The Test is described as below:

export class Test {
   @PrimaryColumn('integer')
   id: number;
 
   @Column('varchar', { length: 255 })
   testName: string;
 }

I wanted to do a batch insert for:

const test1 = new Test();
script.id = 1;
script.testName = 't1';

const test2 = new Test();
test2.id = 2;
test2.testName = 't2';

const test3 = new Test();
test3.id = 3;
test3.testName = 't3';

const tests = [test1, test2, test3];

When I try:

const testRepository = dataSource.getRepository(Test);
await testRepository.save(tests);

It does not work, however, testRepository.save(test1); works.

Could you please let me know how to do a batch insert? Especially if there are 1000s of rows.

Answer 1

You can use the insert method to do bulk inserts with TypeORM.

The basic syntax would be:

const testRepository = dataSource.getRepository(Test);

await testRepository.insert(tests);

The key things to note:

• insert takes an array of entity objects
• The entities must have their primary keys/ids populated if using a database that requires explicit ids (like postgres)
• For databases that support auto-increment ids (like sqlite, mysql), you can omit the ids

So for your example:

const tests = [
  { id: 1, testName: 't1'},
  { id: 2, testName: 't2'}, 
  { id: 3, testName: 't3'} 
];

await testRepository.insert(tests);

This will insert all 3 tests in a single query.

Some other tips:

• You can mix existing and new entities in the array
• Use chunks for huge batches to avoid query size limits
• Transactions can ensure atomic inserts