[Feature request] Add section on how to connect to and query the database once deployed to Azure

[danielhardej]

Hi Azure team 👋

This issue/request pertains to the tutorial in the MS Learn documentation: Deploy a Python (Django or Flask) web app with PostgreSQL in Azure

First off, I just want to say that the resources in that documentation and in the example repo are awesome. I've found them super helpful in getting hands-on learning for Azure, deploying Python apps, and creating servers and databases - both for day to day work and personal projects.

The YouTube tutorials (here and here) by @pamelafox were particularly helpful too.

One important thing that's missing though - something that would be immensely helpful to follow on from this particular exercise - was guidance on how to connect to and query the databases once they're deployed to Azure (I know that it's mentioned briefly in one of Pamela's videos, but this showed how to use SQLTools to browse the database locally in Codespaces, rather than how to access the DB deployed to Azure from the tutorial.)

In short, it's super easy to set up the Azure resources and deploy the app, but feels impossible to connect to and query the data.

I tried to figure this out myself, but with no success.

I've tried to connect using multiple methods from the Connect page in the Azure portal, including Azure Data Studio, DBeaver, pgAdmin, pg_dump, and psql and all end up in a similar situation, with an inability to connect or an error message about the server name not being recognised.

I've also gone through several guides and pieces of documentation (including this, this, this, and this), but keep hitting the same dead ends. Usually ending somewhere like this:

It seems like having private network access and needing to access via a private endpoint adds to the complexity:

Maybe I'm being a massive idiot, completely missing something obvious, but I've exhausted all of the guides/docs/tutorials and can find and I'm 99.9% I completed the steps correctly, with absolute certainty that I was entering details (i.e. server names, IP addresses, credentials etc.) correctly and trying on both flexible and single servers too.

Perhaps this would warrant another new tutorial on the MS Learn docs, rather than an extra section on the existing tutorial.

But this would be very helpful as most of the value of having a database comes from being able to query it to glean insights from the data.

PS - FWIW, and if it helps at all, I'd be more than happy to help prepare the materials if I had an understanding on how to do it 😅

PPS - I work at GitHub, so you can reach out directly via the GH or MS emails or Slack if you want to chat!

[pamelafox]

@danielhardej To clarify, the issue isn't having the deployed app connect to the Database, the issue is with connecting locally? I believe @charris-msft has connected to a DB inside a vnet from his local machine. Chris, can you share your incantation?

[charris-msft]

Hi @danielhardej - what a nicely written issue! And thanks for the kind words on the content!

Give this a shot and let me know how it works for you and what you think of the approach.

How to use psql to connect to your PostgreSQL server from App Service running in a VNET

This is intended to be a temporary psql connection because psql will be removed from your App Service container during deployment, maintenance, or some other unexpected time.

You have to do this from some kind of compute running in your VNET (App Service, VM), because you can't reach the PostgreSQL server from outside the vnet

If you deployed your Postgres server using: Tutorial: Deploy a Python Django or Flask web app with PostgreSQL - Azure App Service | Microsoft Learn, there will be an App Service web app in your VNET already, and that App Service web app will have an environment variable called $AZURE_POSTGRESQL_CONNECTIONSTRING that contains everything psql needs to connect to your database.

1. In the Azure Portal, connect to the App Service with SSH a. Open the properties for your App Service resource. b. Under Development Tools > SSH, click Go.

2. In the SSH Terminal a. Install psql: apt install -y postgresql-client b. Connect to your database with psql (double-quotes are important): psql "$AZURE_POSTGRESQL_CONNECTIONSTRING"

Voilà! You're in!

[danielhardej]

Hi @pamelafox! Thanks for the reply.

the issue isn't having the deployed app connect to the Database, the issue is with connecting locally

Yes, that's right.

And my initial comment ☝️ was probably more rambling than necessary 😅, but put simply what I thought would be helpful was just to have a section in the learning materials on how to connect to the database that gets created in the Deploy a Python (Django or Flask) web app with PostgreSQL in Azure tutorial.

If @charris-msft has a how-to on doing that already, then that would be awesome!

[danielhardej]

LOL, whoops, I posted that reply before refreshing the page and seeing that you had already responded @charris-msft 😅

[charris-msft]

@danielhardej - You made me wonder if I hadn't pressed the button to save my comment. it certainly wouldn't be the first time!

[danielhardej]

