Does not dump JSON correctly with Polymorphic Embed

[joshuataylor]

I've seen this - #41

Seems that using the dumper dumps to string that can't be later read properly with SQLite, it stores it like this in a JSON field in SQLite:

"{\"property_type\":\"foo\",\"__type__\":\"something\"}"

instead of like this:

{"property_type": "foo", "__type__": "something"}

Means functions like json_extract etc won't work natively in SQLite.

I believe it's because when dumping it's done this way:

dumper.(:map, map)

So with SQLite, this dumps as a string:

{:ok,
 "{\"foo\":\"x\",\"bar\":\"2021-12-03T07:22:16.086910Z\",\"baz\":\"xxx\",\"__type__\":\"something\"}"}

But with Postgres it dumps as a map:

{:ok,
 %{
   :something => "hello",
   "__type__" => :"Whatever"
 }}

Not sure if this is an ecto_sqlite3 problem or a polymorphic_embed issue.

It looks like creating normal maps works properly as JSON.

[warmwaffles]

I'll look into this a little bit more.

The json data type in sqlite3 is not actually a native type. It is stored as a string and parsed when it is loaded.

[joshuataylor]

What's strange is that if I have a "normal" map it stores correctly as JSON

[warmwaffles]

What's strange is that if I have a "normal" map it stores correctly as JSON

Can you provide a quick example of that?

[joshuataylor]

Sorry for the delay. Here is an example repo that reproduces the issue: https://github.com/joshuataylor/sqlite_example

When doing a mix ecto.migrate:

INSERT INTO "foobar" ("baz","hello","world") VALUES (?,?,?) RETURNING "id" [42, "{\"b\":22.22,\"v\":1,\"z\":\"x\"}", "earth"]
INSERT INTO "widget" ("hello","metadata") VALUES (?,?) RETURNING "id" ["{\"b\":22.22,\"v\":1,\"z\":\"x\"}", "\"{\\\"id\\\":null,\\\"ping\\\":\\\"chrome\\\",\\\"pong\\\":100,\\\"__type__\\\":\\\"bar\\\"}\""]

It looks like it's a normal string for the widget type.

[warmwaffles]

Excellent, I'll take a look soon.

[warmwaffles]

I don't think I quite understand the issue.

Here's the module I used to insert a record.

defmodule SqliteExample.Schema do
  alias SqliteExample.Repo

  alias SqliteExample.Widget
  alias SqliteExample.Bar

  def insert_widget! do
    %Widget{}
    |> Widget.changeset(%{
      hello: %{},
      metadata: %{__type__: "bar", ping: "ping 1", pong: 1}
    })
    |> Repo.insert!()
  end

  def get_widget(id) do
    Repo.get(Widget, id)
  end
end

Result

iex(5)> SqliteExample.Schema.insert_widget!
[debug] QUERY OK db=9.0ms decode=0.4ms idle=1575.0ms
INSERT INTO "widget" ("hello","metadata") VALUES (?,?) RETURNING "id" ["{}", "\"{\\\"id\\\":null,\\\"ping\\\":\\\"ping 1\\\",\\\"pong\\\":1,\\\"__type__\\\":\\\"bar\\\"}\""]
%SqliteExample.Widget{
  __meta__: #Ecto.Schema.Metadata<:loaded, "widget">,
  hello: %{},
  id: 1,
  metadata: %SqliteExample.Bar{id: nil, ping: "ping 1", pong: 1}
}

Fetching the result

iex(7)> SqliteExample.Schema.get_widget(1)
[debug] QUERY OK source="widget" db=0.0ms idle=1506.8ms
SELECT w0."id", w0."hello", w0."metadata" FROM "widget" AS w0 WHERE (w0."id" = ?) [1]
%SqliteExample.Widget{
  __meta__: #Ecto.Schema.Metadata<:loaded, "widget">,
  hello: %{},
  id: 1,
  metadata: %SqliteExample.Bar{id: nil, ping: "ping 1", pong: 1}
}

[joshuataylor]

sqlite> .open sqlite_example_dev.db
sqlite> select json_extract(hello, '$.b') from foobar;
22.22
sqlite> select json_extract(hello, '$.v') from foobar;

This is expected. Now as the other values are stored as a string, we can't do this.

sqlite> select json_extract(hello, '$.b') from foobar;
22.22
sqlite> select json_extract(hello, '$.v') from foobar;

select json_extract(metadata, '$.ping') from widget

sqlite> select metadata from widget;
"{"id":null,"ping":"chrome","pong":100,"type":"bar"}"

This is stored as a string, so we can't do JSON queries on this.

[warmwaffles]

Oh this is strange. Let me play with exqlite and ecto_sqlite3's serialize and deserialize stuff. I hope this doesn't break someone's stuff that exists already.

[warmwaffles]

Created a test to verify that the column was serialized appropriately.

9c5f5a4

[warmwaffles]

I'm not quite done with it yet because clearly I can reproduce it with your repository and inspect with sqlite3 on the command line.

[joshuataylor]

With postgres, putting a IEX.pry on dumper.(:map, map) (here)

returns:
{:ok, %{:id => nil, :ping => "chrome", :pong => 100, "__type__" => :bar}}

With sqlite, it returns the string:
{:ok, "{\"id\":null,\"ping\":\"chrome\",\"pong\":100,\"__type__\":\"bar\"}"}

[joshuataylor]

I believe it's because it's double json encoded, once from a map to a string, then from a string to another string.

If a check is added to json_encode for checking if it's a bitstring, this fixes it.

  def json_encode(value) when is_bitstring(value), do: {:ok, value}

It would be better to check upstream in the dumpers in sqlite3.ex, but I'm unsure how to do this (the &Codec.json_encode/1 looks like magic to me).

[warmwaffles]

Let me noodle on promoting that into exqlite. I think it has merit for sure.

[warmwaffles]

In the mean time, let's put your first suggestion with the guard clause in.

[joshuataylor]

Sorry, I meant https://github.com/elixir-sqlite/ecto_sqlite3/blob/main/lib/ecto/adapters/sqlite3.ex#L383 .

I don't believe it's an exqlite issue, but an ecto_sqlite3 issue of double encoding. I misspoke and should have said in the other function. 🤦