Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

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

Slow SQL Server? These SentryOne resources share tips and tricks for not only troubleshooting SQL Server performance issues, but also preventing them before they hit your production environment.

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 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=postgres

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.

postgres=>


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:

$ curl http://localhost:7071/api/hello\?name\=Azure
Hello, Azure.
$ 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.

Database monitoring tools letting you down? See how SentryOne empowers Enterprises to go faster.

Topics:
jooq ,postgres ,azure ,serverless ,database ,tutorial

Published at DZone with permission of

Opinions expressed by DZone contributors are their own.

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

{{ parent.tldr }}

{{ parent.urlSource.name }}