Best database for Python

Python works well with almost any database. There are mature drivers for PostgreSQL, MySQL, MongoDB and Redis, and SQLite already ships in the standard library. That is good news, but it turns the choice into a long list of options that look equivalent.

In practice, the decision comes down to a few questions: what shape your data has, which framework you use, how many processes write at the same time, and whether your code is synchronous or asynchronous. Below you will find the criteria, each option with its current library, and a recommendation per scenario.

Which criteria matter when choosing?#

1. Shape of the data. Users, orders and payments linked to each other call for tables, foreign keys and JOIN, which means a relational database. Documents whose fields vary a lot from record to record fit a document database better. The difference between the two models is covered in SQL vs NoSQL.
2. Framework. Django has its own ORM and officially supports five relational databases. FastAPI and Flask impose nothing, and the most common pick is SQLAlchemy.
3. Concurrency. A script or single-process bot is different from an API with several workers writing at the same time.
4. Sync or async. If your code uses asyncio (FastAPI, bots), prefer a driver with an async API so you do not block the event loop.
5. Operations. A SQLite file needs no server. The others need a host, credentials, connections and backups.

When is SQLite enough?#

The sqlite3 module is part of Python's standard library. SQLite stores everything in one file and runs inside your process, with no separate server. It is ideal for scripts, command-line tools, prototypes, tests and small single-process apps.

import sqlite3
from contextlib import closing

with closing(sqlite3.connect("app.db")) as con:
    con.execute("PRAGMA journal_mode=WAL")
    con.execute(
        "CREATE TABLE IF NOT EXISTS notes (id INTEGER PRIMARY KEY, text TEXT NOT NULL)"
    )
    with con:  # commit at the end of the block, rollback on error
        con.execute("INSERT INTO notes (text) VALUES (?)", ("hello",))
    for row in con.execute("SELECT id, text FROM notes"):
        print(row)

A detail that trips up many people: using the connection in a with block commits or rolls back, but does not close the connection. That is why the example uses contextlib.closing, as the documentation itself suggests.

The limit is on writes. The SQLite documentation explains that it allows one writer at a time per database file and is not a good fit when many machines access the same database over a network. If the file lives in a folder your deploy replaces, the data goes with it.

Why is PostgreSQL the default choice?#

PostgreSQL is a client-server relational database built for many simultaneous connections, with transactions, a wide range of indexes, JSONB and extensions. For almost any Python project that will grow, it is the choice with the fewest regrets. Learn more in what PostgreSQL is.

The most common libraries:

• psycopg 3 (the psycopg package): the current driver, with sync and async APIs. Django's documentation recommends it and warns that psycopg2 support is likely to be deprecated in the future.
• asyncpg: an async-only driver, common in FastAPI projects.
• SQLAlchemy 2: an ORM and SQL toolkit widely used outside Django. It works with PostgreSQL, MySQL and MariaDB, SQLite, Oracle and SQL Server, and on PostgreSQL it accepts psycopg, psycopg2, asyncpg and pg8000.

With SQLAlchemy, the same code runs on SQLite on your machine and on PostgreSQL in production. Only the URL changes: without DATABASE_URL, the example uses sqlite:///app.db; in production, set something like postgresql+psycopg://user:password@host/dbname.

import os

from sqlalchemy import String, create_engine, select
from sqlalchemy.orm import DeclarativeBase, Mapped, Session, mapped_column


class Base(DeclarativeBase):
    pass


class User(Base):
    __tablename__ = "users"
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(80))


engine = create_engine(os.environ.get("DATABASE_URL", "sqlite:///app.db"))
Base.metadata.create_all(engine)

with Session(engine) as session:
    session.add(User(name="Ana"))
    session.commit()
    for user in session.scalars(select(User).where(User.name == "Ana")):
        print(user.id, user.name)

Create the engine once, when the process starts. It keeps a connection pool; creating one per request is the recipe for the too many connections error. For production, install the driver with pip install "psycopg[binary]" sqlalchemy. Differences between SQLite and PostgreSQL still show up in types and functions, so test on the same database you will use in production before you ship.

