Sqlite, schemaless queries and mapping

One current limitation of the excellent sqlite3 adapter is schemaless queries: Ecto.Adapters.SQLite3 — Ecto SQLite3 v0.5.6

Using schemaless Ecto queries will not work well with SQLite. This is because the Ecto SQLite adapter relies heavily on the schema to support a rich array of Elixir types, despite the fact SQLite only has five storage classes. The query will still work and return data, but you will need to do this mapping on your own.

There’s an open issue to add examples for such mapping: Add examples to documentation for schemaless queries with mappings · Issue #14 · elixir-sqlite/ecto_sqlite3 · GitHub

I was wondering: does any one have examples of how these queries would look? (And these could be added to documentation perhaps)

1 Like

I’ve thought about this the last year (when I still thought I’ll have time and energy for xqlite) and the only two options I could figure out back then were these:

  1. Utilize SQL comments in the table/view DDLs in the manner described in this SO answer: Sqlite: adding comments to tables and columns? - Stack Overflow. Ideally you’d want this to be DBML or some instance of XML so it absolutely can’t be gotten wrong but really, anything would work.

  2. Have a special table inside your schema a la schema_migrations for Ecto. For my library I am planning to call it xqlite_metadata but I might get bolder and use some more generic naming like sqlite_typing_info. And then you put whatever you like in that table. Of course this has to come with a HUGE WARNING in your docs: “Don’t delete this table or the library won’t work properly!” but what can you do? Developers are, on average, better users than the non-technical people so this should mostly be fine. I mean, I am not seeing people deliberately deleting the schema_migrations table after all.

I am leaning to #2 because, if done well, it can be reused even by other libraries so we could all stop reinventing the same wheels. And if done really well then one day it could be used by sqlite3’s authors to finally start and gradually integrate strict types in the engine. Who knows. It might happen! :slight_smile:

Hmmm… I like the idea. But how would then you’d use that in a schemaless query?

Currently when you do a schemaless query with the sqlite3 ecto adapter, it tries to figure out the types and may have weird results

Almost exactly like you mentioned. Instead of the library trying to guess the types, it will consult this extra table containing the typing information. It could work.

I never got quite as far as actually implementing it but it was a little over a year ago when I wrote a small prototype in Rust that complemented its rusqlite library and it seemed to work fine on a first glance.

1 Like