Separate DB Drivers Required?

[liquidgenius]

If the need is to create and interact with a Postgres database, it seems from the documentation that standard SQL queries are executed to create the tables. Once created Databases can interact with them using the Sqlalchemy (SQLA) Core syntax.

What if we wish to generate the database using SQLA syntax as well with create_all() and not standard SQL?

The following code fails with app.models.sqla holding valid SQLA models to create the database:

# Standard libraries
import asyncio

# Third party libraries
import asyncpg
import uvicorn
import databases
import sqlalchemy as sa

# Local libraries
import app.core.security
from app.models.sqla import *

# database
DATABASE_URL = "postgresql://postgres:abc123@localhost:5432/db"
engine = sa.create_engine("postgresql://postgres:abc123@localhost:5432/db")
metadata.create_all(engine)
db = databases.Database(DATABASE_URL)

Error:
ModuleNotFoundError: No module named 'psycopg2'

Attempts to define the driver dialect explicitly fail as well with "postgresql+asnycpg://..."

Databases does not support psycopg2 and SQLA does not support asyncpg. This presents some complexity right off the bat. Is it standard practice to import both drivers; psycopg2 to create the database, and then use the database through Databases with asyncpg?

[liquidgenius]

Installing psycopg2, pip install psycopg2-binary, and designating it as the dialect in the Sqlalchemy connection string allows successful creation of the database while Databases still uses asyncpg driver. I assume Databases does not let you specify a dialect in the connection string because currently only asyncpg is supported.

# Connection strings
SA_URL = "postgresql+psycopg2://postgres:abc123@localhost:5432/db"
DB_URL = "postgresql://postgres:abc123@localhost:5432/db"

# SQLA create database
engine =  sa.create_engine(SA_URL)
metadata.create_all(engine)

# Databases initialization
db = databases.Database(DB_URL)

It would be nice to either reduce the complexity by eventually moving to a cross-compatible driver, or more clearly define this nuance in examples and documentation. (Unless I missed it somewhere.) Thanks again for your work on this, it looks promising!

[liquidgenius]

I know there is limited benefit because you only do this once at the start, but I threw a quick convenience class together, just to practice with Dataclasses:

from typing import Optional
from dataclasses import dataclass, field

@dataclass
class MultipleDrivers:
    """ Composes both Databases and Sqlalchemy connection strings. psycopg2 should also be installed. """
    db:       Optional[str] = field(init=False, repr=True)
    sa:       Optional[str] = field(init=False, repr=True)
    user:     str = field(repr=False)
    password: str = field(repr=False)
    domain:   str = field(repr=False)
    port:     str = field(repr=False)
    database: str = field(repr=False)
    
    def __post_init__(self):
        assert self.user or self.password or self.domain or self.port or self.database is not None, 'Please provide ' \
            'values for user, password, domain, port and database.'
        self.db = f"postgresql://{self.user}:{self.password}@{self.domain}:{self.port}/{self.database}"
        self.sa = f"postgresql+psycopg2://{self.user}:{self.password}@{self.domain}:{self.port}/{self.database}"

Usage

from app.core.utils import MultipleDrivers

# Initialize multiple drivers
drivers = MultipleDrivers('johndoe', 'pass123', 'localhost', 5432, 'db')

# SQLA create database
engine =  sa.create_engine(drivers.sa)
metadata.create_all(engine)

# Databases initialization
db = databases.Database(drivers.db)

[pmsoltani]

Came across something similar today, when I tried (and failed) to do alembic upgrade head. So to be clear, do I need to install psycopg2 just for DB migrations?

[vmarkovtsev]

We no longer require synchronous drivers, as we should. sqla/alembic are synchronous.

[pmsoltani]

@vmarkovtsev Thanks for the comment.

[aminalaee]

The README has this info now.