PostgreSQL is a powerful, open-source object-relational database system that uses and extends the SQL language combined with many other features that safely store complicated data workloads.
The database comes with many features that not only help developers build applications but also help administrators protect data no matter how big or small the dataset is.
As developers, we often work on different projects depending on the level of expertise. As a beginner, you worked on an app like a ‘to-do app’ which didn’t require a heavy database mechanism to store records of tasks. But as you advance, the kind of projects you handle change with time. A point will reach where you will need to build a production-ready project which will need a database structure.
Usually a basic Django project will ship with a SQLite database as the default. Even though this is the default database, Django also supports other databases like:
- PostgreSQL
- MySQL
- Oracle
In this article we will focus on PostgreSQL and how we can implement it on Django projects.
Why use PostgreSQL
PostgreSQL comes with many features that not only help developers build applications but also help administrators protect data no matter how big or small the dataset is.
So, why should you consider PostgreSQL over the others:
- It is free to use and it’s also an open-source program which makes it easy to upgrade or extend.
- PostgreSQL is highly extensible – for example, you can define your own data types, build your own functions and even write code from different programming languages without having to recompile your database.
- It supports many SQL features.
- It also supports multiple programming languages like Python, Java, C/C++, Ruby, etc…
- Works on most popular Operating Systems.
- It is not controlled by any cooperation meaning it is free.
Let’s get started and see how to implement PostgreSQL.
Setting up Django project
Now, for us to be able to make a database migration it means we need a project to work with. Let’s go ahead and create a new Django project.
As always the first step is to create a virtual environment, which is accomplished in two steps:
pip install virtualenvwrapper-win
This installs virtual a environment, so the next step is to name the virtualenv:
mkvirtualenv [name]
Naming it automatically activates the virtual environment.
Now, since we are working on a Django project, installing it is essential. It will be installed in the virtual environment
pip install django
Next we have to create our Django project, in this case, it can be named testproject.
django-admin startproject testproject
Then, we create an app within our project folder.
python manage.py startapp projectApp
Finally, we can run the project so as to initialize the sqLite database with this command.
python manage.py runserver
We will be using this simple Django application to perform the migration in the following steps.
Step 1: Backup existing Database
The first step we will need to create a backup of our current data which we are going to export into PostgreSQL later on.
To perform a data backup, we use the following command:
python manage.py dumpdata > data.json
This command will generate a data.json file in the root of your project, meaning you generated the dumpdata from SQLite and stored it in JSON format.
Step 2: Installing PostgreSQL
When working with PostgreSQL, we can conduct the operations in two ways; we can either use the desktop application or use the terminal to execute specific commands.
1. Desktop Setup
In order to install PostgreSQL you will need to download it from the official website HERE. After downloading, run the installation and then launch it by opening the pgAdmin which will redirect you to the dashboard.
By default we will have a Postgres database created there under the Database tab. At this point what we want to do is create a new database for our Django app and then connect the app to the database.
Step 3: Configure settings.py file.
Back in our Django project, settings.py file in the DATABASE section. By default this section will have the below configurations:
DATABASES = {
'default': {
'ENGINE': 'django.db.backends.sqlite3',
'NAME': BASE_DIR / 'db.sqlite3',
}
}
We are going to replace this with the configurations below:
DATABASES = {
'default': {
'ENGINE': 'django.db.backends.postgresql_psycopg2',
'NAME': 'test',
'USER': 'postgres',
'PASSWORD': 'test123',
'HOST': 'localhost',
'PORT': '5432',
}
}
Below is an overview of what we have added into our Databases section:
Name– Name of the database we will be using.User– User with access to the dataPassword– it’s the key required to connect to PostgreSQL.Host– it’s the server name on which PostgreSQL is running.Port– it’s the port number that will be used when listening to the connections, by default it is always set to 5432.
Step 4: Install psycopg2 adapter
Psycopg is the most popular database adapter for developers to connect databases to python.
One of its amazing features is the power it has to convert PostgreSQL array data types into Python lists.
To install it, we use the following command:
pip install psycopg2
Step 5: Creating PostgreSQL Database
Back in the pgAdmin dashboard, under the Database tab right click and create a Database in our case we are going to name it test because that is what we specified in the settings file.
The next step we will take is to utilize the new database and connect it to the Django app.
Step 6: Sync Database
python manage.py migrate --run-syncdb
This command will change the database backend to PostgreSQL.
Step 7: Load Data
python manage.py loaddata data.json
This command dumps our previous data from SQlite into postgres.
To confirm this, we should head back to pgAdmin to refresh the page. Under the database we created our tables in, we should see the updates.
2. Use of Terminal
The use of the terminal comes in handy if you are running PostgreSQL on a server operating system.
Just like we did in the desktop setup, we will follow the same steps only this time we will be using the terminal.
Step 1: Installing PostgreSQL
To install PostgreSQL using the terminal, follow these steps:
- Add the PostgreSQL package repository on your server OS the command below.
echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg main" | sudo tee /etc/apt/sources.list.d/pgsql.list
- Next add the GPG key of the PostgreSQL package repository by running this command:
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
- The next step is to update the APT
apt-get update
- Install PostgreSQL
apt-get install -y postgresql
Step 2: Create Database and User
- Log into Postgres.
By default after installation user postgres is created, we will use this user to perform administrative tasks
sudo -u postgres psql
- Then create a Database
CREATE DATABASE projectname;
- Set up a Database User
This will be used to connect and interact with databases.
CREATE USER projectuser WITH PASSWORD 'password';
- We will also need to grant access to our user of the database.
GRANT ALL PRIVILEGES ON DATABASE projectname TO projectuser;
Now, at this point, we are ready to change settings in the Django project in the settings.py file.
The changes you make should reflect what we created in the PostgreSQL that is the username and password. In this case:
DATABASES = {
'default': {
'ENGINE': 'django.db.backends.postgresql_psycopg2',
'NAME': '',#database name
'USER': '',#projectuser
'PASSWORD': '',#password
'HOST': 'localhost',
'PORT': '5432',
}
}
Step 3: Migrate Database
Now that we have configured the Django settings, we can go ahead and migrate our data.
python manage.py makemigrations python manage.py migrate
By executing the commands successfully we have managed to successfully migrate our database from SQLite3 to PostgreSQL.
Conclusion
In this article we have gone through the steps to install and configure PostgreSQL as a backend for a Django project. Go make a change on your projects and start using PostgreSQL.
If you want to learn more about what you can do with PostgreSQL, take a look at this article about “How to Use Kubernetes to Deploy Postgres.”
