How to create a PostgreSQL Database on Azure
Tags: azure, postgres, database, tutorial
Published: 2023-04-18 10:57:14

## Introduction
In this blog we will learn how to create your own PostgreSQL database on the Azure. We will then generate a SQL script that creates a table on our Azure PostgreSQL Database with dummy data inserted into it.

---

## Pre-requisite

An [Azure Account](https://azure.microsoft.com/en-in/free/search/?ef_id=_k_EAIaIQobChMIwcbo-f6y_gIVFXl9Ch0w5w56EAAYASAAEgKQ3_D_BwE_k_&OCID=AIDcmmf1elj9v5_SEM__k_EAIaIQobChMIwcbo-f6y_gIVFXl9Ch0w5w56EAAYASAAEgKQ3_D_BwE_k_&gclid=EAIaIQobChMIwcbo-f6y_gIVFXl9Ch0w5w56EAAYASAAEgKQ3_D_BwE) 

> _**Pro Tip:** If you are a student in college, you can easily get Azure Student Subscription for free without a credit card. With this subscription you get $100 free credit for 12 months as well. Signup here:- https://azure.microsoft.com/en-in/free/students/_

---

## Getting started
To follow along this tutorial I hope you have some basic knowledge of what a database is? what is PostgreSQL?, if not do check these resources:

- [What is database?](https://www.oracle.com/in/database/what-is-database/)
- [What is PostgreSQL?](https://www.postgresql.org/about/)

**Create the database on Azure**
1. Go to [Azure Portal](https://portal.azure.com) and sign-in using your account.
2. Once you are on the portal search for a big plus icon that says **_Create a resource_**
![Create a resource](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/v0t4mahjci6n3vcdy8uf.png)
3. Search for "postgresql" in the  search box and select **_Create_** on  the **Azure Database for PostgreSQL Flexible Server** as seen below.
![Azure Database for PostgreSQL Flexible Server](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/e0dldsxwcp9pohjw4tdl.png)
4. A page will open asking for Basic details for this flexible server.
First one being the **Project details**.
 - Select your **_Subscription_**
 - Select a **_Resource group_** or create a new one like below.
![Project details](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/o5vdt53dpra6p4groc6c.png)
5. Next, we enter the server details. Select the options as seen below. Server name does not have to be the same.
![Server details](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/yvo8s9png3o6744yz6z8.png)
6. Skip the High Availablity section and move onto Authentication.
Select **_PostgreSQL authentication only_** and enter the Admin username, password you like.
![Authentication](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/b5ix6yblr6mhovu4kgga.png)
Click on **_Next: Networking >_** and move to the next configuration.
7. In the Networking tab we will allow our computer/laptop to be able to connect to the Azure database. 
For this we will click on the '**_Add current client IP address (YOUR IP ADDRESS)_** in **Firewall Rules** section.
![Firewall Rules](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/2eop9ryrp61dh41kvgdy.png)
8. Lastly, we will click on **_Review + Create_** button and review out details and then click **_Create_** button as a final step.
![Review+Create](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ss4gozklg2ujg1xx23lp.png)
9. You should see your deployment in progress. It will take some time to create the resource and when it does you should see a screen like below with your connection details.
![Deployment page](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/8gvvcp1f0s2y5fjh7jdz.png)

**Connecting to the database**
To connect to your newly created database we will head over to our created resource.
![Resource page](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ko715wj7xj2uz6ebz1bg.png)

To connect to the database we have two options:
 - **pgAdmin**
 - **psql cli**

With the given server name and credentials you can connect to it using either of the two options but I will be using **psql** in powershell.

Open a powershell/commmand prompt and type the below command by replacing the connection details to your server details:
`psql --host=<servername> --port=<port> --username=<user> --dbname=<dbname>`

**SUCCESS!** You should see the connection was sucessfull and the psql prompt pointing to postgres database.
![Connecting to database](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ca55b3fgorcsbgwz17oi.png)

**Creating a table with dummy data**
We will create a SQL script that will create a table named **Car** and Insert into this table 1000 records.

We can generate this SQL script by simply heading over to [Mockaroo](https://www.mockaroo.com/)

**Mockaroo** is the best website to generate mock data for your projects. It's highly customizable in terms of the types of fields you want in your data, precision of the data, leaving a certain percentage of data blank and so on. You can just download the mock data in a number of fromats available.

- Add in the fields as seen in the below image. 
- Select the **Format** as SQL and give the table a name. 
- Check the option which says **_inlcude CREATE TABLE_** and click **Download Data**.
![Mockaroo dummy data](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/jsy606fs6t4er7e7arbx.png)

Open the downloaded **_car.sql_** script in an editor.
You will mainly see two SQL commands.
 1. **CREATE** - This will create a table in our database with the given columns.
 2. **INSERT INTO** - This will insert data into our table 'Car'.

I will modify this script a bit and add some constraints such as all columns to be **NOT NULL** and id column should be a **PRIMARY KEY**. The resultant script should look like this:
![Car.sql](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/fnpp486nf0okwehnm3z5.png)

**Running the script on our Azure PostgreSQL database**
Make sure you are connected to the database as we saw in the section above.
In the PLSQL CLI we will type the following command to run the script.
`\i 'D:/Tutorial/car.sql'`

`\i` - It means we want to execute the commands from the file path mentioned after this.

After hitting the above command with the correct path to car.sql script you should see multiple `INSERT 0 1` in the terminal which means that the records are being inserted into the table. It should take some time as there 1000 records.
![Insert Into](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/rhvn8x53afm1c13uw8ot.png)

**Final Result**
You should see the data has been inserted into the database by executing the following SQL:
`SELECT * FROM car;`

![SELECT](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/0qgcnm06hfnraafqkmly.png)

> **IMPORTANT:** After you are done with trying out your database make sure to clean up the resource on Azure so that you don't incur charges on your credit card.

---

## Conclusion
We have now created our own PostgreSQL Database hosted on Azure without the need to configuring a server to host the same. We also created a table and inserted dummy data into it using Mockaroo. 

I hope you found this blog helpful and feel free to reach out in the comments section for any doubts or suggestions. Thank you for reading.

Happy Coding!

Cover Image Credits: <a href="https://www.freepik.com/free-vector/server-room-cloud-storage-icon-datacenter-database-concept-data-exchange-process_3628676.htm#query=database&position=43&from_view=search&track=robertav1_2_sidr">Image by fullvector</a> on Freepik

## References
- https://learn.microsoft.com/en-us/azure/postgresql/single-server/overview
- https://www.oracle.com/in/database/what-is-database/
- https://www.postgresql.org/about/
- https://learn.microsoft.com/en-us/azure/postgresql/flexible-server/quickstart-create-server-portal