Using PostgreSQL with Azure And Node.js
TL;DR — Here is a video that covers all of the content in this post in case you’re all like “I’m not reading all of this”.
Azure has released support for PostgresSQL and it’s pretty rad. If you’re all like, “what is Postgres and how do you say that word and why would I use it??”, I’ll let Rob Conery explain that for me.
Rob really likes Postgres. He even sticks documents in there. Enough Rob Conery links, though. That’s not why you came here.
Let’s talk about how to get rolling with Postgres on Azure. Cause like most things on Azure, it’s easy, you just have to know which pieces you need and how to assemble them. So without further ado…
Create A Postgres Database In The Portal
This part is easy. In the Azure Portal, just go to databases and look for PostgreSQL.
Now I know what you’re thinking. You’re thinking, “Dude. Did you just Rickroll me with the Azure Portal?”.
YES. But only because Azure has the option to put a video on your dashboard. It just seemed like the right thing to do.
OK — now that your Postgres database is baking in the Azure oven, let’s install the Postgres admin tool. We’re going to need that to add tables and nifty things like that to our new database.
pgAdmin
pgAdmin is the Postgres admin tool and it’s a free download. Who doesn’t love free? When you open it, you need to connect to the Postgres instance that you created in Azure. You can do that by right-clicking the “Server” node and select “Create”.
Now go to the “Connections” tab and enter in the connection information. Most of it can be found in the Azure Portal. You also need to select “Require” on the SSL mode. This is because SSL mode is toggled on by default in Azure.
Don’t click “save” just yet! Don’t be hasty.
We need to punch a hole in the PostgreSQL server firewall to allow our machine to access it. Being a cloud provider, Azure errs on the side of security. Things are locked down until you come and add some rules to unlock them.
In the Azure Portal, click on the “Connection security” tab and then select “Add My IP”.
Notice that you can also change the SSL mode here as well. But SSL is your friend. It keeps you safe at night and watches out for your kids, so let’s leave it toggled on.
OK! We’re all set! NOW you can hit that “save” button. Your server should show up in the explorer in the left-hand side.
You are now connected and can start writing some queries. For the sake of taking this thing all the way over the finish line, let me show you how to actually use this thing from a Node.js application.
Using PostgreSQL With Node.js
Connecting to a PostgreSQL database is done with an npm package called pg. It’s fairly simple to use. The hardest part is getting the connection string right, but fear not for I am going to “break it down”, as the kids say.
First, you need to…
npm install pg --save
Then…
Basically you need your username@server name — just like in the pgadmin tool. Then it’s your password, the server name again with the fully qualified domain path and lastly the database you want to connect to.
After that, it’s all downhill. Just open a new client and then query that sucker. In this example I’m querying a table called “Items” which I created with my mad SQL skills in the pgAdmin tool and added a couple of rows of boring sample data.
It’s worth noting that for brevity’s sake, I am just straight up connecting with pg. In a real-life scenario, you are going to want to use the pg.Pool object. This object enables the use of “connection pooling”. This is simply the idea of using an existing database connection instead of opening and closing one for each request. This is super efficient, faster and ultimately ensures that your database stays on it’s feet when your application get super popular and everyone is using it and you get acquired for eleventy billion dollars.
You can read more about pg.Pool in the pg docs.
That’s It!
Now you know everything I do about Postgres, Azure and Node.js! The world is your oyster. The only thing left is to figure out how to pronounce “PostgreSQL”. 🤔
