API Template with Flask-SQLAlchemy-PostgreSQL

Building an API from scratch is time consuming so I decided to create a template for more time building and less time configuring. Here's a run through of how it works. We will be working with Python, Flask, SQLalchemy, Marshmellow, PostgreSQL, and Ubuntu terminal.

IMPORTANT NOTE: If you don't have PostgreSQL installed, follow the steps here and install it before continuing. You will need the user, password, and dbname to connect your Flask API to PostgreSQL.

Let's get started!

If you haven't already, fork and clone the repo:

git clone [repoURL]

CD into the repo and run:

pipenv install
pipenv shell

Test API server:

To make sure the Flask API application is working run:

python app.py

Your terminal output should look something like this:

Open up a chrome browser and navigate to localhost:5555. You should see this message:

Press CTRL+C to exit the server and be sure to resolve any errors before continuing...

Connect API to PostgreSQL Database Server

To connect the API to your PostgreSQL database in your local machine you'll need to use a URL string that looks something like this:

f'postgresql://{user}:{password}@localhost:5432/{dbname}'

In your .env file create the following variables: USER, PASSWORD, & DBNAME

In the config.py file, use the variable names in the URL string. The port:5432 is the default port your PostgreSQL database uses, be sure to change it if the port is different. The URL string is what will connect your database to your API. The final results in the config.py file should look something like this:

FLASK

In order to test the connection we need to initiate flask. Run the following commands and make sure there are no error after each command.

Make sure you are in python shell before running the flask commands. An error will be thrown if you're not in it. The command is pipenv shell.

flask db init
flask db migrate -m 'initial migration'
flask db upgrade

You should see a folder called migrations and inside a versions folder with the script you created with initiating flask. The version folder will have all the versions you create when communicating with the database.

Testing the API connection with PostgreSQL server

PostgreSQL database server is separate from the flask API server. When starting the database, you can start it from any root user in the ubuntu terminal. It doesn't need to be at the root of the projects directory but that is okay too.

Start your PostgreSQL server in the ubuntu terminal:

sudo service postgresql start
psql -U {user} -d {dbname}

Once your in the PostgreSQL server, you should see something like this:

"animals" will be the name of the database. Now run this:

\d

This command should list the table that flask created in the database when you ran the flasks commands. You should see the "users" table since the repo has a users model. It should look something like this:

USER ACCOUNT FEATURE

In order to use the user account feature, a secret key has to be created to allow cors to receive and send request securely.

In a new terminal run this command:

python -c 'import os; print(os.urandom(16))'

Use the given string and assign it a variable in the .env file. I called mine "SECRET_KEY" but you can call it anything you want.

The string look like this:

b"\x13'\xe3\xde \xa1T\xf3w\xf0\xee\xe1\x82BD\x8f"

Now go to the config.py file and retrieve the key and assign it to app.secrete_key. It should look like this:

To test this, open up postman and make a post request to localhost:5555/signup to create a user. Send an object with the following key/value pairs:

{
"name": "sandy",
"username":"sandymoore",
"email":"sandymoore@gmail.com",
"password":"pass124"
}

You should get a response that looks like this:

And the cookie session, under the "Cookies" tab should look something like this:

You can now delete the test folder or use it to test your routes but the test folder is no longer needed. That's it! You are now ready to get started with your flask API project. Happy coding!