Insert Queries

Insert data into your database using Drizzle ORM’s type-safe insert query builder with support for single and bulk inserts, conflict handling, and returning values.

Drizzle ORM provides a type-safe and intuitive API for inserting data into your database tables.

Basic Insert

Insert a single row into a table:

import { db } from './db';
import { users } from './schema';

const result = await db.insert(users).values({
  name: 'John Doe',
  email: '[e-mail]',
  age: 30,
});

Bulk Insert

Insert multiple rows in a single query:

const result = await db.insert(users).values([
  {
    name: 'John Doe',
    email: '[e-mail]',
    age: 30,
  },
  {
    name: 'Jane Smith',
    email: '[e-mail]',
    age: 28,
  },
  {
    name: 'Bob Johnson',
    email: '[e-mail]',
    age: 35,
  },
]);

Bulk inserts are more efficient than multiple single inserts as they execute in a single database round-trip.

Insert with Returning

Get the inserted row(s) back from the database:

Return All Columns

const [newUser] = await db.insert(users)
  .values({
    name: 'John Doe',
    email: '[e-mail]',
  })
  .returning();

// newUser contains all columns including auto-generated id
console.log(newUser.id);

.returning() is supported in PostgreSQL, SQLite, and MySQL 8.0+. It’s not available in older MySQL versions.

On Conflict Do Nothing

Ignore inserts that would violate constraints:

PostgreSQL

import { db } from './db';

// Ignore conflicts on email column
await db.insert(users)
  .values({ name: 'John', email: '[e-mail]' })
  .onConflictDoNothing();

// Ignore conflicts on specific columns
await db.insert(users)
  .values({ name: 'John', email: '[e-mail]' })
  .onConflictDoNothing({ target: users.email });

On Conflict Do Update (Upsert)

Update existing rows when conflicts occur:

Basic Upsert

import { db } from './db';

// Update name if email already exists
await db.insert(users)
  .values({ email: '[e-mail]', name: 'John Doe' })
  .onConflictDoUpdate({
    target: users.email,
    set: { name: 'John Doe Updated' },
  });

Insert from Select

Insert data from another query:

import { db } from './db';
import { users, archivedUsers } from './schema';

// Copy inactive users to archive
await db.insert(archivedUsers)
  .select(
    db.select().from(users).where(eq(users.active, false))
  );

The selected columns must match the target table’s column structure.

Insert with SQL Expressions

Use SQL expressions in insert values:

import { sql } from 'drizzle-orm';

await db.insert(users).values({
  name: 'John Doe',
  email: '[e-mail]',
  createdAt: sql`now()`,
  metadata: sql`jsonb_build_object('source', 'web')`,
});

Insert with Default Values

Omit columns with default values:

// If createdAt has a default value in schema
await db.insert(users).values({
  name: 'John Doe',
  email: '[e-mail]',
  // createdAt will use database default
});

PostgreSQL: Override System Values

Insert into generated columns:

// PostgreSQL only: override GENERATED ALWAYS columns
await db.insert(users)
  .overridingSystemValue()
  .values({
    id: 100, // Override auto-increment
    name: 'John',
    email: '[e-mail]',
  });

Type Safety

Drizzle provides complete type safety for inserts:

await db.insert(users).values({
  name: 'John Doe',
  email: '[e-mail]',
  age: 30,
  // TypeScript error: 'invalid' is not a valid column
  // invalid: 'value'
  // TypeScript error: age must be a number
  // age: 'thirty'
});

With Clause (CTE)

Use Common Table Expressions with inserts:

const newUserIds = db.$with('new_users').as(
  db.insert(users)
    .values([{ name: 'John' }, { name: 'Jane' }])
    .returning({ id: users.id })
);

const result = await db.with(newUserIds)
  .select()
  .from(newUserIds);

Batch Inserts

For very large datasets, use batch processing:

const BATCH_SIZE = 1000;
const largeDataset = [...]; // Array of 10,000 items

for (let i = 0; i < largeDataset.length; i += BATCH_SIZE) {
  const batch = largeDataset.slice(i, i + BATCH_SIZE);
  await db.insert(users).values(batch);
}

Insert Examples by Database

PostgreSQL

// PostgreSQL specific features
await db.insert(users)
  .values({ name: 'John', email: '[e-mail]' })
  .onConflictDoUpdate({
    target: users.email,
    set: { name: sql`excluded.name` },
    where: sql`${users.updatedAt} < excluded.updated_at`,
  })
  .returning();

MySQL

// MySQL upsert
await db.insert(users)
  .values({ name: 'John', email: '[e-mail]' })
  .onDuplicateKeyUpdate({
    set: { name: sql`VALUES(name)` },
  });

SQLite

// SQLite conflict handling
await db.insert(users)
  .values({ name: 'John', email: '[e-mail]' })
  .onConflictDoUpdate({
    target: users.email,
    set: { name: sql`excluded.name` },
  })
  .returning();

Performance Tips

1. Use Bulk Inserts

   Insert multiple rows in a single query instead of multiple queries

2. Batch Large Datasets

   Split very large datasets (10k+ rows) into batches of 1000-5000 rows

3. Use Transactions

   Wrap multiple inserts in a transaction for consistency and performance

4. Avoid Returning When Unnecessary

   Only use .returning() when you need the inserted data back

Next Steps

Update Queries

Learn how to update existing data

Select Queries

Query and filter your data

Transactions

Use transactions for data consistency