drizzle-team / drizzle-team/drizzle-orm

[FEATURE]: Optimize default column values in bulk inserts

Open
#2,983 3 comments 2 reactions 0 assignees View on GitHub
enhancement performance qb/crud
Dominant language
TypeScript
Stars
35.8k
Forks
1.6k
Avg merge
2d 7h
Merged PRs (30d)
4

Description

### Describe what you want

In my use case with Drizzle, I need to store up to thousands of rows in a Postgres table within a single transaction. Most column values should use the default values defined in the table schema.

I am using a table similar to the following Drizzle schema:

```ts
const ipAddress = pgTable(
'IpAddress',
{
address: orm.inet('address').primaryKey().notNull(),
meta1: orm.varchar('meta1', { length: 255 }),
meta2: orm.varchar('meta2', { length: 255 }),
meta3: orm.varchar('meta3', { length: 255 }),
meta4: orm.varchar('meta4', { length: 255 }),
createdAt: orm.timestamp('createdAt').defaultNow().notNull(),
updatedAt: orm.timestamp('updatedAt').defaultNow().notNull()
}
)
```

When inserting rows, I run the following code to add up to 1,000 rows per batch:

```ts
await transaction.insert(ipAddress)
.values([
{ address: '1.1.1.1' },
{ address: '1.0.0.1' },
// ...
{ address: '8.8.8.8' },
])
.onConflictDoNothing({ target: ipAddress.address })
```

However, the debugging logs show that the resulting SQL query looks like this:

```sql
insert into "IpAddress"
("address", "meta1", "meta2", "meta3", "meta4", "createdAt", "updatedAt")
values
($1, default, default, default, default, default, default),
($2, default, default, default, default, default, default),
($3, default, default, default, default, default, default),
/* ... */
($1000, default, default, default, default, default, default)
on conflict ("address") do nothing
```

I was surprised to see that the query includes default values for all columns, even though they are already defined with default values in the schema. Given that only the address column is explicitly being set in the values object, I expected the generated SQL query to be:

```sql
insert into "IpAddress"
("address")
values
($1),
($2),
($3),
/* ... */
($1000)
on conflict ("address") do nothing
```

Although the query executes successfully, this behavior seems like an optimization opportunity. By omitting the unnecessary default values in the generated SQL, the query could be more efficient in query size and parsing, especially when handling large batches of data.

I am suggesting an optimization where Drizzle detects columns with defaults and omits them from the INSERT statement unless they are explicitly provided in the values. This could reduce the query size and improve performance, particularly when inserting a large number of rows.

While I understand this is not a bug, I’m introducing it as a feature request to explore its feasibility and potential benefits. As I’m relatively new to contributing to an ORM, I’m unsure of the trade-offs or potential complexities involved in making this change, so I’d appreciate any thoughts on whether this optimization is viable and worthwhile.

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.