Add a client option for handling dates as strings in @effect/sql-pg and @effect/sql-mysql2

[f15u]

What is the problem this feature would solve?

Working with dates often presents the challenge of converting between strings and Date objects (i.e., serializing and deserializing).

The following shows the default implementation of date-related types in the postgres package: https://github.com/porsager/postgres/blob/089214e85c23c90cf142d47fb30bd03f42874984/src/types.js#L28-L33

Ref: https://discord.com/channels/795981131316985866/1134177528098078861/1341711841147093086

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

Add an option to handle this scenario directly with PgClient and MySqlClient.

In the MySQL package, we can simply expose dateStrings since mysql2 supports it.

In the Pg package, we could add a new option that sets or merges types accordingly. I was considering using Schema as it seems natural, but I'd appreciate another perspective.

What alternatives have you considered?

When working with PgClient, declare the types configuration as:

  types: {
    date: {
      to: 1184,
      from: [1082, 1083, 1114, 1184],
      serialize: x => x,
      parse: x => x
    }
  }

or by selecting the field with ::text, although this is not feasible when using *.

Source: porsager/postgres#161 (comment)

[jamesone]

Another motivator to use string dates is that if you use a tool like superjson, dates over the wire do not preserve the timezone 😐 So you end up with some ugly use cases to handle flightcontrolhq/superjson#130 Why this is relevant: If we can have a fully e2e handling of dates via strings, we don't have to worry about layers that are nuanced, we simply worry about Effect schema.