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
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:
Create the database on Azure
- Go to Azure Portal and sign-in using your account.
- Once you are on the portal search for a big plus icon that says Create a resource
- Search for "postgresql" in the search box and select Create on the Azure Database for PostgreSQL Flexible Server as seen below.
- 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.
- Next, we enter the server details. Select the options as seen below. Server name does not have to be the same.
- Skip the High Availablity section and move onto Authentication.
Select PostgreSQL authentication only and enter the Admin username, password you like.
Click on Next: Networking > and move to the next configuration. - 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.
- Lastly, we will click on Review + Create button and review out details and then click Create button as a final step.
- 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.
Connecting to the database
To connect to your newly created database we will head over to our created resource.
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.
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
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.
Open the downloaded car.sql script in an editor.
You will mainly see two SQL commands.
- CREATE - This will create a table in our database with the given columns.
- 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:
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.
Final Result
You should see the data has been inserted into the database by executing the following SQL:
SELECT * FROM car;
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: Image by fullvector on Freepik
Top comments (0)