Say for instance you’re working in a small team of newbie developers and you need to store and fetch data to a database. You can create the database locally but that would mean only one person can store and fetch data. Other team members cannot access the data.
This scenario is what led me to a PGAdmin and ElephantSQL connection.
What is ElephantSQL?
Elephant SQL is a PostgreSQL database administrator that installs and sets up database management in the cloud. I used their free Shared Instance Tiny Turtle plan that offers shared high performance server, 20 MB data, and 5 concurrent connections.
I’m not sure how many team members are allowed in the free plan, but me and my team member used it for a previous project.
To use:
- Create an account here.
- Click on Create New Instance.
- Name your instance and tag it.
- Choose a Data center closest to you. For me, that’s Ireland.
- Confirm new instance and create the instance. For the purposes of this article, I’ve named mine Medium.
- Click on browser. This opens a SQL Browser view where you can run SQL queries. Start by creating a Database and database tables.
- Click on the newly created instance. It opens up a Details view. We’re going to use these details to connect to PGAdmin.
What is PGAdmin?
PGAdmin is a management tool for postgreSQL and derivative relational databases.
While PGAdmin is used to perform administrative tasks, query development, and manage databases on local systems, ElephantSQL is used for cloud-based PostgreSQL database hosting.
Therefore, if you’re developing in a team, you can create the database on ElephantSQL, and then each team member can establish a connection to the database for easier collaboration.
How do you connect ElephantSQL and PGAdmin?
To connect the databases, go to the details view of ElephantSQL.
Copy the URL and use it in your code to establish a connection. I used flask and sqlalchemy so my code will look like this:
`import os
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
app = Flask(name)
app.config['SECRET_KEY'] = "key"
app.config['SQLALCHEMY_DATABASE_URI'] = 'postgres://retxeucg:SUceqLZZySPh56oIBKKc3_c_5ImgGwWf@trumpet.db.elephantsql.com/retxeucg'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
db = SQLAlchemy(app)`
If it throws an error, change the URL. Notice that my url starts with ‘postgres’, make it ‘postgresql://…’
After making this change in your code, head over to pgadmin for desktop or MacBook. I’ve had it installed for more than a year so I don’t remember much about the setup. If you haven’t set up pgadmin, do that then come back.
- Launch pgAdmin: Open pgAdmin after installation.
Add a new Server: In the pgAdmin interface, you will see a “Browser” section on the left sidebar. Right-click on “Servers” and select “Create” > “Server…”
General Tab: Enter a name for the server in the “Name” field (e.g., MyElephantSQL). Switch to the “Connection” tab.
Connection Tab:
In the “Host name/address” field, enter the database URL provided by ElephantSQL.
In the “Port” field, enter the port number provided by ElephantSQL (usually 5432).
In the “Maintenance database” field, enter the default database name (usually the same as your username).
In the “Username” field, enter the username provided by ElephantSQL.
In the “Password” field, enter the password associated with your ElephantSQL account.
Save the connection: Click on the “Save” button to save the connection settings.
Connect to the database: In the left sidebar, under “Servers,” you should now see the server you just added. Expand it, and you will see the databases available on your ElephantSQL instance. You can now interact with your PostgreSQL database using pgAdmin.
From my URL,; postgres://retxeucg:SUceqLZZySPh56oIBKKc3_c_5ImgGwWf@trumpet.db.elephantsql.com/retxeucg, my connection details that’d need to add on pgadmin are:
Host name/address: trumpet.db.elephantsql.com
Port: Default PostgreSQL port is usually 5432.
Maintenance database: retxeucg
Username: retxeucg
Password: SUceqLZZySPh56oIBKKc3_c_5ImgGwWf
This is because the URL takes the format
postgres://username:password@hostname/databasename
A successful connection should look like this:
The elephant sign depicts a successful connection. The cancel sign depicts an unsuccessful connection.
This should give you a head start in creating and using a postgreSQL database.
This post first appeared on Medium.
_
_I'm an ALX Full Stack Engineering student and a QA Engineer. Find me on LinkedIn, Twitter, Github and Medium.
Top comments (0)