jOOQ, PostgreSQL, and Serverless: Time for a spin of Java and Relational Data
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
$ 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 \
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 \
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:
Now trigger a connection to the database using the fullyQualifiedDomainName and the administratorLogin you gave during create:
$ psql \
Password for user firstname.lastname@example.org:
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.
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 (
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 email@example.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
▶ 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 :-)