So, I have an SQLite3 DB I am trying to connect to a Phoenix app. It’s the backup file of a popular book cataloguing app and this is the list of its tables:
I am trying to connect to ZBOOK, so I have prepared a MW schema:
defmodule MyApp.Schema do
defmacro __using__(_) do
quote do
use Ecto.Schema
@primary_key {:Z_PK, :integer, autogenerate: false}
@foreign_key_type :integer
end
end
end
defmodule MyApp.Book do
use MyApp.Schema
schema "ZBOOK" do
field :ZTITLE, :string
end
end
All of ZBOOK, Z_PK and ZTITLE exist and are typed correctly. Now, when I try spin up the Phoenix server using iex, no errors are thrown, which means that SQLite3 DB’s path is correct. But then, when I try to run (in sequence):
alias MyApp.{Repo, Book}
import Ecto.Query
query = from(Book)
Repo.all(query)
I get the error:
** (Exqlite.Error) no such table: ZBOOK
SELECT Z0."Z_PK", Z0."ZTITLE" FROM "ZBOOK" AS Z0
(ecto_sql 3.11.1) lib/ecto/adapters/sql.ex:1054: Ecto.Adapters.SQL.raise_sql_call_error/1
(ecto_sql 3.11.1) lib/ecto/adapters/sql.ex:952: Ecto.Adapters.SQL.execute/6
(ecto 3.11.2) lib/ecto/repo/queryable.ex:232: Ecto.Repo.Queryable.execute/4
(ecto 3.11.2) lib/ecto/repo/queryable.ex:19: Ecto.Repo.Queryable.all/3
iex:6: (file)
To be sure, I ran the query SELECT Z0."Z_PK", Z0."ZTITLE" FROM "ZBOOK" AS Z0 into the SQLite3 shell and it worked without any problem. What am I doing wrong?
This is actually not true, so without more info this likely has to do with your problem. If a sqlite db doesn’t exist at the path you give it (at least in dev, maybe in prod too??) it just creates it. It will also run migrations if you have them. If you .read or cat your schema into one db and happened to have it named wrong in config/dev.exs, then this would be the result. This is a guess, though!
My only other (very doubful) thought was that the all caps had something to do with it, but I did a quick test out of curiosity and it worked fine.
Yeah and when it doesn’t have permissions to create that file it crashes, taking down erlang runtime with it, without any logs. Really fun experience .
Yes, I feel out of all “official” adapters, SQLite3 seems to one with the least amount of love, but this seems to be somewhat of a trend in the BEAM community (cf. this feed, for example).
Don’t get me wrong: If I could, I would also do everything I need SQLite3 for with ETS / Mnesia, but the problem comes from 3rd party apps that already use SQLite3 as a DB, for exports or as a backup storage. Eventually, you’ll have to interact with SQLite3, or you use tools like pgloader and try to get the DB into PG before accessing it via Ecto.
I wouldn’t worry too much, it is just a question of time until it will get better.
I never used mnesia, however I would always advocate for sqlite3, simply because you have much better tooling around it and you don’t depend on any specific technology. It is also great compared to a self-hosted postgres (or similar databases) the fact that you don’t have a separate runtime or configuration hurdles, it just works (until it doesn’t ).
Yes, definitely. SQLite3 is frankly insane, all the benefits of file-based storage (CSV, JSON, etc.) with built-in SQL. The Erlang dependency is brutal, because as soon as you leave the BEAM bubble (in which I am very comfortable, don’t get me wrong), a lot of devs have no idea what you are talking about when you mention Mnesia.
Definitely, even prior to moving to the BEAM, I very often demoed apps with SQLite3 in order to avoid the overhead of running a PG instance. I still do it sometimes, especially if I want to throw together something quick. But of course, if you want to leverage the QoL features of the DB you’re using, you can’t, which is the reason why today I have a PG dev DB running as a docker compose service in almost every project with a backend.
Not sure what you call official, but exqlite and sqlite_ecto3 are certainly not developed by the ecto team. It’s also tricky, because a lot of the assumptions made by ecto_sql (what we usually interact with) don’t really map cleanly to sqlite, because there is no server, no connections different constraints around blocking between transactions and so on. You can look at Replace DirtyNIF execution model with a different mechanism · Issue #192 · elixir-sqlite/exqlite · GitHub if you’re interested in more of the details.
Also the thread you linked was around ecto_sqlite2, which ran into similar problems, but had an even harder time because it was powered by an erlang library, which wasn’t even built with ecto in mind. Exqlite does quite a bit better in that regard.
Yeah, that was some unhappy wording on my end, I rather meant that it’s somehow the default adapter driver like postgrex is for PG, e.g., when running mix phx.new with the --database sqlite3 flag it defaults to exqlite.
Yes, I agree, SQLite3 is very different from PG and MySQL in the way it works. I was not criticizing Ecto in any way and maybe it’s easier to plug into SQLite3 directly w/o the Ecto layer, but I don’t know because I haven’t tried it yet.
Good point, I missed the fact that the discussion was about a different driver. I apologize.
Why yes it is “easy” although I highly suggest you don’t do that. There are so many goodies that come with Ecto and it’s changesets. You can indeed just query an sqlite database without ecto. You can utilize Exqlite.Basic if you really want this, but be warned it may be going away.
Was this a bug on the consumer side or was the library expanding the path wrong? If the library was expanding it incorrectly, this may need an issue opened up.
.
