{"id":5476,"date":"2022-07-25T16:29:20","date_gmt":"2022-07-25T16:29:20","guid":{"rendered":"https:\/\/codevoweb.com\/?p=5476"},"modified":"2023-05-05T20:27:25","modified_gmt":"2023-05-05T20:27:25","slug":"crud-restful-api-server-with-python-fastapi-and-postgresql","status":"publish","type":"post","link":"https:\/\/codevoweb.com\/crud-restful-api-server-with-python-fastapi-and-postgresql\/","title":{"rendered":"CRUD RESTful API Server with Python, FastAPI, and PostgreSQL"},"content":{"rendered":"\n<p>This article will teach you how to create a CRUD RESTful API with Python, FastAPI, <a href=\"https:\/\/docs.sqlalchemy.org\/\" target=\"_blank\" rel=\"noreferrer noopener\">SQLAlchemy ORM<\/a>, <a href=\"https:\/\/pydantic-docs.helpmanual.io\/usage\/settings\/\" target=\"_blank\" rel=\"noreferrer noopener\">Pydantic<\/a>, <a href=\"https:\/\/alembic.sqlalchemy.org\/\" target=\"_blank\" rel=\"noreferrer noopener\">Alembic<\/a>, PostgreSQL, and Docker-compose to perform the basic <strong>Create<\/strong>\/<strong>Read<\/strong>\/<strong>Update<\/strong>\/<strong>Delete<\/strong> operations against a database.<\/p>\n\n\n\n<p>API with Python, FastAPI, SQLAlchemy ORM, Alembic, and PostgreSQL:<\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li><a href=\"\/restful-api-with-python-fastapi-access-and-refresh-tokens\">RESTful API with Python &amp; FastAPI: Access and Refresh Tokens<\/a><\/li>\n\n\n\n<li><a href=\"\/restful-api-with-python-fastapi-send-html-emails\">RESTful API with Python &amp; FastAPI: Send HTML Emails<\/a><\/li>\n\n\n\n<li><a href=\"\/crud-restful-api-server-with-python-fastapi-and-postgresql\">CRUD RESTful API Server with Python, FastAPI, and PostgreSQL<\/a><\/li>\n<\/ol>\n\n\n\n<p>Related articles:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><a href=\"\/node-express-typeorm-postgresql-rest-api\">Node.js, Express, TypeORM, PostgreSQL: CRUD Rest API<\/a><\/li>\n\n\n\n<li><a href=\"\/crud-restful-api-server-with-golang-and-mongodb\">Build CRUD RESTful API Server with Golang, Gin, and MongoDB<\/a><\/li>\n\n\n\n<li><a href=\"\/nextjs-full-stack-app-with-react-query-and-graphql-codegen\">Next.js Full-Stack App with React Query, and GraphQL-CodeGen<\/a><\/li>\n\n\n\n<li><a href=\"\/fullstack-trpc-crud-application-with-nodejs-and-reactjs\">Build Full-Stack tRPC CRUD Application with Node.js, and React.js<\/a><\/li>\n\n\n\n<li><a href=\"\/graphql-crud-api-nextjs-mongodb-typegraphql\">GraphQL CRUD API with Next.js, MongoDB, and TypeGraphQL<\/a><\/li>\n<\/ul>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"850\" height=\"478\" src=\"https:\/\/codevoweb.com\/wp-content\/uploads\/2022\/07\/CRUD-RESTful-API-Server-with-Python-FastAPI-and-PostgreSQL.webp\" alt=\"CRUD RESTful API Server with Python, FastAPI, and PostgreSQL\" class=\"wp-image-5548\" srcset=\"https:\/\/codevoweb.com\/wp-content\/uploads\/2022\/07\/CRUD-RESTful-API-Server-with-Python-FastAPI-and-PostgreSQL.webp 850w, https:\/\/codevoweb.com\/wp-content\/uploads\/2022\/07\/CRUD-RESTful-API-Server-with-Python-FastAPI-and-PostgreSQL-300x169.webp 300w, https:\/\/codevoweb.com\/wp-content\/uploads\/2022\/07\/CRUD-RESTful-API-Server-with-Python-FastAPI-and-PostgreSQL-768x432.webp 768w, https:\/\/codevoweb.com\/wp-content\/uploads\/2022\/07\/CRUD-RESTful-API-Server-with-Python-FastAPI-and-PostgreSQL-100x56.webp 100w, https:\/\/codevoweb.com\/wp-content\/uploads\/2022\/07\/CRUD-RESTful-API-Server-with-Python-FastAPI-and-PostgreSQL-700x394.webp 700w\" sizes=\"auto, (max-width: 850px) 100vw, 850px\" \/><\/figure>\n\n\n<style>.kb-table-of-content-nav.kb-table-of-content-id_b8b4ed-6e .kb-table-of-content-wrap{padding-top:10px;padding-right:10px;padding-bottom:10px;padding-left:10px;border-top:1px solid #abb8c3;border-right:1px solid #abb8c3;border-bottom:1px solid #abb8c3;border-left:1px solid #abb8c3;}.kb-table-of-content-nav.kb-table-of-content-id_b8b4ed-6e .kb-table-of-contents-title-wrap{padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;}.kb-table-of-content-nav.kb-table-of-content-id_b8b4ed-6e .kb-table-of-contents-title-wrap{color:#ffffff;}.kb-table-of-content-nav.kb-table-of-content-id_b8b4ed-6e .kb-table-of-contents-title{color:#ffffff;font-weight:regular;font-style:normal;}.kb-table-of-content-nav.kb-table-of-content-id_b8b4ed-6e .kb-table-of-content-wrap .kb-table-of-content-list{color:#ffffff;font-weight:regular;font-style:normal;margin-top:10px;margin-right:0px;margin-bottom:0px;margin-left:-5px;}@media all and (max-width: 1024px){.kb-table-of-content-nav.kb-table-of-content-id_b8b4ed-6e .kb-table-of-content-wrap{border-top:1px solid #abb8c3;border-right:1px solid #abb8c3;border-bottom:1px solid #abb8c3;border-left:1px solid #abb8c3;}}@media all and (max-width: 767px){.kb-table-of-content-nav.kb-table-of-content-id_b8b4ed-6e .kb-table-of-content-wrap{border-top:1px solid #abb8c3;border-right:1px solid #abb8c3;border-bottom:1px solid #abb8c3;border-left:1px solid #abb8c3;}}<\/style>\n\n\n<h2 class=\"wp-block-heading\">Python, FastAPI, PostgreSQL CRUD API Overview<\/h2>\n\n\n\n<p>We&#8217;ll build a CRUD RESTful API with Python and FastAPI to perform <strong>Create<\/strong>\/<strong>Read<\/strong>\/<strong>Update<\/strong>\/<strong>Delete<\/strong> operations against a PostgreSQL database.<\/p>\n\n\n\n<p>You can <a href=\"https:\/\/www.getpostman.com\/collections\/969286ffb3ee641b3a83\" target=\"_blank\" rel=\"noreferrer noopener\">import the Postman collection<\/a> I used in testing the API into your Postman to make your life easier.<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table><thead><tr><th>RESOURCE<\/th><th>HTTP METHOD<\/th><th>ROUTE<\/th><th>DESCRIPTION<\/th><\/tr><\/thead><tbody><tr><td>posts<\/td><td>GET<\/td><td><mark style=\"background-color:rgba(0, 0, 0, 0)\" class=\"has-inline-color has-vivid-purple-color\">\/api\/posts<\/mark><\/td><td>Fetch all posts<\/td><\/tr><tr><td>posts<\/td><td>POST<\/td><td><mark style=\"background-color:rgba(0, 0, 0, 0)\" class=\"has-inline-color has-vivid-purple-color\">\/api\/posts<\/mark><\/td><td>Create a new post<\/td><\/tr><tr><td>posts<\/td><td>GET<\/td><td><mark style=\"background-color:rgba(0, 0, 0, 0)\" class=\"has-inline-color has-vivid-purple-color\">\/api\/posts\/:postId<\/mark><\/td><td>Request a single post<\/td><\/tr><tr><td>posts<\/td><td>PUT<\/td><td><mark style=\"background-color:rgba(0, 0, 0, 0)\" class=\"has-inline-color has-vivid-purple-color\">\/api\/posts\/:<mark style=\"background-color:rgba(0, 0, 0, 0)\" class=\"has-inline-color has-vivid-purple-color\">postId<\/mark><\/mark><\/td><td>Update a post<\/td><\/tr><tr><td>posts<\/td><td>DELETE<\/td><td><mark style=\"background-color:rgba(0, 0, 0, 0)\" class=\"has-inline-color has-vivid-purple-color\">\/api\/posts\/:<mark style=\"background-color:rgba(0, 0, 0, 0)\" class=\"has-inline-color has-vivid-purple-color\">postId<\/mark><\/mark><\/td><td>Remove a post<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<span id=\"ezoic-pub-video-placeholder-107\"><\/span>\n\n\n\n<p>-You can create a new post in the database by making a <strong>POST<\/strong> request to the<code>\/api\/posts<\/code> endpoint with the necessary data.<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"870\" src=\"https:\/\/codevoweb.com\/wp-content\/uploads\/2022\/07\/python-fastapi-postgresql-pydantic-and-sqlalchemy-crud-restful-api-create-new-post-1024x870.webp\" alt=\"python, fastapi, postgresql, pydantic, and sqlalchemy crud restful api create new post\" class=\"wp-image-5483\" srcset=\"https:\/\/codevoweb.com\/wp-content\/uploads\/2022\/07\/python-fastapi-postgresql-pydantic-and-sqlalchemy-crud-restful-api-create-new-post-1024x870.webp 1024w, https:\/\/codevoweb.com\/wp-content\/uploads\/2022\/07\/python-fastapi-postgresql-pydantic-and-sqlalchemy-crud-restful-api-create-new-post-300x255.webp 300w, https:\/\/codevoweb.com\/wp-content\/uploads\/2022\/07\/python-fastapi-postgresql-pydantic-and-sqlalchemy-crud-restful-api-create-new-post-768x652.webp 768w, https:\/\/codevoweb.com\/wp-content\/uploads\/2022\/07\/python-fastapi-postgresql-pydantic-and-sqlalchemy-crud-restful-api-create-new-post-100x85.webp 100w, https:\/\/codevoweb.com\/wp-content\/uploads\/2022\/07\/python-fastapi-postgresql-pydantic-and-sqlalchemy-crud-restful-api-create-new-post-530x450.webp 530w, https:\/\/codevoweb.com\/wp-content\/uploads\/2022\/07\/python-fastapi-postgresql-pydantic-and-sqlalchemy-crud-restful-api-create-new-post.webp 1209w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<p>-To update a post in the database, you need to make a <strong>PUT<\/strong> request to the<code>\/api\/posts\/:postId<\/code> endpoint with the edited data.<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"870\" src=\"https:\/\/codevoweb.com\/wp-content\/uploads\/2022\/07\/python-fastapi-postgresql-pydantic-and-sqlalchemy-crud-restful-api-update-a-post-1024x870.webp\" alt=\"python, fastapi, postgresql, pydantic, and sqlalchemy crud restful api update a post\" class=\"wp-image-5484\" srcset=\"https:\/\/codevoweb.com\/wp-content\/uploads\/2022\/07\/python-fastapi-postgresql-pydantic-and-sqlalchemy-crud-restful-api-update-a-post-1024x870.webp 1024w, https:\/\/codevoweb.com\/wp-content\/uploads\/2022\/07\/python-fastapi-postgresql-pydantic-and-sqlalchemy-crud-restful-api-update-a-post-300x255.webp 300w, https:\/\/codevoweb.com\/wp-content\/uploads\/2022\/07\/python-fastapi-postgresql-pydantic-and-sqlalchemy-crud-restful-api-update-a-post-768x652.webp 768w, https:\/\/codevoweb.com\/wp-content\/uploads\/2022\/07\/python-fastapi-postgresql-pydantic-and-sqlalchemy-crud-restful-api-update-a-post-100x85.webp 100w, https:\/\/codevoweb.com\/wp-content\/uploads\/2022\/07\/python-fastapi-postgresql-pydantic-and-sqlalchemy-crud-restful-api-update-a-post-530x450.webp 530w, https:\/\/codevoweb.com\/wp-content\/uploads\/2022\/07\/python-fastapi-postgresql-pydantic-and-sqlalchemy-crud-restful-api-update-a-post.webp 1207w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<p>-You can request a single post from the database by making a <strong>GET<\/strong> request to the <code>\/api\/posts\/:postId<\/code> endpoint.<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"868\" src=\"https:\/\/codevoweb.com\/wp-content\/uploads\/2022\/07\/python-fastapi-postgresql-pydantic-and-sqlalchemy-crud-restful-api-get-a-single-post-1024x868.webp\" alt=\"python, fastapi, postgresql, pydantic, and sqlalchemy crud restful api get a single post\" class=\"wp-image-5485\" srcset=\"https:\/\/codevoweb.com\/wp-content\/uploads\/2022\/07\/python-fastapi-postgresql-pydantic-and-sqlalchemy-crud-restful-api-get-a-single-post-1024x868.webp 1024w, https:\/\/codevoweb.com\/wp-content\/uploads\/2022\/07\/python-fastapi-postgresql-pydantic-and-sqlalchemy-crud-restful-api-get-a-single-post-300x254.webp 300w, https:\/\/codevoweb.com\/wp-content\/uploads\/2022\/07\/python-fastapi-postgresql-pydantic-and-sqlalchemy-crud-restful-api-get-a-single-post-768x651.webp 768w, https:\/\/codevoweb.com\/wp-content\/uploads\/2022\/07\/python-fastapi-postgresql-pydantic-and-sqlalchemy-crud-restful-api-get-a-single-post-100x85.webp 100w, https:\/\/codevoweb.com\/wp-content\/uploads\/2022\/07\/python-fastapi-postgresql-pydantic-and-sqlalchemy-crud-restful-api-get-a-single-post-531x450.webp 531w, https:\/\/codevoweb.com\/wp-content\/uploads\/2022\/07\/python-fastapi-postgresql-pydantic-and-sqlalchemy-crud-restful-api-get-a-single-post.webp 1208w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<p>-You can also fetch all the posts from the database or a paginated list of the posts by making a <strong>GET<\/strong> request to the <code>\/api\/posts?page=1&amp;limit=10<\/code> endpoint.<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"867\" src=\"https:\/\/codevoweb.com\/wp-content\/uploads\/2022\/07\/python-fastapi-postgresql-pydantic-and-sqlalchemy-crud-restful-api-fetch-all-posts-1024x867.webp\" alt=\"python, fastapi, postgresql, pydantic, and sqlalchemy crud restful api fetch all posts\" class=\"wp-image-5486\" srcset=\"https:\/\/codevoweb.com\/wp-content\/uploads\/2022\/07\/python-fastapi-postgresql-pydantic-and-sqlalchemy-crud-restful-api-fetch-all-posts-1024x867.webp 1024w, https:\/\/codevoweb.com\/wp-content\/uploads\/2022\/07\/python-fastapi-postgresql-pydantic-and-sqlalchemy-crud-restful-api-fetch-all-posts-300x254.webp 300w, https:\/\/codevoweb.com\/wp-content\/uploads\/2022\/07\/python-fastapi-postgresql-pydantic-and-sqlalchemy-crud-restful-api-fetch-all-posts-768x650.webp 768w, https:\/\/codevoweb.com\/wp-content\/uploads\/2022\/07\/python-fastapi-postgresql-pydantic-and-sqlalchemy-crud-restful-api-fetch-all-posts-100x85.webp 100w, https:\/\/codevoweb.com\/wp-content\/uploads\/2022\/07\/python-fastapi-postgresql-pydantic-and-sqlalchemy-crud-restful-api-fetch-all-posts-531x450.webp 531w, https:\/\/codevoweb.com\/wp-content\/uploads\/2022\/07\/python-fastapi-postgresql-pydantic-and-sqlalchemy-crud-restful-api-fetch-all-posts.webp 1208w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<p>-Finally, you can also delete a post in the database by making a <strong>DELETE<\/strong> request to the <code>\/api\/posts\/:postId<\/code> endpoint.<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"868\" src=\"https:\/\/codevoweb.com\/wp-content\/uploads\/2022\/07\/python-fastapi-postgresql-pydantic-and-sqlalchemy-crud-restful-api-delete-a-post-1024x868.webp\" alt=\"python, fastapi, postgresql, pydantic, and sqlalchemy crud restful api delete a post\" class=\"wp-image-5487\" srcset=\"https:\/\/codevoweb.com\/wp-content\/uploads\/2022\/07\/python-fastapi-postgresql-pydantic-and-sqlalchemy-crud-restful-api-delete-a-post-1024x868.webp 1024w, https:\/\/codevoweb.com\/wp-content\/uploads\/2022\/07\/python-fastapi-postgresql-pydantic-and-sqlalchemy-crud-restful-api-delete-a-post-300x254.webp 300w, https:\/\/codevoweb.com\/wp-content\/uploads\/2022\/07\/python-fastapi-postgresql-pydantic-and-sqlalchemy-crud-restful-api-delete-a-post-768x651.webp 768w, https:\/\/codevoweb.com\/wp-content\/uploads\/2022\/07\/python-fastapi-postgresql-pydantic-and-sqlalchemy-crud-restful-api-delete-a-post-100x85.webp 100w, https:\/\/codevoweb.com\/wp-content\/uploads\/2022\/07\/python-fastapi-postgresql-pydantic-and-sqlalchemy-crud-restful-api-delete-a-post-531x450.webp 531w, https:\/\/codevoweb.com\/wp-content\/uploads\/2022\/07\/python-fastapi-postgresql-pydantic-and-sqlalchemy-crud-restful-api-delete-a-post.webp 1209w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<p>-You can open the pgAdmin application to see all the posts you created.<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"545\" src=\"https:\/\/codevoweb.com\/wp-content\/uploads\/2022\/07\/python-fastapi-postgresql-pydantic-and-sqlalchemy-crud-restful-api-check-pgadmin-to-see-the-data-1024x545.webp\" alt=\"python, fastapi, postgresql, pydantic, and sqlalchemy crud restful api check pgadmin to see the data\" class=\"wp-image-5493\" srcset=\"https:\/\/codevoweb.com\/wp-content\/uploads\/2022\/07\/python-fastapi-postgresql-pydantic-and-sqlalchemy-crud-restful-api-check-pgadmin-to-see-the-data-1024x545.webp 1024w, https:\/\/codevoweb.com\/wp-content\/uploads\/2022\/07\/python-fastapi-postgresql-pydantic-and-sqlalchemy-crud-restful-api-check-pgadmin-to-see-the-data-300x160.webp 300w, https:\/\/codevoweb.com\/wp-content\/uploads\/2022\/07\/python-fastapi-postgresql-pydantic-and-sqlalchemy-crud-restful-api-check-pgadmin-to-see-the-data-768x409.webp 768w, https:\/\/codevoweb.com\/wp-content\/uploads\/2022\/07\/python-fastapi-postgresql-pydantic-and-sqlalchemy-crud-restful-api-check-pgadmin-to-see-the-data-100x53.webp 100w, https:\/\/codevoweb.com\/wp-content\/uploads\/2022\/07\/python-fastapi-postgresql-pydantic-and-sqlalchemy-crud-restful-api-check-pgadmin-to-see-the-data-700x373.webp 700w, https:\/\/codevoweb.com\/wp-content\/uploads\/2022\/07\/python-fastapi-postgresql-pydantic-and-sqlalchemy-crud-restful-api-check-pgadmin-to-see-the-data.webp 1174w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\">Prerequisites<\/h2>\n\n\n\n<p>Before you start this tutorial:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Basic knowledge of Python, and FastAPI will be helpful<\/li>\n\n\n\n<li>Intermediate understanding of SQLAlchemy, and how ORMs work will be highly beneficial.<\/li>\n\n\n\n<li>Have Docker and Python <strong>+3.6<\/strong> installed<\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading\">Setting up FastAPI and PostgreSQL<\/h2>\n\n\n\n<p>By default, the FastAPI framework supports both NoSQL and SQL databases, making it a great choice for developers.<\/p>\n\n\n\n<p>The code we are going to write can easily be adapted to utilize any database supported by SQLAlchemy, like:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>PostgreSQL<\/strong><\/li>\n\n\n\n<li><strong>MySQL<\/strong><\/li>\n\n\n\n<li><strong>MS-SQL<\/strong><\/li>\n\n\n\n<li><strong>Firebird<\/strong><\/li>\n\n\n\n<li><strong>SQLite<\/strong><\/li>\n\n\n\n<li><strong>Oracle<\/strong>, etc.<\/li>\n<\/ul>\n\n\n\n<p>When it comes to working with the PostgreSQL database, the easiest way to run the Postgres server on your system is by using <a href=\"https:\/\/docs.docker.com\/get-docker\/\" target=\"_blank\" rel=\"noreferrer noopener\">Docker<\/a> and <a href=\"https:\/\/docs.docker.com\/compose\/\" target=\"_blank\" rel=\"noreferrer noopener\">Docker-compose<\/a>. At this point, you need to make sure you already have <a href=\"https:\/\/docs.docker.com\/get-docker\/\" target=\"_blank\" rel=\"noreferrer noopener\">Docker installed<\/a> on your machine.<\/p>\n\n\n\n<p>I going to use <a href=\"https:\/\/code.visualstudio.com\/download\" target=\"_blank\" rel=\"noreferrer noopener\">VS Code<\/a> (Visual Studio Code) as my text editor but feel free to use any IDE or text editor of your choice.<\/p>\n\n\n\n<p>Let&#8217;s start by creating a new project called <code>python_fastapi<\/code> to contain the FastAPI project:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>$ mkdir python_fastapi\n$ cd python_fastapi\n$ code . # opens the project with VS Code<\/code><\/pre>\n\n\n\n<p>Open the integrated terminal in your text editor or IDE and run this command to create a virtual environment:<\/p>\n\n\n\n<p><strong>Windows Machine:<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>$ py -3 -m venv venv<\/code><\/pre>\n\n\n\n<p><strong>macOS Machine:<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>$ python3 -m venv venv<\/code><\/pre>\n\n\n\n<p>Now create <code>docker-compose.yml<\/code> file in the root project and add the following configurations:<\/p>\n\n\n\n<p> <strong>docker-compose.yml<\/strong><\/p>\n\n\n\n<pre class=\"line-numbers language-js\"><code>\nversion: '3'\nservices:\n  postgres:\n    image: postgres\n    container_name: postgres\n    ports:\n      - '6500:5432'\n    restart: always\n    env_file:\n      - .\/.env\n    volumes:\n      - postgres-db:\/var\/lib\/postgresql\/data\nvolumes:\n  postgres-db:\n\n<\/code>\n<\/pre>\n\n\n\n<p>Next, create a <code>.env<\/code> file in the root project and add the following credentials. These credentials will be used by the Postgres Docker image to configure the PostgreSQL database. <\/p>\n\n\n\n<p><strong>.env<\/strong><\/p>\n\n\n\n<pre class=\"line-numbers language-js\"><code>\nDATABASE_PORT=6500\nPOSTGRES_PASSWORD=password123\nPOSTGRES_USER=postgres\nPOSTGRES_DB=fastapi\nPOSTGRES_HOST=postgres\nPOSTGRES_HOSTNAME=127.0.0.1\n<\/code>\n<\/pre>\n\n\n\n<p><strong>.gitignore<\/strong><\/p>\n\n\n\n<pre class=\"line-numbers language-txt\"><code>\n__pycache__\nvenv\/\n.env\n<\/code>\n<\/pre>\n\n\n\n<p>Start the PostgreSQL Docker container with this command:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>$ docker-compose up -d<\/code><\/pre>\n\n\n\n<p>You can stop the container with this command:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>$ docker-compose down<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">Building the FastAPI Server<\/h3>\n\n\n\n<p>First and foremost, We need to activate the virtual environment we created. To do that, create a <code>app\/main.py<\/code> file for VS Code to prepare the Python development environment.<\/p>\n\n\n\n<p>Next, close and reopen the integrated terminal for VS Code to automatically activate the virtual environment.<\/p>\n\n\n\n<p>Also, let&#8217;s turn the app folder into a Python package by creating an empty <code>app\/__init__.py<\/code> file.<\/p>\n\n\n\n<p>Install the FastAPI library and its peer dependencies:<\/p>\n\n\n\n<pre class=\"line-numbers language-shell\"><code>\npip install fastapi[all]\n<\/code>\n<\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">Starting the FastAPI Server<\/h3>\n\n\n\n<p>Open the <code>app\/main.py<\/code> file and add the following code to help us initialize the FastAPI server.<\/p>\n\n\n\n<pre class=\"line-numbers language-py\"><code>\nfrom fastapi import FastAPI\n\napp = FastAPI()\n\n\n@app.get('\/api\/healthchecker')\ndef root():\n    return {'message': 'Hello World'}\n<\/code>\n<\/pre>\n\n\n\n<p>Start the FastAPI server with this command:<\/p>\n\n\n\n<pre class=\"line-numbers language-py\"><code>\nuvicorn app.main:app --host localhost --port 8000 --reload\n<\/code>\n<\/pre>\n\n\n\n<p>Let&#8217;s evaluate the above command:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><code><a href=\"https:\/\/www.uvicorn.org\/\" target=\"_blank\" rel=\"noreferrer noopener\">uvicorn<\/a><\/code> &#8211; &nbsp;a high-performance <strong>ASGI<\/strong> server for python applications.<\/li>\n\n\n\n<li><code>app.main<\/code>: the path to the <code>app\/main.py<\/code> file<\/li>\n\n\n\n<li><code>app.main:app<\/code> : the object returned by evoking <code>FASTAPI()<\/code> <\/li>\n\n\n\n<li><code>--host<\/code> : specifies the hostname<\/li>\n\n\n\n<li><code>--port<\/code> : specifies the port. Default is <strong>8000<\/strong>.<\/li>\n\n\n\n<li><code>--reload<\/code>: auto-reload the server upon every file change<\/li>\n<\/ul>\n\n\n\n<p>Open any API testing tool of your choice and make a <strong>GET<\/strong> request to <code>http:\/\/localhost:8000\/api\/healthchecker<\/code> . You should get the <strong>Hello World<\/strong> message we returned from the path operation function.<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"782\" src=\"https:\/\/codevoweb.com\/wp-content\/uploads\/2022\/07\/testing-the-fastapi-with-postman-1024x782.png\" alt=\"testing the fastapi with postman\" class=\"wp-image-4511\" srcset=\"https:\/\/codevoweb.com\/wp-content\/uploads\/2022\/07\/testing-the-fastapi-with-postman-1024x782.png 1024w, https:\/\/codevoweb.com\/wp-content\/uploads\/2022\/07\/testing-the-fastapi-with-postman-300x229.png 300w, https:\/\/codevoweb.com\/wp-content\/uploads\/2022\/07\/testing-the-fastapi-with-postman-768x587.png 768w, https:\/\/codevoweb.com\/wp-content\/uploads\/2022\/07\/testing-the-fastapi-with-postman-100x76.png 100w, https:\/\/codevoweb.com\/wp-content\/uploads\/2022\/07\/testing-the-fastapi-with-postman-589x450.png 589w, https:\/\/codevoweb.com\/wp-content\/uploads\/2022\/07\/testing-the-fastapi-with-postman.png 1258w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\">Setting up Environment Variables<\/h2>\n\n\n\n<p><strong>Out-of-the-box<\/strong>, <a href=\"https:\/\/pydantic-docs.helpmanual.io\/usage\/settings\/\" target=\"_blank\" rel=\"noreferrer noopener\">Pydantic<\/a> has a built-in tool for reading, loading, and validating the environment variables specified in a configuration file.<\/p>\n\n\n\n<p>All we have to do is to create a custom class model that inherits the <code>BaseSettings<\/code> class provided by <a href=\"https:\/\/pydantic-docs.helpmanual.io\/usage\/settings\/\" target=\"_blank\" rel=\"noreferrer noopener\">Pydantic<\/a>.<\/p>\n\n\n\n<p>Replace the content of the <code>.env<\/code> file with the following environment variables:<\/p>\n\n\n\n<p><strong>.env<\/strong><\/p>\n\n\n\n<pre class=\"line-numbers language-js\"><code>\nDATABASE_PORT=6500\nPOSTGRES_PASSWORD=password123\nPOSTGRES_USER=postgres\nPOSTGRES_DB=fastapi\nPOSTGRES_HOST=postgres\nPOSTGRES_HOSTNAME=127.0.0.1\n\nACCESS_TOKEN_EXPIRES_IN=15\nREFRESH_TOKEN_EXPIRES_IN=60\nJWT_ALGORITHM=RS256\n\nCLIENT_ORIGIN=http:\/\/localhost:3000\n\nJWT_PRIVATE_KEY=LS0tLS1CRUdJTiBSU0EgUFJJVkFURSBLRVktLS0tLQpNSUlCT2dJQkFBSkJBSSs3QnZUS0FWdHVQYzEzbEFkVk94TlVmcWxzMm1SVmlQWlJyVFpjd3l4RVhVRGpNaFZuCi9KVHRsd3h2a281T0pBQ1k3dVE0T09wODdiM3NOU3ZNd2xNQ0F3RUFBUUpBYm5LaENOQ0dOSFZGaHJPQ0RCU0IKdmZ2ckRWUzVpZXAwd2h2SGlBUEdjeWV6bjd0U2RweUZ0NEU0QTNXT3VQOXhqenNjTFZyb1pzRmVMUWlqT1JhUwp3UUloQU84MWl2b21iVGhjRkltTFZPbU16Vk52TGxWTW02WE5iS3B4bGh4TlpUTmhBaUVBbWRISlpGM3haWFE0Cm15QnNCeEhLQ3JqOTF6bVFxU0E4bHUvT1ZNTDNSak1DSVFEbDJxOUdtN0lMbS85b0EyaCtXdnZabGxZUlJPR3oKT21lV2lEclR5MUxaUVFJZ2ZGYUlaUWxMU0tkWjJvdXF4MHdwOWVEejBEWklLVzVWaSt6czdMZHRDdUVDSUVGYwo3d21VZ3pPblpzbnU1clBsTDJjZldLTGhFbWwrUVFzOCtkMFBGdXlnCi0tLS0tRU5EIFJTQSBQUklWQVRFIEtFWS0tLS0t\nJWT_PUBLIC_KEY=LS0tLS1CRUdJTiBQVUJMSUMgS0VZLS0tLS0KTUZ3d0RRWUpLb1pJaHZjTkFRRUJCUUFEU3dBd1NBSkJBSSs3QnZUS0FWdHVQYzEzbEFkVk94TlVmcWxzMm1SVgppUFpSclRaY3d5eEVYVURqTWhWbi9KVHRsd3h2a281T0pBQ1k3dVE0T09wODdiM3NOU3ZNd2xNQ0F3RUFBUT09Ci0tLS0tRU5EIFBVQkxJQyBLRVktLS0tLQ==\n<\/code>\n<\/pre>\n\n\n\n<p>Next, we need to configure the custom class to know the path to the configuration file. This will ensure that the model class initializer reads the content of the environment variables file if we do not initialize the class attributes.<\/p>\n\n\n\n<p>Now create a <code>app\/config.py<\/code> file and add the custom class model <code>Settings<\/code> :<\/p>\n\n\n\n<p><strong>app\/config.py<\/strong><\/p>\n\n\n\n<pre class=\"line-numbers language-py\"><code>\nfrom pydantic import BaseSettings\n\nclass Settings(BaseSettings):\n    DATABASE_PORT: int\n    POSTGRES_PASSWORD: str\n    POSTGRES_USER: str\n    POSTGRES_DB: str\n    POSTGRES_HOST: str\n    POSTGRES_HOSTNAME: str\n\n    JWT_PUBLIC_KEY: str\n    JWT_PRIVATE_KEY: str\n    REFRESH_TOKEN_EXPIRES_IN: int\n    ACCESS_TOKEN_EXPIRES_IN: int\n    JWT_ALGORITHM: str\n\n    CLIENT_ORIGIN: str\n\n    class Config:\n        env_file = '.\/.env'\n\n\nsettings = Settings()\n\n<\/code>\n<\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">Connecting to the PostgreSQL Server<\/h2>\n\n\n\n<p>There are numerous libraries we can use to interact with the PostgreSQL server in a Python application. Most of the libraries depend on the native PostgreSQL driver to function properly.<\/p>\n\n\n\n<p>There are a couple of PostgreSQL adapters but <a href=\"https:\/\/psycopg.org\/\" target=\"_blank\" rel=\"noreferrer noopener\">Psycopg<\/a> is the most popular&nbsp;<a href=\"https:\/\/www.postgresql.org\/\" target=\"_blank\" rel=\"noreferrer noopener\">PostgreSQL<\/a>&nbsp;database adapter for Python programming language.<\/p>\n\n\n\n<p>Below are the features of a good PostgreSQL driver:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>It should support client-side and&nbsp;<a href=\"https:\/\/www.psycopg.org\/docs\/usage.html#server-side-cursors\" target=\"_blank\" rel=\"noreferrer noopener\">server-side<\/a>&nbsp;cursors<\/li>\n\n\n\n<li>It should have &nbsp;<a href=\"https:\/\/www.psycopg.org\/docs\/advanced.html#async-support\" target=\"_blank\" rel=\"noopener\">asynchronous communication<\/a>&nbsp;and&nbsp;<a href=\"https:\/\/www.psycopg.org\/docs\/advanced.html#async-notify\" target=\"_blank\" rel=\"noopener\">notifications<\/a> support<\/li>\n\n\n\n<li>It should support Python types and adapt to <a href=\"https:\/\/www.psycopg.org\/docs\/usage.html#python-types-adaptation\" target=\"_blank\" rel=\"noopener\">matching PostgreSQL data types<\/a>.<\/li>\n<\/ul>\n\n\n\n<p>By default, <a href=\"https:\/\/www.sqlalchemy.org\/\" target=\"_blank\" rel=\"noreferrer noopener\">SQLAlchemy<\/a> uses <strong>psycopg2<\/strong> under the hood to interact with the PostgreSQL server.<\/p>\n\n\n\n<pre class=\"line-numbers language-shell\"><code>\npip install sqlalchemy psycopg2\n<\/code>\n<\/pre>\n\n\n\n<ul class=\"wp-block-list\">\n<li><a href=\"https:\/\/www.sqlalchemy.org\/\" target=\"_blank\" rel=\"noreferrer noopener\">SQLAlchemy<\/a> &#8211; a popular Python ORM (object-relational mapper)<\/li>\n\n\n\n<li><a href=\"https:\/\/psycopg.org\/\" target=\"_blank\" rel=\"noreferrer noopener\">Psycopg<\/a> &#8211; a popular PostgreSQL driver<\/li>\n<\/ul>\n\n\n\n<p>Now let&#8217;s create the utility functions to connect and discount the PostgreSQL server. Create a <code>app\/database.py<\/code> file and add the following code:<\/p>\n\n\n\n<p><strong>app\/database.py<\/strong><\/p>\n\n\n\n<pre class=\"line-numbers language-py\"><code>\nfrom sqlalchemy import create_engine\nfrom sqlalchemy.ext.declarative import declarative_base\nfrom sqlalchemy.orm import sessionmaker\nfrom .config import settings\n\nSQLALCHEMY_DATABASE_URL = f\"postgresql:\/\/{settings.POSTGRES_USER}:{settings.POSTGRES_PASSWORD}@{settings.POSTGRES_HOSTNAME}:{settings.DATABASE_PORT}\/{settings.POSTGRES_DB}\"\n\nengine = create_engine(\n    SQLALCHEMY_DATABASE_URL\n)\nSessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)\n\nBase = declarative_base()\n\n\ndef get_db():\n    db = SessionLocal()\n    try:\n        yield db\n    finally:\n        db.close()\n\n<\/code>\n<\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">Installing the UUID OSSP PostgreSQL Plugin<\/h3>\n\n\n\n<p>By default, PostgreSQL has native support for the <a href=\"https:\/\/en.wikipedia.org\/wiki\/Universally_unique_identifier\" target=\"_blank\" rel=\"noreferrer noopener\">UUID<\/a> data type, however, since we want to use it as a default value for the <strong>ID<\/strong> column, we need to install the <strong>UUID OSSP<\/strong>&nbsp;module plugin for it to work.<\/p>\n\n\n\n<p>To install the <strong>UUID OSSP<\/strong> module extension, we first need to access the bash shell of the running PostgreSQL Docker container with <code><em>docker exec<\/em>&nbsp;-it &lt;<em>container<\/em>&nbsp;name&gt; bash<\/code> and run the <code><a href=\"https:\/\/www.postgresql.org\/docs\/current\/sql-createextension.html\" target=\"_blank\" rel=\"noreferrer noopener\">CREATE EXTENSION<\/a><\/code> command to install it.<\/p>\n\n\n\n<pre class=\"line-numbers language-shell\"><code>\ndocker exec -it postgres bash\n<\/code>\n<\/pre>\n\n\n\n<p>Once you have access to the bash shell, we can use execute any PostgreSQL commands to communicate with the Postgres server.<\/p>\n\n\n\n<p>Now use the following steps to install the <strong>UUID<\/strong> plugin:<\/p>\n\n\n\n<p><strong>Step 1:<\/strong> Log into the running Postgres database with this command <code>psql -U &lt;database username&gt; &lt;database name&gt;<\/code>:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>psql -U admin fastapi<\/code><\/pre>\n\n\n\n<ul class=\"wp-block-list\">\n<li><code>admin<\/code> &#8211; is the database username provided in the <code>.env<\/code> file<\/li>\n\n\n\n<li><code>fastapi<\/code> &#8211; is the database name provided in the <code>.env<\/code> file<\/li>\n<\/ul>\n\n\n\n<p><strong>Step 2:<\/strong> You can display all the available extensions with this command<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>select * from pg_available_extensions;<\/code><\/pre>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"685\" src=\"https:\/\/codevoweb.com\/wp-content\/uploads\/2022\/06\/postgresql-extensions-1024x685.png\" alt=\"postgresql extensions\" class=\"wp-image-3926\" srcset=\"https:\/\/codevoweb.com\/wp-content\/uploads\/2022\/06\/postgresql-extensions-1024x685.png 1024w, https:\/\/codevoweb.com\/wp-content\/uploads\/2022\/06\/postgresql-extensions-300x201.png 300w, https:\/\/codevoweb.com\/wp-content\/uploads\/2022\/06\/postgresql-extensions-768x514.png 768w, https:\/\/codevoweb.com\/wp-content\/uploads\/2022\/06\/postgresql-extensions-100x67.png 100w, https:\/\/codevoweb.com\/wp-content\/uploads\/2022\/06\/postgresql-extensions-673x450.png 673w, https:\/\/codevoweb.com\/wp-content\/uploads\/2022\/06\/postgresql-extensions.png 1295w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<p>After displaying the available extensions, you will notice the <code><a href=\"https:\/\/www.postgresql.org\/docs\/current\/uuid-ossp.html\" target=\"_blank\" rel=\"noreferrer noopener\">uuid-ossp<\/a><\/code> module plugin is available but not installed.<\/p>\n\n\n\n<p>You can hit the Enter key multiple times to scroll down the list and press <code>q<\/code> to exit.<\/p>\n\n\n\n<p><strong>Step 3:<\/strong> Now install the <code>uuid-ossp<\/code> module with this command<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>CREATE EXTENSION IF NOT EXISTS \"uuid-ossp\";<\/code><\/pre>\n\n\n\n<p>After installing the <strong>UUID OSSP<\/strong> module, use <code>\\q<\/code> to exit the Postgres server shell and <code>exit<\/code> to exit the Docker container bash shell.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">How to Create Database Models with SQLAlchemy<\/h2>\n\n\n\n<p>We already covered <a href=\"\/restful-api-with-python-fastapi-access-and-refresh-tokens\">RESTful API with Python &amp; FastAPI: Access and Refresh Tokens<\/a> where we defined the <code>users<\/code> table with SQLAlchemy, so I&#8217;ll skip the finer details. If you arrived here by Googling questions about <strong>How to build RESTful APIs with FastAPI and Python<\/strong>, you should probably catch up on how to implement JWT authentication with FastAPI.<\/p>\n\n\n\n<p>SQLAlchemy allows us to define module-level constructs to represent the structure of the data we will be fetching from the database.<\/p>\n\n\n\n<p>We use&nbsp;<a href=\"https:\/\/docs.sqlalchemy.org\/en\/14\/orm\/mapping_styles.html#orm-declarative-mapping\" target=\"_blank\" rel=\"noreferrer noopener\">Declarative Mapping<\/a> to create both a Python object model and <a href=\"https:\/\/docs.sqlalchemy.org\/en\/14\/glossary.html#term-database-metadata\" target=\"_blank\" rel=\"noreferrer noopener\">database metadata<\/a>&nbsp;to represent the real SQL tables in the database.<\/p>\n\n\n\n<p>Now replace the content of the <code>app\/models.py<\/code> file with the following models:<\/p>\n\n\n\n<p><strong>app\/models.py<\/strong><\/p>\n\n\n\n<pre class=\"line-numbers language-py\"><code>\nimport uuid\nfrom .database import Base\nfrom sqlalchemy import TIMESTAMP, Column, ForeignKey, String, Boolean, text\nfrom sqlalchemy.dialects.postgresql import UUID\nfrom sqlalchemy.orm import relationship\n\n\nclass User(Base):\n    __tablename__ = 'users'\n    id = Column(UUID(as_uuid=True), primary_key=True, nullable=False,\n                default=uuid.uuid4)\n    name = Column(String,  nullable=False)\n    email = Column(String, unique=True, nullable=False)\n    password = Column(String, nullable=False)\n    photo = Column(String, nullable=True)\n    verified = Column(Boolean, nullable=False, server_default='False')\n    verification_code = Column(String, nullable=True, unique=True)\n    role = Column(String, server_default='user', nullable=False)\n    created_at = Column(TIMESTAMP(timezone=True),\n                        nullable=False, server_default=text(\"now()\"))\n    updated_at = Column(TIMESTAMP(timezone=True),\n                        nullable=False, server_default=text(\"now()\"))\n\n\nclass Post(Base):\n    __tablename__ = 'posts'\n    id = Column(UUID(as_uuid=True), primary_key=True, nullable=False,\n                default=uuid.uuid4)\n    user_id = Column(UUID(as_uuid=True), ForeignKey(\n        'users.id', ondelete='CASCADE'), nullable=False)\n    title = Column(String, nullable=False)\n    content = Column(String, nullable=False)\n    category = Column(String, nullable=False)\n    image = Column(String, nullable=False)\n    created_at = Column(TIMESTAMP(timezone=True),\n                        nullable=False, server_default=text(\"now()\"))\n    updated_at = Column(TIMESTAMP(timezone=True),\n                        nullable=False, server_default=text(\"now()\"))\n    user = relationship('User')\n\n<\/code>\n<\/pre>\n\n\n\n<p>In layman&#8217;s terms, when we create a model with an ORM like SQLAlchemy, that translates to an SQL table in the database.<\/p>\n\n\n\n<p>In the above code, we created two class models that inherited the <code>Base<\/code> class we exported in the <code>app\/database.py<\/code> file to help us create the <code>users<\/code> and <code>posts<\/code> tables in the PostgreSQL database.<\/p>\n\n\n\n<p>Also, we used <code>uuid.uuid4<\/code> function as a default value for the <strong>ID<\/strong> column. This will instruct PostgreSQL to generate a UUID for the ID column anytime a new record is added.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Creating Validation Schemas with Pydantic<\/h2>\n\n\n\n<p> We are now ready to create the validation schemas with <a href=\"https:\/\/pydantic-docs.helpmanual.io\/\" target=\"_blank\" rel=\"noreferrer noopener\">Pydantic<\/a> to validate the requests and responses.<\/p>\n\n\n\n<p>Replace the content of the <code>app\/schemas.py<\/code> file with the following schema definitions:<\/p>\n\n\n\n<p><strong>app\/schemas.py<\/strong><\/p>\n\n\n\n<pre class=\"line-numbers language-py\"><code>\nfrom datetime import datetime\nfrom typing import List\nimport uuid\nfrom pydantic import BaseModel, EmailStr, constr\n\n\nclass UserBaseSchema(BaseModel):\n    name: str\n    email: EmailStr\n    photo: str\n\n    class Config:\n        orm_mode = True\n\n\nclass CreateUserSchema(UserBaseSchema):\n    password: constr(min_length=8)\n    passwordConfirm: str\n    role: str = 'user'\n    verified: bool = False\n\n\nclass LoginUserSchema(BaseModel):\n    email: EmailStr\n    password: constr(min_length=8)\n\n\nclass UserResponse(UserBaseSchema):\n    id: uuid.UUID\n    created_at: datetime\n    updated_at: datetime\n\n\nclass FilteredUserResponse(UserBaseSchema):\n    id: uuid.UUID\n\n\nclass PostBaseSchema(BaseModel):\n    title: str\n    content: str\n    category: str\n    image: str\n    user_id: uuid.UUID | None = None\n\n    class Config:\n        orm_mode = True\n\n\nclass CreatePostSchema(PostBaseSchema):\n    pass\n\n\nclass PostResponse(PostBaseSchema):\n    id: uuid.UUID\n    user: FilteredUserResponse\n    created_at: datetime\n    updated_at: datetime\n\n\nclass UpdatePostSchema(BaseModel):\n    title: str\n    content: str\n    category: str\n    image: str\n    user_id: uuid.UUID | None = None\n    created_at: datetime | None = None\n    updated_at: datetime | None = None\n\n    class Config:\n        orm_mode = True\n\n\nclass ListPostResponse(BaseModel):\n    status: str\n    results: int\n    posts: List[PostResponse]\n\n\n<\/code>\n<\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">Creating the FastAPI Controllers<\/h2>\n\n\n\n<p>Now that we&#8217;ve defined the validation schemas and database model, let&#8217;s create the FastAPI path operation functions to perform the CRUD functionalities.<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><code>@router.get('\/api\/posts\/')<\/code> &#8211; This path operation function will retrieve a paginated list of post items from the database and return them to the client.<\/li>\n\n\n\n<li><code>@router.post('\/api\/posts\/')<\/code> &#8211; This path operation function will add a new post item to the database.<\/li>\n\n\n\n<li><code>@router.put('\/api\/notes\/{id}')<\/code> &#8211; This path operation function will update the fields of a post item in the database.<\/li>\n\n\n\n<li><code>@router.get('\/api\/notes\/{id}')<\/code> &#8211; This path operation function will get a single post item from the database and return it to the client.<\/li>\n\n\n\n<li><code>@router.delete('\/api\/notes\/{id}')<\/code> &#8211; This path operation function will delete a single post item in the database.<\/li>\n<\/ul>\n\n\n\n<p>To begin, create a &#8220;<strong>routers<\/strong>&#8221; folder in the app directory. Then, create a <code>post.py<\/code> file in the &#8220;<strong>routers<\/strong>&#8221; folder and add these module imports:<\/p>\n\n\n\n<p><strong>app\/routers\/post.py<\/strong><\/p>\n\n\n\n<pre class=\"line-numbers language-py\"><code>\nfrom datetime import datetime\nimport uuid\nfrom .. import schemas, models\nfrom sqlalchemy.orm import Session\nfrom fastapi import Depends, HTTPException, status, APIRouter, Response\nfrom ..database import get_db\nfrom app.oauth2 import require_user\n<\/code>\n<\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">Fetch All Posts Handler<\/h3>\n\n\n\n<p>The first path operation function will perform the <strong>READ<\/strong> functionality of CRUD. This route handler will be evoked to return a selected list of records. To avoid sending a huge JSON payload of all the records to the client, we&#8217;ll add a pagination feature where a user can select a range of records in the database. If the user doesn&#8217;t provide the <strong>limit<\/strong> parameter in the URL, the route controller will only return the first <strong>10<\/strong> records.<\/p>\n\n\n\n<p>So, open the <code>app\/routers\/post.py<\/code> file and add this block of code:<\/p>\n\n\n\n<p><strong>app\/routers\/post.py<\/strong><\/p>\n\n\n\n<pre class=\"line-numbers language-py\"><code>\n# [...] import\nrouter = APIRouter()\n\n# [...] Get All Posts\n\n\n@router.get('\/', response_model=schemas.ListPostResponse)\ndef get_posts(db: Session = Depends(get_db), limit: int = 10, page: int = 1, search: str = '', user_id: str = Depends(require_user)):\n    skip = (page - 1) * limit\n\n    posts = db.query(models.Post).group_by(models.Post.id).filter(\n        models.Post.title.contains(search)).limit(limit).offset(skip).all()\n    return {'status': 'success', 'results': len(posts), 'posts': posts}\n\n<\/code>\n<\/pre>\n\n\n\n<p>Above, we calculated the number of records to skip based on the <strong>page<\/strong> and <strong>limit<\/strong> parameters. Next, we created a query and appended all the relevant query methods before calling the <code>.all()<\/code> function to execute it.<\/p>\n\n\n\n<p>Then, we returned the list of records retrieved from the database to the client in the JSON response.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Create New Post Handler<\/h3>\n\n\n\n<p>This path operation function will perform the <strong>CREATE<\/strong> functionality of CRUD. When a <strong>POST<\/strong> request is made to the <code>\/api\/notes\/<\/code> endpoint, this route handler will be evoked to add the data to the database.<\/p>\n\n\n\n<p>To validate the request body and send appropriate validation errors to the client, we&#8217;ll pass the <strong>CreatePostSchema<\/strong> as a dependency to the path operation function. <\/p>\n\n\n\n<p>FastAPI will use the <strong>CreatePostSchema<\/strong> to deserialize the JSON payload, validate the data against the rules defined in the schema and assign the resulting Python object to the <code>post<\/code> variable.<\/p>\n\n\n\n<p><strong>app\/routers\/post.py<\/strong><\/p>\n\n\n\n<pre class=\"line-numbers language-py\"><code>\n# [...] import\nrouter = APIRouter()\n\n# [...] Get All Posts\n\n# [...] Create Post\n@router.post('\/', status_code=status.HTTP_201_CREATED, response_model=schemas.PostResponse)\ndef create_post(post: schemas.CreatePostSchema, db: Session = Depends(get_db), owner_id: str = Depends(require_user)):\n    post.user_id = uuid.UUID(owner_id)\n    new_post = models.Post(**post.dict())\n    db.add(new_post)\n    db.commit()\n    db.refresh(new_post)\n    return new_post\n\n<\/code>\n<\/pre>\n\n\n\n<p>The <code>require_user<\/code> dependency will ensure that the user is authenticated before the actual request is processed. To add the new record to the database, we created an instance of the <strong>Post<\/strong> model using the data provided in the request body and called the <code>db.add()<\/code> method to add the data to the current transaction.<\/p>\n\n\n\n<p>Then, we called the <code>db.commit()<\/code> method to save the data in the database. Once the data has been stored in the database, the <code>db.refresh()<\/code> method will be called to update the fields of the model instance with the fields of the newly-created record.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Update Post Handler<\/h3>\n\n\n\n<p>Now that we are able to add new records, let&#8217;s create a path operation function to edit a single record in the database. This route controller will perform the <strong>UPDATE<\/strong> operation of CRUD.<\/p>\n\n\n\n<p>To begin, we&#8217;ll construct a query to find the record by ID and assign the query to the <code>post_query<\/code> variable. Then, we&#8217;ll execute the query to get the record that matches the provided ID. If no record was found, a <strong>404<\/strong> error will be returned to the client.<\/p>\n\n\n\n<p><strong>app\/routers\/post.py<\/strong><\/p>\n\n\n\n<pre class=\"line-numbers language-py\"><code>\n# [...] import\nrouter = APIRouter()\n\n# [...] Get All Posts\n\n# [...] Create Post\n\n# [...] Update Post\n@router.put('\/{id}', response_model=schemas.PostResponse)\ndef update_post(id: str, post: schemas.UpdatePostSchema, db: Session = Depends(get_db), user_id: str = Depends(require_user)):\n    post_query = db.query(models.Post).filter(models.Post.id == id)\n    updated_post = post_query.first()\n\n    if not updated_post:\n        raise HTTPException(status_code=status.HTTP_200_OK,\n                            detail=f'No post with this id: {id} found')\n    if updated_post.user_id != uuid.UUID(user_id):\n        raise HTTPException(status_code=status.HTTP_403_FORBIDDEN,\n                            detail='You are not allowed to perform this action')\n    post.user_id = user_id\n    post_query.update(post.dict(exclude_unset=True), synchronize_session=False)\n    db.commit()\n    return updated_post\n\n<\/code>\n<\/pre>\n\n\n\n<p>Above, we called the <code>.update()<\/code> method on the current query to update the fields with the ones provided in the request body. Then, we called the <code>db.commit()<\/code> method to save the changes to the database. Lastly, we returned the newly-updated record to the client in the JSON response.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Get a Single Post Handler<\/h3>\n\n\n\n<p>This path operation function will also perform a <strong>READ<\/strong> functionality but it will only return a single record. Here, we&#8217;ll extract the ID parameter from the request URL and query the database to find the record that matches the current query.<\/p>\n\n\n\n<p>To do this, we&#8217;ll construct a new query and append the <code>.filter()<\/code> method to select the record with the provided ID. After that, we&#8217;ll call the <code>.first()<\/code> method to execute the query and return the found record to the client.<\/p>\n\n\n\n<p><strong>app\/routers\/post.py<\/strong><\/p>\n\n\n\n<pre class=\"line-numbers language-py\"><code>\n# [...] import\nrouter = APIRouter()\n\n# [...] Get All Posts\n\n# [...] Create Post\n\n# [...] Update Post\n\n# [...] Get a single post\n@router.get('\/{id}', response_model=schemas.PostResponse)\ndef get_post(id: str, db: Session = Depends(get_db), user_id: str = Depends(require_user)):\n    post = db.query(models.Post).filter(models.Post.id == id).first()\n    if not post:\n        raise HTTPException(status_code=status.HTTP_404_NOT_FOUND,\n                            detail=f\"No post with this id: {id} found\")\n    return post\n<\/code>\n<\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">Remove Post Handler<\/h3>\n\n\n\n<p>Finally, let&#8217;s create a path operation function to handle <strong>DELETE<\/strong> requests. This route handler will be called to delete a record in the database when a DELETE request is made to the <code>\/api\/posts\/{id}<\/code> endpoint.<\/p>\n\n\n\n<p><strong>app\/routers\/post.py<\/strong><\/p>\n\n\n\n<pre class=\"line-numbers language-py\"><code>\n# [...] import\nrouter = APIRouter()\n\n# [...] Get All Posts\n# [...] Create Post\n# [...] Update Post\n# [...] Get a single post\n\n# [...] Delete Post\n@router.delete('\/{id}')\ndef delete_post(id: str, db: Session = Depends(get_db), user_id: str = Depends(require_user)):\n    post_query = db.query(models.Post).filter(models.Post.id == id)\n    post = post_query.first()\n    if not post:\n        raise HTTPException(status_code=status.HTTP_404_NOT_FOUND,\n                            detail=f'No post with this id: {id} found')\n\n    if str(post.user_id) != user_id:\n        raise HTTPException(status_code=status.HTTP_403_FORBIDDEN,\n                            detail='You are not allowed to perform this action')\n    post_query.delete(synchronize_session=False)\n    db.commit()\n    return Response(status_code=status.HTTP_204_NO_CONTENT)\n\n<\/code>\n<\/pre>\n\n\n\n<p>Let&#8217;s evaluate the above code. First, we created a query to find a record by the ID provided in the request URL. Then, we evoked the <code>post_query.first()<\/code> method to retrieve the found record and assigned it to the &#8220;<strong>post<\/strong>&#8221; variable.<\/p>\n\n\n\n<p>Next, we called the <code>post_query.delete()<\/code> method to delete the record in the current transaction. After that, we called the <code>db.commit()<\/code> method to permanently delete the record in the database.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Complete Code for the Post Handlers<\/h3>\n\n\n\n<p><strong>app\/routers\/post.py<\/strong><\/p>\n\n\n\n<pre class=\"line-numbers language-py\"><code>\nimport uuid\nfrom .. import schemas, models\nfrom sqlalchemy.orm import Session\nfrom fastapi import Depends, HTTPException, status, APIRouter, Response\nfrom ..database import get_db\nfrom app.oauth2 import require_user\n\nrouter = APIRouter()\n\n\n@router.get('\/', response_model=schemas.ListPostResponse)\ndef get_posts(db: Session = Depends(get_db), limit: int = 10, page: int = 1, search: str = '', user_id: str = Depends(require_user)):\n    skip = (page - 1) * limit\n\n    posts = db.query(models.Post).group_by(models.Post.id).filter(\n        models.Post.title.contains(search)).limit(limit).offset(skip).all()\n    return {'status': 'success', 'results': len(posts), 'posts': posts}\n\n\n@router.post('\/', status_code=status.HTTP_201_CREATED, response_model=schemas.PostResponse)\ndef create_post(post: schemas.CreatePostSchema, db: Session = Depends(get_db), owner_id: str = Depends(require_user)):\n    post.user_id = uuid.UUID(owner_id)\n    new_post = models.Post(**post.dict())\n    db.add(new_post)\n    db.commit()\n    db.refresh(new_post)\n    return new_post\n\n\n@router.put('\/{id}', response_model=schemas.PostResponse)\ndef update_post(id: str, post: schemas.UpdatePostSchema, db: Session = Depends(get_db), user_id: str = Depends(require_user)):\n    post_query = db.query(models.Post).filter(models.Post.id == id)\n    updated_post = post_query.first()\n\n    if not updated_post:\n        raise HTTPException(status_code=status.HTTP_200_OK,\n                            detail=f'No post with this id: {id} found')\n    if updated_post.user_id != uuid.UUID(user_id):\n        raise HTTPException(status_code=status.HTTP_403_FORBIDDEN,\n                            detail='You are not allowed to perform this action')\n    post.user_id = user_id\n    post_query.update(post.dict(exclude_unset=True), synchronize_session=False)\n    db.commit()\n    return updated_post\n\n\n@router.get('\/{id}', response_model=schemas.PostResponse)\ndef get_post(id: str, db: Session = Depends(get_db), user_id: str = Depends(require_user)):\n    post = db.query(models.Post).filter(models.Post.id == id).first()\n    if not post:\n        raise HTTPException(status_code=status.HTTP_404_NOT_FOUND,\n                            detail=f\"No post with this id: {id} found\")\n    return post\n\n\n@router.delete('\/{id}')\ndef delete_post(id: str, db: Session = Depends(get_db), user_id: str = Depends(require_user)):\n    post_query = db.query(models.Post).filter(models.Post.id == id)\n    post = post_query.first()\n    if not post:\n        raise HTTPException(status_code=status.HTTP_404_NOT_FOUND,\n                            detail=f'No post with this id: {id} found')\n\n    if str(post.user_id) != user_id:\n        raise HTTPException(status_code=status.HTTP_403_FORBIDDEN,\n                            detail='You are not allowed to perform this action')\n    post_query.delete(synchronize_session=False)\n    db.commit()\n    return Response(status_code=status.HTTP_204_NO_CONTENT)\n\n<\/code>\n<\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">Adding the FastAPI Routers<\/h2>\n\n\n\n<p>At this point, we&#8217;ve implemented the five <strong>CRUD<\/strong> functionalities. Now let&#8217;s register the router in the app so that a client can interact with the path operation functions.<\/p>\n\n\n\n<p><strong>app\/main.py<\/strong><\/p>\n\n\n\n<pre class=\"line-numbers language-py\"><code>\nfrom fastapi import FastAPI\nfrom fastapi.middleware.cors import CORSMiddleware\nfrom app.config import settings\nfrom app.routers import user, auth, post\n\napp = FastAPI()\n\norigins = [\n    settings.CLIENT_ORIGIN,\n]\n\napp.add_middleware(\n    CORSMiddleware,\n    allow_origins=origins,\n    allow_credentials=True,\n    allow_methods=[\"*\"],\n    allow_headers=[\"*\"],\n)\n\n\napp.include_router(auth.router, tags=['Auth'], prefix='\/api\/auth')\napp.include_router(user.router, tags=['Users'], prefix='\/api\/users')\napp.include_router(post.router, tags=['Posts'], prefix='\/api\/posts')\n\n\n@app.get('\/api\/healthchecker')\ndef root():\n    return {'message': 'Hello World'}\n\n<\/code>\n<\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">Conclusion<\/h2>\n\n\n\n<p>With this FastAPI, PostgreSQL, Pydantic, SQLAlchemy, Alembic, and Docker-compose example in Python, you&#8217;ve learned how to build a Restful CRUD API server to perform the basic <strong>Create<\/strong>\/<strong>Read<\/strong>\/<strong>Update<\/strong>\/<strong>Delete<\/strong> operations.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">FastAPI and PostgreSQL CRUD API Source Code<\/h2>\n\n\n\n<p>You can get the complete <a href=\"https:\/\/github.com\/wpcodevo\/python_fastapi\/tree\/python_fastapi_crud_api\" target=\"_blank\" rel=\"noreferrer noopener\">source code in this GitHub repository<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>This article will teach you how to create a CRUD RESTful API with Python, FastAPI, SQLAlchemy ORM, Pydantic, Alembic, PostgreSQL, and Docker-compose to perform the&#8230;<\/p>\n","protected":false},"author":1,"featured_media":5548,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[20],"tags":[25,31],"class_list":["post-5476","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-python","tag-python","tag-python-development"],"acf":[],"_links":{"self":[{"href":"https:\/\/codevoweb.com\/wp-json\/wp\/v2\/posts\/5476","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/codevoweb.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/codevoweb.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/codevoweb.com\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/codevoweb.com\/wp-json\/wp\/v2\/comments?post=5476"}],"version-history":[{"count":3,"href":"https:\/\/codevoweb.com\/wp-json\/wp\/v2\/posts\/5476\/revisions"}],"predecessor-version":[{"id":11271,"href":"https:\/\/codevoweb.com\/wp-json\/wp\/v2\/posts\/5476\/revisions\/11271"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/codevoweb.com\/wp-json\/wp\/v2\/media\/5548"}],"wp:attachment":[{"href":"https:\/\/codevoweb.com\/wp-json\/wp\/v2\/media?parent=5476"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/codevoweb.com\/wp-json\/wp\/v2\/categories?post=5476"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/codevoweb.com\/wp-json\/wp\/v2\/tags?post=5476"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}