Feature request: Support for JSONB in postgresql

[vanvuongngo]

Hi there,

can you please give me an advice how to store JSONB (binary) values into postgresql?

Table

create table tb_test
(
  "value" jsonb,
);

What I tried

let params = vec![
  ParameterValue::Binary(json_string.into_bytes()),
];
connection.execute("INSERT INTO tb_test VALUES ($1)", &params);

error output:

Error::QueryFailed("Error { kind: ToSql(1), cause: Some(WrongType { postgres: Jsonb, rust: \"alloc::vec::Vec<u8>\" }) }")

also as string does not work

let params = vec![
  ParameterValue::Str(json_string.clone()),
];
connection.execute("INSERT INTO tb_test VALUES ($1)", &params);

error output:

fail: Error::QueryFailed("Error { kind: ToSql(1), cause: Some(WrongType { postgres: Jsonb, rust: \"alloc::string::String\" }) }")

environment

• spin: 3.0.0
• postgresql: 17
• rust

[itowlson]

The Spin Postgres API doesn't yet support the jsonb type. I can take a look at adding that. In the meantime, is it practical for you to work around this by using a binary column (or a string column with the JSON as text)?

[vanvuongngo]

Hi @itowlson, thanks a lot and that would be great.
I really like your workaround. I will add an additional string column and would just have to migrate later. I had the idea of making a separate rust app, but that would be counterproductive.
Could you please tell me why postgresql and mysql are classified as "experimental"? They are pretty basic for a service, aren't they?

[itowlson]

"Experimental" here means "we are not sure we have the right interfaces", rather than "we are not sure it works." In practice they have been stable across multiple versions of Spin, and we have retained backward compatibility, so we should really remove that designator!

But for example we introduced a new Postgres interface in Spin 3, to allow for date-time types. And those are a minefield which we may not have gotten right on the first go! So in a sense those are experimental. But if we do change them it will be "new interface alongside the existing one," just as Spin 3 continues to support the Spin 2 Postgres interface. The existing interfaces won't break.

[vanvuongngo]

Thanks for clarifying the term as it is in use. To me, as an user of Spin, “experimental” looks like it might mean I shouldn't use it in production. Perhaps it should be called stable for mysql/postgres in general and explain which functions in particular are “experimental”.

As a further workaround for jsonb, I have now created a stored procedure and do not need a migration script:

CREATE PROCEDURE insert_data(a text)
LANGUAGE SQL
AS $$
INSERT INTO tbl VALUES (jsonb(a));
$$;

CALL insert_data("test")