Basic insert syntax
To insert a single row into a table using Drizzle ORM, use `await db.insert(users).values({ name: 'Andrew' })`. This generates SQL like `insert into `users` (`name`) values ('Andrew')`.
drizzle-orm/insert
17 notes, read out of this brain and free to use. Each one was extracted from a source and is re-checked against its exam.
Infer insert type from table schema
Use `typeof usersTable.$inferInsert` to get the TypeScript type for insert operations on a specific table. This allows type-safe insert operations. Example: `type NewUser = typeof users.$inferInsert;`
$returningId for SingleStore inserts
SingleStore does not support native RETURNING after INSERT. Use the `$returningId()` method to automatically retrieve inserted primary keys. This works with autoincrement or serial primary key types, returning an array of objects with the inserted IDs. Example: `await db.insert(usersTable).values([{ name: 'John' }]).$returningId()` returns `{ id: number }[]`.
$returningId with custom primary keys
The `$returningId()` method also works with primary keys defined using `$defaultFn()` for custom key generation at runtime. The returned array will include the custom-generated primary key values.
Insert multiple rows
To insert multiple rows in a single operation, pass an array of objects to the `values()` method: `await db.insert(users).values([{ name: 'Andrew' }, { name: 'Dan' }])`.
SingleStore ON DUPLICATE KEY UPDATE
SingleStore supports conflict handling using `onDuplicateKeyUpdate()` instead of ON CONFLICT. SingleStore automatically determines the conflict target based on primary key and unique indexes. Example: `await db.insert(users).values({ id: 1, name: 'John' }).onDuplicateKeyUpdate({ set: { name: 'John' } })`.
SingleStore no-op on duplicate key
SingleStore does not directly support doing nothing on duplicate key conflict. To achieve the same effect, set any column's value to itself using `onDuplicateKeyUpdate()`. Example: `await db.insert(users).values({ id: 1, name: 'John' }).onDuplicateKeyUpdate({ set: { id: sql`id` } })`.
INSERT ... SELECT with query builder
Use `db.insert(employees).select(db.select({ name: users.name }).from(users).where(eq(users.role, 'employee')))` to insert rows from a SELECT query. The query builder can be passed directly or inside a callback function.
INSERT ... SELECT with callback
Pass a callback function to `.select()` for INSERT ... SELECT operations. The callback receives the query builder: `await db.insert(employees).select(() => db.select({ name: users.name }).from(users).where(eq(users.role, 'employee')))`.
INSERT ... SELECT with SQL template tag
Use the `sql` template tag to write custom SELECT queries within INSERT statements: `await db.insert(employees).select(sql`select users.name as name from users where users.role = 'employee`)`.
createInsertSchema function for SingleStore
The createInsertSchema function from drizzle-orm/valibot generates a Valibot schema that validates the shape of data to be inserted into the database. It can be used to validate API requests. Columns with autoincrement are not required in the insert schema. All non-autoincrement columns marked as notNull remain required.
Insert a single todo item into the database
Use db.insert(todo).values({ id: id, text: text }) to insert a new todo item. The values method accepts an object with the column values. This operation can be awaited to complete the insertion.
Insert data with Drizzle ORM
Use db.insert(tableName).values(data) to insert records into a table. The data parameter should match the inferred insert type for the table.
Insert data with Drizzle into Supabase
Use db.insert(tableName).values(data) to insert records. For example, to insert a user: await db.insert(usersTable).values(data) where data is of type InsertUser. For posts: await db.insert(postsTable).values(data) where data is of type InsertPost.
Turso with Drizzle insert query example
Use `db.insert(table).values(data)` to insert data into a Turso table. Pass the table and typed data object.
Insert data with Drizzle ORM
Example insert operation: import { db } from '../index'; import { InsertUser, usersTable } from '../schema'; export async function createUser(data: InsertUser) { await db.insert(usersTable).values(data); }
Insert example with async function
Example of inserting data using async function: export async function createUser(data: InsertUser) { await db.insert(usersTable).values(data); }