Summary

The web content provides a step-by-step guide on creating a PostgreSQL database using Render and connecting to it with pgAdmin.

Abstract

The article outlines a process for setting up a PostgreSQL database on Render, a cloud service platform, and connecting to it via pgAdmin, a popular database management tool. It details the creation of a database on Render's free tier, which offers limited resources suitable for educational purposes or small-scale applications. The guide includes instructions for registering on Render, configuring pgAdmin to connect to the newly created database, and executing SQL commands to create and populate a sample 'students' table. The tutorial aims to facilitate learning and development by providing a straightforward method for database setup and management.

Opinions

The author suggests that Render's free tier is adequate for teaching and demonstration purposes, emphasizing its usefulness in educational settings.
The article implies that the combination of Render and pgAdmin is an efficient way to quickly set up and access a PostgreSQL database in the cloud.
The author expresses that the process of creating and connecting to a PostgreSQL database is straightforward and accessible, even mentioning the potential for creating API endpoints, indicating the versatility of the setup.
The use of Render's cloud-based services is presented as a modern and convenient alternative to traditional database hosting methods.

How to Create and Connect to a PostgreSQL Database With Render and pgAdmin

Create a PostgreSQL database in minutes

In this post, we will create a PostgreSQL database with Render and connect with pgAdmin. Render is a cloud application hosting for developers, and they offer an array of services, such as static sites, web services, privates service, etc. Also, PostgreSQL, which is free for the first 90 days and will be deleted if not upgraded. We have 256 MB RAM, a shared CPU, and 1GB storage in the free tier. You can read more about the free plan from here and also more here on paid plans.

There are many use cases where we can use the free tier, and one of the prominent ones is for teaching. For example, you want to show someone how to create and connect to a database. Since it’s hosted on the cloud, the student can also connect to the database and make changes.

Setup

For this setup, you need a Render account; visit this link if you don’t have one. If you don’t have pgAdmin installed on your local machine, then download pgAdmin. Awesome, we are good to go for the rest of the steps.

PostgreSQL

First, visit the dashboard and click on the New + drop-down button. From the drop-down list, select the PostgreSQL option.

You should see the following screen. For the Name field, I am using PostgreSQL-pgAdmin, and kept all the fields as it is. Now click the Create Database button.

Excellent! Now you should be able to see the following information. Later we will use these to establish a database connection with pgAdmin.

pgAdmin

For the pgAdmin setup, launch the pgAdmin application and click the Add New Server icon. Select the General tab and use any name for the name field. In this case, I’ll use PostgreSQL-pgAdmin. Now move to the Connection tab and use the following one-to-one relationship to complete the empty fields.

Launch and create a database connection — Image by Author

The left side represents the fields in pgAdmin, and the right side represents the fields in Render.

Name: Name
Hostname/address: External Database URL (If the format of the URL is postgres://aaa:[email protected]/ddd then Hostname would be ccc.oregon-postgres.render.com) 
Port: Port
Maintenance database: Database
Username: Username
Password: Password

If you click the Save button, you should have a connection to the PostgreSQL database. Now you can execute SQL (Structured Query Language). For example, let’s create a students table with three rows. First, expand the left panel, write-click on the Tables option, and select the Query Tool option. It will now open a Query panel on the top right side.

Copy and paste the following code snippets inside the Query panel and click the play icon.

DROP TABLE IF EXISTS students;

CREATE TABLE students (
  id SERIAL PRIMARY KEY,
  first_name VARCHAR(100) NOT NULL,
  last_name VARCHAR(100) NOT NULL,
  city VARCHAR(255) NOT NULL,
  phone VARCHAR(255) NOT NULL,
  gender VARCHAR(255) NOT NULL,
  email VARCHAR(255) NOT NULL,
  address VARCHAR(255) NOT NULL,
  postcode INT NOT NULL,
  date_of_birth DATE NOT NULL
);

INSERT INTO students (first_name, last_name, city, phone, gender, email, address, postcode, date_of_birth) VALUES ( 'Ailn', 'Rathmouth', 'Rathmouth', '05144461974', 'Female', '[email protected]', '97228 Emmalee Harbors Suite 421 South Emmet, TX 54950', 23031, '2001-12-16');
INSERT INTO students (first_name, last_name, city, phone, gender, email, address, postcode, date_of_birth) VALUES ( 'Hounson', 'Port Lolamouth', 'Port Lolamouth', '1-136-366-9496', 'Female', '[email protected]', '62654 Hirthe Lodge Port Zeldafurt, DE 87270', 27108, '1977-01-21');
INSERT INTO students (first_name, last_name, city, phone, gender, email, address, postcode, date_of_birth) VALUES ( 'Tison', 'Lavernastad', 'Lavernastad', '157-283-0337x872', 'Female', '[email protected]', '9107 Blanda Plains Apt. 476 North Burdettechester, NM 91601', 76631, '1984-03-26');

Amazing, now we have created a table with some data. To see the data, right-click on the table name (students) and select the View/Edit Data -> All Rows option.

The All Rows option will execute the following SQL query for us:

SELECT * FROM public.students
ORDER BY id ASC

Finally, you will be able to see the result in the bottom right corner under the Data Output panel.

If you are having issues creating and connecting, then look into the official documentation.

In this post, we learned how to create a PostgreSQL database in minutes and connect pgAdmin to that database from our local machine. As I have mentioned, it’s a great way to teach someone about databases and SQL. The possibilities are endless. Also, you can use the database to create API endpoints, etc. So go ahead and create or import a database using pgAdmin. Happy coding!