Granular SQL errors

[vecerek]

What is the problem this feature would solve?

There are various reasons why a sql query may fail: ConnectTimeout, LockWaitTimeout, Deadlock, DuplicateEntry, SyntaxError, etc. Some errors are retriable (e.g. LockWaitTimeout), some are retriable with some modifications to the "query values" (e.g. DuplicateEntry), others aren't retriable at all.

Currently, the only way to distinguish the various types of errors is by doing something like this:

import type { QueryError } from "mysql2";

Effect.catchTag("SqlError", (error) => {
  if (error.cause instanceof Error) {
    const originalError = error.cause as QueryError;

    switch (originalError.code) {
      "ER_DUP_ENTRY": // ...
      "ER_LOCK_WAIT_TIMEOUT": // ...
      // etc.
      default: // ...
    }
  }
})

What is the feature you are proposing to solve the problem?

Extend the SqlError type with a code field, so that the block becomes a bit simpler:

Effect.catchTag("SqlError", (error) => {
  switch (error.code) {
    "ER_DUP_ENTRY": // ...
    "ER_LOCK_WAIT_TIMEOUT": // ...
    // etc.
    default: // ...
  }
})

The challenge is to pick the right type for code. It cannot simply be an enumeration such as:

type Code = "ER_DUP_ENTRY" | "ER_LOCK_WAIT_TIMEOUT" | ...;

These codes are not database-agnostic. For example, the duplicate entry key constraint violation error is called:

• ER_DUP_ENTRY by MySQL.
• unique_violation by Postgres

Typing it as string is more robust but lacks a good DX.
Another way to go would be to use the greatest common divisor approach, standardize some error codes within Effect SQL and perform some mapping within the sql-xyz libraries themselves.
Another option would be to ditch the codes but use SQLSTATE, a standardized five-character alphanumeric code used in database systems to identify the outcome of a SQL statement.

What alternatives have you considered?

I have considered two alternative solutions:

1. Replacing SqlError with a union of its specific errors: DuplicateEntrySqlError | LockWaitTimeoutSqlError | ... | OtherSqlError. The problem with this approach would be too many error tags every time an Effect has to interact with the DB. Also, all these errors would appear on all queries regardless of their nature. For example, it is impossible to get a DuplicateEntrySqlError for a read-only query.
2. Extending the SqlError type with a isRetriable boolean. The problem is that errors such as DuplicateEntry are not retriable without the right modifications to the query (maybe one of the ids has to be incremented, or so). In such case would it be a retriable or a non-retriable error? Maybe a retryStrategy: "without_modification" | "with_modification" | "never" might work better (I can't think of a better wording at the moment).

[vecerek]

I have another idea that could represent some sort of a middle ground.

Each Effect SQL client implementation could export a function that could map the sql operation to one that can fail with a specific list of errors: DuplicateEntry, LockWaitTimeout, Deadlock, etc. These errors would be defined in the @effect/sql package as they represent a unified API layer but the client-specific mapping functions would live in their respective client lib.

The solution being an extra function the change is backwards-compatible and users would have to opt-in to gain the granular errors and only the errors they are specifically requesting. Getting all the errors would simply be a nightmare to handle downstream. Here's an example of how the API could look like:

import { granularErrors, type MysqlClient } from "@effect/sql-mysql2"
import type { SqlError } from "@effect/sql"
import { Effect } from "effect"

type Row = { id: string }

declare const sqlOperation: Effect.Effect<Row[], SqlError.SqlError, MysqlClient.MysqlClient>

sqlOperation.pipe(
  Effect.mapError((e) => granularErrors(e, "DuplicateEntry", "LockWaitTimeout", "Deadlock"))
  //                     ^^^^^^^^^^^^^^ for a lack of a better name at this moment
) satisfies Effect.Effect<
  Row[],
  SqlError.DuplicateEntry | SqlError.LockWaitTimeout | SqlError.Deadlock | SqlError.SqlError,
  MysqlClient.MysqlClient
>

@tim-smart you're probably busy with Effect v4 and other work but what do you think about this idea? I'm willing to put the work in and contribute this to the lib if you agree it brings value.

[tim-smart]

Effect v3 is under a feature freeze right now, so if you decide you want to work on this then it should be against the v4 repository.

We have updated the v4 error model to lean into a special reasons field, which contains more grantular errors.

The base SQL module could define some, then each driver would be responsible to mapping errors to the right reasons.

I could probably throw AI at it initially and see how far it gets