elixir-sqlite/ecto_sqlite3

Make map encoding configurable

Closed this issue · 4 comments

a-nassim commented

I wanted to try the SQLite JSONB format, i.e. JSON stored as a BLOB instead of a TEXT.
I was able to do so by specifying the :blob type instead of :map in a migration.

defmodule Migration do
  @moduledoc """
  Updates resources based on their most recent snapshots.

  This file was autogenerated with `mix ash_sqlite.generate_migrations`
  """

  use Ecto.Migration

  def up do
    create table(:resources, primary_key: false) do
      # add :my_map, :map, null: false <- generated line
      add :my_map, :blob, null: false #  <- edited line
      add :id, :bigserial, null: false, primary_key: true
    end
  end

  def down do
    drop table(:resources)
  end
end

It would be convenient if we could use :map in migrations, configuring :blob just once for the adapter, similar to how it is done in the Postgres adapter. TEXT could still be the default.

What do you think?

warmwaffles commented

I think mimic'ing how it is done with the postgres adapter is a good idea. Since this feature was added to SQLite 3.45, (we are at 3.49), we will need to have the default be what it is now and set a timeline on when it will be swapped to be jsonb by default.

warmwaffles commented

Feel free to open a PR for it! I don't have time this week to add it.

  • 👍1
a-nassim commented

So this change allowed configuring the database column type (BLOB or TEXT) for :map fields

However actually using SQLite's JSONB format seems to require explicit usage of the functions jsonb during inserts, and json for reads, which doesn't integrate smoothly with how Ecto currently works.

I initially thought that inserting JSON data in a BLOB column would cast automatically to JSONB, but that doesn't appear to be the case. Without a strict table, SQLite still allows storing TEXT in BLOB columns without any type enforcement.

As it stands, this setting probably cannot become the default in the future. In hindsight I am unsure whether supporting this configuration adds value without deeper Ecto integration.

Here is a Livebook exploring some issues that arise when using SQLite's JSONB with Ecto

What do you think?

sqlite-jsonb-ecto.livemd

SQLite JSONB with Ecto

SQLite JSONB Ecto

Mix.install([:jason, {:ecto_sqlite3, "0.19.0"}, :kino_db])

Section

defmodule Repo do
  use Ecto.Repo, otp_app: :my_app, adapter: Ecto.Adapters.SQLite3
end
{:module, Repo, <<70, 79, 82, 49, 0, 0, 86, ...>>, :ok}

database = System.tmp_dir!() |> Path.join(Ecto.UUID.generate())
"/var/folders/1r/_mzmjvtd0hq5n9b7mwwcktnc0000gn/T/ca202987-aa60-4b96-b6e2-d9b0c653a47f"

