Scala for Backend Engineering & Functional Programming · Lesson

Inserts and Updates

Write data functionally.

Inserts and Updates is a free Scala for Backend Engineering & Functional Programming lesson on CoddyKit — lesson 3 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the Scala for Backend Engineering & Functional Programming learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

Update0 for Writes

Writes use the same sql interpolator, but you call .update to get an Update0 instead of .query.

Running .run on an Update0 returns a ConnectionIO[Int]: the number of affected rows.

import doobie.implicits._

def insert(name: String, age: Int): ConnectionIO[Int] =
  sql"insert into users (name, age) values ($name, $age)".update.run

Affected Row Counts

The Int from .run is the JDBC update count. For an UPDATE or DELETE it tells you how many rows matched.

You can branch on it to detect, for example, an update that matched no rows.

def deactivate(id: Long): ConnectionIO[Int] =
  sql"update users set active = false where id = $id".update.run

Returning Generated Keys

To get an auto-generated primary key, use .withUniqueGeneratedKeys, naming the columns the database returns.

This runs the insert and reads back the generated values in one round trip.

def insertReturningId(name: String, age: Int): ConnectionIO[Long] =
  sql"insert into users (name, age) values ($name, $age)"
    .update
    .withUniqueGeneratedKeys[Long]("id")

Returning a Whole Row

You can return several generated or defaulted columns at once by reading them into a tuple or case class.

This is great for capturing server-side defaults like timestamps right after insert.

case class User(id: Long, name: String, createdAt: java.time.Instant)

def create(name: String): ConnectionIO[User] =
  sql"insert into users (name) values ($name)"
    .update
    .withUniqueGeneratedKeys[User]("id", "name", "created_at")

The Update Class for Batches

Update[A] is a prepared, parameterized statement you can run many times. You define the SQL once with ? placeholders and supply values of type A.

It is built from a SQL string plus a Write[A] instance.

val ins: Update[(String, Int)] =
  Update[(String, Int)]("insert into users (name, age) values (?, ?)")

Efficient Batch Inserts

updateMany sends a whole collection in one JDBC batch, far faster than looping single inserts.

It returns the total number of affected rows. Use it whenever you load many rows at once.

val rows = List(("Ada", 36), ("Linus", 54))

val batch: ConnectionIO[Int] = ins.updateMany(rows)

Batch Insert with Keys

Update can also stream generated keys for a batch via updateManyWithGeneratedKeys, giving back a row per inserted record.

This combines bulk loading with reading back assigned identifiers.

import fs2.Stream

val keyed: Stream[ConnectionIO, Long] =
  ins.updateManyWithGeneratedKeys[Long]("id")(rows)

Upserts with ON CONFLICT

Database-specific upserts work fine through Doobie. On PostgreSQL you write insert ... on conflict ... do update as normal SQL.

Doobie just binds your parameters; the upsert semantics belong to the database.

def upsert(email: String, name: String): ConnectionIO[Int] =
  sql"""insert into accounts (email, name) values ($email, $name)
         on conflict (email) do update set name = excluded.name"""
    .update.run

Write Instances and Defaults

Parameters are encoded with the Write[A] typeclass, derived for primitives, tuples, and case classes. Each field needs a Put instance.

If you omit a column from the insert, the database applies its DEFAULT, which is how generated timestamps appear.

case class NewUser(name: String, age: Int)

val mk = Update[NewUser](
  "insert into users (name, age) values (?, ?)"
)

Custom Column Mappings with Meta

To persist a custom type, define a Meta[A] by mapping it to an existing column type with imap (or timap).

A Meta provides both reading and writing, so the type works in queries and updates alike.

import doobie.util.meta.Meta

case class UserId(value: Long)

implicit val uidMeta: Meta[UserId] =
  Meta[Long].imap(UserId.apply)(_.value)

Deletes Are Updates Too

DELETE statements also use .update.run and return the count of removed rows.

Treating inserts, updates, and deletes uniformly as Update0 keeps the API small and composable.

def purgeInactive: ConnectionIO[Int] =
  sql"delete from users where active = false".update.run

Quick Check

Pick the most efficient approach.

Recap

Writes use .update to produce an Update0; .run returns affected rows and .withUniqueGeneratedKeys reads back generated values.

For bulk work, Update[A].updateMany batches efficiently. Custom types get Meta instances, and upserts are just normal database SQL bound by Doobie.

Frequently asked questions

Is the “Inserts and Updates” lesson free?

Yes — the full text of “Inserts and Updates” is free to read here on the web, and the Scala for Backend Engineering & Functional Programming course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the Scala for Backend Engineering & Functional Programming course, upgrade to CoddyKit PRO.

What will I learn in “Inserts and Updates”?

Write data functionally. You practise Scala for Backend Engineering & Functional Programming with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start Scala for Backend Engineering & Functional Programming?

No prior experience is required. Scala for Backend Engineering & Functional Programming on CoddyKit is structured for beginners through advanced learners; this is — lesson 3 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Inserts and Updates” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this Scala for Backend Engineering & Functional Programming lesson?

Yes. Every Scala for Backend Engineering & Functional Programming lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

← Back to Scala for Backend Engineering & Functional Programming