jOOQ, PostgreSQL, and Serverless: Java and Relational Data

DZone 's Guide to

jOOQ, PostgreSQL, and Serverless: Java and Relational Data

Take your serverless functions on a walk on the Java side. Today, we're going to explore how to use jOOQ, Postgres, and relational data together, with some help from the cloud.

· Database Zone ·
Free Resource

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:

$ brew update && brew install azure-cli

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 \

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 \

Password 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.


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:


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:

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

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.

jooq ,postgres ,azure ,serverless ,database ,tutorial

Published at DZone with permission of Bruno Borges . See the original article here.

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}