In this tutorial we will implement a Python based FastAPI with PostgreSQL CRUD. We will focus on implementing Asynchronous REST Endpoints with the help of Python based module databases that gives simple asyncio support for a range of databases including PostgreSQL. Read in detail <a href="https://www.tutlinks.com/fastapi-with-postgresql-crud-async/">here</a>

Implementing Async REST APIs in FastAPI with PostgreSQL CRUD - TutLinks

Helps you build a CRUD REST API using FastAPI

In this tutorial we will implement a Python based FastAPI with PostgreSQL CRUD. We will focus on implementing Asynchronous REST Endpoints with databases asyncio

computerscience

Computer Science

<ul><li>Post author: </li><li>Reading time: 19 mins read </li> </ul>

In this tutorial we will implement a Python based FastAPI with PostgreSQL CRUD. We will focus on implementing Asynchronous REST Endpoints with the help of Python based module databases that gives simple asyncio support for a range of databases including PostgreSQL.

Full Source Code of this tutorial is available on <code>fastapi-postgresql-azure-deploy </code>branch of this <a href="https://github.com/windson/fastapi/tree/fastapi-postgresql-azure-deploy">repository </a>.

Run the following commands to create our development workspace and launch VS Code that opens our workspace as active directory.

Create & Activate Python Virtual Environment

In VS Code navigate to <code>View </code> and click <code>Terminal </code> to launch command prompt. Run the below scripts in Terminal to create <a href="https://www.tutlinks.com/virtual-environments-in-python-for-beginners/">Python virtual environment </a>.

If you are a Windows based OS Users, run the following command from terminal to create <a href="http://bit.ly/2Qt3z3l">Python virtual environment </a>.

If you are a Linux based OS Users, run the following command from terminal.

Run the following command in terminal to install FastAPI, Uvicorn, Gunicorn and <a href="https://github.com/encode/databases">databases </a> packages

Note that we are using <a href="https://github.com/encode/databases">databases </a> package as it uses <a href="https://github.com/MagicStack/asyncpg">asyncpg </a> as an interface to talk to PostgreSQL database. And as per official documentation of <code>asyncpg </code>, <a href="http://magic.io/blog/asyncpg-1m-rows-from-postgres-to-python/">asyncpg is 3x faster than other Python based PostgreSQL drivers psycopg2 and aipog </a>.

Run the following command to freeze dependencies to <code>requirements.txt </code>file in terminal.

If you want to use <code>sqlite </code>database for development purpose, you need to install <code>sqlite </code>module support for <code>databases </code>package. Run the following command to install <code>sqlite </code>extension for <code>databases </code>module.

To speed up things, I’ll be using the code from official documentation of <a href="https://fastapi.tiangolo.com/advanced/async-sql-databases">FastAPI using Async SQL Databases </a>. So following are the enhancements that my implementation has, in addition to what is available from code base from official reference.

<ul><li>Build <code>DATABASE_URL </code> for PostgreSQL server via environment variables. </li><li>In addition to Add and Get all notes, I cover REST End Points to<ul><li>Get a note by ID </li><li>Get Paginated notes collection </li><li>Update note </li><li>Delete note </li> </ul> </li> </ul>

Let’s Code FastAPI with PostgreSQL CRUD from the scratch

Add a file <code>main.py </code> in the root directory of the workspace.

To start with, in the <code>main.py </code> file add the following references.

Configure Database FastAPI PostgreSQL Connection String

Add the following line to <code>main.py </code> to define connection string for our application to talk to sqlite database.

In case you have a PostgreSQL server then add the following lines to <code>main.py </code> and configure the values accordingly for environment variables db_username, db_password, host_server, db_server_port, database_name, ssl_mode.

If your database server required SSL then replace <code>prefer </code>with <code>required </code>in the <code>DATABASE_URL </code> connection string for PostgreSQL.

Simply put, the FastAPI PostgreSQL Connection string follows the following format.

Once we have <code>DATABASE_URL </code> url built, create instance of <a href="https://github.com/encode/databases">database </a> by adding the following line to <code>main.py </code>

