Why does sqlite use a separate adapter

[D4no0]

I've saw that postgres, mysql use the same ecto adapter (Ecto.Adapters.SQL).

Is there a reason why the same adapter cannot be used for sqlite? That would solve a lot of problems with libraries integration that make use of ecto and currently do not support sqlite.

[warmwaffles]

Is there a reason why the same adapter cannot be used for sqlite?

SQL syntax is different. The grammars are not 1:1. SQLite also does not support ALTER COLUMN well.

MySQL and Postgres support a bunch of different column types. In SQLite everything can only be these 5 types https://www.sqlite.org/datatype3.html so we have to coerce types differently than the other libraries.

Have you seen https://github.com/elixir-ecto/ecto_sql/blob/v3.10.2/lib/ecto/adapters/sql.ex? It delegates to the implementations for a lot of query building.

• https://github.com/elixir-ecto/ecto_sql/blob/v3.10.2/lib/ecto/adapters/myxql/connection.ex
• https://github.com/elixir-ecto/ecto_sql/blob/v3.10.2/lib/ecto/adapters/postgres/connection.ex

Setup options for SQLite are also vastly different and wider ranging. You are allowed to specify the journal mode you want, the amount of caching you want, full database encryption, extension support, etc...

It's been a while since I started this project and I can take a look at trying to re-use the Ecto.Adapters.SQL interface, but I do remember running into weird issues doing so. It also ties the implementation tighter to db_connection which creates issues around opening the database and writing to it while other processes are doing the same. It's not impossible, the WAL helps with this, but there are still some things that are not supported, like :sandbox.

The SQLite adapter is also different in the sense that the database is embedded in the current running unix process. There is no TCP connection, thus access patterns are different. I've already tried to bring SQLite into ecto_sql proper and was told "no" because they only want TCP connections in there and SQLite is a one off. They could perhaps revisit bringing it into the fold again, but I don't think they'll bite.

We have already been discussing dropping the db_connection dependency from exqlite because it really isn't that necessary and our own version of pooling can be achieved that is tailor made for the embedded access pattern. elixir-sqlite/exqlite#192

That would solve a lot of problems with libraries integration that make use of ecto and currently do not support sqlite.

Do you have an example of this? I would like to ease any friction if there is some and I need a concrete issue.

[D4no0]

Thanks a lot for the clarification. I was expecting for the adapter to just be a layer that knows how to handle sql, however it does much more under the hood. It seems you are right that going with a custom adapter is the way to go, things like pooling will never work with sqlite correctly, especially if when using things like in-memory database.

It's not impossible, the WAL helps with this, but there are still some things that are not supported, like :sandbox.

This is peculiar and still don't understand how to be handled even to this day. Is it not possible to run integration tests with sqlite?

Do you have an example of this? I would like to ease any friction if there is some and I need a concrete issue.

I work currently on a project where I want to have the option of switching between sqlite and postgres database (for ease of deployment on self-hosted instances), and it seems there is a lot of friction when trying to define the adapter at runtime, I ended up with a hacky way of defining 2 repos as for some reason the adapter cannot be configured from runtime configuration.

We were also thinking integrating tools like https://github.com/curiosum-dev/kanta, but it seems they will have to add another layer of abstraction if you want to configure the adapter.

[warmwaffles]

This is peculiar and still don't understand how to be handled even to this day. Is it not possible to run integration tests with sqlite?

It is possible. I run them async in my private project with generally low flapping. But there is a huge caveat with running parallel with SQLite. If the database is stored over and NFS mount, you will probably encounter some issues. The reason being is that NFS does not have OS level file locking that you would normally have like on a local partition.

I don't remember why I chose not to implement the sandbox. I should look into this again.

There are ways you can configure it at compile time that may help you. Something generally to this effect should work if you want to flip adapters at compile time. Now if you want runtime switching, that's gonna require a DynamicRepo

# In your config/dev.exs
config :my_app, MyApp.Repo,
  otp_app: :my_app,
  adapter: Ecto.Adapters.SQLite3

# In your config/test.exs
config :my_app, MyApp.Repo,
  otp_app: :my_app,
  adapter: Ecto.Adapters.SQLite3

# In your config/prod.exs
config :my_app, MyApp.Repo,
  otp_app: :my_app,
  adapter: Ecto.Adapters.Postgres

# In your `my_app/repo.ex
defmodule MyApp.Repo do
  use Ecto.Repo, Application.compile_env!(:my_app, MyApp.Repo)
end

[D4no0]

I don't remember exactly, but I think I tried the config in the manner you describe and it didn't work with runtime.exs. The idea is that I want it to be configurable via a env variable at runtime.

What I ended up doing is:

defmodule SslMoon.ChecksRepo.Sqlite do
  @moduledoc """
    Checks repo with sqlite driver.
  """
  use Ecto.Repo,
    otp_app: :ssl_moon,
    adapter: Ecto.Adapters.SQLite3
end

defmodule SslMoon.ChecksRepo.Postgres do
  @moduledoc """
    Checks repo with postgres driver.
  """
  use Ecto.Repo,
    otp_app: :ssl_moon,
    adapter: Ecto.Adapters.Postgres
end

And start at runtime only the correct repo.

[warmwaffles]

Yea, you are going to need to have it not be in runtime because it doesn't work for compile_env!.

[warmwaffles]

But your solution will work definitely

[D4no0]

It works, however I still find it strange that it is not possible without 2 repos, because now the code is not that readable, as you have to call a helper function that returns the correct repo instead of Repo directly.

[D4no0]

Anyway, thanks for the fast response, and for the libraries. I think I will start using sqlite more in the future as it is very powerful for small self-contained projects.