Using views

[dleavitt802]

Any suggestions on how to use views from within sqlite_orm? Can the sync_schema functionality create views and can they be accessed the same way as tables?

[fnc12]

Hi. View is not implemented right now but it exists in TODO list. The way how I see view API is:
There are two options of creating view:

1. static view specified in make_storage function call. It will call CREATE VIEW during sync_schema
2. dynamic view specified with a storage's member function like storage.create_view<ViewClass>(make_table(...));. It will call CREATE TEMP VIEW at once.

Also you need to have a class in C++ to map to your view.

What do you think?

[fnc12]

What view do you need? TEMP or not?

[dleavitt802]

Both capabilities would be great but either will suffice. I have a query with six joins to the same part table from a table with foreign keys to those parts that I could not figure out how to do “join as alias” with with this API to make them distinct, so I have manually made a view to flatten it out but I’m not then sure how to do view access with it yet. So there’s another question, how to get the as alias to work with joins? Dan

[fnc12]

You can use aliases in the lib until views are not implemented

[gbittoun]

Hello,

I’d also like to be able to read a view… Read only is enough for me today. is there already a way for doing so ? (There doesn’t seem to be any but I ask just in case :) ). I tried with a make_table method but obviously, it didn’t work.

Thanks,
PS: this lib is great ! \o/

[fnc12]

Hello. Views feature is not implemented right now unfortunately. It exists in TODO list. You can implement it by yourself or wait until I or anyone else implement it.

[Alvov1]

Hello! I am greatly interested if there is a way to bypass the no-existence of views in the current version of the library. Is it somehow possible to use the raw string queries for achieving so? I've seen the conversation here #302 about them, but did not understand completely

Do I understand correctly that the closest way to get this working right now is to setup a lambda for creating a view with storage.on_open = [](sqlite3 *db){};, then make an arbitrary query/transaction to the library, and then clear things up by resetting the storage.on_open with an empty lambda?

Thank you all very much for such a nice and stylish library! I really enjoy coding with it instead of using the old school sqlite3 c-style API🤗😎

[dleavitt802]

I haven’t worked in that space for several years now but, if I recall, we created views with a python script and then coded against them as if they were actual tables.On Apr 19, 2025, at 2:53 PM, Alexander Lvov ***@***.***> wrote: Hello! I am greatly interested if there is a way to bypass the no-existence of views in the current version of the library. Is it somehow possible to use the raw string queries for achieving so? I've seen the conversation here #302 about them, but did not understand completely And thank you all very much for such a nice and stylish library! I really enjoy coding with it instead of using the old school sqlite3 c-style API🤗😎—Reply to this email directly, view it on GitHub, or unsubscribe.You are receiving this because you authored the thread.Message ID: ***@***.***> Alvov1 left a comment (fnc12/sqlite_orm#306) Hello! I am greatly interested if there is a way to bypass the no-existence of views in the current version of the library. Is it somehow possible to use the raw string queries for achieving so? I've seen the conversation here #302 about them, but did not understand completely And thank you all very much for such a nice and stylish library! I really enjoy coding with it instead of using the old school sqlite3 c-style API🤗😎 —Reply to this email directly, view it on GitHub, or unsubscribe.You are receiving this because you authored the thread.Message ID: ***@***.***>

[trueqbit]

Hi @Alvov1 @gbittoun Depending on the application, I also synchronize the schema outside of sqlite_orm. So I do the same as @dleavitt802 and only define and use the views in code like they were tables.

There's an experimental branch where I've started playing with SQL views. My goal is to be able to create an SQL view just by specifying the C++ structure, and the factory function magically figures out everything else - i.e. column name and type -, which is really great for SQL views.

The branch currently depends on the "boost/pfr" library, but now that the “reflection” feature is becoming a reality with C++26 (I believe), this will soon become much easier and simpler to implement.