Now, we will create the PostgreSQL database that we will use as a repository for our API. You will have to download and install a PostgreSQL database in case you aren't already running it in your computer or in a development server. You can download and install this database management system from its web page: http://www.postgresql.org. In case you are working with macOS, Postgres.app provides a really easy way to install and use PostgreSQL on this operating system: http://postgresapp.com:

We will use the PostgreSQL command-line tools to create a new database named messages. In case you already have a PostgreSQL database with this name, make sure that you use another name in all the commands and configurations. You can perform the same task with any PostgreSQL GUI tool. In case you are developing on Linux, it is necessary to run the commands as the postgres user. Run the following command in macOS or Windows to create a new database named messages. Note that the command won't produce any output:

createdb messages

In Linux, run the following command to use the postgres user:

sudo -u postgres createdb messages

Now, we will use the psql command-line tool to run some SQL statements to create a specific user that we will use in Flask and assign the necessary roles for it. In macOS or Windows, run the following command to launch psql:

psql

In Linux, run the following command to use the postgres user:

sudo -u psql

Then, run the following SQL statements and finally enter q to exit the psql command-line tool. Replace user_name with your desired user name to use in the new database and password with your chosen password. We will use the user name and password in the Flask configuration. You don't need to run the steps in case you are already working with a specific user in PostgreSQL and you have already granted privileges to the database for the user. You will see the output indicating that the permission was granted.

CREATE ROLE user_name WITH LOGIN PASSWORD 'password';
GRANT ALL PRIVILEGES ON DATABASE messages TO user_name;
ALTER USER user_name CREATEDB;
q

It is necessary to install the Psycopg 2 package (psycopg2). This package is a Python-PostgreSQL Database Adapter and SQLAlchemy will use it to interact with our recently created PostgreSQL database.

Once we made sure that the PostgreSQL bin folder is included in the PATH environmental variable, we just need to run the following command to install this package:

pip install psycopg2

The last lines of the output will indicate that the psycopg2 package has been successfully installed:

Collecting psycopg2
Installing collected packages: psycopg2
  Running setup.py install for psycopg2
Successfully installed psycopg2-2.6.2

In case you are using the same virtual environment that we created for the previous example, the api folder already exists. If you create a new virtual environment, create a folder named api within the root folder for the created virtual environment.

Create a new config.py file within the api folder. The following lines show the code that declares variables that determine the configuration for Flask and SQLAlchemy. The SQL_ALCHEMY_DATABASE_URI variable generates an SQLAlchemy URI for the PostgreSQL database.

Make sure you specify the desired database name in the value for DB_NAME and that you configure the user, password, host, and port based on your PostgreSQL configuration. In case you followed the previous steps, use the settings specified in these steps. The code file for the sample is included in the restful_python_chapter_06_01 folder:

import os 
 
 
basedir = os.path.abspath(os.path.dirname(__file__)) 
DEBUG = True 
PORT = 5000 
HOST = "127.0.0.1" 
SQLALCHEMY_ECHO = False 
SQLALCHEMY_TRACK_MODIFICATIONS = True 
SQLALCHEMY_DATABASE_URI = "postgresql://{DB_USER}:{DB_PASS}@{DB_ADDR}/{DB_NAME}".format(DB_USER="user_name", DB_PASS="password", DB_ADDR="127.0.0.1", DB_NAME="messages") 
SQLALCHEMY_MIGRATE_REPO = os.path.join(basedir, 'db_repository') 

We will specify the module created earlier as an argument to a function that will create a Flask app. This way, we have one module that specifies all the values for the different configuration variables and another module that creates a Flask app. We will create the Flask app factory as our final step towards our new API.