Updating Data

Now that we've learned how to retrieve data in Selecting Data, let's look at how to write data to the database. This page explains the basics of Data Manipulation Language (DML) such as INSERT, UPDATE, and DELETE.

Basics of Data Updating

Write operations to the database behave somewhat differently from read operations because they change the state of the database. ldbc provides appropriate abstractions to safely perform these operations.

The basic flow for performing write operations is as follows:

Create an SQL query using the sql interpolator
Call the appropriate method (.update, .returning, etc.) to specify the type of query
Execute the query with .commit() or .transaction()
Process the results

Inserting Data (INSERT)

Basic INSERT Operation

To insert data, use the SQL INSERT statement and call ldbc's .update method. Here's an example of inserting a row into the user table:

// Method to insert a new user into the user table
def insertUser(name: String, email: String): DBIO[Int] =
  sql"INSERT INTO user (name, email) VALUES ($name, $email)"
    .update

Let's use this method to actually insert data and check the result:

// Execute the insert operation
insertUser("dave", "dave@example.com").commit(conn).unsafeRunSync()
// The return value is the number of affected rows (in this case, 1)

// Check the inserted data
sql"SELECT id, name, email FROM user WHERE name = 'dave'"
  .query[(Int, String, String)]
  .to[Option]
  .readOnly(conn)
  .unsafeRunSync()
  .foreach { case (id, name, email) =>
    println(s"ID: $id, Name: $name, Email: $email")
  }

The .update method returns the number of affected rows (in this case, 1).

Retrieving Auto-generated Keys

In many cases, tables have auto-generated keys such as auto-increment IDs. To retrieve this auto-generated key value at the time of insertion, use the .returning[T] method:

// Method to insert and get the generated ID
def insertUserAndGetId(name: String, email: String): DBIO[Long] =
  sql"INSERT INTO user (name, email) VALUES ($name, $email)"
    .returning[Long]

Let's use this method to insert a new user and get the auto-generated ID:

// Insert and get the auto-generated ID
val newUserId = insertUserAndGetId("frank", "frank@example.com")
  .commit(conn)
  .unsafeRunSync()

println(s"New user ID: $newUserId")

Note: The .returning method only works with columns that have AUTO_INCREMENT set in MySQL.

Retrieving Inserted Data

If you want to retrieve all the information about the inserted data at the same time, you can combine two steps using the auto-generated key:

// Case class representing a user
case class User(id: Long, name: String, email: String)

// Method to insert a user and return the inserted user's information
def insertAndRetrieveUser(name: String, email: String): DBIO[Option[User]] =
  for
    id   <- sql"INSERT INTO user (name, email) VALUES ($name, $email)".returning[Long]
    user <- sql"SELECT id, name, email FROM user WHERE id = $id".query[User].to[Option]
  yield user

Usage example:

// Insert a user and get the inserted user's information
insertAndRetrieveUser("grace", "grace@example.com")
  .commit(conn)
  .unsafeRunSync()
  .foreach { user =>
    println(s"Inserted user: ID=${user.id}, Name=${user.name}, Email=${user.email}")
  }

Updating Data (UPDATE)

To update data, use the SQL UPDATE statement and call the .update method similarly:

// Method to update a user's email address
def updateUserEmail(id: Long, newEmail: String): DBIO[Int] =
  sql"UPDATE user SET email = $newEmail WHERE id = $id"
    .update

Usage example:

// Update a user's email address
updateUserEmail(1, "alice+updated@example.com")
  .commit(conn)
  .unsafeRunSync()

// Check the updated data
sql"SELECT id, name, email FROM user WHERE id = 1"
  .query[User]
  .to[Option]
  .readOnly(conn)
  .unsafeRunSync()
  .foreach { user => 
    println(s"Updated user: ID=${user.id}, Name=${user.name}, Email=${user.email}")
  }

Updating with Multiple Conditions

It's also possible to update with complex conditions:

// Update email addresses for users matching a specific name
def updateEmailsByName(name: String, newEmail: String): DBIO[Int] =
  sql"""
    UPDATE user 
    SET email = $newEmail 
    WHERE name LIKE ${"%" + name + "%"}
  """.update

This example updates the email address of all users who match the specified name pattern.

Deleting Data (DELETE)

To delete data, use the SQL DELETE statement and call the .update method:

// Delete a user by ID
def deleteUser(id: Long): DBIO[Int] =
  sql"DELETE FROM user WHERE id = $id"
    .update

Usage example:

// Delete a user
deleteUser(5)
  .commit(conn)
  .unsafeRunSync()

