Insert
The db.insert() method is used to add new rows to a table. It returns a query builder for constructing the INSERT statement.
Inserting a Single Row
To insert a single record, use the .values() method with a single object. After building the query, call .execute() to run it.
import { db } from '$lib/db';
import { users } from '$lib/db/schema';
// Type inference ensures you provide the correct properties.
// Optional columns (nullable or with defaults) can be omitted.
const newUser: InferInsertModel<typeof users> = {
fullName: 'John Smith',
email: 'john.smith@example.com',
};
const result = await db.insert(users).values(newUser).execute();
console.log(result);
// [{ rowsAffected: 1, lastInsertId: ... }]
InferInsertModel: This is a helper type that correctly represents the shape of an object for insertion, marking columns with default values or that are nullable as optional. See Type Safety & Inference for more.Inserting Multiple Rows
You can also insert multiple rows in a single, more efficient query by passing an array to .values().
await db.insert(users).values([
{ fullName: 'Alice', email: 'alice@example.com' },
{ fullName: 'Bob', email: 'bob@example.com' },
]).execute();
Returning Inserted Data
By default, an INSERT statement returns the number of affected rows and the last inserted ID. If you need the actual data from the newly created rows, you can use .returning(), .returningAll(), or .returningFirst().
.returningFirst() - Most Common Use Case
When inserting a single row, use .returningFirst() to get the inserted record directly without array destructuring:
// ✅ Clean and ergonomic
const insertedUser = await db.insert(users)
.values({ fullName: 'Carol', email: 'carol@example.com' })
.returningFirst();
console.log(insertedUser?.id); // The ID generated by the database
// Type: InferSelectModel<typeof users> | undefined
.returningAll() - For Multiple Rows
When inserting multiple rows or when you always expect an array:
// Return all columns for the newly inserted users
const insertedUsers = await db.insert(users)
.values([
{ fullName: 'Carol', email: 'carol@example.com' },
{ fullName: 'David', email: 'david@example.com' }
])
.returningAll();
console.log(insertedUsers[0].id);
// Type: InferSelectModel<typeof users>[]
.returning() - For Specific Columns
Return only the columns you need:
// Return only specific columns
const [insertedId] = await db.insert(users)
.values({ fullName: 'David', email: 'david@example.com' })
.returning('id');
console.log(insertedId); // { id: ... }
Quick Comparison
// Old way - need array destructuring
const [user] = await db.insert(users).values(data).returningAll();
// New way - cleaner for single inserts
const user = await db.insert(users).values(data).returningFirst();
Conflict Handling (UPSERT)
SQLite's ON CONFLICT clause is supported for handling cases where an insert would violate a UNIQUE or PRIMARY KEY constraint.
.onConflictDoNothing()
If a conflict occurs (e.g., trying to insert a user with an email that already exists), this method will cause the INSERT operation to be silently ignored for that row.
// If a user with this email already exists, this query does nothing.
await db.insert(users)
.values({ fullName: 'Jane Doe', email: 'jane.doe@example.com' })
.onConflictDoNothing()
.execute();
.onConflictDoUpdate()
This enables "upsert" (update or insert) functionality. If a conflict occurs, it instead performs an UPDATE on the existing row.
// If a user with this email exists, update their name.
// Otherwise, insert a new user with this name and email.
await db.insert(users)
.values({ fullName: 'John Smith (Updated)', email: 'john.smith@example.com'})
.onConflictDoUpdate({
target: users._.columns.email, // The column with the UNIQUE constraint
set: {
fullName: 'John Smith (Updated)', // Values to set on update
}
})
.execute();
Upsert Shorthand
For simple upserts, use the .upsert() helper:
// Insert or update user by email
await db.upsert(
users,
{ fullName: 'John Doe', email: 'john@example.com' },
['email'] // Conflict target (unique column)
);