Storing lists of strings in postgresql database leads to every string having "s" prepended to it

[tysonzero]

Pretty much title, if I insert ["foo", "bar", "baz"] into the database via some field that has type [String], and then I look at the database directly, I have ["sfoo", "sbar", "sbaz"], which I would prefer not to have.

[naushadh]

Stumbled upon the source of this issue when investigating something else.

• Backends are storing lists as JSON encoded string (and decoding it on read): postgresql example
• Additionally the ToJSON instance of PersistList is also invoking to ToJSON instance of the value
• The ToJSON instance for textual types is tacking on 's' prefix.

While how it's stored isn't what was committed, on read (using persistent) the s prefix will be gracefully removed ensuring integrity of what was written. This affects sharing the database with non persistent writers/readers.

[tysonzero]

Ah I see. Is this approach necessary? It seems like Persistent should have enough information (or be able to request any additionally info needed from instances) to not need that extra prefix.

[parsonsmatt]

@snoyberg initially wrote that 6 years ago, it would seem. Do you recall why that was done?

[snoyberg]

Not necessarily... I think I did that as a future-proofing mechanism in case the encoding format changed. Unfortunately, 6-years-ago me was much worse at documenting things, and today-me is much worse at remembering things ☹️

[parsonsmatt]

No worries!

This does actually make it relatively easy to ensure that the Haskell types haven't been switched. If we have, eg,

Foo
   stuff [ByteString]

and then later do

Foo
    stuff [Text]

then the database type does not change, but the data may not be valid. There would be no way to check that from the value itself, as it'd be coming back as a String.

We actually don't have the info we need to know what we're expecting at this point. FromJSON PersistValue only tells us the serialized, database value. We'd need FromJSON (FieldDef, PersistValue) and use the expected type from FieldDef to drive deserialization.

[parsonsmatt]

A breaking change to a type class instance for serialization/deserialization is going to require a big version bump, though.

[parsonsmatt]

fwiw, this behavior should not happen if we use the PersistArray type for an underlying thing. possibly:

newtype PgArray a = PgArray [a]

instance PersistField a => PersistField (PgArray a) where ...