We will create a table named <code>notes </code>. The purpose is to store detail of note in <code>text </code>column and its status in <code>completed </code> column. We will use <code>sqlalchemy </code> to define the <code>notes </code> table that resembles the relational database schema in the form of Python code. Add the following lines to define schema for <code>notes </code> table.

If you are using sqlite database, then add the following lines to <code>main.py </code>

If you are having <code>DATABASE_URL </code> built to point to PostgreSQL server database, then add the following lines to <code>main.py </code>

Add the following models to <code>main.py </code>. These are models built with Pydantic’s <code>BaseModel </code>. <a href="https://github.com/samuelcolvin/pydantic">Pydantic  </a>models help you define request payload models and response models in Python Class object notation. FastAPI also uses these models in its auto generated OpenAPI Specs (Swagger) to indicate response and request payload models.

<code>NoteIn </code> is the model in its JSON form used as payload to Create or Update note endpoints. <code>Note </code> is the model in its JSON form will be used as response to retrieve notes collection or a single note given its id.

The JSON notation of these models will look something similar as mentioned below.

JSON for <code>NoteIn </code>model.

JSON for <code>Note </code>model.

In order for our REST API endpoints to be consumed in client applications such as Vue, React, Angular or any other Web applications that are running on other domains, we should tell our FastAPI to allow requests from the external callers to the endpoints of this FastAPI application. We can enable <a href="https://en.wikipedia.org/wiki/Cross-origin_resource_sharing,">CORS </a> (Cross Origin Resource Sharing) either at application level or at specific endpoint level. But in this situation we will add the following lines to <code>main.py </code> to enable CORS at the application level by allowing requests from all origins specified by <code>allow_origins=[*] </code>.

<code>allow_origins=[*] </code> is not recommended for Production purposes. It is recommended to have specified list of origins such as mentioned below.

For more details, refer the official documentation on <a href="https://fastapi.tiangolo.com/tutorial/cors/">How to configure CORS for FastAPI </a>

FastAPI can be run on multiple worker process with the help of Gunicorn server with the help of <code>uvicorn.workers.UvicornWorker </code> worker class. Every worker process starts its instance of FastAPI application on its own Process Id. In order to ensure every instance of application communicates to the database, we will connect and disconnect to the database instance in the <a href="https://fastapi.tiangolo.com/advanced/events/">FastAPI events </a> <code>startup </code> and <code>shutdown </code> respectively. So add the following code to <code>main.py </code> to do that.

We will use <code>POST </code> HTTP Verb available as <code>post </code> method of FastAPI’s instance variable <code>app </code> to create/ insert a new note in our <code>notes </code> table.

The status code on successful creation of note will be 201. This can be seen as an argument <a href="https://fastapi.tiangolo.com/tutorial/response-status-code/#about-http-status-codes">status_code </a> passed to post method which accepts integer value held by status.HTTP_201_CREATED. Add the following code to <code>main.py </code> to add a note to the table.

We will use <code>PUT </code> HTTP Verb available as <code>put </code> method of FastAPI’s instance variable <code>app </code>to update / modify an existing note in our <code>notes </code> table. Add the following code to <code>main.py </code> to modify a note from the <code>notes </code> table.

Get Paginated List of Notes using HTTP Verb GET

We will use <code>GET </code> HTTP Verb available as <code>get </code> method of FastAPI’s instance variable <code>app </code>to retrieve paginated 🗐 collection of notes available in our <code>notes </code> table. Add the following code to <code>main.py </code> to get list of notes from the table.

Here the <code>skip </code>and <code>take </code>arguments will define how may notes to be skipped and how many notes to be returned in the collection respectively. If you have a total of 13 notes in your database and if you provide <code>skip </code>a value of 10 and <code>take </code>a value of 20, then only 3 notes will be returned. <code>skip </code>will ignore the value based on the identity of the collection starting from old to new.

Get single Note Given its Id using HTTP Verb GET

