[BUG]: SQLite JSON should be TEXT, not blob

[arjunyel]

What version of drizzle-orm are you using?

0.24.2

What version of drizzle-kit are you using?

0.17.5

Describe the Bug

Right now drizzle has JSON under blob when it should be under text https://github.com/drizzle-team/drizzle-orm/tree/main/drizzle-orm/src/sqlite-core#column-types

SQLite does not (currently) support a binary encoding of JSON. (...) All JSON functions currently throw an error if any of their arguments are BLOBs because BLOBs are reserved for a future enhancement in which BLOBs will store the binary encoding for JSON. https://www.sqlite.org/json1.html

Expected behavior

I'd expect Drizzle stores JSON as TEXT and eventually natively supports JSON operators. It would be cool if the driver automatically handled JSON.stringify and JSON.parse too :)

Environment & setup

No response

[205g0]

@arjunyel just read through the sqlite's JSON functions and I wonder about their actual use case. They take strings (which look like JSON), so why not just do all JSON operations on app level before a final JSON.stringify() and before they enter sqllite as text?

Pros:

• You put load on the app servers/edge functions which are way easier to scale and not the db
• You keep JSON logic/validations always in the app
• With Drizzle custom types and mapFromDriverValue() you can automate the last JSON.stringify() before insert, IDK about the way around or I missed it, @dankochetov ?

Cons:

• JSONs in the db are just dumb data which can't be queried but tbh, to do proper stuff you would need native JSON support like MongoDB

For my use case I don't have the con because if I wanted the JSON or some of its props to have "meaning", I would make columns out of it bc of type safety, otherwise I could use Mongo right away

So, what does speak against saving JSONs just as text and stringified before?

[205g0]

Forget what I've written before, it's BS.

We need to have those JSON ops, hence OP's expected behaviour makes total sense. An example:

Without these sqlite fucntions we'd need to get the entire JSON, with these we can just slice a portion (with the -> or ->> operators) and send the smaller portion to the app server. This can make a difference in perfomance but cost as well.

So, until this is fixed, why not just save JSON as text as use the sqlite JSON functions with a plain SQL query without Drizzle?!

[cometkim]

fyi jsonb soon to be supported in SQLite

https://sqlite.org/draft/jsonb.html

Why don't "query builder" support the json/jsonb types provided by DB natively? Even though text/blob mode:json has its advantages, it would be better to leave it to the user's choice.

[Angelelz]

I think this issue is outdated. See the current docs where they recommend json to be stored in text. I also just tested this and it works properly.

Edit: Please feel free to re-open if you think there's still a problem.