What about MySQL or MariaDB?#

MySQL and MariaDB are relational databases as capable as PostgreSQL for most web applications. Django supports both and recommends the mysqlclient driver. Other options are PyMySQL, written in pure Python, and Oracle's MySQL Connector/Python. In SQLAlchemy, the URL becomes mysql+mysqldb:// or mysql+pymysql://.

Pick MySQL when your team already knows it, when you will integrate with a system that already runs it, or when it is what your host offers. The detailed comparison is in PostgreSQL vs MySQL.

When does MongoDB make sense in Python?#

MongoDB stores documents that look like Python dictionaries, with no fixed schema. It fits catalogs with varying attributes, event logs and profiles that change with each app version. See what MongoDB is.

The official driver is PyMongo. Watch out for old tutorials: MongoDB deprecated Motor on May 14, 2025 in favor of PyMongo's own async API (AsyncMongoClient). Motor received only bug fixes until May 14, 2026 and, after that, only critical fixes until its end of support.

import os

from pymongo import MongoClient

client = MongoClient(os.environ["MONGODB_URI"])
profiles = client["app"]["profiles"]

profiles.update_one(
    {"_id": "user-42"},
    {"$inc": {"xp": 10}, "$setOnInsert": {"plan": "basic"}},
    upsert=True,
)
print(profiles.find_one({"_id": "user-42"}))

In Django, MongoDB has no official support. There is the third-party django-mongodb-backend, and Django's documentation warns that the features third-party backends support vary considerably. If the project is Django, stay relational.

Can Redis be the main database?#

Redis keeps data in memory and answers very fast. In Python, the redis package includes both a sync and an async client (redis.asyncio). It is the right tool for caching, sessions, rate limiting, task queues (Celery and RQ can use Redis as a broker) and counters.

As a main database it needs care: writing to disk is configurable, and the data has to fit in RAM. In most projects Redis sits next to PostgreSQL or MongoDB, not in their place. The difference is explained in Redis as a cache or as a database.

Which database should you pick for each scenario?#

When in doubt, start with PostgreSQL and SQLAlchemy (or the Django ORM). You get a database that can grow and a layer that hides most differences between engines. For bots, the detailed analysis is in which database to use for a Discord bot.

Which mistakes should you avoid?#

• Building SQL with f-strings. Always use parameters (? in sqlite3, %s in psycopg, or the ORM). An f-string with user input opens the door to SQL injection.
• Opening a connection per request. Create the engine or pool once and reuse it.
• A sync driver inside async code. One blocking query stalls the whole FastAPI or bot event loop.
• Credentials in code. Read the connection URL from an environment variable, as in the examples.
• No copy of the data. Schedule dumps (pg_dump, mongodump) and test a restore before you need one.

To ship the application together with the database, see how to host FastAPI, Flask or Django.

How to do this on Square Cloud#

On Square Cloud you can host MongoDB, PostgreSQL, MySQL and Redis starting on the Standard plan (R$44.99 per month); Hobby does not include databases. You create the database in the dashboard (name, memory and engine), and it is provisioned in seconds in the same datacenter as your applications, with an average latency of about 1 ms. PostgreSQL is native PostgreSQL, without extensions, and Redis has persistence.

Almost nothing changes on the Python side: put the full connection URL the dashboard hands you in an environment variable on your application (DATABASE_URL, MONGODB_URI) and read it with os.environ, as in the examples above. The change applies on the next restart, with no rebuild. TLS is mandatory: download the certificate in the dashboard and point the driver at the CA; on PostgreSQL and MongoDB the client also presents the .pem certificate. On Redis, use rediss://. Because the database accepts outside connections, you can run the same code on your machine with the same certificate.

You take manual snapshots whenever you want, and every plan that includes databases also has an automatic daily snapshot, kept for 7 days. A snapshot can be restored into the same database, another one of the same engine or a new database, or downloaded as a .zip. See the engines and plans on the database hosting page.