Support for Json field type in SQLite

[m5nv]

Problem

SQLite has had support for JSON field type for a while. Sequelize supports this field type on par with Postgres and MySQL.

Lack of support for this field type by Prisma is preventing us from porting over our project.

SQLite is a great bootstrapping technology that allows developers to quickly prototype applications without introducing new dependencies such as docker or worry about running yet another service in the development flow. JSON field type gives us the option to have a nosql database colocated in a sql database.

Suggested solution

Please implement support for this feature. See JSON Extension documentation and refer to Sequelize's implementation if required.

Alternatives

Defer migration to Prisma and stick with Sequelize. Maintain status quo.

Additional context

cc: @ryands17
ref: #3575

[empz]

Would love to see this implemented in Prisma!

[alex-kinokon]

Do you have any update regarding this? Please tell us so those of us who need JSON field type support can decide whether to skip Prisma.

[davidharting]

I am interested in this to make Prisma even more viable for interacting with SQLite from an electron app or CLI app.

[wolffparkinson]

This is the first result that comes up for Prisma JSON SQLite search. Some response on whether this is in near future plans or not would be appreciable from the maintainers, considering this issue is over an year old.

[calumk]

I'd like to also vote for this feature.
Typeorm solves it with its simple-json feature.

[alex-kinokon]

At this point the lack of response is more concerning than the lack of this feature.

[janpio]

We never respond to feature requests unless we concretely work on issues, need more information or similar.
That this feature request still exists (and was not closed) is our information that we consider it valid and might prioritize it in the future.
Leaving comments with use cases and additional information is helpful for us to make sure we understood the problem you are tring to solve and what exactly you are looking for from the feature.

[dgb23]

It has been mentioned that people use SQLite for local development, but I'm evaluating Prisma for a project where SQLite is a (soft-) requirement for production use. Of course this can be worked around by using text fields, but that would limit the both leverage of what SQLite provides out of the box and the utility of using Prisma. I'm hopeful for a more feature complete integration with SQLite.

[Philzen]

With Json datatype being available in prisma already for MySql and Postgres, and json being natively supported in SQLite for quite a while now, this would be a huge win for inter-operability. With this issue being over 1 1/2 years standing, it would be lovely to see this on the roadmap in the near future.

Until this lands, is there any workaround that can be used for simple use cases (just querying and writing w/o and fancy filtering functions)? Idea is, that we could use this datatype in schema definitions and implement something like a custom adapter/plugin for local sqlite development to handle the de-/serialization.

[kof]

We want to support both Sqlite and PostgreSQL in Webstudio and while migrating from MongoDB discovered the missing support for JSON type.

With the recent motion in favor of SQLite serverside by multiple startups and announcement of D1 from cloudflare, this should be a priority for Prisma.

[zomars]

Can we sponsor this feature? I'm really interested in offering a sqlite version of our DB (calcom/cal.com#3388) so it can be played with out of the box and our only limitation is the lack of JSON fields.

[janpio]

Relevant information that changes the situation for this issue:

The JSON functions and operators are built into SQLite by default, as of SQLite version 3.38.0 (2022-02-22).
[...]
Prior to version 3.38.0, the JSON functions were an extension that would only be included in builds if the -DSQLITE_ENABLE_JSON1 compile-time option was included. In other words, the JSON functions went from being opt-in with SQLite version 3.37.2 and earlier to opt-out with SQLite version 3.38.0 and later.
via https://www.sqlite.org/json1.html

🚀!

What could help here right now:
If someone has an application that makes use of a lot of Prisma's Json functionality, it would be awesome if you could collect the queries Prisma generates in PostgreSQL (https://pris.ly/d/logging) for your app and then would try to run these against a SQLite database where you added the Json column by hand. Do they all work out of the box?

[noxware]

+1

I'm trying Prisma on a CLI app and I reached this "inconvenience".
At least would be nice to have a way to define a custom type with its serialization/deserialization logic.

[rodbs]

Any updates on this? Why is JSON not implemented in SQLite while it's in PostgreSQL? thx!

[janpio]

Because JSON was not enabled by default in the past on SQLite, see the message above.

[zomars]

run these against a SQLite database where you added the Json column by hand.

What do you mean by "add the Json column by hand"? @janpio

[janpio]

Prisma can not handle and create JSON columns for SQLite. So to be able to test the queries we use, you would need to add a Json column manually to the SQLite database and then run the queries against that SQLite database with that column present to be able to tell us if they all work, or horribly crash.

[jimablue]

hi janpio, appreciate the work!
I added a JSON column manually and have been using prisma.$queryRaw() method to SELECT and UPDATE that column for the past week. So far everything works out of the box for me! Hope it helps

[LeonFedotov]

+1
would love to use sqlite in dev and need that json type
please add support

[biels]

+1 to this, now JSON is natively supported in SQLite. And this must be easy to implement since the syntax and everything is already in place for postgres.

[zomars]

Can we at least enable it under a preview feature flag?

[janpio]

If someone does a PR to add it to our codebases under a preview feature flag, sure. To be accepted it will require tests as well though. We currently are working on other things, so this is a not a priority and no one can spend time on it. As soon as this changes, we might tackle this.

[jonasmerlin]

@janpio Really don't get why my "+1" comment above was "marked as abuse" (I'm assuming this was done by you or someone else on the Prisma team since I can't seem to report other comments as abuse. If not, I'm sorry.) I sense a mild annoyance with this feature request in general in your comments and to a certain degree I get that, but to quote from the prisma guidelines for feature requests:

If the feature on the roadmap is linked to a GitHub issue, please make sure to leave a +1 on the issue and ideally a comment with your thoughts about the feature!

