Example Code to bulk import into Postgres via anvil-app-server

Anvil Q&A
1

Hi Team,

Is there a full example of how to bulk import data into Postgres via anvil-app-server? I apologize if I’ve missed it.

I would like to take .csv exports of my current Anvil.works data tables, and import them into my anvil-app-server instance running on a VPS. I have been able to migrate the data table structures, but not the data. Is there a more elegant/faster method to upload the data (other than via the uplink)?

I have also been able to connect DBeaver for direct access to Postgres but I have never worked with tables that are in the format/style Anvil is using. I’m assuming this is all a flavor of storing JSON? Are there any tutorials info out there on how to interact with it via SQL queries?

Thank you,

Jeff

2

You can try the first part of this docs, I am about to do so as well
https://anvil.works/docs/data-tables/csv-and-excel

I am not too sure if it works when your tables have files

1 Like
3

Thanks Tony, this was helpful.

Any other examples or insight on direct SQL queries into the database tables generated by Anvil?

Jeff

4

There is, but I guess it’s quite pricey. May I ask the reason behind that? I used to think the same, but later find that the current way to query the tables appears to be good enough though there is a bit of learning curve.

1 Like
5

Potentially faster and able to do joins/more advanced queries in one call to the database. Running the anvil-app-server essentially puts you on the dedicated hosting plan and gives you that access. The other reasons are because of the opacity of the account file storage/DB limits at the moment. If we top out our current plan, the dedicated hosting option is too expensive for us.

6

For simple apps I use Anvil’s app_tables, I use SQL only with complex apps. But even in the apps that use SQL, I only use it when required. In most simple functions of those complex apps I still use app_tables.

One thing to be careful with is that there is one transaction for all app_tables operations and one different transaction for all the SQL operations. So I try to get every function to be either SQL or app_tables (even if it’s impossible to be only SQL because I leave the management of the user authentication to Anvil).

In some cases I ended up with some functions called by both simple and complex functions, so there was a mix of app_tables and SQL that I didn’t like. In those cases I switch to SQL also for simple stuff.

EDIT
One more thing to mention: using SQL in Anvil is faster and allows to use joins, but it doesn’t give you full control on the database. You can’t use triggers, constraints or other things that you would use when designing your DB.

Also, you can’t use media columns with SQL.

7

That sounds like a very solid approach, thank you for sharing @stefano.menci.

In this case, the bulk of the app (an online ordering platform uses app_tables). I’ve landed on using SQL with a MySQL server to access read only historical order information (~90k orders). We already had the MySQL server so I’m taking advantage of that.