Sqlite, schemaless queries and mapping

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: https://stackoverflow.com/a/7426289/285154. 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!

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.