jOOQ, PostgreSQL, and Serverless: Time for a spin of Java and Relational Data

Published in

Microsoft Azure

4 min readMar 21, 2018

The Azure PostgreSQL Service — as well MySQL Service — recently became GA, meaning many can now safely bring their databases (or create new ones) with all the benefits of a database service that easily allows provisioning, management, monitoring, scaling, back-up, and a lot more.

For developers, what really matters though is the ability to quickly spin a new PostgreSQL database instance, and quickly connect an application to it, without the need to download/install/configure PostgreSQL — although of course you can always use a local Docker image. What is really interesting in this case of course is that the database instance may be shared with other development team members.

If you haven't already installed Azure CLI, here's how:

Windows: install downloading MSI
Mac:

$ brew update && brew install azure-cli

Linux:

And if you still don't have an Azure account, try these two options:

Free Azure ($250 over 30 days plus 25+ free services for 12 months)
Free Azure for Students (similar as above; no credit card verification required)

So let's get started:

Step 1: Authenticate through Azure CLI

$ az login

And follow the instructions.

Step 2: Create a PostgreSQL Database using the CLI

$ az group create --name myresourcegroup --location westus
$ az extension add --name rdbms
$ az postgres server create --resource-group myresourcegroup \
  --name mydemoserver --location westus --admin-user myadmin \
  --admin-password <server_admin_password> --sku-name GP_Gen4_2 \
  --version 9.6

These three commands should take no more than 5 minutes.

Pro-tips: don't try to set the admin-user to values like root/admin. These are reserved, and the command will eventually fail. The password also must comply with these requirements:

Must have digits, letters and special characters
Minimum length is 8 characters

If all goes well, you should get a JSON with all the details of your newly create PostgreSQL database.

The next step is to allow connections to this database by setting a firewall rule.

Step 3: Setup Firewall Rule

Be careful with this configuration. Ideally you should *NOT* open your database on the internet and only allow for applications deployed on Azure services behind the firewall. We will look into this later.

$ az postgres server firewall-rule create \
  --resource-group myresourcegroup --server mydemoserver \
  --name AllowAllIps --start-ip-address 0.0.0.0 \
  --end-ip-address 255.255.255.255

Step 4: Test your connection with PostgreSQL command line

In this other article I share some hints on setting up command-line tools for connecting to multiple databases, including Postgres.

Before we can connect to the database, we must find the address of it.

Run the following command:

$ az postgres server show --resource-group myresourcegroup --name mydemoserver

You should see a JSON like this:

{
   "administratorLogin": "myadmin",
   "earliestRestoreDate": null,
   "fullyQualifiedDomainName": "mydemoserver.postgres.database.azure.com",
   "id": "/subscriptions/.../resourceGroups/myresourcegroup/providers/Microsoft.DBforPostgreSQL/servers/mydemoserver",
   "location": "westus",
   "name": "mydemoserver",
   "resourceGroup": "myresourcegroup",
...
}

Now trigger a connection to the database using the fullyQualifiedDomainName and the administratorLogin you gave during create:

$ psql \
  --host=mydemoserver.postgres.database.azure.com \
  --username=myadmin@mydemoserver.postgres.database.azure.com \
  --dbname=postgresPassword for user myadmin@mydemoserver.postgres.database.azure.com:
psql (10.3, server 9.6.6)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-SHA384, bits: 256, compression: off)
Type "help" for help.postgres=>

Make sure you do the necessary changes to the command before, and note that the database postgres is created by default, thus why we are connecting to it.

Done! You have successfully created a PostgreSQL database on Azure and can now load up some data.

Step 5: Running a sample Java function on Azure using jOOQ to connect to PostgreSQL

Once on the Postgre command-line, make sure you create the following table:

CREATE TABLE greeted (
  id SERIAL,
  name VARCHAR NULL
);

Before proceeding, ensure you have the latest Azure Functions runtime on your local computer. Check the install procedures here.

Example for installing Azure Functions Tools on Mac:

$ brew tap azure/functions
$ brew install azure-functions-core-tools

Now you can clone the project Azure Functions Sample for jOOQ and PostgreSQL:

$ git clone git@github.com:brunoborges/azure-function-jooq-postgre-sample.git

Next, go into the project folder, copy/rename and modify the file local.host.json with your database credentials and information, and run the project locally:

$ cd azure-function-jooq-postgre-sample
$ cp local.host.settings.rename.me local.host.json
$ vi local.settings.json
$ mvn clean package azure-functions:run

And finally, try to hit these two URLs, with the expected output as it follows:

$ curl http://localhost:7071/api/hello\?name\=Azure
$ curl http://localhost:7071/api/greetedPeople

If everything goes well, you should have the following output:

~/work/azure-function-jooq-postgre-sample ⍉
▶ curl http://localhost:7071/api/hello\?name\=Azure
Hello, Azure.~/work/azure-function-jooq-postgre-sample ⍉
▶ curl http://localhost:7071/api/greetedPeople
{"greetedPeople":['Azure']}

If you feel brave enough, deploy this Azure Function to your environment:

$ mvn azure-functions:deploy

I hope you enjoy the code, because the very early version generated quite some Twitter storm in code review :-)