How configure Anvil to use ready PostgreSQL db "as is"?

Anvil Q&A
1

Hi there! I’m novice in anvil, have looked through tutorials and forum questions, but…still have some questions.
What I’m trying to do:
I"m trying out anvil app solution, since anvil looks amazing and very promising for our purposes.
Lets suppose there are database(s)(PostgreSQL) and some other services(not anvil) can use it. I want to add an anvil app(s) with UI to manage some of the data in the db.
The requested UI is thought to be pretty complex to implement(and expand later) it with Flask or Django (that’s why anvil:) )
Of course, I would like not to change the schema or duplicate the data in other tables, just use the database “as is”. For some new user-interface features, there is no restrictions how to store it (e.g. user access management, profiles, groups, dashboards etc)

What I’ve tried and what’s not working:
I created a small “hello postgres” app.
Having used the anvil database IDE I’ve got very specific schema for data, which does not look like a solution in our case.
If i use up-link and connect as external database I haven’t got how anvil.server manages this database.
From tutorial i see one can write directly (in each?) server.callable method: create connection, session, commit and close. It’s fine for some specific extra operations, but not for the main database.
Probably I haven’t found how to configure database connection, i see only single db configuration in yaml-file.

My questions are:

  1. is it possible to use my own PostgreSQL database schema instead of generated by anvil database IDE .
    If yes, is any example, how proper to configure and try it out. What the flaws are in such case?
    If not:
  2. is it possible to use my own PostgreSQL database/schema for data storing and management as external db and another one (engined by anvil) for user management access e.g. or other features from anvil. Any examples how to configure app for this?
  3. Are there examples/experience of built anvil apps with manageable database schema? Including unsuccessful cases, sure.

Maybe I’m thinking wrong about what anvil is suitable for. Any advice on how to properly cook our case with the tool would be helpful.

Thank you in advance,
Stepan

2

Hi Stepan,

You can definitely do what you’re talking about, but, as far as I know it means throwing out the Anvil Tables service, except (as you mention) for things like user management and other ‘application operation’ type functions (logging for example).

We do what you’re describing in a few different use-cases. You are basically owning the CRUD and serialization/deserialization operations yourself as opposed to leaning on Anvil Tables to do it for you.

Because of the serialization to/from the Server this usually means dealing with simple dictionaries for that piece. Server-side, for CRUD operations, you’re in ‘native Python’ and can do what you like… for example use SQLAlchemy to simplify database interactions, but you don’t have to if you prefer to live in ‘raw SQL’/psycopg2.

Here is a very rough example of how you can do this with SQLAlchemy running server-side and handling the interaction with your external DB. Everything sent to/from the client is a simple dictionary.

Don’t expect that to actually work, but, it gives you a sense for how we’ve done it in the past. I’m a big fan of SQLAlchemy so that’s why we use that here, but note again if you wanted to just deal with SQL cursors you could (but really, SQLAlchemy is great).

1 Like
3

There’s also a tutorial that walks you through using an external database: Anvil | Building a Web App with an External Database

4

Hi, Dan. Thanks for the example.
It is exactly what I have tried to do: sqlalchemy connection, one session inside each callable server function, serialization. You supported me with idea that is possible.
But could you share more about configuration and structure of your solution:

  • starting app I want to check connection to db( e.g. check availability, make migrations if needed etc) - where can I put this calls? Where is an entry point for the server app
  • how disable anvil.server.tables for external db and keep it for user management e.g.
  • and actually how to configure running app-server with external db and main db?. I would suppose to put credentials for all(both) used db into one config file for each environment

One off topic-question, since you used sqlalchemy

  • using sqlalchemy orm is any chance in anvil to use the model automatically on the client side too? Of course, serialized and simplified to pure types
    thx
5

Yes, thanks, I’ve tried it.
Now I have questions, where I can configure my external and not external db connections?

In the examples connection to db is created directly from each python script, which is fine for testing, but not for proper implementation for deployment.
Maybe i didn’t see some documentation.

6

Some of Anvil services like Users or Data Files use the Anvil database. You can’t change that.

There is nothing to disable. You are free to connect to another database service and do whatever you like with it, but you can’t ask Anvil to use an external database for its internal services.

