Write[T] in Doobie Fails to Persist Derived Values When Mapping from Case Class

[shamwilA]

Issue Description

Environment

• Scala Version: 3.3.3
• Doobie Version: 1.0.0-RC5
• Database: MySQL
  When using Doobie’s Write[T] derivation, any value that needs to be derived from the case class fields (rather than being a direct field) is not persisted in the database. However, manually mapping and passing the derived value in the SQL query works as expected.

This suggests that Doobie’s Write instance does not correctly handle computed values when inserting via Update[T].

Expected Behavior

• Derived values (such as converting a ZonedDateTime to its ZoneId) should be persisted correctly when using Write[T] in an insert statement.
• Write[T] should respect all transformations applied via contramap.

Actual Behavior

• Any field that is derived within Write[T] and not a direct property of the case class is ignored or stored as NULL.
• Using manual mapping in the SQL query works as expected, meaning the issue lies in the Write[T] derivation.

---

Reproduction Steps

Database Schema

MySQL

CREATE TABLE parentAvailability (
    id          INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    startDate   DATE NOT NULL,
    timeZone    VARCHAR(40)
);

---

Scala Case Class

case class ParentAvailability(
  id: Option[Long] = None,
  startDate: ZonedDateTime
)

---

Read and Write Instances

✅ Read Mapping (Works Correctly)

implicit val readAvailability: Read[ParentAvailability] = Read[(Long, LocalDate, String)].map {
  case (id, startDate, timeZone) =>
    val zoneId = ZoneId.of(timeZone)
    ParentAvailability(
      Some(id),
      startDate.atStartOfDay(zoneId)
    )
}

❌ Write Mapping (Fails to Persist Derived timeZone)

implicit val writeAvailability: Write[(Option[Long], LocalDate, String)] = Write[(Option[Long], LocalDate, String)].contramap {
  case ParentAvailability(id, startDate) =>
    val startDateStr = startDate.toLocalDate
    val timeZoneStr = startDate.getZone.getId  // This is derived but not persisting
    (id, startDateStr, timeZoneStr)
}

---

Insert Methods

❌ Failing Method: Using Write[T]

def insertParentAvailabilityUsingWrite(availability: ParentAvailability): EitherT[doobie.ConnectionIO, Throwable, Int] =
  val insertParentAvailability =
    "INSERT INTO Store.parentAvailability (id, startDate, timeZone) VALUES (?,?,?)"
  
  EitherT(Update[ParentAvailability](insertParentAvailability)(writeAvailability).run(availability).attempt)

📌 Issue: timeZone fails with error or always stored as NULL if NULL value allowed for column when using this method.

---

✅ Working Method: Manual Mapping

def insertParentAvailabilityManualMap(availability: ParentAvailability): EitherT[doobie.ConnectionIO, Throwable, Int] =
  EitherT(sql"""
    INSERT INTO Store.parentAvailability (startDate, timeZone)
    VALUES (${availability.startDate.toLocalDate}, ${availability.startDate.getZone.getId})
  """.update.withUniqueGeneratedKeys[Int]("id").attempt)

📌 This works correctly and persists the derived value.

---

Key Issue: Write[T] Does Not Handle Derived Values

When a value is not explicitly stored in the case class but needs to be derived from other fields, Write[T] does not ensure its proper inclusion in the database.

For example, timeZone is derived from startDate in Write[T], but it is never actually written when using Update[T].

---

Questions & Possible Causes

1. Does Doobie’s Write[T] ignore computed values in contramap?

   • The manual insert correctly retrieves startDate.getZone.getId, but writeAvailability doesn’t seem to pass it correctly.

2. Should Write[T] allow transformations that introduce new values?

   • Perhaps Write only works when fields map 1:1 to the table columns.

---

Workarounds & Fixes

1. Use manual mapping in SQL queries instead of relying on Write[T] for computed values.
2. Enable SQL logging to confirm what values Doobie is actually passing.

---

Request for Help

Has anyone else encountered this issue with Doobie’s Write[T]?

• Is this expected behavior for Write[T]?
• Should Doobie allow values derived within contramap to be written?
• Are there best practices to ensure computed values are correctly inserted?

Any insights would be greatly appreciated! 🙌

[jatcwang]

Hi @shamwilA, thanks for the detailed report. I think this may be a case where the automatically-derived instance is being used.

This line of code seems a bit odd to me
EitherT(Update[ParentAvailability](insertParentAvailability)(writeAvailability).run(availability).attempt)

According to your example, writeAvailability has the type Write[(Option[Long], LocalDate, String)] but then the Update accepts value of type ParentAvailability so it shouldn't compile?
But yeah if writeAvailability is of type Write[ParentAvailability] with your custom mapping, it should've worked.

If it still doesn't work, can you provide a reproducer using https://github.com/jatcwang/doobie_quick_start?
There's mysql in the docker-compose file you can uncomment.