But thanks @charris-msft! I'm going to try that now...

[danielhardej]

That works! Thanks again @charris-msft 🙏

FWIW, and in case there's anyone else out there reading this and wondering about it too, what I did after apt install -y postgresql-client and psql "$AZURE_POSTGRESQL_CONNECTIONSTRING" was:

• Connect to PostgreSQL using the psql tool: psql -U postgres -W

• Use the \dt command from the PostgreSQL command prompt to show tables in the database, which outputs:

               List of relations
 Schema |      Name       | Type  |   Owner    
--------+-----------------+-------+------------
 public | alembic_version | table | enudupjokt
 public | restaurant      | table | enudupjokt
 public | review          | table | enudupjokt
(3 rows)

• Then, after filling out a few test examples in the web app:

SELECT * FROM restaurant;

Which gives:

 id |      name      | street_address  |      description      
----+----------------+-----------------+-----------------------
  1 | KFC            | Guildford Road  | Finger lickin chicken
  2 | Short Order    | Hay Street      | Burgs
  3 | Guzman y Gomez | Beaufort Street | Hermanos burritos
(3 rows)

(Just some dummy data I threw in via the web app!)

[danielhardej]

Hi again @charris-msft

Really getting into the weeds with this! And have a couple more questions:

1. Is it possible to connect to and query the database using Azure Data Studio (or similar apps, such as DBeaver and pgAdmin)? And, if so, how?
2. And how can we make use of the pg_dump options (under Settings > Connect for the Postgres server resource)?

If I understood correctly, that first pg_dump command would enable you to export all of the data in the tables in the database into a plain text file. That would be very useful.

[charris-msft]

Hey @danielhardej - If you want to query using one of those apps, you will most likely need to create a VM in the VNET then use something like Bastion to connect to it.

It's theoretically possible to deploy a container to the VNET running a proxy that would proxy calls from your local machine on port 443 to port 5432 in the VNET. I have never tried this though.

One other approach that will let you explore the data a bit more graphically, and still works over SSH to your App Service is visidata. The following script will install visidata, convert your current connection string into a new connection URI that visidata can use and then open visidata.

pip install visidata

# Input string
input_string=$AZURE_POSTGRESQL_CONNECTIONSTRING

# Extract individual components
dbname=$(echo $input_string | grep -o -P '(?<=dbname=).*?(?= )')
host=$(echo $input_string | grep -o -P '(?<=host=).*?(?= )')
port=$(echo $input_string | grep -o -P '(?<=port=).*?(?= )')
user=$(echo $input_string | grep -o -P '(?<=user=).*?(?= )')
password=$(echo $input_string | grep -o -P '(?<=password=).*?(?= |$)')

# Construct the new string format
vd_connection_string="postgres://${user}:${password}@${host}:${port}/${dbname}"

# Connect
visidata $vd_connection_string

I just figured this out yesterday and would love to hear your thoughts about the approach.

It will let you view data, but doesn't seem to allow modification yet.

[danielhardej]

Thanks for that @charris-msft! Visidata is a very cool way to view the databases.

Also wondering if you've got any tips on the pg_dump command? For example:

pg_dump  -h msdocs-python-postgres-djh-server.postgres.database.azure.com -U czcvierzav -p 5432 > _dump.sql

Which is a direct copy and paste from the Settings > Connect page for the Postgres server resource in Azure, would send the output of the command to the _dump.sql file in plain text, and I understand that by default the file would be in the current directory.

That commend in the Azure SSH runs just fine and so, if my understanding is correct, where would an admin locate that file in Azure?

[charris-msft]

Hey @danielhardej - You won't be able to run pg_dump directly from your computer. It has the same issues accessing the postgres server in the vnet as the other tools. But you can run it from the App Service container. Just follow the same instructions as psql:

In the Azure Portal, connect to the App Service with SSH

1. Open the properties for your App Service resource.
2. Under Development Tools > SSH, click Go.

In the SSH Terminal

1. Install psql: apt install -y postgresql-client
2. Dump your database with pg_dump (double-quotes are important): pg_dump "$AZURE_POSTGRESQL_CONNECTIONSTRING" > _dump.sql

Once you have a dump of the database on the App Service machine, you can use scp to copy the dump from the App Service machine to your local machine.

[danielhardej]

Awesome! Thanks again @charris-msft - all makes sense now.

Really appriciate your help with all this.