#55: How to build a solid backend for a scalable app?

Now that we have a functional app with a decent interface, we can focus on the backend database storage. I used Postgres to log data of the users, images processed and alerts sent from the system.

What did I achieve today ? I created postgres tables to store data collected from the app. When the user is on the platform, and enters their details to send alerts, two tables are populated (alerts, and users). There are two tables: inspections and images that store details like id, timestamp, and defect metrics. I have included some more images in the Appendix to de-clutter this space.

What can you expect ? You'll find a quick tutorial on Postgres and Docker in this post.

First, let's setup Postgres (on a Mac):

1. If you have Homebrew installed you can install PostgreSQL using the following command:

brew install postgresql        

If you don't have it installed, you can use this link: https://brew.sh/

2. Start PostgreSQL using:

brew services start postgresql        

3. Create a profile:

CREATE USER my_user WITH PASSWORD 'my_password';        

4. Create a Database:

CREATE DATABASE my_database;        

5. Connecting with Python:

# Import the library 
import psycopg2

# Establish the connection with the database 
conn = psycopg2.connect(
    dbname="my_database",
    user="my_user",  
    password="my_password",
    host="localhost",
    port="5432"
)        

6. Creating a table

cursor = conn.cursor()
cursor.execute("""
    CREATE TABLE IF NOT EXISTS users (
      id SERIAL PRIMARY KEY,
      email VARCHAR(255) UNIQUE NOT NULL,
      name VARCHAR(100),
      phone_number VARCHAR(20),
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    );
    """)
conn.commit()
cursor.close()
conn.close()        

I created a my_database.py file that stored all functions to create tables, insert data into the tables and dropping them. One important SQL tip is to include the 'CASCADE' keyword at the end of the query to successfully drop all the tables. Without this, you won't be able to drop them, because these tables are related to each other, and therefore need to eliminate the 'bond' completely.

These functions were imported to the `main_app.py` and included at relevant places in the existing code.

# Use keyword CASCADE 
cursor.execute("DROP TABLE IF EXISTS alerts CASCADE;")         cursor.execute("DROP TABLE IF EXISTS images CASCADE;")         cursor.execute("DROP TABLE IF EXISTS inspections CASCADE;")         cursor.execute("DROP TABLE IF EXISTS users CASCADE;")        

Now, let's setup Docker

Let's first know what really is. Docker is a platform designed to make it easier to create, deploy, and run apps in isolated environments called containers. A container bundles the 'whole app' with its dependencies so that it runs the same way in any environment.

1. We'll start by downloading Docker Desktop from this link: (https://www.docker.com/products/docker-desktop/)
2. Once downloaded, start Docker. You can check it it is running by writing into the terminal:

docker version        

3. Create a Dockerfile in the root of your project directory. This file contains instructions that tell Docker how to build the image. it looks like this:

4. Now that we have instructions, we'll build a Docker image by entering this command in the terminal:

docker build -t streamlit-app .        

After this, you'll see a successful build in the Docker app:

5. Finally, to run the container, use this code in the terminal:

docker run -p 8501:8501 streamlit-app        

A random useful tip I found:

# To automatically create a requirements file: 
pip freeze > requirements.txt         

What next?

Need to create an Amazon S3 bucket to store the data on images. Currently, the data isn't much, but to replicate a real industrial app, need to create a solid backend.

That's all folks !

Seeya !

Appendix