Hey! I'm trying to write a API with Ktor using SQL...

# squarelibraries

[Tech]

Hey! I'm trying to write a API with Ktor using SQLDelight for the database transactions.
I was wondering how everyone handles retrieving data from the database then sending it back to the client? Like I want to get a partial select of a table then just send that response back with Ktor however, it is not marked with @Serializable so sending it back fails.

Making a mapper for each model gets a little annoying with all the mappings I need to do and whenever I change the table schema then I need to redo my mapper.
Is there an easier way to send back a JSON representation of the retrieved data?

[griffio]

🤔 You could use the json functions, that return the partial query data as json directly and avoid the need to serialise. https://github.com/cashapp/sqldelight/blob/0e76f86bc1f55b17c9f21909963db8db26ae84d[…]p/cash/sqldelight/dialects/postgresql/PostgreSqlTypeResolver.kt in SqlDelight. (I haven’t really seen them all tested)
e.g select json_agg(c.city_name) FROM (SELECT * FROM City) c; would return a json array ["Tokyo","Singapore",...]

🤫 See https://www.crunchydata.com/blog/generating-json-directly-from-postgres for Ideas

[Tech]

@griffio I’m gonna be so honest with you, I just wrote a gradle task that runs after generate to attach @kotlinx.serialization.Serializable at the top of all the data classes and it works.

In SQLDelight you can do

CREATE TABLE Test(
  id BIGSERIAL PRIMARY KEY,
  test UUID AS @Contextual UUID
);

And that actually attaches @Contextual to the data class.

[Tech]

And it’s been working fairly well for me, I wrote a quick util function to also remove specific keys from the JSON object to send back to my client

[Tech]

I didn’t know about this syntax though, thank you for the suggestion!