View on GitHub

Flask, SQLAlchemy, and PostgreSQL

Blog Post | How to build a CRUD API using Python Flask and SQLAlchemy ORM with PostgreSQL

How to build a CRUD API using Python Flask and SQLAlchemy ORM with PostgreSQL

In this tutorial, you will learn how to build a simple CRUD API using Flask, SQLAlchemy, and PostgreSQL.

Flask, SQLAlchemy, and PostgreSQL

 

Table of Contents

 

Introduction

CRUD refers to the four basic operations that a software application must be able to perform: Create, Read, Update, and Delete.

📝 Note: This is a shallow app with the best practice for file structuring, to get the idea and start learning the framework!

Flask Vs Django: Which Python Framework to Choose? You can find the detailed differences between Django and Flask in this article.

Tutorial Result

This tutorial will create a Flask CRUD application that allows users to create, read, update, and delete database entries using an API. The API will be able to:

Tutorial Steps

  1. Project Setup:

    • Create PostgreSQL Database
    • Initialize the Virtual Environment
    • Install the Project Dependencies

  2. Writing the Project Code:

    • Writing the Main Files
    • Writing the Applications Files
    • Send Requests Using Postman

Definitions

💡 Tip: Skip these definitions at the first reading time!

 

Prerequisites

 

Project Setup

#1 Create PostgreSQL Database

Target: Create a new database with a new user.

💡 Tip: First create a test database with the same names & passwords below, then you can create a real database with the names & passwords you want!

We will create a database called “testdb” and user “testuser” with password “testpass”.

  1. In Windows Terminal, Run the PostgreSQL Server

    ~ sudo service postgresql start
➜ * Starting PostgreSQL 14 database server
# 14 is the PostgreSQL Server Version

    📝 Important Note: We need to run the PostgreSQL server every time we start coding!

  2. Activate the PostgreSQL Shell

    ~ sudo -u postgres psql
➜ postgres=#

  3. Create a New Database

    <!-- create database DBNAME; -->
postgres=# create database testdb;
➜ CREATE DATABASE

  4. Create a Database User, then Grant Privileges to it

    <!-- create user USERNAME with encrypted password 'PASSWORD'; -->
postgres=# create user testuser with encrypted password 'testpass';
➜ CREATE ROLE

<!-- grant all privileges on database DBNAME to USERNAME; -->
postgres=# grant all privileges on database testdb to testuser;
➜ GRANT

  5. Exit the Shell

    postgres=# \q

  6. Connect to the New Database

    ~ psql -U testuser -h 127.0.0.1 -d testdb
Password for user testuser: testpass
➜ testdb=>

  7. Check the Connection

    testdb=> \conninfo
➜ You are connected to database "testdb" as user "testuser" on host "127.0.0.1" at port "5432".
<!-- We need this information later for the env file -->

Now that our new PostgreSQL database is up and running, let’s move on to the next step!

#2 Initialize the Virtual Environment

We’ll create a virtual environment and activate it using the following commands

# virtualenv -p python3 ProjectName
~ virtualenv -p python3 Flask-SQLAlchemy-PostgreSQL
➜ created virtual environment

cd Flask-SQLAlchemy-PostgreSQL

source bin/activate

#3 Install the Project Dependencies

After creating and activating the virtualenv, let’s start with installing the project’s dependencies

pip install python-dotenv flask flask-sqlalchemy Flask-Migrate flask_validator psycopg2-binary

Then make a folder called src which will contain the project codes

mkdir src && cd $_

The Last step before starting with the code, create a requirements file using this command:

python -m pip freeze > requirements.txt

 

Writing the Project Code

📝 Note: In Flask, you can structure and name the files however you like, but we will learn the best practices for the naming and files structuring.

├── bin
├── include
├── lib
├── pyvenv.cfg
└── src
    ├── config.py
    ├── .env
    ├── .env.sample
    ├── __init__.py
    ├── app.py
    ├── accounts
    │   ├── controllers.py
    │   ├── models.py
    │   └── urls.py
    ├── items
    │   ├── controllers.py
    │   ├── models.py
    │   └── urls.py
    ├── requirements.txt
    └── README.md

#1 Getting Started with the Main Files “app, __init__, config, env”

In most Flask tutorials, you’ll notice that they only have the app.py file, which works. However, it is better to have multiple files, which makes the code clean and file management much easier, especially in large projects.

So, let’s create the 4 main files with this command:

touch app.py __init__.py config.py .env

Now let’s start diving deeper into each file:

Unpopular opinion: Better to start with config.py than app.py

Now our basic app is ready to go! We can run the server in the terminal by using one of the following commands:

# To Run the Server in Terminal
flask run

# To Run the Server with specific host and port
# flask run -h HOSTNAME -p PORTNUMBER
flask run -h 127.0.0.2 -p 5001

# To Run the Server with Automatic Restart When Changes Occur
FLASK_DEBUG=1 flask run

You can open your browser at http://127.0.0.1:5000 and see the result!

#2 Getting Started with the Applications Files

All the pains and headaches above are for the first time starting the project; most code is written inside the files of the applications.

💡 Tip: It is a best practice to have each app in a separate folder.

Each app should have its own models, urls, and controllers.

Let’s start by creating an app called Accounts with this command:

mkdir accounts && touch $_/models.py $_/urls.py $_/controllers.py

Now, let’s break down all these files:

