A database wrapper that allows you to use sqlite3 instead anvil's database without changing any app code

Show and Tell
1

What I’m trying to do:
A database wrapper that allows you to use sqlite3 instead anvil’s database without changing any app code.
Upto 10x faster than existing anvil tables

What I’ve tried and what’s not working:
A wrapper that replaces app_tables functionalities, and a migration script, that can migrate full data in first run of the script.

Github repo: GitHub - voilacs/db_wrapper: A database wrapper for anvil that uses an sqlite3 database instead of the anvil db

Report any errors/bugs that you find and everyone is invited to contribute to add any functions or add any documentation or bug reports.

I wrote this code for my own personal use which is a large generative AI application with a complex structure and several files.

10 Likes
2

I’d suggest putting this is “Show & Tell” category.

edit - I’ve not tried this yet, but I think the idea is pretty cool. Is it feature complete with app_tables?

1 Like
3

That ist really great Work, thx. I will try and report Back.

4

Very interesting concept! I’m curious, what advantages do you get out of using sqlite3 instead of the built in sql database?

5

I think the advantages would be for people who want the convenience of the built in app_table mechanisms coupled with the flexibility of direct local access to their data when needed without the requirement to run their own Anvil Server.

2 Likes
6

The primary advantage of using an sqlite wrapper instead of the anvil app_tables is that the bottleneck to the page loading speeds on anvil is the app_tables call, also if in the future a very big application wants to move to a different cloud than anvil app tables then moving certain parts of the application slowly will be easier and faster.

2 Likes
7

So your wrapper also has a performance boost for reading/writing tables?

8

Feature completion is being worked on, I keep adding features as I require for my project.

9

For me, the big win would be supporting standard SQL declarative data integrity checks, via triggers and foreign key enforcement. (Garbage in, garbage out…)

So far, I’ve been working on layers on top of Anvil for that. It’s doable, but a lot of work, especially when you want something that generalizes to tables and relationships not yet created.

1 Like
10

Where does sqlite live? In the files service?

11

when you run the migration script for the very first time, an sqlite database will be created on your local/cloud machine and that will have all the tables with the required schema in an sqlite file called local.db

12

If your targeting a specific application ( and it’s tables ) you must
edit the migration_script.py , see the TABLES_TO_MIGRATE list. That was needed for my app to work.

13

yes that is correct, if you don’t want to hard code the names of tables by hand just do it with a simple script that fetches the names of the tables from the anvil app and populates the list automatically, I needed to avoid migrating certain tables, that is why I prefer to hard code TABLES_TO_MIGRATE for now.

1 Like