# Python CRUD Rest API in Python using Flask, SQLAlchemy, Postgres, Docker, and Docker Compose

Let's create a CRUD Rest API in Python, using:

* Flask (Python web framework)
    
* SQLAlchemy (ORM)
    
* Postgres (database)
    
* Docker (containerization)
    
* Docker Compose (to run the application and the database in containers)
    

If you prefer a video version:

[![Blurred youtube thumbnail of Build a CRUD Rest API in Python using Flask, SQLAlchemy, Postgres, Docker](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/mwqovqifxsjomdtcr12c.png align="left")](https://youtube.com/live/Uv-jMWV29rU)

---

## 🏁 Intro

Here is a schema of the architecture of the application we are going to create:

[![crud, read, update, delete, to a flask app and postgres service, connected with docker compose. Postman and tableplus to test it](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/3jcj60p7sltmblkov9q1.png align="left")](https://youtube.com/live/Uv-jMWV29rU)

We will create 5 endpoints for basic CRUD operations:

* Create
    
* Read all
    
* Read one
    
* Update
    
* Delete
    

Here are the steps we are going through:

1. Create a Flask application using SQLALchemy as an ORM.
    
2. Dockerize the Flask application writing a Dockerfile and a docker-compose.yml file to run the application and the database
    
3. Run the Postgres database in a container using Docker Compose, and test it with TablePlus
    
4. Run the Flask application in a container using Docker Compose, and test it with Postman
    

We will go with a step-by-step guide, so you can follow along.

---

## 🏁 Create a Flask application using SQLALchemy as an ORM

Create a new folder:

```bash
mkdir flask-crud-api
```

step into the folder:

```bash
cd flask-crud-api
```

Open the folder with your favorite IDE. I am using VSCode, so I will use the command:

```bash
code .
```

We need just 4 files for the Flask application, including containerization.

You can create these files in different ways. One of them is to create them manually, the other one is to create them with the command line:

```bash
touch requirements.txt app.py Dockerfile docker-compose.yml
```

Your folder structure should look like this:

[![folder structure - Build a CRUD Rest API in Python using Flask, SQLAlchemy, Postgres, Docker](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/7w5oc01ujfjxcg6pc9wa.png align="left")](https://youtube.com/live/Uv-jMWV29rU)

---

## 🗒️requirements.txt file

  

Let's add them to the `requirements.txt` file:

```bash
flask
psycopg2-binary
Flask-SQLAlchemy
```

Short explanation of the dependencies:

`flask` is the Python web framework we are gonna use.

`psycopg2-binary` is the driver to make the connection with the Postgres database.

`Flask-SQLAlchemy` is the ORM to make the queries to the database.

---

## 🐍 [app.py](http://app.py) file

  

Populate the [app.py](http://app.py) file as follows:

```python
from flask import Flask, request, jsonify, make_response
from flask_sqlalchemy import SQLAlchemy
from os import environ

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = environ.get('DB_URL')
db = SQLAlchemy(app)

class User(db.Model):
    __tablename__ = 'users'

    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), unique=True, nullable=False)
    email = db.Column(db.String(120), unique=True, nullable=False)

    def json(self):
        return {'id': self.id,'username': self.username, 'email': self.email}

db.create_all()

#create a test route
@app.route('/test', methods=['GET'])
def test():
  return make_response(jsonify({'message': 'test route'}), 200)


# create a user
@app.route('/users', methods=['POST'])
def create_user():
  try:
    data = request.get_json()
    new_user = User(username=data['username'], email=data['email'])
    db.session.add(new_user)
    db.session.commit()
    return make_response(jsonify({'message': 'user created'}), 201)
  except e:
    return make_response(jsonify({'message': 'error creating user'}), 500)

# get all users
@app.route('/users', methods=['GET'])
def get_users():
  try:
    users = User.query.all()
    return make_response(jsonify([user.json() for user in users]), 200)
  except e:
    return make_response(jsonify({'message': 'error getting users'}), 500)

# get a user by id
@app.route('/users/<int:id>', methods=['GET'])
def get_user(id):
  try:
    user = User.query.filter_by(id=id).first()
    if user:
      return make_response(jsonify({'user': user.json()}), 200)
    return make_response(jsonify({'message': 'user not found'}), 404)
  except e:
    return make_response(jsonify({'message': 'error getting user'}), 500)

# update a user
@app.route('/users/<int:id>', methods=['PUT'])
def update_user(id):
  try:
    user = User.query.filter_by(id=id).first()
    if user:
      data = request.get_json()
      user.username = data['username']
      user.email = data['email']
      db.session.commit()
      return make_response(jsonify({'message': 'user updated'}), 200)
    return make_response(jsonify({'message': 'user not found'}), 404)
  except e:
    return make_response(jsonify({'message': 'error updating user'}), 500)

# delete a user
@app.route('/users/<int:id>', methods=['DELETE'])
def delete_user(id):
  try:
    user = User.query.filter_by(id=id).first()
    if user:
      db.session.delete(user)
      db.session.commit()
      return make_response(jsonify({'message': 'user deleted'}), 200)
    return make_response(jsonify({'message': 'user not found'}), 404)
  except e:
    return make_response(jsonify({'message': 'error deleting user'}), 500)
```

Explanation:

We are importing:

* Flask as a framework
    
* request to handle the HTTP
    
* jsonify to handle the json format, not native in Python
    
* make\_response to handle the HTTP responses
    
* flask\_sqlalchemy to handle the db queries
    
* environ to handle the environment variables
    

We are creating Flask app, configuring the database bu setting an environment variable called 'DB\_URL'. We will set it later in the docker-compose.yml file.

Then we are creating a User class with an id, a username and an email. the id will be autoincremented automatically by SQLAlchemy when we will create the users. the `__tablename__ = 'users'` line is to define the name of the table in the database

An important line is `db.create_all()`. This will synchronize the database with the model defined, for example creating an "users" table.

Then we have 6 endpoints

* test: just a test route
    
* create a user: create a user with a username and an email
    
* get all users: get all the users in the database
    
* get one user: get one user by id
    
* update one user: update one user by id
    
* delete one user: delete one user by id
    

All the routes have error handling, for example if the user is not found, we will return a 404 HTTP response.

You can check a video-explanation [here](https://youtube.com/live/Uv-jMWV29rU)

---

## 🐳 Dockerize the Flask application

  

Dockerfile:

```bash
FROM python:3.6-slim-buster

WORKDIR /app

COPY requirements.txt ./

RUN pip install -r requirements.txt

COPY . .

EXPOSE 4000

CMD [ "flask", "run", "--host=0.0.0.0", "--port=4000"]
```

`FROM` sets the base image to use. In this case we are using the python 3.6 slim buster image

`WORKDIR` sets the working directory inside the image

`COPY requirements.txt ./` copies the requirements.txt file to the working directory

`RUN pip install -r requirements.txt` installs the requirements

`COPY . .` copies all the files in the current directory to the working directory

`EXPOSE 4000` exposes the port 4000

\`\`CMD \[ "flask", "run", "--host=

`CMD [ "flask", "run", "--host=0.0.0.0", "--port=4000"]` sets the command to run when the container starts

---

## 🐳🐳Docker compose

  

Populate the `docker-compose.yml` file:

```yaml
version: "3.9"

services:
  flask_app:
    container_name: flask_app
    image: dockerhub-flask_live_app:1.0.0
    build: .
    ports:
      - "4000:4000"
    environment:
      - DB_URL=postgresql://postgres:postgres@flask_db:5432/postgres
    depends_on:
      - flask_db
  flask_db:
    container_name: flask_db
    image: postgres:12
    ports:
      - "5432:5432"
    environment:
      - POSTGRES_PASSWORD=postgres
      - POSTGRES_USER=postgres
      - POSTGRES_DB=postgres
    volumes:
      - pgdata:/var/lib/postgresql/data

volumes:
  pgdata: {}
```

We just defined 2 services: `flask_app` and `flask_db`

`flask_app` is the Flask application we just dockerized

`flask_db` is a Postgres container, to store the data. We will use the official Postgres image

Explanation:

`version` is the version of the docker-compose file. We are using the verwion 3.9

`services` is the list of services (containers) we want to run. In this case, we have 2 services: flask\_app and flask\_db

`container_name` is the name of the container. It's not mandatory, but it's a good practice to have a name for the container. Containers find each other by their name, so it's important to have a name for the containers we want to communicate with.

`image` is the name of the image we want to use. I recommend replacing "dockerhub-" with YOUR Dockerhub account (it's free).

`build` is the path to the Dockerfile. In this case, it's the current directory, so we are using `.`

`ports` is the list of ports we want to expose. In this case, we are exposing the port 4000 of the flask\_app container, and the port 5432 of the flask\_db container. The format is `host_port:container_port`

`depends_on` is the list of services we want to start before this one. In this case, we want to start the flask\_db container before the flask\_app container

`environment` is to define the environment variables. for the flask\_app, we will have a database url to configure the configuration. For the flask\_db container, we will have the environment variables we have to define when we wan to use the Postgres container (we can't change the keys here, because we are using the Postgres image, defined by the Postgres team).

`volumes` in the flask\_app defines a named volume we will use for persistency. Containers are ephimerals by definition, so we need this additional feature to make our data persist when the container will be removed (a container is just a process).

`volumes` at the end of the file is the list of volumes we want to create. In this case, we are creating a volume called `pgdata`. The format is `volume_name: {}`

---

## 👟 Run the Postgres container and test it with TablePlus

  

```bash
docker compose up -d flask_db
```

The `-d` flag is to run the container in detached mode, so it will run in the background.

You should see something like this:

[![docker downlading image - Build a CRUD Rest API in Python using Flask, SQLAlchemy, Postgres, Docker](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/0fs51fnuw6aizk63jilf.png align="left")](https://youtube.com/live/Uv-jMWV29rU)

Docker is pulling (downloading) the Postgres image on our local machine and it's running a container based on that Postgres image.

To check if the container is running, type:

```bash
docker compose logs
```

If everything is ok, you should see something like this:

[![databse log](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/vt49v1vidtc2f9gufhl7.png align="left")](https://youtube.com/live/Uv-jMWV29rU)

If the last line is `LOG: database system is ready to accept connections`, it means that the container is running and the Postgres server is ready to accept connections.

But to be sure, let's make another test.

To show all the containers (running and stopped ones) type:

```bash
docker ps -a
```

The output should be similar to this:

[![one container running](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/qfgpagm8d5h079gfigz4.png align="left")](https://youtube.com/live/Uv-jMWV29rU)

Now, to test the db connection, we can use any tool we want. Personally, I use TablePlus.

Use the following configuration:

Host: [`localhost`](http://localhost)

Port: `5432`

User: `postgres`

Password: `postgres`

Database: `postgres`

[![Tableplus interface](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/s8zt4zbr169a8xxerx92.png align="left")](https://youtube.com/live/Uv-jMWV29rU)

Then hit "Test" (at the bottom-right).

If you get the message "connection is OK" you are good to go.

[![TAbleplus, OK connection](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/96boisx1566xyj1bqwzn.png align="left")](https://youtube.com/live/Uv-jMWV29rU)

You can also click "Connect" and you will see an empty database. This is correct.

[![Tableplus empty but connected db](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/em8sr7gb57fj5w12cxhp.png align="left")](https://youtube.com/live/Uv-jMWV29rU)

---

## 🔨 Build and run the Flask application

  

Let's go back to the folder where the `docker-compose.yml` is located and type:

```bash
docker compose build
```

This should BUILD the flask\_app image, with the name defined in the "image" value. In my case it's francescoxx/flask\_live\_app:1.0.0 because that's my Dockerhub username. You should replace "francescoxx" with your Dockerhub username.

You can also see all the steps docker did to build the image, layer by layer. You might recognize some of them, because we defined them in the Dockerfile.

[![docker build](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/3jww2z32ppxbd8jybbr8.png align="left")](https://youtube.com/live/Uv-jMWV29rU)

Now, to check if the image has been built successfully, type:

```bash
docker images
```

We should see a similar result, with the image we just built:

[![2 docker images](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/zu9au7jt5nnyltmtwdyl.png align="left")](https://youtube.com/live/Uv-jMWV29rU)

---

## ⚗️ Run the flask\_app service

  

To do that, we can just type:

```bash
docker compose up flask_app
```

In this case we don't use the -d flag, because we want to see the logs in the terminal.

We should see something like this:

[![docker compose up command](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/i34lbcku4p3fryf8dmaq.png align="left")](https://youtube.com/live/Uv-jMWV29rU)

---

## 🔍 Test the application

  

You shoulds see this result:

[![test endpoint](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/q4u3e2bxykr6i3ob04r5.png align="left")](https://youtube.com/live/Uv-jMWV29rU)

(note that if you visit [`localhost:4000`](http://localhost:4000) you get an error because there is no route associated to this endpoint, but by getting an error is a good thing, because it means that the server is running!)

Now it's time to test all the endpints using Postman. Feel free to use any tool you want.

If we make a GET request to [`localhost:4000/users`](http://localhost:4000/users) we will get an empty array. This is correct

[![GET request to localhost:4000/users](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ni9s8w54nozg4jbz31cz.png align="left")](https://youtube.com/live/Uv-jMWV29rU)

---

### 📝 Create a user

  

[![POST request to localhost:4000/users](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/nhj6pbm3vigzl6g4wp56.png align="left")](https://youtube.com/live/Uv-jMWV29rU)

Let's crete another one:

[![POST request to localhost:4000/users](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/qr5neakvs2o0iz6edczk.png align="left")](https://youtube.com/live/Uv-jMWV29rU)

One more:

[![POST request to localhost:4000/users](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/seqamoqwe2gbzwxu2y54.png align="left")](https://youtube.com/live/Uv-jMWV29rU)

---

### 📝 Get all users

  

[![GET request to localhost:4000/users](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/hxbcspxlig6ankku30e4.png align="left")](https://youtube.com/live/Uv-jMWV29rU)

We just created 3 users.

---

### 📝 Get a specific user

  
\`\`.

For example, to get the user with id 2, you can make a GET request to [`localhost:4000/users/2`](http://localhost:4000/users/2)

[![GET request to localhost:4000/users/2](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/4y6pdhs1zrtw4kqv1lab.png align="left")](https://youtube.com/live/Uv-jMWV29rU)

---

### 📝 Update a user

  
If you want to update a user, you can make a PUT request to \`\`[localhost:4000/users/\`\`](http://localhost:4000/users/``).

For example, to update the user with id 2, you can make a PUT request to [`localhost:4000/users/2`](http://localhost:4000/users/2) with the body below as a request body:

[![PUT request to localhost:4000/users/2](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/k6gvahavougmdazszjmx.png align="left")](https://youtube.com/live/Uv-jMWV29rU)

To check if the user has been updated, you can make a GET request to [`localhost:4000/users/2`](http://localhost:4000/users/2)

[![GET request to localhost:4000/users/2](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/9qmjcpw9f5i0ht1ibk8c.png align="left")](https://youtube.com/live/Uv-jMWV29rU)

---

### 📝 Delete a user

  
To delete a user, you can make a DELETE request to \`\`[localhost:4000/users/\`\`](http://localhost:4000/users/``).

For Example, to delete the user with id 2, you can make a DELETE request to [`localhost:4000/users/2`](http://localhost:4000/users/2)

[![DELETE request to localhost:4000/users/2](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ffxhozmam7eoa2bjimoj.png align="left")](https://youtube.com/live/Uv-jMWV29rU)

To check if the user has been deleted, you can make a GET request to [`localhost:4000/users`](http://localhost:4000/users)

[![GET request to localhost:4000/users](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/t49gkwvkiz33kj3gy0dw.png align="left")](https://youtube.com/live/Uv-jMWV29rU)

As you can see the user with id 2 is not there anymore.

---

## 🏁 Conclusion

  
We made it! We have built a CRUD rest API in Python, using Flask, SQLAlchemy, Postgres, Docker and Docker compose.

This is just an example, but you can use this as a starting point to build your own application.

All the code is available in the GitHub repository (link in the video description)

[![Build CRUD Rest API using Flask, SQLAlchemy, Postgres, Docker and docker Compose](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/mwqovqifxsjomdtcr12c.png align="left")](https://youtube.com/live/Uv-jMWV29rU)

That's all.

If you have any question, drop a comment below.

[Francesco](https://francescociulla.com)