Working with PostgreSQL and flask_sqlalchemy
Tags: flask, python, sqlalchemy, postgres
Published: 2021-05-03 15:15:32

In this article, we'll be looking at creating and connecting a simple web app built with `flask`; a python based microframework, to a `PostgreSQL` database. Here we'll be making use of `sqlalchemy` which is an object-relational mapper for python. Without further ado, let us get right into it.

PS: I assume you already have python and flask installed so I won't be going into details about that. If you don't, you can visit the [python website](https://python.org) to download it then run the following command in your terminal to install flask.

```sh
$ pip install flask
```



## What is PostgreSQL?
According to its Wikipedia page, "PostgreSQL, also known as Postgres, is a free and open-source relational database management system (RDBMS) emphasizing extensibility and SQL compliance" -[Wikipedia](https://en.wikipedia.org/wiki/PostgreSQL)

What distinguishes Postgres from other relational databases like `MySQL` is the fact that it is object-oriented and ships with more use cases and features which is important to run some applications.

I am currently working on a Linux operating system (Parrot to be specific). So we'll be going through these processes the Linux way. Cheers.


## Installing Postgres

If like me, you are using the latest version of your Linux distro, then it is very likely that you already have Postgres installed on your machine. Then you can skip to [this](#starting-the-server). If you don't have it installed, however, you can go through the process below to install it.

First, create the file repository configuration
```sh
$ sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
```

Then import the repository signing key
```sh
$ wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
```

Then go ahead to update the package list
```sh
$ sudo apt-get update
```

With all these done, you can then install Postgresql by doing
```sh
$ sudo apt-get install postgresql
```

## Starting The Server
With the installation done, we can then proceed to start the Postgres server by running the following in our terminal

```sh
$ sudo service postgresql start
[sudo] password for user:
$
```

Once this runs without any issue, we know our server is running. With this, let us proceed to create our user and then our database. We are creating a new user here because it is bad practice to use the postgres user (personal opinion though). To do this, we enter the following to get into the postgres cli.

```sh
$ sudo -i -u postgres
```

from the above command, we get a prompt similar to this
```sh
postgres@parrot:~$
```

then we proceed by typing in `psql`
```sh
postgres@parrot:~$psql
...
...
postgres=#
```

this takes us to an interactive postgres environment where we can run general SQL queries and also postgres specific commands.

## Creating a user
We proceed to create a user by typing the SQL command for that.

```sh
postgres=# CREATE ROLE user WITH CREATEDB LOGIN PASSWORD 'password';
```
Here, we created a user with the ability to create databases and also gave the user a login password (password).

We can then create a database for this particular user by doing

```sh
postgres=# CREATE DATABASE ourdb WITH OWNER user;
```

with this done, we have created a database named `ourdb` for `user`. Exit the environment by typing `\q` and `exit` one after the other.

To read more on PostgreSQL, visit the page [here](https://www.postgresql.org/)


## Creating our web app
Next, we'll be creating our simple web app with flask, a microframework for the python programming language. Read more about it [here](https://flask.palletsprojects.com/en/1.1.x/).

We will set up a simple app by doing

```py
from flask import Flask

app = Flask(__name__)

@app.route('/')
def index():
    return 'Hello, World!'

if __name__ == "__main__":
    app.run()
```

We would be using flask_sqlalchemy to connect to our Postgres database we created earlier. But first,

## What is Flask_sqlalchemy?
"Flask-SQLAlchemy is an extension for Flask that adds support for SQLAlchemy to your application. It aims to simplify using SQLAlchemy with Flask by providing useful defaults and extra helpers that make it easier to accomplish common tasks." - [PyPi](https://pypi.org/project/Flask-SQLAlchemy/)

### SQLAlchemy?
"SQLAlchemy is the Python SQL toolkit and Object Relational Mapper that gives application developers the full power and flexibility of SQL.

It provides a full suite of well-known enterprise-level persistence patterns, designed for efficient and high-performing database access, adapted into a simple and Pythonic domain language." [SQLAlchemy](https://www.sqlalchemy.org/)

With that, we can proceed to writing our configurations, creating our database tables (also known as `models`), and connecting to the database.

We make our flask app more workable by adding the following lines

```py
from flask import Flask
from flask_sqlalchemy import SQLAlchemy  # 1

app = Flask(__name__)
app.config['SECRET_KEY'] = "xxxxxxxx"    # 2

db = SQLAlchemy(app)

# our database uri
username = "user"
password = "password"
dbname = "ourdb"

app.config["SQLALCHEMY_DATABASE_URI"] = "postgresql://{username}:{password}@localhost:5432/{dbname}"

if __name__ == "__main__":
    app.run()
```

We also create our model by adding the class right after the database configuration

```py
# basic model
class User(db.Model):
    __tablename__ = 'user'
    id = db.Column(db.Integer, primary_key=True)
    email = db.Column(db.String(64), unique=True, index=True)
    password = db.Column(db.String(128))

if __name__ == "__main__":
    app.run()
```

With this done, we can then create our tables for the database by proceeding to the terminal. However, before we do that, we have to install a library to help python connect to the PostgreSQL database. The library is known as `psycopg2`.

We can install this by running the code below in the terminal

```sh
$ pip install psycopg2
```

If for dependency reasons, we get an error, we can do

```sh
$ pip install psycopg2-binary
```

With that installed, we can proceed to create our table. The connection to the database takes place here. We open a python shell session in the terminal and run the following commands

```sh
$ python
...
>>>from ourapp import app, db, User
>>>app.app_context().push()
>>>db.create_all()
>>>
```

As long as this runs without any errors, we have successfully connected to our database and created the User table. We can then populate the DB either by going to our database and running the SQL commands for that

i.e
```sh
$ sudo -i -u postgres
```

Then we enter the database `ourdb` owned by `user` with the following command. (The password would be required here. For us, the password is `password`)

`psql -h <hostname> -d <dbname> -U <username> -p <port>`
i.e

```sh
postgres@parrot:~$ psql -h localhost -d ourdb -U user -p 5432
```

This would ask for our password then take us to an interactive command-line environment where we can run SQL commands to populate our database.

We can also do that from our python shell as flask_sqlalchemy allows us to do that.

```py
>>> from ourapp import app, db, User
>>>app.app_context().push()
>>>db.create_all()
>>>
>>>user = User(email="example@email.com", password="password_hash")
>>>db.session.add(user)
>>>db.session.commit()
>>>
```

With this, we have successfully created a user and added the details to our database.

## Conclusion
In this article, we learned about PostgreSQL, how to install and work with it. We also created a simple flask app and saw how to create tables, connect our flask app to the database and populate the database.

I trust with this you are capable of setting up your flask projects for use with the PostgreSQL database. If you have any questions, you can contact me on Twitter [@agba_dr3](https://twitter.com/agba_dr3).

Cheers!