// Confirm deletion
sql"SELECT COUNT(*) FROM user WHERE id = 5"
  .query[Int]
  .unsafe
  .readOnly(conn)
  .unsafeRunSync() match {
    case 0 => println("User with ID 5 has been deleted")
    case n => println(s"User with ID 5 still exists (count: $n)")
  }

Deleting Multiple Rows

It's also possible to delete multiple rows at once that match a condition:

// Delete all users with email addresses from a specific domain
def deleteUsersByEmailDomain(domain: String): DBIO[Int] =
  sql"DELETE FROM user WHERE email LIKE ${"%@" + domain}"
    .update

Batch Processing (Bulk Operations)

Bulk Insertion of Multiple Rows

To efficiently insert many rows, you can specify multiple value sets in the VALUES clause:

import cats.data.NonEmptyList

// Bulk insert multiple users
def insertManyUsers(users: NonEmptyList[(String, String)]): DBIO[Int] =
  val values = users.map { case (name, email) => sql"($name, $email)" }
  (sql"INSERT INTO user (name, email) VALUES " ++ Fragments.values(values)).update

Usage example:

// Define multiple users
val newUsers = NonEmptyList.of(
  ("greg", "greg@example.com"),
  ("henry", "henry@example.com"),
  ("irene", "irene@example.com")
)

// Execute bulk insertion
val insertedCount = insertManyUsers(newUsers).commit(conn).unsafeRunSync()
println(s"Number of rows inserted: $insertedCount") // Should output "Number of rows inserted: 3"

Transactions for Multiple Operations

To atomically execute multiple operations, use transactions. This ensures that either all operations succeed or all operations fail (rollback):

// Example of inserting a user and related information for that user
def createUserWithProfile(name: String, email: String, bio: String): DBIO[Long] =
  for
    userId    <- sql"INSERT INTO user (name, email) VALUES ($name, $email)".returning[Long]
    profileId <- sql"INSERT INTO user_profile (user_id, bio) VALUES ($userId, $bio)".returning[Long]
  yield userId

By executing this method using .transaction, both user insertion and profile insertion are processed as a single transaction:

// Execute within a transaction
val userId = createUserWithProfile("julia", "julia@example.com", "Programmer")
  .transaction(conn)
  .unsafeRunSync()

println(s"Created user ID: $userId")

If the insertion into the user_profile table fails, the insertion into the user table will also automatically roll back.

Selecting Query Execution Methods

ldbc provides the following query execution methods for data update operations:

.commit(conn) - Executes a write operation in auto-commit mode (suitable for simple, single update operations)
.rollback(conn) - Executes a write operation and always rolls back (for testing and verification)
.transaction(conn) - Executes operations within a transaction, committing only upon success (suitable when you want to treat multiple operations as a single unit)

// Execution in auto-commit mode (simple single operation)
updateUserEmail(1, "new@example.com").commit(conn)

// Execution for testing (changes are not saved)
insertUser("test", "test@example.com").rollback(conn)

// Multiple operations within a transaction (all succeed or all fail)
(for {
  userId <- insertUser("kate", "kate@example.com").returning[Long]
  _      <- sql"INSERT INTO user_roles (user_id, role_id) VALUES ($userId, 2)".update
} yield userId).transaction(conn)

Error Handling

It's also important to handle errors that may occur during data update operations. In ldbc, you can use the IO monad from cats-effect to process errors:

import cats.effect.unsafe.IORuntime
import cats.effect.IO

implicit val runtime: IORuntime = IORuntime.global

// Example of error handling
def safeUpdateUser(id: Long, newEmail: String): Unit = {
  val result = updateUserEmail(id, newEmail)
    .commit(conn)
    .attempt // Convert IO result to Either[Throwable, Int]
    .unsafeRunSync()
    
  result match {
    case Right(count) => println(s"Number of rows updated: $count")
    case Left(error)  => println(s"An error occurred: ${error.getMessage}")
  }
}

// Execution example (updating with a non-existent ID)
safeUpdateUser(9999, "nonexistent@example.com")

Summary

When performing data update operations with ldbc, it's good to remember the following points:

Use .update or .returning[T] (to retrieve auto-generated keys) for insert operations
Use .update for update operations, specifying target rows with a WHERE clause
Also use .update for delete operations (though it's a DELETE statement, the operation method is common)
Use .transaction() to execute multiple operations atomically
Use .commit() for simple single operations
Use .rollback() for testing purposes to discard changes

By appropriately combining these data update operations, you can efficiently and safely manipulate your database.

Next Steps

Now you understand how to insert, update, and delete data in your database. At this point, you've learned all the basics of using ldbc. You've acquired the knowledge necessary for everyday database operations, including database connection, query execution, data reading and writing, and transaction management.

From here, we'll move on to more advanced topics. Let's start with Error Handling to learn how to properly handle exceptions that can occur in database operations.