I didn't add a comment since I felt it wouldn't add much, since the feature was clear and already discussed extensively. I don't know if these reports are something that GitHub tracks about me, but I wouldn't want for this to lead to any issues with my account in the future so I would really appeciate it if you could reverse the report if that is possible.

[janpio]

I updated the README to avoid such a misunderstanding, and clarified that issues should get a 👍 reaction.

+1 comments are pure noise that keep us from responding to actual issues and comments. We get dozens of these per day, hundreds per week, which led to most colleagues not even reading the comments on feature requests anymore. Users that leave actual comments on issues are complaining that they get notifications and emails for updates - to then nothing useful or helpful. If we don't hide them, we will get more and more and our issues will get less and less useful to us and our users. (And no, those are not reports to GitHub but just a local way to hide comments that do not add anything to the discussion. But I deleted your comment instead so there is not "abuse" label on your comment even in this repository - the mention of this in the issue timeline is why we usually do not do that - it often leads to even more questions that are offtopic to the actual issue).

And that you "sense mild annoyance with this feature request" is you reading too much into what I wrote (or did not write). I am pretty eager to get this feature implemented - just don't have the capacity to prioritize it right now.

(Sorry for everyone being subscribed to the isseu that now also got a notification about this message.)

[rogeriorioli]

the basics of javscript JSON.stringfy

model Teste {
  json String
}

{

   data : {
     json :  JSON.stringfy(OBJECT) 
   }
}

[entrptaher]

Maybe we can use sqld to mimic postgresql for now, and test prisma against it?

https://github.com/libsql/sqld

[jdkdev]

I wanted to add my eagerness for this to be implemented as well, as well as get notified if there is movement on this ticket!

Thanks Prisma team!

P.S.

for anyone interested the best hack I have for now is doing this:

and then I have prisma middleware that is aware of the default values and knows how to the treat them from there...

[mikeseese]

@janpio

If someone has an application that makes use of a lot of Prisma's Json functionality, it would be awesome if you could collect the queries Prisma generates in PostgreSQL (pris.ly/d/logging) for your app and then would try to run these against a SQLite database where you added the Json column by hand. Do they all work out of the box?

I'm not sure why it's taken this long for someone to pipe up here, but the answer is simply "no" here. Here are some initial findings with a small subset of the functions, but I didn't bother continuing to research as it just seems like the Prisma implementation of how it handles the JSON queries is just not compatible with SQLite.

My Prisma model is:

model User {
  id String @id @default(uuid())
  data Json
}

An INSERT using a postgres datasource looks like:

INSERT INTO "public"."User" ("id","data") VALUES ($1,$2) RETURNING "public"."User"."id", "public"."User"."data"
["918bab14-5f26-4ef5-bed9-0661156a09a7",{"foo":"bar"}]

but SQLite expects the $2 param to be text. For example {"foo":"bar"} needs to become '{"foo":"bar"}'.

Perhaps that's a logging or library manipulation mismatch issue however, so looking at one of the more complicated queries:

await prisma.user.findFirst({
  where: {
    data: {
      path: ["foo"],
      equals: "bar"
    }
  }
});

looks like:

SELECT "public"."User"."id", "public"."User"."data" FROM "public"."User" WHERE ("public"."User"."data"#>ARRAY[$1]::text[])::jsonb::jsonb = $2 LIMIT $3 OFFSET $4
["foo","bar",1,0]

SQLite errors: SQLITE_ERROR: unrecognized token: "#" which makes sense because there is no #> or #>> operator in the SQLite JSON functions, only -> and ->>. Mind you, the ::json or ::jsonb tokens would also likely choke.

A comparable call in SQLite would look like:

SELECT "User"."id", "User"."data" FROM "User" WHERE "User"."data"->'$.foo' = '"bar"' LIMIT 1 OFFSET 0

or using ->> also works with a different value for checking bar

SELECT "User"."id", "User"."data" FROM "User" WHERE "User"."data"->>'$.foo' = 'bar' LIMIT 1 OFFSET 0

or removing the $. in front of foo also works

[weleo]

SQLite’s 3.45.0 release introduces changes to JSON data handling, using JSONB as the new internal format for all JSON functions, which now generate binary JSONB under the hood.

This might be a good time to revisit the implementation of JSON field type support in Prisma for >= SQLite 3.45.0.

[ryantando]

Is there any temporary solution for this?

[mikeseese]

@ryantando I am specifying the type of the field as String in the schema and then provide extensions to transform the string to/from JSON in the client creation. You'll have to do this with each JSON variable.

client = new PrismaClient({
  datasourceUrl,
}).$extends({
  result: {
    playerCharacter: {
      metadata: {
        needs: { metadata: true },
        compute(obj) {
          return JSON.parse(obj.metadata as string);
        },
      },
    },
  },
  query: {
    playerCharacter: {
      create({ args, query }) {
        args.data.metadata = JSON.stringify(args.data.metadata);
        return query(args);
      },
      update({ args, query }) {
        if (args.data.metadata !== undefined) {
          args.data.metadata = JSON.stringify(args.data.metadata);
        }
        return query(args);
      },
      updateMany({ args, query }) {
        if (args.data.metadata !== undefined) {
          args.data.metadata = JSON.stringify(args.data.metadata);
        }
        return query(args);
      },
      upsert({ args, query }) {
        args.create.metadata = JSON.stringify(args.create.metadata);
        if (args.update.metadata !== undefined) {
          args.update.metadata = JSON.stringify(args.update.metadata);
        }
        return query(args);
      },
    },
  },
});