Kino.start_child({Repo, database: database})
{:ok, #PID<0.239.0>}

defmodule Migration do
  use Ecto.Migration

  def change do
    create table("settings") do
      add(:properties, :blob)
    end
  end
end

00:15:55.183 [error] Exqlite.Connection (#PID<0.246.0>) failed to connect: ** (Exqlite.Error) database is locked

{:module, Migration, <<70, 79, 82, 49, 0, 0, 12, ...>>, {:change, 0}}

Ecto.Migrator.down(Repo, 1, Migration)
Ecto.Migrator.up(Repo, 1, Migration)

00:15:55.225 [info] == Running 1 Migration.change/0 forward

00:15:55.226 [info] create table settings

00:15:55.231 [info] == Migrated 1 in 0.0s

:ok

defmodule Setting do
  use Ecto.Schema

  schema "settings" do
    field(:properties, :map)
  end
end
{:module, Setting, <<70, 79, 82, 49, 0, 0, 41, ...>>, :ok}

Data is inserted without error but as TEXT

%Setting{properties: %{a: 1, b: 2, c: 3}}
|> Repo.insert!

00:15:55.284 [debug] QUERY OK source="settings" db=0.2ms idle=96.0ms
INSERT INTO "settings" ("properties") VALUES (?1) RETURNING "id" [%{c: 3, a: 1, b: 2}]

%Setting{
  __meta__: #Ecto.Schema.Metadata<:loaded, "settings">,
  id: 1,
  properties: %{c: 3, a: 1, b: 2}
}

No error reading either

import Ecto.Query
Setting |> last() |> Repo.one()

00:15:55.288 [debug] QUERY OK source="settings" db=0.0ms idle=102.0ms
SELECT s0."id", s0."properties" FROM "settings" AS s0 ORDER BY s0."id" DESC LIMIT 1 []

%Setting{
  __meta__: #Ecto.Schema.Metadata<:loaded, "settings">,
  id: 1,
  properties: %{"a" => 1, "b" => 2, "c" => 3}
}

defmodule StrictMigration do
  use Ecto.Migration

  def change do
    create table("strict_settings", options: "STRICT") do
      add(:properties, :blob)
    end
  end
end
{:module, StrictMigration, <<70, 79, 82, 49, 0, 0, 13, ...>>, {:change, 0}}

Ecto.Migrator.down(Repo, 2, StrictMigration)
Ecto.Migrator.up(Repo, 2, StrictMigration)

00:15:55.306 [info] == Running 2 StrictMigration.change/0 forward

00:15:55.306 [info] create table strict_settings

00:15:55.306 [info] == Migrated 2 in 0.0s

:ok

defmodule StrictSetting do
  use Ecto.Schema

  schema "strict_settings" do
    field(:properties, :map)
  end
end
{:module, StrictSetting, <<70, 79, 82, 49, 0, 0, 42, ...>>, :ok}

%StrictSetting{properties: %{a: 1, b: 2, c: 3}}
|> Repo.insert!

00:15:55.329 [debug] QUERY ERROR source="strict_settings" db=0.1ms idle=108.6ms
INSERT INTO "strict_settings" ("properties") VALUES (?1) RETURNING "id" [%{c: 3, a: 1, b: 2}]

** (Exqlite.Error) cannot store TEXT value in BLOB column strict_settings.properties
INSERT INTO "strict_settings" ("properties") VALUES (?1) RETURNING "id"
    (ecto_sql 3.12.1) lib/ecto/adapters/sql.ex:1096: Ecto.Adapters.SQL.raise_sql_call_error/1
    (ecto 3.12.5) lib/ecto/repo/schema.ex:837: Ecto.Repo.Schema.apply/4
    (ecto 3.12.5) lib/ecto/repo/schema.ex:416: anonymous fn/15 in Ecto.Repo.Schema.do_insert/4
    (ecto 3.12.5) lib/ecto/repo/schema.ex:311: Ecto.Repo.Schema.insert!/4
    #cell:qfo3w7xwxhptdmw4:2: (file)

This is the intended way to store data as JSONB, not sure whether this could be integrated in Ecto easily

value = %{a: 1, b: 2, c: 3} |> Jason.encode!()
Repo.query!("insert into strict_settings (properties) select jsonb('#{value}')")

00:16:03.149 [debug] QUERY OK db=1.1ms idle=1979.2ms
insert into strict_settings (properties) select jsonb('{"c":3,"a":1,"b":2}') []

%Exqlite.Result{command: :execute, columns: [], rows: [], num_rows: 0}

import Ecto.Query
StrictSetting |> last() |> Repo.one()

00:16:07.426 [debug] QUERY OK source="strict_settings" db=0.4ms queue=0.4ms idle=1256.2ms
SELECT s0."id", s0."properties" FROM "strict_settings" AS s0 ORDER BY s0."id" DESC LIMIT 1 []

** (ArgumentError) cannot load `<<204, 12, 23, 99, 19, 51, 23, 97, 19, 49, 23, 98, 19, 50>>` as type :map for field :properties in %StrictSetting{__meta__: #Ecto.Schema.Metadata<:loaded, "strict_settings">, id: nil, properties: nil}
    (ecto 3.12.5) lib/ecto/repo/queryable.ex:431: Ecto.Repo.Queryable.struct_load!/6
    (ecto 3.12.5) lib/ecto/repo/queryable.ex:246: anonymous fn/5 in Ecto.Repo.Queryable.preprocessor/3
    (elixir 1.18.4) lib/enum.ex:1714: Enum."-map/2-lists^map/1-1-"/2
    (ecto 3.12.5) lib/ecto/repo/queryable.ex:237: Ecto.Repo.Queryable.execute/4
    (ecto 3.12.5) lib/ecto/repo/queryable.ex:19: Ecto.Repo.Queryable.all/3
    (ecto 3.12.5) lib/ecto/repo/queryable.ex:154: Ecto.Repo.Queryable.one/3
    #cell:ephtgpofnke6eas4:2: (file)

Repo.query!("select json(properties) from strict_settings")

00:16:13.010 [debug] QUERY OK db=2.1ms queue=0.8ms idle=1835.0ms
select json(properties) from strict_settings []

%Exqlite.Result{command: :execute, columns: ["json(properties)"], rows: [["{\"c\":3,\"a\":1,\"b\":2}"]], num_rows: 1}
warmwaffles commented

JSONB and strict settings on tables are fairly recent addition to sqlite.

Longer term, I think ecto probably needs to change a little bit up stream and send along type encoding where possible for columns so that the adapters know which cases we can use specific functions. Do I think this will happen? Probably not.

This also falls a bit on the SQLite team as well. We only have 5 native column types: NULL, INTEGER, REAL, TEXT, or BLOB. There is no dedicated JSONB type and if we were to set that for a column type, under the hood IIRC gets set as a BLOB.

https://www.sqlite.org/datatype3.html
https://sqlite.org/jsonb.html

  • 👍1