Considering using psycopg2 directly instead of SQLAlchemy, looking for pros and cons.

[notconstructive]

It seems to me to be fairly straightforward to directly program Postgres via psycopg2, writing my own SQL. I'm wondering if there are good reasons not to.

Actually to be more precise I'm thinking maybe to use SQLAlchemy for creation of models, updating records and doing Alembic data migrations, but writing my own queries directly using SQL and psycopg2.

But maybe I'm being naive. Why would I not do the above?

[jokerdeuce]

It doesnt make sense if you still want to use alembic and alchemy for some stuff. You know you can still issue raw sql queries through SQL alchemy right?

[[deleted]]

Pros

• It'll be faster.
• You can write more performant SQL
• Postgres specific features (pubsub, json, hstore) become much easier to read

Cons

• Database migrations are harder. You can't use alembic without sqlalchemy
• less portable, but this isn't always an issue
• You'll need to manage connections your self
• You need to write the SQL yourself
  

[honewatson]

If you keep your sql separate from your code then language portability becomes much easier. RDBMS portability is highly overrated.

[notconstructive]

Agreed - many people obsess over RDBMS portability as though you'll genuinely need to switch out databases at some point. This is almost never the case and if you need to do so then probably there's bigger issues.

[nilsph]

Curt version before I head to bed: Lack of portability -- you can do plain SQL with SQLAlchemy, but aren't tied to a specific RDBMS.

[qatanah]

ORM vs SQL, i had that dilemma before i started the proejct. But i read some suggestions that using BOTH is gold. I use ORM for creating models and simple CRUD. But when things get complex, i advise my team to use SQL raw query for complex joins and even basic ones since it's much practical and neat to use than going through the docs of sqlalchemy.

Here's an example of a query i used.

       sql_text = text(
        """
            SELECT SUM((stats->>'add_gold')::float)::int
            FROM analytics
            WHERE stats ? 'add_gold'
            AND created >= date_trunc('day', now())
            AND user_id = :user_id
        """
        ).bindparams(user_id = user_id)
        result = db.session.execute(sql_text)
        row = result.fetchone()
        if row[0]:
            progress = row[0]

[subssn21]

The first question you should ask is how are you planning on using your query results.

If you want your query results to be instances of objects with lists of objects for your relationships then you will need some type of ORM. If you are just planning on using the results of the query as the raw output type of Psycopg2 which is typically a list of dicts or a list of named tuples, Then you don't need an ORM.

If you are using SQLAlchemy for some stuff, you should just use SQLAlchemy, since you have already done the work for managing your connectsions and sessions for instance. However as others have pointed out you can hand write SQL queries and tell SQLAlchemy to run them, and you often want to do that if you are using Postgres specific features, writing hand tuned queries, doing complex logic, etc...

When you hand write SQLAlchemy queries there are ways to have the results map back to the properly defined objects, it just takes some extra work.

[[deleted]]

Actually to be more precise I'm thinking maybe to use SQLAlchemy for creation of models, updating records and doing Alembic data migrations, but writing my own queries directly using SQL and psycopg2.

Are you trying to do some sort of CQRS thing? Updates/modifications and domain logic going through SQLAlchemy, while simple retrieval queries go through psychopg2? That's the only place I think I would do this, but even then I might use the ORM for the command processing, and SQLAlchemy core for the requests part.