Can not run diesel migration run when using json or jsonb with sqlite

[hellozyemlya]

Backend

SQLite

Diesel version

2.3.6

Diesel Features

"sqlite", "returning_clauses_for_sqlite_3_35", "serde_json"

Operating System Version

No response

Third party libraries

No response

What do you want to do?

Run diesel migration run with json or jsonb column type. I gives error

➜  hello-world git:(master) ✗ diesel migration run
Unsupported type: `jsonb`

Manually changing type to Jsonb in schema.rs just works perfectly in runtime.

Compile time error

No response

What code do you already have?

No response

Additional details

No response

Checklist

• I have already looked over the issue tracker and the discussion forum for similar possible closed issues.

[weiznich]

Duplicate of #4447

[hellozyemlya]

Ah, sorry, so its not released yet at all? Pretty long. Sorry for dupe, many thanks for such nice project.

[weiznich]

Ah, yes. You are correct. That other discussion is not really a duplicate. I remembered having a similar request before with a discussion about the details but I cannot find it with 2 minutes of searching. So let's just quickly summarize the response I remember:

The current behaviour is expected. There is no jsonb column type in sqlite 1. For json/jsonb the expected workflow is to use the json or jsonb SQL functions to turn your storage type (Text or Blob) into a json or jsonb object. This then can be deserialized by diesel.

Now you also can change your generated schema.rs file but then diesel cannot fully grantee sane type mapping anymore. E.g. for the case that you managed to put in a non json value into that column.

The proper long term fix for this is to allow specifying custom mappings on column/table/type basis in your diesel.toml file. We are looking forward for contributions there. (There are more details on this in some discussions here, if you would like to contribute I'm happy to search for the relevant thread)