Access to postgress database tables on local anvil app server

Anvil Q&A
1

Hi,
I am trying to access the postgress database using pgadmin4 when using the anvil app server on a local server. I can connect to the database but cannot see any tables. I only see views. How do I get access to the actual tables and manipulate the data in this database?

2

Are you trying to connect to an external (non Anvil) database or the Anvil datatables?

If the latter try the documentation. It’s not accessed or manipulated like a sql database.

1 Like
3

To me, it looks like

  1. They have their own local installation of Anvil Server on their local PC.
  2. They are trying to access its built-in Postgres database via standard Postgres utilities, not via Anvil code.

In that case, some of this documentation may apply: SQL Access and Indexing. It’s not the whole story, but it may be enough to get them started.

For example, it states that the views they found are not just readable but writable.

2 Likes
4

Hi,
I am trying to use the user database tables. I use the user login functions and users are in this table. However I need to access this table to add values to a column I added (to specify the role of the user). The users interface is not available so I have to do this myself. And also at some point I need to be able to delete users.

5

Welcome to the Forum, @awpmbakker!

If pgadmin4 isn’t doing the job for you, don’t worry, there is more than one way to access your tables. See Uplink: Code outside Anvil for a way to do this from your own custom Python code. This code isn’t part of your App, and can run on any PC that you control. The Uplink approach is especially helpful when you need to automate some (or all) of your steps.

1 Like
6

Thank you for your suggestions. I already use the Uplink in order to connection with my external MySQL database. I use the Anvil Editor to create the front end and use the Uplink module. I am then cloning the app to run on the open source anvil-app-server. I would like to use the users service in the open source service. I can do this but not to the extent that I would like. The user management web interface is not available with the open source anvil-app-server. With the Anvil Editor I added a role column, but I cannot control this in the open source anvil-app-server. I would like to register users and assign them a role. And I have not seen any programmatic option using python to update a user details, or at least to set the role of the user.
So at the moment I think I may have to find another solution to use a user service, i.e. implement the role based server outside the anvil users service.

7

All you have to do before cloning your app locally is add a column to your Users table to hold the user roles. Then you can update that column in each row the same way you can any row in any data table in anvil.

1 Like
8

Your App’s User’s table is just another Anvil table that your App can access. Your Uplink code can work with it in exactly the same way as it does other Anvil tables.

As for structural changes to a database, it’s possible to add a column programmatically. Renaming and removing columns and tables, though, I haven’t seen any way to do that via code. For that, yes, you need a UI.

IIRC other Forum messages, this is usually handled by maintaining a master copy in Anvil, where full IDE access is available. I’m not sure how the two database structures would be kept in sync, though. This is well outside my current experience.

Please feel free to search the Forum to see what others say about syncing local and Anvil-hosted apps. You often get an answer faster with Search.

1 Like