Quick start with free managed PostgreSQL database on ElephantSQL

Lucas Jellema

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.

image

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

image

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

image

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.

image

Press Select Region to move to step 2.

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

image
Press Review to continue.

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

image

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

image

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.

image

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.

image

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.

image

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

image

Click on Next.

Click on PostgreSQL and subsequently click on Next.

image

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

image

You need:

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

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

image

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.

image

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.

image

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

image

Click Next.

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

image

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.

image

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.

image

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:

image

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.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Next Post

Quick Start with initially free Low Code Front End Development using Retool against ElephantSQL PostgreSQL

Front end development can be an art. An almost pixel by pixel design, a careful crafting UX or even CX. And there is a time and place and technology set for that. And there is a range of front end applications that may not require or even justify that same […]
%d bloggers like this: