How to Prepare PostgreSQL Sandbox on Cloud for Free
People learning data analytics often focus primarily on working with text files, such as CSVs, to analyze and process data. While this approach is valuable for understanding basic concepts and techniques, it overlooks a crucial aspect of real-world data work: interacting with databases. In professional environments, data analysts are frequently required to retrieve, manipulate, and store data in various types of databases.
Without familiarity with database management systems, they may struggle to handle large datasets, perform complex queries, or integrate data from multiple sources effectively. Therefore, it’s essential for aspiring data analysts to go beyond text files and invest time in learning how to set up and work within a database sandbox.
This hands-on experience with databases not only enhances their technical skills but also better prepares them for the challenges of real-world data analytics tasks.
Is it Possible to Create a Sandbox Database in The Cloud for Free?
Sure, It is possible to create a sandbox database in the cloud for free, thanks to various cloud providers that offer free tiers and trial plans. Beyond the well-known platforms like Amazon Web Services (AWS), Google Cloud Platform (GCP), and Microsoft Azure, there are specialized cloud services designed specifically for databases, such as Neon, Heroku Postgres, and PlanetScale.
Neon, for instance, provides a serverless PostgreSQL database with a generous free tier, making it ideal for developers and analysts looking to experiment without costs. Similarly, Heroku offers free PostgreSQL databases as part of its platform-as-a-service (PaaS), while PlanetScale provides a free tier for its MySQL-compatible databases with features like branching and scaling.
These services offer easy setup and management, allowing users to quickly spin up a sandbox environment to practice database management, run queries, and test data integration workflows. Leveraging these free cloud database services is an excellent way for beginners and professionals to gain hands-on experience, explore advanced features, and understand the nuances of cloud-based data management — all without significant financial investment.
How to Setup Free Cloud PostgreSQL Database and Load a Sample Data?
Neon provides an easy and free way to set up a PostgreSQL database in the cloud. This article will demonstrate how to perform the steps in setting up and using neon as a sandbox database. The discussion of this article will be structured as follows:
- Create Neon Account
- Create PostgreSQL Instance on Neon
- Install PostgreSQL Client Tools at Local PC/ Notebook
- Load Adventureworks Data into PostgreSQL
- Connect to The Database form Python Notebook
Create Neon Account
Neon is a modern, serverless cloud database service designed for PostgreSQL, offering high scalability, performance, and ease of use. It allows users to quickly set up a free PostgreSQL instance in the cloud, making it ideal for developers, data analysts, and anyone needing a reliable database solution without the overhead of managing infrastructure.
To create Neon Account or Sign Up you need to follows all the steps bellows.
Visit the Neon Website: Go to Neon.
- Sign Up: Click on “Get Started for Free.”
2. Choose Google: Select “Continue with Google” to sign up using your Google account.
3. Grant Permissions: Allow Neon to access your Google account for seamless sign-in.
Create PostgreSQL Instance on Neon
After signing up, you’ll be prompted to create a new project, which will automatically set up your free PostgreSQL database.
You’re now ready to start using Neon for your cloud database needs. You could locate your database on the Dashboard Page. We could also find the connection string for make a connection to the database on that page.
Install PostgreSQL Client Tools at Local PC/ Notebook
Accessing a remote PostgreSQL database via client tools involves using software that allows you to connect to and interact with your database server. This is crucial for managing databases, running queries, and performing administrative tasks. Here’s an explanation of how to access a remote PostgreSQL instance and how to install the necessary client tools on your local PC or notebook.
psql is a versatile and powerful tool for accessing and managing PostgreSQL databases, whether locally or remotely. By installing psql on your local machine and connecting to your remote PostgreSQL instance, you can efficiently execute SQL queries, manage databases, and perform administrative tasks directly from the command line.
The PostgreSQL Client Tools installer is include in the PostgreSQL installation package. It could be downloaded from here.
After the installer is downloaded, run the installer and we only choose to install the PgAdmin and the command line tools.
After the installation process is completed, to make the psql command available, the path to the PostgreSQL bin folder need to be added to the environment variable.
Then try to connect to the database using psql from the command line on local PC/ Notebook.
psql -h hostname -U username -d databaseRemember the connection string can be found on the Neon Dashboard. If the connection is valid, the command line will connected to the database and we could send command from the psql console.
Load Adventureworks Data into PostgreSQL
Now, The PostgreSQL database are ready and connection to the database could be established. Schema, table creation and data loading could be perform by execute SQL command via psql. This article will use the Adventureworks data that has been prepared by NorfolkDataSci and could be found on their github repo, thanks for the data and also the installation script. We modified the Install.sql because there are some problem, and reshare the data and the sql file. Click here to visit the repo and then clone or download it to your local then unzip the file.
Lets start the installation processes, open the command line and write command for execute the Install.SQL file on that folder.
When it is executed, a schemas and tables will be formed in the database, then the data will be loaded into each table.
After the installation is completed, we could check the result from Neon console. If the installation process is done without error, we will find new schemas on database and tables on each schema.
we also could view the data from some table on that page.
Connect to The Database form Python Notebook
The Adventureworks database is ready and we could use it as a sandbox for practicing how to access the database and doing some query from python. We will try to connect to the database using python on the Google Colabs.
First you need to create new notebook on Colabs, and then we will use psycopg2 library for making connection to the PostgreSQL database.
Checking whether psycopg2 has installed on the notebook, and if it has been installed what is the version.
!pip show psycopg2Import the required python libraries
import pandas as pd
import psycopg2
import osCheck whether connection to the database could be established.
DATABASE_URL='postgresql://olapdb_owner:d1zrWN0CihfD@ep-bold-sky-a5y7069l.us-east-2.aws.neon.tech/olapdb'# Get the connection string from the variable
connection_string = DATABASE_URL
# Connect to the Postgres database
conn = psycopg2.connect(connection_string)
# Create a cursor object
cur = conn.cursor()
# Execute SQL commands to retrieve the current time and version from PostgreSQL
cur.execute('SELECT NOW();')
time = cur.fetchone()[0]
cur.execute('SELECT version();')
version = cur.fetchone()[0]
# Close the cursor and connection
cur.close()
conn.close()
# Print the results
print('Current time:', time)
print('PostgreSQL version:', version)If the connection is well established, the current time and the version of PostgreSQL server will be printed on the console.
Send query command to the database, in this case we will retrieve the schema information.
lstables=[]
conn = psycopg2.connect(connection_string)
with conn.cursor() as cur:
cur.execute("SELECT * FROM information_schema.tables WHERE table_type = 'BASE TABLE';")
result = cur.fetchall()
for r in result:
lstables.append([r[1],r[2],r[3]])df_tables = pd.DataFrame(lstables, columns=['schema', 'table','category'])
df_tables
You could check my code on colabs by click here. Copy the notebook to your drive and you could modify it to connect to your own PostgreSQL cloud database.
Conclusion
In conclusion, setting up a PostgreSQL cloud database on Neon provides a powerful and flexible environment for managing and analyzing data. After successfully loading the AdventureWorks sample dataset into your Neon database, you can easily connect to this database from various platforms, including Python on Google Colab.
By using Python libraries such as psycopg2 or SQLAlchemy, you can establish a connection, send queries, and retrieve data directly from your Neon-hosted PostgreSQL database, enabling seamless integration of cloud-based data management with your data analysis workflows in Google Colab.
This setup is ideal for exploring complex datasets, performing data analysis, and gaining hands-on experience with cloud-based PostgreSQL databases. Thank you for taking the time to read this article, I hope it is useful.
