Developing a Web App with Postgres Database using Flask + React.

This post is intended to provide instructions on how to build a locally hosted website which can connect to a postgres database. Please note, this is not supposed to be a best practice tutorial (I am not a web developer). For quick set up visit github. In the next post, I will cover how to host this site using Kubernetes.

We are going to be working with the following files (github):

web-app
  - back_end
  - front_end
  - sql

Each folder is self explanatory. The front-end we develop using React (JS, HTML) and for the back-end we use Flask (Python). Let us first set up the database.

Postgres DataBase

We will be connecting to a postgres database using the Flask API, so if you do not have postgres installed, then do that now. During installation you may be asked to create a default password for user postgres, remember this, and keep default port 5432. In order to make use of the PostgreSQL interactive terminal program, called psql, you may also have to update your PATH.

In this example, we will create a web app which displays two images side by side and the user is asked to select one. In order to display the images we need to fetch metadata from the database. Once the selection is made me want to post the user choice to the database. Displaying the images will make use of the Google Street View API, you will be required to create a Street View API key from the Google Cloud Platform Service to display images.

Example DB

First of all log in to postgres using the default user and create example database with schema images and users:

postgres=# CREATE DATABASE example;
postgres=# \c example;
example=# CREATE SCHEMA images;
example=# CREATE SCHEMA users;

Copy over the example database from here:

web-app/sql$ psql -U postgres example < ratings
web-app/sql$ psql -U postgres example < images

This creates the following two tables: images.preprocessing and users.perceptions. You can find the structure of the tables in psql using the command:

example=# \d images.preprocessing;

Returning the following:

Back-end: Flask

Flask is a micro web framework written in Python. Follow the instructions for installation of the website and then create the following files (venv folder is the virtual environment and should have been created during installation):

web-app
  - back_end
    - app.py
    - requirements.txt
    - venv/
  - sql
    - ratings
    - images

Our entire back-end will be written within the app.py file. The requirements.txt file acts as instructions for packages to install. run: pip install -r requirements.txt in your shell.

We’ll initialise app.py as follows

import flask
import json
from flask import jsonify
app = flask.Flask(__name__)
app.config["DEBUG"] = True
@app.route('/post_data', methods=['POST'])
def post_data():
    insert_rating(json.loads(request.data))
    return print("Inserted row into DB")
@app.route('/get_data', methods=['GET'])
def get_data():
    response = jsonify(get_image())
    return response
...
if __name__ == '__main__':
    app.run(host=os.getenv("app_host"), port="5000")

We now need to define the two functions: insert_rating(), get_image(). These will use a package psycopg2 to connect to the postgres database. Install the package and add it to the requirements.txt file. Import it into the app.py file along with os, we will use this to retrieve environmental variables.

import flask
import json
from flask import jsonify
import psycopg2
import os
...
dbconn = {'database': os.getenv("db"),
          'user': os.getenv("db_user"),
          'host': os.getenv("db_host"),
          'port': os.getenv("port")}
pg_conn = psycopg2.connect(**dbconn)
pg_cur = pg_conn.cursor()
if __name__ == '__main__':
    app.run(host=os.getenv("app_host"), port="5000")

This establishes a connection to your local database. In your terminal define your environmental variables (default as outlined above):

export app_host='localhost'
export db='example'
export db_user='postgres'
export db_host='localhost'
export db_port=5432

You may also have to add the postgres user password to your environmental variables and app.py file.

Comment out the @ app.route functions and check that the DB connection is working: python app.py. You should get the following output:

The Flask API server is running on IP 127.0.0.1 which is localhost and port 5000, as defined above.

We’ll define the insert_rating() function:

def insert_rating(data):
    sql = """insert into users.perceptions
            select img_1, img_2, perception, choice, user_id, time
            from json_to_recordset(%s) x (img_1 varchar(60),
                                          img_2 varchar(60), 
                                          perception varchar(60),
                                          choice varchar(60), 
                                          user_id varchar(100),
                                          time varchar(100)
            )
        """
    pg_cur.execute(sql, (json.dumps([data]),))
    pg_conn.commit()

Here we are taking the variable data=json.loads(request.data) and inserting it into the database using the sql query and pg_cur.commit().

And the get_image() function:

def get_image():
    sql = """select * from images.preprocessing where exist=1 ORDER
                        BY random() LIMIT 20
           """
    pg_cur.execute(sql)
    data = pg_cur.fetchall()
    return data

Here we fetch data from the database using a select query and pg_cur.fetchall().

Again run: python app.py.

That’s it, the back-end is done. Now let’s move on to the front-end.

Front-end: React

npm is the default package manager for the JavaScript runtime environment Node.js. It consists of a command line client, also called npm, and an online database of public and paid-for private packages, called the npm registry. Follow the instructions for installation on the website. Create a new react app:

web_app$ npm init react-app ./front_end

This will create the following files within front_end/

web_app
  - back_end
  - sql
  - front_end 
    - node_modules/
    - public/
    - src/
    - package.json
    - package-lock.json
    - README.md

Once you have set up the React App, in the terminal run npm start. This will open http://localhost:3000 to view in the browser the src/App.js contents. We will write all our code within the .js and .css files in the src/ folder. The .css file is purely for formatting html.

For our site, we will simply have 3 pages. The home page, another page to display the images, and a contact page. Each page will require its own .js and .css files. The files are available on github here.

web_app
  - front_end 
    - node_modules/
    - public/
    - package.json
    - package-lock.json
    - README.md
    - .env
    - src/
      - App.js
      - App.css
      - Contact.js
      - Contact.css
      - Images.js
      - Images.css
      - Home.js
      - Home.css

We need to install react-youtube and react-bootstrap for embedding YouTube video (on the Home page) and using React bootstrap:

web-app/front_end$ npm install react-youtube
web-app/front_end$ npm install react-bootstrap

We’ll just take a look at the App.js page for an example:

import {useState} from "react";
import Contact from "./Contact"
import Images from "./Images"
import Home from "./Home"
import "./App.css";
require('dotenv').config({path: '../.env'});
function App() {
const [view, setView] = useState(2);
  const [meta, setMeta] = useState({ meta: 'aaa' });
const host = process.env.REACT_APP_BACK_END_HOST;
  const port = process.env.REACT_APP_BACK_END_PORT;
const fetchImage = () => {
    const requestOptions = {
      method: 'GET',
      header: { 'Content-Type': 'application/json'}
    };
    fetch('http://' + host + ':' + port + '/get_data', requestOptions)
      .then(response => response.json())
      .then(result => {
        setMeta({
          meta: result.map(item => ({
            panoid: item[0],
            month: item[1],
            idx: item[2],
            angle: item[3],
            head: item[4],
            cluster: item[5],
            pp: item[6],
            pp_float: item[8]
          }))
        });
      });
  };
return (
    <div className="App">
      <p>perceptions</p>
      <button onClick={() => setView(0)}>contactInfo</button>
      <button onClick={() => { setView(1); fetchImage() }}>images</button>
      <button onClick={() => setView(2)}>home</button>
      { view === 0 ? <Contact/> : null}
      { view === 1 ? <Images setView={setView} fetchImage={fetchImage} meta={meta} /> : null}
      { view === 2 ? <Home setView={setView}/> : null}
    </div>
  );
}
export default App;

The file is in 3 parts, the imports, main body of javascript and the return html rendering. We import each of the other pages, as well as the .css formatting. We won’t be looking at formatting in this blog. We import global variables from require(‘dotenv’).config({path: ‘../.env’}). We therefore need to specify our global variables in a separate file .env. It is important that all variables begin with REACT_APP.

REACT_APP_BACK_END_HOST ='localhost'
REACT_APP_BACK_END_PORT = '5000'
REACT_APP_API_KEY = 'API_KEY_from_GOOGLE_STREET_VIEW_API'

The view constant controls which page we view, each page is enumerated within the html return. The meta constant will hold the image.preprocessing table information after making a fetch request. The host and the port call our Flask API. Using the function fetchImage(), we ping the back-end and request metadata as per the function get_data() in the app.py script. The page has 3 buttons, each leading to a different page. When the ‘images’ button is clicked, the fetchImage() function is called, loading the metadata. The view is also set to 1 and we are therefore directed to the Images.js page. This line:

{ view === 1 ? <Images setView={setView} fetchImage={fetchImage} meta={meta} /> : null}

passes the constants and functions to Images.js and these are called using props.setView, for example.

Once you have configured your pages and gloval variables run the page again using npm start. In this instance, you should see the following page:

And voila, you have successfully developed a Web App with Postgres Database using Flask + React.