💡 Tip: Always start with building the models classes

 

Now, two steps are required to get our accounts app ready to go:

  1. Import the urls file in the app.py

    The final shape of the app.py file should look like this:

    import os

# App Initialization
from . import create_app # from __init__ file
app = create_app(os.getenv("CONFIG_MODE"))

# ----------------------------------------------- #

# Hello World!
@app.route('/')
def hello():
    return "Hello World!"

# Applications Routes
from .accounts import urls

# ----------------------------------------------- #

if __name__ == "__main__":
    # To Run the Server in Terminal => flask run -h localhost -p 5000
    # To Run the Server with Automatic Restart When Changes Occurred => FLASK_DEBUG=1 flask run -h localhost -p 5000

    app.run()

  2. Migrate the new database models with these commands:

    flask db init
flask db migrate
flask db upgrade

    If you face this error: AttributeError: '_FakeStack' object has no attribute '__ident_func__', then fix it with these commands:

    python -m pip uninstall flask-sqlalchemy
python -m pip install flask-sqlalchemy

    You can learn more about the Flask-Migrate library from https://flask-migrate.readthedocs.io/en/latest

#3 Send Requests Using Postman

In this section, we will use Postman to test all of the CRUD operations we created.

What is Postman?

Postman is an application that allows us to do API testing. It’s like a browser that doesn’t render HTML. In the browser, we can hit only GET HTTP requests but here we can hit GET, POST, PUT, DELETE, and many more HTTP requests in API.

Postman is the world’s largest public API hub. It’s an API platform for developers to design, build, test, and iterate their own APIs.

Post New Account
List All Accounts
Get a Specific Account
Put a Specific Account
Delete a Specific Account

Get Started with SQLAlchemy Basic Relationships

Let’s say we have multiple applications like Accounts & Items and we need to establish a relationship between their models!

📝 Note: This is a short summary of the model’s relationships, we’ll go deeper into their CRUD operations in another article!

  1. One to Many Relationship

    The Account may own many Items, but the Item is owned by one Account!

    💡 Tip: Use ForeignKey in the many side!

    class Account(db.Model):
  id = db.Column(db.String(50), primary_key=True, nullable=False, unique=True)
     .
     .
     .

# Relations:
  items = db.relationship("Item", back_populates='account')

    class Item(db.Model):
  id = db.Column(db.String(50), primary_key=True, nullable=False, unique=True)
     .
     .
     .

# Relations:
  account_id = db.Column(db.String(100), db.ForeignKey("account.id"))
  account    = db.relationship("Account", back_populates="items")

  2. Many to One Relationship

    The Item may be owned by many Accounts, but the Account has only one Item!

    💡 Tip: Use ForeignKey in the many side!

    class Account(db.Model):
 id = db.Column(db.String(50), primary_key=True, nullable=False, unique=True)
    .
    .
    .

# Relations:
 item     = db.relationship("Item", back_populates="accounts")
 item_id  = db.Column(db.String(100), db.ForeignKey("item.id"))

    class Item(db.Model):
  id = db.Column(db.String(50), primary_key=True, nullable=False,
     .
     .
     .

# Relations:
  accounts = db.relationship("Account", back_populates='item')

  3. One to One Relationship

    The Account can own one Item, and the Item owned by one Account!

    💡 Tip: Use uselist=False in one side & ForeignKey in the other side!

    class Account(db.Model):
  id = db.Column(db.String(50), primary_key=True, nullable=False, unique=True)
     .
     .
     .

# Relations:
  item = db.relationship("Item", back_populates='account', uselist=False)

    class Item(db.Model):
  id = db.Column(db.String(50), primary_key=True, nullable=False, unique=True)
     .
     .
     .

# Relations:
  account    = db.relationship("Account", back_populates='item')
  account_id = db.Column(db.String(100), db.ForeignKey("account.id"), unique=True)

  4. Many to Many Relationship

    The Account may own many Items, and the Item may be owned by many Accounts!

    💡 Tip: Use Association class with multi ForeignKey!

    class Association(db.Model):
  item         = db.relationship("Item", back_populates="accounts")
  account      = db.relationship("Account", back_populates="items")
  item_id      = db.Column('item_id', db.String, db.ForeignKey('item.id'), primary_key=True)
  account_id   = db.Column('account_id', db.String, db.ForeignKey('account.id'), primary_key=True)

  def toDict(self):
     return { c.key: getattr(self, c.key) for c in inspect(self).mapper.column_attrs }

class Account(db.Model):
  id = db.Column(db.String(50), primary_key=True, nullable=False, unique=True)
     .
     .
     .

# Relations:
  items = db.relationship("Association", back_populates='account')

    class Item(db.Model):
  id = db.Column(db.String(50), primary_key=True, nullable=False, unique=True)
     .
     .
     .

# Relations:
  accounts = db.relationship("Association", back_populates="item")

Check out the Concept of backref and back_populate in SQLalchemy from this Stack Overflow Answer.

 

Conclusion

In this post, we have introduced ORMs, specifically the SQLAlchemy ORM. Using Flask and Flask-SQLAlchemy, we’ve created a simple API that displays and manipulates data in a PostgreSQL database. Finally, we introduce the basic relationships of SQLAlchemy.

The source code for the project in this post can be found on GitHub.

Article on Hashnode, Medium, DEV Community, and GitHub Pages