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

Questions / Help
, , ,
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!

2

I’m not sure it would work in this context, but have you considered using Ecto’s fragment/1?

https://hexdocs.pm/ecto/Ecto.Query.API.html#fragment/1

3

I don‘t think you can merge support like that in from the ecto.type level. The db driver needs to support the column type, which then can be used by an ecto type.

You can take a look host postgrex supports extension for column types (e.g. in geo_postgrex). I‘m not sure if exqlite supports a similar extension system.

4

Thanks for the responses.

I’ve considered using fragment/1 but the problem is where to hook it in.

Thanks for the pointer to postgrex extensions, I’ll look into that if I find the time.