Call SQL function when dumping custom Ecto type with SQLite and virtual table

Questions & Help Questions
, , ,
1

I have a custom Ecto type and want to call a SQL function when dumping a value of this type into a virtual table with SQLite.

I’m developing this library to use sqlite-vec with Elixir.

There are 3 vector types in sqlite-vec: float32, int8, and bit.
The most performant way to use sqlite-vec is to create a virtual table.
Then you can create vectors with SQL functions as constructors.

So, in SQL that would be something like this:

CREATE VIRTUAL TABLE vt USING vec0(id INTEGER PRIMARY KEY, embedding int8[2])

INSERT INTO vt(id, embedding) VALUES(1, vec_int8('[1, 2]'))

I’ve created a custom Ecto type for each of the three vector types, e.g. here for int8:

defmodule SqliteVec.Ecto.Int8 do
  @moduledoc """
  `Ecto.Type` for `SqliteVec.Int8`
  """
  use Ecto.Type

  def type, do: :binary

  def cast(value) do
    {:ok, SqliteVec.Int8.new(value)}
  end

  def load(data) do
    {:ok, SqliteVec.Int8.from_binary(data)}
  end

  def dump(%SqliteVec.Int8{} = vector) do
    {:ok, SqliteVec.Int8.to_binary(vector)}
  end

  def dump(_), do: :error
end

What I want to achieve is inserting vectors with regular Ecto.Repo functions.

For that, I define a schema:

defmodule VT do
  use Ecto.Schema

  schema "vt" do
    field(:embedding, SqliteVec.Ecto.Int8)
  end
end

And now I want to insert using

MyApp.Repo.insert(%VT{
  embedding: SqliteVec.Int8.new([1, 2])
})

The problem is that this fails as I’m dumping the binary data directly instead of calling the vec_int8 constructor function.
Actually, sqlite-vec interprets plain binary data as float32 (as if I would call the vec_f32 constructor function).
So it works for float32 vectors without constructor function but fails for int8 and bit vectors because the types don’t match.

What I’ve tried:

  • parameterized types, I don’t think they add any capabilities that would work here
  • changing the type of the custom type from :binary to :string and dump a string of the constructor function with interpolated arguments, e.g. “vec_int8(‘[1, 2]’)”

I also had a look at ecto_sqlite3 as I’m assuming that the correct place for this functionality would be the adapter, and in particular the codec.
I guess I would need a way to define my own codec for the custom types, and call the constructor function there, not sure if that would work though.

Thanks in advance for any input!