External databases: uplink vs psycopg2 (or other standard libraries)

Anvil Q&A
1

I have read several times in the forum, here is the latest, that people using external databases prefer to use the uplink rather than connecting using standard Python libraries like psycopg2.

Is a generic tool like uplink really faster than something like psycopg2, which has been created, optimized, refined and perfected over the years to do this very job, only this, and do it really well?

Or is it better to use the uplink because, even if it’s slower, it gives a chance to reduce some traffic by executing some of the logic in the same machine with the database server?

Or is my intuition is actually wrong and the uplink is actually faster?

EDIT
I just realized that I incorrectly referred to psycopg2 as a “standard library”. To be precise, it is not part of the standard library, but rather it is a library commonly used in Python programming.

2

Stefano,

I am not sure whether I communicated correctly:

I have a three-tier set-up here. My Anvil App front-end communicates with my server code through uplink. Thus all my server code executes on my local machine that is in turn interacting with the Azure DB as backend.

I only maintain minimal login/user data on Anvil Server.

I cannot/don’t want to develop in the online editor due to lack of debugging and other tools (mainly SQL-console).

I use PyCharm instead. Already client development can be tedious in the online editor.

There is, furthermore, the not insignificant problem that if you want to access Azure from another cloud location (ie in server code in Anvil) you run into firewall/authorization problems on the Azure side.

My (maybe stupid) plan is to multiply my servers that interact with Azure.

In PyCharm, I use the pyodbc driver to interact (it is Microsofts version). That seems to work pretty well.

I appreciate any suggestion that improves the scalability of my app!

Franz

1 Like
3

Are the authorization problems easier to solve from a local machine than from a cloud server?

Premise: I have never used an external database and I don’t have scalability problems, because my user base is limited to my company’s users and a few external users.

Said that, well… don’t use uplink! :slight_smile:

I do use uplink because my apps interact with Notes, CAD and CAM scripts, Excel VBA macros and other local resources that only work on my Windows machines. My uplink servers are designed to be easily duplicated, so I can quickly add more if I need, but I would need to do it manually. Also, by utilizing uplink servers, any network outages in my local network are compounded with those experienced by Anvil, ultimately leading to increased overall fragility.

I use PyCharm too during the development and testing, but the production app runs on the server.

Here I describe how I create a module with classes that can be used on both client and server side, and can be tested and developed locally with PyCharm.

When I want to debug some server callables with PyCharm, I include them in an uplink and run that in PyCharm, so the calls to those functions will run locally rather than running on the Anvil server. Then I use the app normally and when those callables are called, I can debug them. This is not the smoothest setup, because sometimes it may not be clear what executes where, but when you get it working, it allows to run the production app on the Anvil server and keeps the burden of managing local infrastructure limited to the development phase.

I never used the open source Anvil server locally during development, and I never used an external database (hence this post). But I would rather use a local Anvil server during development (or Amoni) than running and managing local uplink servers in production.

4

Thanks for your time, your effort and suggestions.

It all depends of the use cases. Since I know the context of my app quite well, I might assign different weights to different aspects of an app. Furthermore, I cannot solve all the challenges at once. Step-by-step…

Regarding your points:

I am not so sure whether you win so much, when you discard the uplink. It depends on how Anvil is setup, does it not? How does Anvil communicate with the postgres-db? Is it running on the Anvil-Server or on a separate (virtual) machine?

I am not familiar with postgres to the degree necessary to use it in the project at hand. To go into the details of any complex DB is demanding and time consuming. So I stick with what I am familiar with and can scale and where I can demonstrate a solution.

Additionally, I think I could not use a direct link from Anvil to Azure, because Azure-SQL-DBs have that firewall-feature were you specify the tcp-addresses that can call in and execute SQL-commands.

Since the tcps of Anvil are not fixed (AWS, is it not?) any SQL-calls will be blocked over time, even if you find out where your server is running.

Of course, I could turn-off the firewall, but this is very insecure and I know that any user data, should I become productive, will be client-confidential. If I have to trade security and speed, I choose security. Speed is a question of ressources/money, is it not?

Since I can host Azure-Data in Switzerland, local laws apply. This is not the case with Anvils DB. So I cannot use that DB anyway. I would not host the database myself either for the same reason. Much too complex, much to risky. I know what the say about MS, but they got their act together here.
Thus, in a production context, I would see a number N of Azure VMs that fire-off the SQLs necessary for the users.

But I am still very far from that. My app is using multiple layers of complex forms. They work. The data tables are stable and function.But supposedly simple printing/PDF creation is a construction site. Since my app needs to be multi-lingual (hey, this is Switzerland…) not just the data-fields need be loaded but also the field labels themselves… 30 second time-out, loading the data with parameters. This is simply not feasible.

Document-upload and structured storage is what I will do next. I have the basics working (pdf-encryptions, cloud-storage, secrets (again not Anvil).

Have you come across a decent tree-manager? I find the current JS documentation is not sufficient for me. I am too stupid for the examples provided. Any hint would be appreciated. It seems as if I had to delve into JS now.

5

I just read this brand new post that talks about the same problem: Static / Dynamic IP?

I have a dedicated account. In my case both the web and database server are running on the same AWS instance. And I have access to SQL, very useful for joint queries, but still limited in many aspects.

I imagine the shared accounts utilize separate machines for the web and database servers. This approach is likely preferred due to factors such as load balancing, data redundancy, and other requirements that make it a more optimal choice.

I value my productivity more than a shiny UI. For simple cases I use the DataGridJson (which is not a tree), but I also have tons of nested repeating panels (which are a tree), some loaded lazily, some with canvas that show graphic representations of the data, etc.

I’m sure there are good tree managers out there and it wouldn’t take much integrating them with Anvil. It would require delving into JS, but only once, while creating the custom component. I had a look at the Tabulator (again, not really a tree), it looks great, but I have never used because data grids and repeating panels are good enough for my apps and I don’t have the time to explore too much.

6

There is a fixed set of addresses that those calls can come from that you can get from Anvil support, in the event that you ever want to make the Azure calls from an Anvil server.

1 Like
7

This is good to know! Thank you both!