iTechGuides is reader-supported. When you buy through links on our site, we may earn an affiliate commission. As an Amazon Associate I earn from qualifying purchases. Learn more
Antihost offers Linux VPS plans with full root access, unlimited traffic on a 1 Gbps port and a 14-day free trial.
The most maintainable FastAPI setup on Ubuntu is FastAPI → SQLAlchemy (or SQLModel) → PostgreSQL, with Alembic managing schema changes. Install PostgreSQL from Ubuntu packages, create a least-privileged database role, put the connection URL in an environment variable, define an engine and session factory, map your tables, and apply migrations before starting Uvicorn. The same pattern works synchronously with psycopg2 or asynchronously with asyncpg; choose one deliberately because it changes your URL, route functions, and migration configuration.
What the FastAPI–PostgreSQL stack looks like
FastAPI handles HTTP requests and response validation. SQLAlchemy supplies the database engine, connection pool, SQL expression language, and ORM. PostgreSQL stores the relational data. Alembic records structural changes as numbered migration files so a new server can be built reproducibly instead of relying on manually edited tables.
Windows Errors? Fix Them Before They Spread
Repair common Windows errors and clear accumulated junk for a smoother, more stable PC - no reinstall needed.Free scan · no reinstallCrashes, No Sound, or Screen Glitches?
Random freezes, missing sound and display glitches usually trace back to one bad driver. Find and replace yours safely.Free scan · under a minuteThere are two closely related modeling choices:
- SQLAlchemy declarative models: the direct SQLAlchemy approach, with a separate Pydantic schema layer for request and response validation.
- SQLModel: the approach used by FastAPI’s official SQL tutorial. SQLModel combines SQLAlchemy’s database mapping with Pydantic-style data models. The tutorial demonstrates SQLite, but its stated design applies to any database supported by SQLAlchemy, including PostgreSQL.
FastAPI release 0.115.1 (October 12, 2024) added that SQLModel-based SQL database tutorial to the release documentation. You can therefore follow its dependency-injection ideas while replacing the example SQLite URL with a PostgreSQL URL.
The examples below use SQLAlchemy declarative models for maximum control and show where SQLModel fits. They target a current Ubuntu installation and SQLAlchemy 2.x. Package releases change, so pin versions after checking the versions available for your Python and Ubuntu release rather than copying an old tutorial’s pins blindly.
#1 Best Overall
- Powerful Turbo Fan:CAGIWIRU Electric Air Duster have reached speed up to 130000RPM,Can efficient remove dust and debris.With LED light and Three Adjustable Speed to meet with different cleaning tasks.
- With Attached USB C Fasting Charging cable.Charing 3 hours,Enjoy up to 240 minutes of use on the lowest setting, For third speed can use for 25mins.with four charging options to meet with your needs.
- Wide Application:Upgraded attached 5 different nozzles,making it suitable for a variety of scenes, such as car,pet, pc, keyboards, and other electronic devices. It also serves for office and home clean duster.
- Handy Design & Portable:Cordless air duster electric nozzle is Light weight and small size, design and comfortable to hold and space saving, convenient to carry around. You can put it in your room or inside the car without taking up space.
- Warranty & Support: Our Electric air duster comes with 5 years warranty and 24/7 customer service. During this period, if you have any questions,pls kindly feel free to contact us.
Check Ubuntu and choose a PostgreSQL package source
Ubuntu’s package
Ubuntu’s server documentation installs PostgreSQL with:
sudo apt update
sudo apt install postgresqlThe Ubuntu package enables a service with sensible defaults. Confirm that it is running:
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
systemctl status postgresqlUbuntu ships one fixed PostgreSQL major version for each Ubuntu release. That is usually the safest choice for a single application because security updates arrive through the normal Ubuntu update process.
When you need a newer major version
If your application requires a PostgreSQL major version newer than the one in your Ubuntu release, use the PostgreSQL Global Development Group (PGDG) apt repository and follow its Ubuntu instructions. The repository provides versioned packages such as postgresql-18; the exact package and supported Ubuntu codenames can change. Add the repository before installing, then verify the selected major version with psql --version. Do not mix packages from several PostgreSQL repositories on the same host.
| Requirement | Recommended choice | Reason |
|---|---|---|
| Normal Ubuntu deployment | sudo apt install postgresql |
Integrated updates and the version tested for that Ubuntu release |
| Specific newer major version | PGDG repository, then a versioned package | Access to newer server features; repository support must match your Ubuntu release |
| Application portability | Pin the client/driver and document the server major version | Prevents an unplanned upgrade from changing behavior |
Create a PostgreSQL database and application role
Ubuntu commonly configures local administrative access through the operating-system postgres account. Open a PostgreSQL shell as that account:
sudo -u postgres psqlInside psql, create a login role and a database owned by that role. Replace the example password with a generated secret; do not commit it to source control.
CREATE ROLE api_user WITH LOGIN PASSWORD 'replace-with-a-long-random-secret';
CREATE DATABASE api_db OWNER api_user;
c api_db
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
GRANT USAGE, CREATE ON SCHEMA public TO api_user;
qFor a local-only development database, this role can own the schema. In production, consider a separate migration owner and runtime role: the migration account can create or alter tables, while the runtime account receives only the SELECT, INSERT, UPDATE, and DELETE privileges the API needs.
Rank #2
- Screen Cleaner is Your Screen’s New Bestfriend! - EVEO is proud to present a screen cleaner that’s perfectly suited for your TV. Meet your new screen cleaner, computer screen cleaner, laptop cleaner, iPad cleaner, Macbook,tv cleaner or any other electronic device. Our screen cleaner spray and wipe along with the included plush microfiber cloth, easily removes dust, fingerprints & other smudges on the screen’s surface. Clean effectively for a streak-free screen
- Gentle on your Screens - Our screen cleaner spray and wipes is compatible to be used even for the most sensitive LCD, LED, CRT, and OLED screens in proper use. Be at ease knowing that this product is compatible with all the major brands: LG, SONY, Samsung, Sharp, iPad, iPhone, Android screens, Kindle, PC monitors - we’ve got you covered! Can be used as LCD screen cleaner, tv cleaner, smart tv screen cleaner, and more
- Premium Super Soft Plush Microfiber Cloth - Included in this efficient screen cleaning kit. This screen cleaner spray and wipe has our signature plush microfiber cloth specially designed to comply with screens and monitors and leave them streak-free. Has been shown to effectively absorbs stubborn stains, zero streaks, and no fiber shedding. Always use this together with our screen cleaner for the best results
- Easy-to-Use Screen Cleaner Kit + microfiber cleaning cloth - The EVEO Screen Cleaning Kit will easily be your new favorite tool when it comes to taking care of the things you love most, quick and easy solution for a streak-free screen. Simply screw on the sprayer gun on the screen cleaner bottle. Spray the cleaning liquid onto the microfiber cloth, then wipe the screen until desired cleanliness & shine is achieved.
- Quality Screen Care Kit Worth Purchasing - Enjoy a convenient Screen Cleaner anytime, anywhere you need it. Your satisfaction is our top priority. We stand behind the quality of our products and that’s why, you shouldn’t be worrying at all. We manufacture the Best Screen Cleaner. and can be used for laptop cleaning kit, laptop screen cleaner, tv screen cleaner for smart tv. MacBook screen cleaner, monitor cleaner or screen cleaner spray for electronic devices with microfiber cleaning cloth
Test the credentials before involving FastAPI:
psql 'postgresql://api_user:replace-with-a-long-random-secret@127.0.0.1:5432/api_db' -c 'SELECT current_database(), current_user;'If the password contains characters such as @, :, /, or #, URL-encode it when placing it in a connection URL, or use a driver configuration method that accepts separate keyword values.
Create the Python project and install dependencies
Virtual environment
Install the Python tooling supplied by Ubuntu, create a project directory, and isolate its dependencies:
sudo apt install python3-venv
mkdir -p ~/fastapi-pg-example
cd ~/fastapi-pg-example
python3 -m venv .venv
source .venv/bin/activate
python -m pip install --upgrade pipPick synchronous or asynchronous I/O
| Choice | Install | URL scheme | Route functions | Operational trade-off |
|---|---|---|---|---|
| Synchronous | fastapi uvicorn sqlalchemy alembic psycopg2-binary |
postgresql:// |
def |
Simpler control flow and the most examples; each database call occupies a worker thread while it runs |
| Asynchronous | fastapi uvicorn 'sqlalchemy[asyncio]' alembic asyncpg |
postgresql+asyncpg:// |
async def |
Fits an async-heavy service, but every database boundary and migration setting must be handled consistently |
Install the synchronous set with:
python -m pip install fastapi uvicorn sqlalchemy alembic psycopg2-binaryFor an async project, install this instead (FastAPI itself is still required):
Recommended Free Tools
python -m pip install fastapi uvicorn 'sqlalchemy[asyncio]' alembic asyncpgSave the resolved versions after testing, for example with python -m pip freeze > requirements.txt. A requirements file is a record of what you tested, not a substitute for checking compatibility when upgrading Python, Ubuntu, PostgreSQL, or SQLAlchemy.
Configure a synchronous SQLAlchemy connection
Store the URL outside the repository
Set an environment variable in your shell or service manager:
export DATABASE_URL='postgresql://api_user:replace-with-a-long-random-secret@127.0.0.1:5432/api_db'A dotenv file can be convenient during development, but keep it out of Git and use your deployment platform’s secret store in production. The plain postgresql:// URL selects SQLAlchemy’s synchronous PostgreSQL dialect and the psycopg2 driver.
Create database.py
import os
from sqlalchemy import create_engine
from sqlalchemy.orm import DeclarativeBase, sessionmaker
DATABASE_URL = os.environ['DATABASE_URL']
engine = create_engine(
DATABASE_URL,
pool_pre_ping=True,
)
SessionLocal = sessionmaker(
bind=engine,
autoflush=False,
autocommit=False,
)
class Base(DeclarativeBase):
pass
def get_db():
db = SessionLocal()
try:
yield db
finally:
db.close()pool_pre_ping=True makes SQLAlchemy test an idle pooled connection before handing it to a request, which helps recover from a server or network timeout. Size the pool deliberately for your process count; several Uvicorn workers each create their own pool.
The Tool Desk
Outbyte Driver Updater FREEFix the driver behind crashes, sound loss and screen glitchesFind Drivers →Outbyte PC Repair FREEClear out junk files and repair common Windows errorsFree Scan →Rank #3
- 【Large size】：Measuring 800 x 300 mm/ 31. 5×12 inches, which is wider and taller than others. Large mouse pad can perfectly fit your keyboard and mouse of any size, and there is also ample space for peripherals such as phones, tablets, etc.
- 【LED backlight mouse pad】：Blade Hawks RGB mouse pad provides 7 static modes and 7 dynamic modes will give you the options you are looking for. Power-off memory function remembers the last lighting mode you selected. The super glow fiber Chroma will give you a colorful gaming setup that you want.
- 【Anti-slip rubber base】：Made from Natural rubber, this gaming mousepad will firmly grip your desktop, allowing you to enjoy the ultimate precision in the games you are most passionate about. It also comes with a texture that enhances this ability even further.
- 【Smooth and waterproof surface】: Micro-textured cloth surface, for extremely precise mouse control and a smooth movement. When liquid splashes on the gaming mouse pad, it forms water droplets and slides down. Will not delay your work or gameplay.
- 【Plug and play】: This mouse and keyboard pad is powered by USB, plug and play, no driver required. One button control light modes. Press one time to change the lighting mode, press twice to change the brightness, press and hold about 3 seconds turn ON/OFF.
Define a model in models.py
from sqlalchemy import String
from sqlalchemy.orm import Mapped, mapped_column
from .database import Base
class Item(Base):
__tablename__ = 'items'
id: Mapped[int] = mapped_column(primary_key=True)
name: Mapped[str] = mapped_column(String(200), nullable=False, index=True)
description: Mapped[str | None] = mapped_column(String(1000))The model is metadata, not a database change by itself. Alembic will use this metadata to generate a migration later.
Create request and response schemas
from pydantic import BaseModel, ConfigDict
class ItemCreate(BaseModel):
name: str
description: str | None = None
class ItemRead(ItemCreate):
id: int
model_config = ConfigDict(from_attributes=True)Inject a session into FastAPI routes
from fastapi import Depends, FastAPI
from sqlalchemy import select
from sqlalchemy.orm import Session
from .database import get_db
from .models import Item
from .schemas import ItemCreate, ItemRead
app = FastAPI()
@app.post('/items', response_model=ItemRead)
def create_item(payload: ItemCreate, db: Session = Depends(get_db)):
item = Item(name=payload.name, description=payload.description)
db.add(item)
db.commit()
db.refresh(item)
return item
@app.get('/items', response_model=list[ItemRead])
def list_items(db: Session = Depends(get_db)):
return db.scalars(select(Item).order_by(Item.id)).all()The dependency creates one session for the request and closes it in finally. Commit explicitly after a write; a failed transaction should be rolled back before the session is reused. For larger applications, put transaction boundaries in a service layer so several repository operations can succeed or fail together.
Use SQLModel instead, if it matches your project
SQLModel is built on SQLAlchemy and Pydantic and is the model used in FastAPI’s official SQL tutorial. Install it alongside the PostgreSQL driver, then define a table model with table=True and use SQLModel’s session helpers. The important adaptation is the engine URL: use a PostgreSQL URL rather than the tutorial’s SQLite URL.
python -m pip install sqlmodel psycopg2-binarySQLModel can reduce duplicated model declarations for small services. Explicit SQLAlchemy models plus separate Pydantic schemas usually give clearer control over public API fields, relationships, indexes, and migrations as a codebase grows. Both approaches ultimately rely on SQLAlchemy’s PostgreSQL dialect and can use Alembic metadata.
Configure an asynchronous SQLAlchemy connection
Async engine and session dependency
Use asyncpg with SQLAlchemy’s asyncio extension. The URL must include +asyncpg:
import os
from sqlalchemy.ext.asyncio import AsyncSession, async_sessionmaker, create_async_engine
from sqlalchemy.orm import DeclarativeBase
DATABASE_URL = os.environ['DATABASE_URL']
engine = create_async_engine(
DATABASE_URL,
pool_pre_ping=True,
)
AsyncSessionLocal = async_sessionmaker(
engine,
expire_on_commit=False,
)
class Base(DeclarativeBase):
pass
async def get_db():
async with AsyncSessionLocal() as db:
yield dbSet the variable to a URL such as:
export DATABASE_URL='postgresql+asyncpg://api_user:replace-with-a-long-random-secret@127.0.0.1:5432/api_db'Async route
from fastapi import Depends, FastAPI
from sqlalchemy import select
from sqlalchemy.ext.asyncio import AsyncSession
from .database import get_db
from .models import Item
app = FastAPI()
@app.get('/items')
async def list_items(db: AsyncSession = Depends(get_db)):
result = await db.scalars(select(Item).order_by(Item.id))
return result.all()Do not call blocking psycopg2 operations directly inside an async route. Conversely, changing a function to async def without using an async engine does not make database I/O non-blocking.
Set up Alembic migrations
Alembic is SQLAlchemy’s standard migration tool. It keeps schema history in source control and applies revisions in order.
Rank #4
- 【Softer and More Comfortable】Vaydeer wrist rest has unique diamond pattern, which is the combination of softness and aesthetics. The materials of wrist rest are improved into higher quality memory foam and covered with silky smooth lycra. The computer wrist rest makes you as comfortable and cushiony as like rest your wrists on clouds.
- 【Ergonomic Wrist Saver】The wrist rests for keyboard and mouse comes with a 17.32×3.15×0.83 inch keyboard wrist pad and a 5.94×3.15×0.83 inch mouse wrist support. Based on ergonomic design, the unique concave shape is the perfect fit for your wrist joints. The wrist rest pad fits most computer keyboards and laptops, improve hand and wrist posture, release your wrist and arm stress.
- 【Non-Slip Rubber Bottom】Featuring an anti-skid silicone base on the bottom, this wrist keyboard support stays firmly in place on your desk, preventing the padding from sliding around, ensuring stable and consistent wrist support during extended computer sessions.
- 【Better Experience & Pain Relief】Our keyboard arm rest is beneficial to alleviate the soreness caused by direct contact and friction between your arm and a hard desk surface, reducing the risk of wrist fatigue or carpal tunnel. The soft texture of memory foam can evenly distribute the pressure around your wrists and provide good support with just enough give.
- 【Helpful in Multiple Scenarios】Whether you're working, studying, writing, typing, gaming, this keyboard and mouse rest combo is an essential accessory to add comfort and support to your hands and wrists. It’s also a great gift for men, women, family, friend, coworker, gamer, teacher, etc.
Initialize the migration directory
alembic init alembicThis creates alembic.ini, an alembic/env.py runner, and a versions directory. Keep the generated structure, but edit env.py so Alembic can import your model metadata and read the same environment variable as the application.
Quick wins for a faster PC:
Repair Windows errors before they cause bigger problemsFix Now →Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →Point env.py at your metadata
For a synchronous project, import Base and the model module, then set target_metadata = Base.metadata. Replace the configured URL with the environment value:
import os
from logging.config import fileConfig
from alembic import context
from sqlalchemy import engine_from_config, pool
from app.database import Base
from app import models
target_metadata = Base.metadata
config = context.config
config.set_main_option('sqlalchemy.url', os.environ['DATABASE_URL'])Leave Alembic’s generated offline and online functions in place, using target_metadata in both calls to context.configure. Importing models is essential: without it, the model table may never be registered in Base.metadata, and autogenerate can produce an empty revision.
Generate and apply the first revision
alembic revision --autogenerate -m 'create items table'
alembic upgrade headOpen the generated revision before applying it. Autogenerate detects many table, column, index, and constraint changes, but it cannot infer every semantic change, such as data transformations or certain renames. Edit the migration when a column is being renamed, data must be backfilled, or a constraint needs a staged rollout.
Async application, synchronous migration URL
Alembic’s standard runner is synchronous. Keep the application’s postgresql+asyncpg:// URL for runtime, but give Alembic a synchronous URL for migrations, commonly by removing +asyncpg so SQLAlchemy uses psycopg2:
export DATABASE_URL='postgresql+asyncpg://api_user:secret@127.0.0.1:5432/api_db'
export ALEMBIC_DATABASE_URL='postgresql://api_user:secret@127.0.0.1:5432/api_db'In env.py, read ALEMBIC_DATABASE_URL for Alembic’s engine. This avoids forcing a synchronous migration process to load the async driver and keeps the migration command predictable.
Start and exercise the application
With the virtual environment active, the database running, and migrations applied, launch the development server:
Best Value
- 【No Drill Mounted】Cable management tray can be assembled in as little as 3 minutes or less-simply. Side clips easily clampe the edge of desk, pass through the holes on the sides, and secure excess cables with baskets. Desk cable management supports inward or outward installation to meet your needs in different scenarios, and provides you with great convenience when collecting and organizing wires.
- 【Rubber Pad Protects Desk】Wire organizer under desk built-in soft rubber pads on the clamp protect your desk from scratches, so you will not damage your furniture or office.
- 【Sturdy and Beautiful】Cord management under desk is made of high-quality carbon steel and holds about 15 lbs. The wire basket has 2 holes on each side of the tray to enable your cables to pass freely. Cable baskets are suitable for desk thickness use from 0.4" to 2.4".
- 【Space Saving & Keep Organized】Desk cable management tray holds all your power cords, outlet strips, USB hubs and computer transformers hidden from your feet, and the data cables will not enter the vacuum cleaner to keep your desk clean and tidy. Desk wire organizer is also suitable for use in the kitchen and outdoors.
- 【Reduce Safety Risks】Cable tray under desk keeps all plugs away from your kids, no more worry about tripping. Using 2 holes wire tray saves extra space, helps you easily cabling and wire protection. The mesh design is not easy to accumulate dust, but avoids safety accidents caused by messy wires.
uvicorn app.main:app --reloadOpen the generated interactive documentation at http://127.0.0.1:8000/docs, create an item, then request the collection. The --reload option is for development; it watches files and restarts the process and should not be used as a production process manager.
A deployment normally runs migrations as a release step, then starts one or more Uvicorn workers behind a reverse proxy. Run migrations exactly once per release, not independently in every worker startup hook, or concurrent workers can race while changing the schema.
Free tools Windows power users keep installed
One-click scans. No signup required.
Production configuration and safety checks
- Secrets: supply
DATABASE_URLthrough systemd credentials, a container secret, or your hosting platform. Never log the full URL. - Network exposure: bind PostgreSQL locally when the API is on the same host, or restrict remote access by firewall and PostgreSQL host-based authentication rules. Do not expose port 5432 to the public internet without a specific, hardened design.
- TLS: require SSL when the database crosses an untrusted network and use the certificate options supported by your driver.
- Pool sizing: account for every Uvicorn worker and background process. PostgreSQL’s connection limit is global; a pool per process can exhaust it unexpectedly.
- Transactions: keep write transactions short, roll back after exceptions, and avoid holding a session while waiting on unrelated network calls.
- Indexes and constraints: enforce uniqueness, foreign keys, and not-null rules in PostgreSQL, not only in request validation.
- Backups: test restoration, not merely backup creation. A migration history does not replace a data backup.
- Version control: commit Alembic revisions and dependency pins, but exclude virtual environments, dotenv files, and generated secrets.
Troubleshoot the failures you are most likely to see
| Symptom | Likely cause | Fix |
|---|---|---|
connection refused |
PostgreSQL is stopped or listening on a different address/port | Check systemctl status postgresql, verify the port, and test with psql before starting FastAPI. |
password authentication failed |
Wrong role, password, or authentication rule | Connect as postgres, reset the role password, and confirm the URL names the intended database and user. |
database does not exist |
The database creation step was skipped or the URL has a typo | List databases with sudo -u postgres psql -l and correct the URL or create the database. |
| Autogenerate reports no changes | Models were not imported, or target_metadata is still None |
Import every model module in env.py and set target_metadata = Base.metadata. |
| Async driver or greenlet error | An async URL is being used with a synchronous engine, or blocking code is called from an async path | Use create_async_engine and AsyncSession with postgresql+asyncpg://, or switch the entire path to synchronous SQLAlchemy. |
| Works once, then stale-connection errors | An idle pooled connection was closed by PostgreSQL or the network | Enable pool_pre_ping, set sensible pool recycle/timeouts, and investigate network or server idle limits. |
| Permission denied while creating tables | The runtime role lacks schema or table privileges | Run migrations with the migration owner or grant the minimum required schema privileges; keep runtime permissions narrower. |
Choose the right approach for your project
- Choose synchronous SQLAlchemy when the team values straightforward code, uses mostly blocking libraries, or is migrating an existing synchronous service.
- Choose async SQLAlchemy with asyncpg when the service already performs substantial concurrent asynchronous I/O and the team will consistently use async sessions and queries.
- Choose SQLModel for a compact CRUD service that benefits from one model definition serving both validation and persistence. Move to explicit SQLAlchemy and Pydantic layers when API and database concerns diverge.
- Use SQLite only for a deliberate local or test case. PostgreSQL should be the development database when production depends on PostgreSQL-specific types, locking, constraints, or query behavior.
Whichever option you choose, the reliable sequence is the same: install and verify PostgreSQL, create a constrained role, isolate Python dependencies, configure one correct driver URL, register models, generate and review an Alembic revision, apply it, and only then start FastAPI.
Frequently Asked Questions
Can I use the FastAPI SQL tutorial with PostgreSQL instead of SQLite?
Yes. The tutorial uses SQLModel, which is built on SQLAlchemy and Pydantic; replace its SQLite connection URL with a PostgreSQL URL and install the appropriate PostgreSQL driver.
Do async FastAPI routes require asyncpg?
For the SQLAlchemy asyncio setup described here, use SQLAlchemy’s asyncio extension with asyncpg and a postgresql+asyncpg:// URL. A synchronous psycopg2 engine should not be called directly from an async route.
Why does Alembic use a different URL in an async project?
Alembic’s usual migration runner is synchronous, so it commonly uses a plain postgresql:// URL with psycopg2 while the running application uses postgresql+asyncpg://.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