We will again use the <code>GET </code> HTTP Verb available as <code>get </code> method of FastAPI’s instance variable <code>app </code> to retrieve a single note identified by provided <code>id </code> in the request as a <code>note_id </code> query parameter. Add the following code to <code>main.py </code> to get a note given its id.

Delete single Note Given its Id using HTTP Verb DELETE

We will use <code>DELETE </code> HTTP Verb available as <code>delete </code> method of FastAPI’s instance variable <code>app </code> to permanently delete an existing note in our <code>notes </code> table. Add the following code to <code>main.py </code> to wipe off the note permanently given <code>note_id </code> as query parameter.

Save changes to <code>main.py </code> and run the following command in the terminal to spin up the FastAPI app.

With the above command, we are invoking the call to the Uvicorn ASGI server with the following infrastructure settings.

<ul><li><code>host 127.0.0.1 </code> means we are configuring uvicorn to run our application on the localhost of the PC. The other possible values for host parameter is 0.0.0.0 or localhost. 0.0.0.0 is recommended when deploying the FastAPI to production environments. </li><li><code>port 8000 </code> is the port on which we want our application to run. If you have any other application or service already running on this port, the above command will fail to execute. In such situation, try to change it to any other four digit number of your choice that is found to be freely available for the application to consume. </li><li><code>reload </code> It is recommended to set this flag for the development purposes only. Enabling this flag will automatically restart the uvicorn server with any changes you make to your code while in development. It is obvious that, in case there are any run time failures, you will quickly identify those changes from the error trace that caused the failure of uvicorn server to restart. </li><li><code>main:app </code> This follows a pattern as detailed below.<ul><li><code>main </code> is the module where the FastAPI is initialized. In our case, all we have is <code>main.py </code> at the root level. If you are initializing the FastAPI variable somewhere under the other directories, you need to add an <code>__init__.py </code>file and expose that module so that uvicorn can properly identify the configuration. In our case <code>main </code>becomes the module. </li><li><code>app </code> is the name of the variable which is assigned with the instance of FastAPI. You are free to change these names but reflect the same syntax that follows <code>module-name:fastapi-initialization-variable </code>pattern. </li> </ul> </li> </ul>

There are three ways to perform CRUD for FastAPI REST Endpoints.

<ul><li><a href="https://www.postman.com/downloads/,">Postman </a>, a REST Client (in fact a lot more than a REST Client) to perform calls to REST APIs </li><li>OpenAPI User Interface accessible via <a href="http://127.0.0.1:8000/docs,">/docs </a> (Swagger UI) to perform CRUD operations by clicking <code>Try it out </code> button available for every end point </li><li><a href="https://en.wikipedia.org/wiki/CURL,">cURL </a> commands via a command terminal. </li> </ul>

If you want to explore the hardcore programmer in you, I recommend trying out <code>cURL </code>.

Let’s execute the following cURL commands to perform CRUD on our FastAPI Async REST Endpoints to Create, Read, Update and Delete data into PostgreSQL database.

Run the following command in the command terminal to Add a single Note.

In the above command are commanding cURL with following args:

<ul><li><code>-X </code> indicates the presence of one of the <code>HTTP Verbs </code> or <a href="https://developer.mozilla.org/en-US/docs/Web/HTTP/Methods,"><code>HTTP Request Methods </code> </a> followed by URL. The values include POST, GET, PUT, DELETE, PATCH. </li><li><code>-H </code> indicates header. Headers are optional unless required as specified in API specifications. Some times though they are optional, specifying wrong headers may not guarantee the expected result processed by cURL. </li><li><code>-d </code> is mostly used for non GET HTTP verbs and indicates data or payload that is required by the request. </li><li><code>^ </code> is a line break added just for readability. It works only on Windows OS command terminals. If you are on any Mac or Linux distributions, replace <code>^ </code> with <code>\ </code> (backward slash). Alternatively, you can have the cURL sent in single line without having any <code>^ </code> line breaks. </li> </ul>

Implementing Async REST APIs in FastAPI with PostgreSQL CRUD - TutLinks

Implementing Async REST APIs in FastAPI with PostgreSQL CRUD - TutLinks

Related collections

Other curated ideas on this topic: