Managing SQL Database on Google Cloud
This article was published as a part of the Data Science Blogathon.
Introduction
This article shows how you can create and manage a Cloud SQL Database on Google Cloud Platform and further connect that database to any web application. This tutorial shows how you can join that database with a Django Application. By the end of this article, you will have a sufficient amount of understanding of SQL Databases, their features, properties, and practical use.
What is SQL Database?
SQL stands for Structured Query Language, a programming language used to create and manage Relational Databases. A Relational Database consists of tables containing data in the form of rows and columns in a pre-defined model/schema. Also, all the data in those tables have a relation between them. That’s why they are also called RDBMS (Relational Database Management System). The Relational Databases stores the data in a spreadsheet or an excel sheet format. SQL Databases are an example of Relational Databases. Some examples are:
1. SQLite
2. MySQL
3. PostgreSQL
4. Cassandra
and many more.
Benefits of Cloud SQL?
Cloud SQL is an example of PaaS(Platform as a service). It is one of the best databases to use, providing high availability throughout all regions and automatically managing your latency, updates, backups, etc.
You can use services of various Cloud SQL service providers like Google Cloud Platform (GCP), Microsoft Azure, AWS, etc. This tutorial uses GCP to create a Cloud SQL Database.
Setting Up a SQL Database- Process
1. log in to your GCP Console.
Prerequisites: You must have a billing account on Google Cloud, as this is a paid service.
2. Create a New Project or use an Existing Project.
3. Select your newly created project.
4. Navigate to SQL
5. Click on Create Instance button
6. Choose the type of SQL Database. In this tutorial, we have chosen PostgreSQL
7. Enable the PostgreSQL Instance API
8. Fill up the configurations of the database like ID, Password, etc.
You can also monitor the monthly cost of the Instance according to your configurations.
Finally, create the Instance after choosing all the configurations.
9. Your newly created database looks like this:
10. You can see the Public and Internal IP addresses of your Instance.
You can also open Cloud Shell to access its terminal.
Enabling SQL Admin API
By enabling this API, you can get access to control the Database through Cloud Shell.
1. Navigate to APIs & Services >> Library
2. Search for “Cloud SQL Admin API” and Enable it.
3. Navigate to APIs & Services >> Credentials to create a new Service Account
Creating Database
1. Now, navigate to your SQL Database and open Cloud SQL Shell.
2. In your Cloud SQL shell, type the below command and then your password, which you have set earlier while creating the Instance.
Note: Below command would be different from yours.
$gcloud SQL connect aryansqldb –user=postgres –quiet
You can create a new database using the Shell or the GCP Console. I will show you both ways.
a) Creating DB using Shell:
$ CREATE DATABASE #sqltutdb;
Use the below command to get the list of all your databases:
$ l
You can observe above that your database has been successfully created.
b) Creating DB using GCP Console:
Navigate to Databases
Create a new database of the name of your choice.
Configuring Networking Settings
This section will configure the firewall rules to allow all incoming requests or traffic to your Instance.
1. Navigate to Connections >> Networking
2. Click on Add a Network to add the range of IP Addresses that we want to allow.
We have chosen the IP Address as 0.0.0.0/0. After providing the login credentials, it will allow all the IPV4 clients to connect to the Instance. But in the production build, you must add only a specific IPV4 address on which you want your application to be hosted.
Creating Django Application
In this section, we will create a sample Django application and connect that application remotely with our Cloud SQL Database.
1. Installing Django
$ pip install django $ pip install psycopg2
2. Creating a New project
$ django-admin startproject #sqldbtut
3. Configuring the Settings.py file
We have to change the default database from SQLite to PostgreSQL.
DATABASES = {
'default': {
'ENGINE': 'django.db.backends.postgresql',
'NAME': "", #sqltutdb
'USER': "", #postgres
'PASSWORD': "",
'HOST': "",
'PORT': 5432,
}
}
4. Create migrations in the database
To check if our DB is connected successfully, we will create the migrations in the database.
$ python manage.py makemigrations $ python manage.py migrate
Hurray 🎉, you created a Cloud SQL Database and successfully tested it.
Conclusion
In this article, we have created a SQL Database Server on Google Cloud and used that database in a Django Web Application. As discussed above, SQL Databases have a pre-defined and structured format. On the other hand, we also have NoSQL databases, which don’t have a pre-defined structure and store the raw data in JSON format. NoSQL Databases are of four major types, Key-Value (VK) Stores, Document Stores, Column Family Data stores, and Graph Databases. SQL Databases are verticle scalable, while the NoSQL databases are horizontally scalable.
If your requirements are precise and your dataset is structured, also you want that your database must follow ACID Properties (Atomicity, Consistency, Isolation, and Durability), then you must go towards SQL Databases. On the other hand, if your data requirements are not precise also your dataset is unstructured, then NoSQL databases are the best choice for you.
Some key takeaways of this article are:
1. Firstly, we have briefly talked about SQL Databases.
2. Then, we created a SQL Database on Google Cloud.
3. We have enabled the Cloud SQL Admin API to access the Cloud SQL Shell.
4. After that, we created a Service account and configured firewall settings.
5. Finally, we have created a sample Django Web Application and connected our database with it.
It is all for today. I hope you have enjoyed the article. If you have any doubts or suggestions, feel free to comment below. Or you can also connect with me on LinkedIn, and I will be delighted to get associated with you.
Do check my other articles also.
Thanks for reading, 😊
The media shown in this article is not owned by Analytics Vidhya and is used at the Author’s discretion.
