Sometimes, when writing data to a database, we know the final state we want but aren't sure whether to insert a new row or update an existing one. In other words, we either need to insert new data or update existing records depending on the current state of the database.

Fortunately, Active Record provides a utility method called upsert to handle this scenario. Let’s explore how it works.

Introducing the upsert Method

Returning to our IRB session playground, we have:

• A posts table with title and body columns.
• A Post model that includes a validation and a callback.
• An initial post already created for testing.

Now, let’s use upsert instead of insert, passing a hash of attributes:

Post.upsert(id: 1, title: "New Title", body: "New Text")

Understanding the Generated SQL Query

At first glance, this looks similar to an insert statement, but there’s a key difference:

INSERT INTO posts (id, title, body, created_at, updated_at) 
VALUES (?, ?, ?, ?, ?) 
ON CONFLICT(id) DO UPDATE SET ...

Instead of ignoring conflicts (DO NOTHING), this query updates the record when an id conflict occurs.

How the ON CONFLICT DO UPDATE Clause Works

Breaking it down:

1. If a conflict on id occurs (meaning the record already exists), Active Record updates the existing row with the new title and body values.
2. If no conflict is found, a new row is inserted instead.
3. The updated_at field is only modified if changes were made.

To ensure this, Active Record generates a CASE WHEN statement:

• If values have changed, updated_at is set to the current timestamp.
• If values are the same, updated_at remains unchanged.

What is EXCLUDED in SQLite?

EXCLUDED is a special SQLite keyword representing the newly inserted values in case of a conflict.

This means:

• title gets replaced with the new value.
• body is updated if different.
• updated_at changes only when necessary.

Handling New IDs with upsert

If we now run:

Post.upsert(id: 2, title: "Another Post", body: "More content")

Since ID 2 does not exist, it creates a new record instead of updating one.

Thus, upsert allows us to:

• Insert new records when needed
• Update existing records when necessary
• Efficiently handle large writes in a single SQL query

Bulk Upserts with upsert_all

Like insert_all, we can use upsert_all for batch processing:

Post.upsert_all([
  { title: "Bulk Post 1", body: "Content 1" },
  { title: "Bulk Post 2", body: "Content 2" }
])

This generates a single SQL query to insert or update multiple rows at once, significantly improving performance.

When to Use upsert

upsert is particularly useful for:

• Seeding databases—ensuring records exist without duplication.
• Tests—creating or updating test data efficiently.
• Performance-sensitive bulk updates.

However, keep in mind:

• upsert skips validations.
• upsert does not trigger callbacks.

Use it only when you are confident about your schema and data structure.

Now that we’ve explored efficient data insertion and updates, in the next video, we’ll dive into querying—retrieving and filtering records efficiently in Active Record.