Most Anvil apps that use external databases, still use the internal Anvil database for the Users table, so they don’t need to redesign the user management. Then they can use the Anvil datatable row id for an user when the external database needs to store the user id.

Every server side call starts a new instance, there is no starting point. Every time you click on a form button and the button does a server call, or every time you call an http endpoint, the server starts a new Python interpreter and imports all the server modules. All of them, even if you are calling one function that doesn’t depend from the other modules.

This means that you need to avoid any time consuming import or migration checks. Small apps don’t have any problems, the Anvil infrastructure is very fast at doing this, but large apps or apps that import slow packages or that load large data sets, will have slow response time.

If you have a dedicated plan or above, then you can keep the server running, so the app will load only after it is edited or after it has been killed for whatever reason. I have a dedicated server, some apps log something at the initial import, and I don’t see many imports in the logs, which makes me think that they stay up for a very long time. But you can’t rely on it.

No.
The datatable row objects can be passed back and forth between client and server, but they are internal objects.

It is possible to make your own classes portable, so you may be able to make something helpful, but there are limitations.

7

Every server side call starts a new instance, there is no starting point. Every time you click on a form button and the button does a server call, or every time you call an http endpoint, the server starts a new Python interpreter and imports all the server modules. All of them, even if you are calling one function that doesn’t depend from the other modules.

Running server with uplink I see a single instance only. Does it mean that using uplink is not a way deployment in production?
Also, where I can put credentials to external dbs? I mean prod environment. I see only pythonic import way from each server side script. Is it correct?

If you have a dedicated plan or above, then you can keep the server running, so the app will load only after it is edited or after it has been killed for whatever reason. I have a dedicated server, some apps log something at the initial import, and I don’t see many imports in the logs, which makes me think that they stay up for a very long time. But you can’t rely on it.

Yes, we have business plan for our team to try out Anvil, how could I read more about the configuring the dedicated server and test it? We planned to have deployment our app in our cloud and keep db(s) as RDS services AWS

Sorry for many questions, maybe some example of working configuration would be helpful :slight_smile:

8

You can run the same uplink script that runs the same functions multiple times, on the same machine or on different machines. Each script connects to the server, registers the callable functions and waits for server calls. The server will randomly pick one of the uplinks every time it calls a function.

You can have 3 machines, each with 3 identical uplink scripts, and the server can call 20 of them concurrently. They will be randomly distributed, some scripts will run the function in different threads.

You can also append a suffix like the machine name to the function name when the functions are registered. This allows you to explicitly pick the machine you want.

I have 3 windows 10 machines with identical configuration. Each has 4 uplink scripts. Three of them do quick jobs that can run in parallel and use the random load balancing, which means that there could be many functions running at the same time on multiple threads. The fourth script preforms slow jobs that can last hours, uses cad, cam and other Windows applications that can only have one instance running per machine. This script appends the machine name to the function names, so the server can pick a specific instance.

They have been running for years and they were always able to reconnect after communication problems or after Anvil outages, with the exception of only one day. Windows and Excel instead refused to run many times and required some reboot and reconfiguration (and several times the updates were backward incompatible and required me to change the code). (I use Windows 10 instead of server or Linux because I need CAD, CAM and other applications that only run on Windows 10 with a user logged in.)

You can use the Secrets service:

password = anvil.secrets.get_secret('dedicated_db_password')

The business plan does have the persistent server option! (I thought it was only available on the dedicated plan.)

It does have the persistent server option, but doesn’t have a dedicated server.

We do have the dedicated server, and all I did was talking with the Anvil team and deciding what type and size we need. Then they did the initial configuration and take care of maintenance and upgrades. I don’t even have (and don’t want) access to it.

1 Like
9

For secure storage of credentials, you are probably looking for:

I’m not really sure what you’re asking with respect running an uplink in production and configuring the dedicated server and testing it.

With respect to what @stefano.menci was mentioning, see here:

The ‘so what’ (simplifying here) is don’t rely on global variables persisting server-side between server calls.

1 Like
10

Thank you, @danbolinson, @stefano.menci, @jshaffstall
You helped me better understand anvil app structure.

1 Like