Feature request: Support jsonb columns in lapis model create/update

[JDaance]

I know there is already https://github.com/leafo/lapis/issues/645 but that is getting a bit old and this issue is more specific

Issue description

For a table like this:

schema.create_table("some_table", {
	{ "id", types.serial },
	{ "some_field", "jsonb" },
	"PRIMARY KEY (id)"
})

Currently if I do

t = {}
instance = Model:extend("some_table")
instance:create {}
instance:update { some_field = t }

Lapis will crash that it cant escape t -- which is reasonable

So I do

t = {}
instance = Model:extend("some_table")
instance:create {}
instance:update { some_field = cjson.encode(t) }

And this works well, but now instance.some_field is a json string, and does not model the database in a helpful way.

If I then do instance:refresh() lapis will fetch the json data and correctly convert it to a table. So lapis has excellent support on fetching jsonb, but not on creating/updating it.

Workarounds

(updated with leafos input from below)

Wrap encoded json in db.raw, this will make lapis add a "RETURNING" clause on the update, refreshing the value with decoded json after the update completes:

t = {}
instance = Model:extend("some_table")
instance:create {}
instance:update { some_field = db.raw(db.escape_literal(cjson.encode(t))) }

Perhaps more performant workaround, skipping the returning step:

t = {}
instance = Model:extend("some_table")
instance:create {}
instance:update { some_field = cjson.encode(t) }
instance.some_field = t

API/Implementation ideas

Off the top of my head lapis could encode any unknown table to json in create/update statements, and update the instance field to the table value instead of the encoded json, or it could add some json wrapper like db.raw. I am not familiar enough with the api or codebase to know whats better, or if there are other ways.

Magic version:

t = {}
instance = Model:extend("some_table")
instance:create {}
instance:update { some_field = t } -- just works :)

Wrapper version:

t = {}
instance = Model:extend("some_table")
instance:create {}
instance:update { some_field = db.json(t) } -- some wrapper like this might be a useful api that is easy to implement

[leafo]

This is the helper function I use in my apps:

db_json = (v) ->
  db.raw db.escape_literal json.encode v

Never write code like this:

instance:update { some_field = db.raw("'"..cjson.encode(t).."'") }

If you are using a raw SQL fragment then ensure that all input is properly encoded

[JDaance]

This is the helper function I use in my apps:

db_json = (v) ->
  db.raw db.escape_literal json.encode v

Never write code like this:

instance:update { some_field = db.raw("'"..cjson.encode(t).."'") }

If you are using a raw SQL fragment then ensure that all input is properly encoded

:+1: Updated the workaround section to be more correct