[kumarvvr]

Question from a Python web developer. (Django mainly, exploring Flask presently)

For a complex web-app, would you suggest an ORM (looking at SQLAlchemy) or a custom module with hand written queries and custom methods for conversion to python objects?

My app has a lot of complex queries, joins, etc. and the data-model is most likely to change quite a bit as the app nears production. I feel using an ORM is an unnecessary layer of abstraction in the thinking process. I feel comfortable with direct SQL queries, and in some cases, want to directly get JSON results from PGSQL itself.

Would that be a good idea, and more importantly, scalable?

Note : My app will be solely developed by me, not expecting to have a team or even another developer work on it.

[kangoo1707]

Use both. Many of the business logics are just as simple as query by id, filter/sort by a couple of columns. A smart ORM will handle fetching relationships without hitting N+1 problem

For advanced queries, you can write raw SQL

The way I see it, an ORM has three useful features:

- A migration/seed mechanism (you will need it anyway)

- A schema definition for mapping tables to object

- A query builder

If you feel that an ORM is too heavy, you can seek for just the query builder.

[fernandotakai]

i worked on a mid-sized django app and that was basically what we did:

* for normal queries (select /cols from table where id etc etc) we just used plain django orm. even for weird joins, django orm makes it a lot easier than using raw sql

when we needed raw speed, we just wrote raw sql and delegated to django sql layer -- that way we leverage everything the framework has with raw sql power.

[scardine]

Even when the ORM models start to get cumbersome I like to use sqlalchemy.sql to assemble SQL queries.

It maps pretty much 1:1 to SQL and for me it beats the alternative (using text interpolation for composing queries).

[CuriouslyC]

SQL alchemy is good for fairly straightforward queries where you mainly need to do "select * from ..." and you want to pull down related rows from another table based on a foreign key in the first table as a separate query. It's also good if you have a lot of junior devs that don't know SQL and you want to encapsulate complex sql logic for them.

If you're doing anything more complex than these basic sorts of queries and subqueries, or your developers are proficient in sql, using even a very good ORM like sqlalchemy is going to be a step down.

Since you say you're doing this all yourself, and SQL is probably the most ubiquitous programming language (in terms of percentage of jobs requiring it, not total LOC) so learning opportunities there are more valuable, I would go direct.

[dliff]

I have used Postgres with both Django and Flask quite a bit now.

Since you're probably used to dealing with and migrating your tables manually, I would keep custom SQL for all your complex operations, and use SQLAlchemy for doing basic insert/update/select. Django also has an "unmanaged" mode where you can create a model and it will avoid trying to create a migration to create the table.

Of course, you have to manually update the model if you manually change your DDL.

Watch out for differences on how you are serializing data from Django/SQLAlchemy models vs. raw dicts from PsychoPG.

I like to organize my SQL by keeping each query in a separate .sql file and writing a little wrapper that fetches the files (+1 for caching it) and then executing it. I'm not a fan of lots of inline SQL mixed with Python.

Overall I think it's a great + powerful setup!

[kumarvvr]

I usually put all my SQL statements in a single python file, and import the file as a module. I get to have descriptive variable names for the queries.

[mitch3x3]

Pyscopg2 + Raw SQL inside of “”” “”” strings, and use %(foo)s as a parameter placeholder. Cur.execute will accept a parameter dictionary like:

cur.execute(query, {‘foo’: bar})

Passing values directly into cur.execute is the best way to prevent SQL injection as well since it will sanitize the input params upon running

[luhn]

Fellow Python-Postgres web dev here. (Pyramid is my framework of choice, check it out!)

I'm developing a web application that uses SQLAlchemy. The ORM has been a huge boon for CRUD functionality. We also have some very complicated reporting features and use SQLAlchemy's query builder almost exclusively. I find that the query builder maps very cleanly to SQL, so I can still "think" in SQL while writing and reading it. And the query builder makes complex query composition easier to manage.

[rch]

I've found that 'sqlalchemy.sql.text' works well for complex queries that don't need to be built up incrementally, and the fluent sql interface is great otherwise. Also, reflection can be really useful when working with existing databases, and for maintenance scripts that might not need to be tied directly to your model.

SQLAlchemy provides more than just the ORM... I actually wish the docs were structured differently to better emphasize that in search results, etc.

[nicwolff]

You can use SQLAlchemy Core for SQL generation and execution, without using its ORM. This lets you build queries from reusable Python objects rather than strings, and use Alembic for DB migrations, while still retaining control over the generated SQL.

[mixmastamyk]

> likely to change

Hard to say, but don't forget about migration support, which is quite helpful.