Quick start with free managed PostgreSQL database on ElephantSQL

This article shows you can quickly get started with a free, managed PostgreSQL database with a table full of country data (using ElephantSQL). ElephantSQL is a hosted, managed PostgreSQL service from the team that also provides CloudKarafka and several other nice services. It is a breeze to get going. The free tier offers 20 MB of storage – which is plenty for my R&D activities.

Create an account on ElephantSQL at https://customer.elephantsql.com/signup. Connect, either with your GitHub account (as I will do), using Google account or by creating a new account associated with your email address.

Provide the name of a team – this can be anything.

Click the green *Create New Instance button in the upper-right-hand corner of the screen.

Type the name for the new instance – in my case sql-arena – and select the plan. In my case, I selected the Tiny Turtle Tier, the free plan which is ample for my R&D use cases.

Press Select Region to move to step 2.

Select a cloud region near you – AWS, GCP or Azure is available.

Press Review to continue.

Check if the choices were correct and press Create Instance to do exactly that.

A success message should be shown when the instance is created and is ready for further inspection.

After setting up the database, click the instance name. This takes you to a page with details about the instance, including details for making a connection to it.

Try to click the Browser link in the left-hand menu. The text input at the top of the page is where you can input and run SQL commands.

Connecting to PostgreSQL instance from DBeaver

There are many client tools you can use for connecting to PostgreSQL databases. I would not presume to tell you which one to use. However, one I quite like is DBeaver. So I will show you how I connect to the new PotgreSQL instance on ElephantSQL from DBeaver – and perhaps you like it too.

Download DBeaver from https://dbeaver.io/ and install the tool in the way that is suitable for your platform.

Run DBeaver.

Click on File, New. The select DBeaver | Database Connection.

Click on Next.

Click on PostgreSQL and subsequently click on Next.

At this point, open the Details page for the new PostgreSQL instance in ElephantSQL to retrieve the connection details.

You need:

• server (aka host)
• user & default database
• password

Continue in DBeaver: enter the requested details for the connection.

Then press Finish.

Expand the new node created in the navigator tree for the new database connection. Check that we can read databases and schemas from the connection.

Create Countries Table in PostgreSQL Database

Using a csv file on GitHub brimming with country data, I will now create the COUNTRIES table. Download the contents for file countries.csv to a local file.

Right click on the Tables node in the Public Schema. Select Import Data from the menu.

Accept the default option in the wizard to import data from CSV source.

Click Next.

Select the csv file downloaded from GitHub with the country data.

Press Next.

Inspect the details (if you feel like it) in the next three steps. They indicate how the data will be imported and what the name and structure is of the table that will be created.

Finally, press Proceed to have the table created and the data loaded.

When the import job is complete, the data view for the new table is displayed and you can inspect the data.

The data is now imported into the PostgreSQL instance in ElephantSQL. This can easily be verified in the browser section in the browser front end of ElephantSQL:

The query used here: select region, count(id) country_count from countries group by region 

I hope you have gotten a good idea about how to get started really quickly and easily with PostgreSQL in a cloud environment somewhere near you. For free even.