Self-hosted Anvil, managing data tables

polat · January 17, 2022, 8:17am

As an individual plan user, I decided to use Anvil platform as my development environment and use my own VPS for production. This is mainly for performance and latency issues I had faced so far.
For my planned usage, the biggest hurdle is “data tables” -up to now at least.
I find data tables quite helpful, especially for small amount of data and lookup tables. You can quickly create/modify them and I think Anvil designer tools are integrated & optimized to use its own data tables rather than external databases.

The problem is, when switching to my own VPS (pulling the app and data schema), I can’t find an easy way to transfer the data or make changes on it. Exporting csv and importing through Pandas is very problematic from my side: datetime columns, boolean columns, “NaN” errors etc.

My question is: what is the best way to manage the data tables on self-hosted Anvil? If there is no easy way, do you suggest me to give up with data tables and use my own MySQL or PostgreSQL maybe? In that case, does using PostgreSQL have any advantage over MySQL or even SQLite (for small and portable data) regarding Anvil design environment?

owen.campbell · January 17, 2022, 9:16am

The runtime docs describe exactly how to access your data tables.

polat · January 17, 2022, 9:38am

Thank you but accessing the raw database does not help at all.
The native tools (like pgadmin) is almost unusable for data tables since Anvil keeps its data in a very custom way. It is like trying to edit an excel file through notepad.

owen.campbell · January 17, 2022, 9:52am

The data tables are available to you in the ‘data-tables’ schema and can be manipulated by whatever db tool you prefer.

Failing that, you can use python scripts via uplink, if you prefer.

polat · January 17, 2022, 4:26pm

Thanks for trying to help but I can’t add/edit records through pgAdmin.
“data-tables” schema shows “Anvil data tables” as views named like “table_X” but there is no editing option on those views. As far as I can see, it is only possible through SQL queries (using the views) which doesn’t help. Please let me know if I’m missing something.

Dear Anvil staff, please consider providing a way to manage our on-site data-tables. If you don’t want to give everything in the open-source app (which is understandable), you can consider keeping it for paid plans only. This tool can run on your online environment and reach our postgres instance via a connection. Also there can be an on-demand bidirectional sync of data between the online app tables and the local ones. Bubble has a similar feature to transfer data between dev and prod tables infact.

stefano.menci · January 17, 2022, 4:40pm

Why do you say it doesn’t help?
You can use SQL to read and write the Anvil table/views… what else do you need?

I don’t have a self-hosted server, but I have SQL access in the dedicated plan, so I’m guessing my options are more limited than yours. And yet I remember in the past I was able to create new tables that are not part of the Anvil infrastructure and transfer data between Anvil tables and my own tables.

I played with it, it worked, then I decided that I was playing with the fire and I left it alone. I am not a Postgres expert and I don’t want to risk to bother the Anvil infrastructure.

You are in a self-hosted server, so you should have more control about the size of your tables and the queries you can execute and the namespaces and tables and all the goodies.

ianbuywise · January 17, 2022, 7:02pm

If I am understanding what you would like, you want a pgadmin, or PHPpgAdmin style interface to click and edit or insert records?

This should be easy to build an editing dashboard yourself, using anvil datagrids, pre-filled text fields in the columns that update the row when you hit enter, etc.

Just make sure nobody but you can access the tool you build, and you should be fine.

Example: (comes right from anvil datatables, and you can edit the fields, etc)

You could even put a box at the bottom that lets you execute a raw query, but again, you should make sure only you ever get to use the tool. (Because: https://bobby-tables.com/ )

polat · January 18, 2022, 12:32am

@ianbuywise, thank you very much, this will solve my problem partially, I hope.
Do you have a suggestion for data transfer as well?
I’m getting errors using Pandas dataframe (read_csv) even for simple tables. Am I making a mistake or do I need to deal with datatypes of each column or “NaN” values? I’m not experienced in Python tbh… I can share my code if needed.

@stefano.menci, it doesn’t help, because if I need to write SQL queries for simplest operations, there is no point to use data tables. As far as I understand, using SQL in Anvil only makes sense for bulk operations.