Allow using SQLite extensions

[CaptainDario]

Is your feature request related to a problem? Please describe. I have been using your package in production already for some months and it has been awesome and just works great, thank you so much! Currently, I am thinking of a way for a user to synchronize his SQLite database between devices. While researching, I stumbled upon this package cr-sqlite. When searching the docs I found the supported extensions but I could not find a way to load runtime extensions. Therefore, I am interested in the ability to load runtime SQLite extensions using this package. If this is already possible, I am sorry for the inconvenience.

Describe the solution you'd like A way to load and execute SQLite runtime extensions. database.extension("cr-sqlite").run("SELECT crsql_as_crr('table_name')")

[galacticgibbon]

Not sure if it's applicable to you, but check this discussion about syncing:

https://github.com/simolus3/drift/discussions/2880

Personally, I am interested in powersync option with supabase backend and will be implementing it next year in my own app. https://www.powersync.com

They have flutter packages and even drift interop package

[CaptainDario]

Thank you for the suggestion, I will look into it!

However, I am still interested in loading runtime extensions. But maybe this is better to ask in the sqlite package...

[simolus3]

Drift sits at a fairly high level in the sqlite3 stack, so integrating runtime extensions requires a fair bit of work:

First, you'll have to make sure that the actual extension code is part of your Flutter app. This highly depends on the platforms you intend to support (an example from @rodydavis is here, I didn't have the chance to take an in-depth look yet though https://github.com/simolus3/sqlite3.dart/pull/256). For security reasons, the sqlite3 package does not support the autoextension feature, but you can use sqlite3.ensureExtensionLoaded to load the extension before opening the database. For cr-sqlite, that likely involves using dart:ffi to obtain a DynamicLibrary of the sqlite3 features followed by SqliteExtension.inLibrary(yourCrSqliteLibrary, 'sqlite3_crsqlite_init').

Once you have the extension running, drift is able to use it in custom statements. Depending on what the extension offers, you can use FunctionCallExpressions to call SQL functions or TableValuedFunctions to select from functions returning result sets.

[CaptainDario]

@simolus3 thank you so much!