5 ms·

I guess I've been corrupted by working in a heavily database oriented environment, and further corrupted by the fact that it's Oracle, but for most of our own u

I guess I've been corrupted by working in a heavily database oriented environment, and further corrupted by the fact that it's Oracle, but for most of our own use cases, plain SQL is preferable to this. We'll probably have to run it ad hoc against the database anyway to figure out what it's doing and/or tune it. Many of our queries go into the hundreds of lines[1][2], so anything that obscures the underlying SQL itself tends to make life more difficult. But perhaps it's just a matter of getting used to a different format. [1] Legacy environment. [2] Edited to add: most of our newer apps don't actually invoke huge queries from the app layer. We now tend to layer a view or pl/sql table function on top of the hairy query, so that at the app level it's mostly "select * from blah." For that simple a query, a DSL like this is probably fine, albeit, unnecessary.

ghotli 15y ago
SQL that is hundreds of lines long can be hard to follow and harder to maintain. SQL is probably the fastest path to execution but perhaps not the most testable and maintainable. Perhaps that's the lens to view it through.
zzzeek 15y ago
I wrote SQLAlchemy after spending about eight years in three all-Oracle jobs one after the next, and I wrote it specifically around the use case of being able to compose hundreds-of-lines queries from smaller pieces.
- jaylevitt 15y ago
  Can SQLAlchemy core do arbitrary, non-ANSI things, like create a Postgres user-defined type or a SQL function? And if so, can I use SQLAlchemy as a standalone pre-processor? My app's in Rails so I can't exactly go integrate it..
  - zzzeek 15y ago
    > Can SQLAlchemy core do arbitrary, non-ANSI things yes. many DB specific constructs are included and you can make your own: http://www.sqlalchemy.org/docs/core/compiler.html http://www.sqlalchemy.org/docs/core/compiler.html philosophically SQLA does not consider "abstraction" to be "hiding", so ultimately no features of the backend store are concealed > like create a Postgres user-defined type sure, the ENUM type does this right now automatically (i.e. CREATE TYPE...AS ENUM). For other kinds of PG types, you can emit the CREATE TYPE yourself then link to it with a SQLAlchemy-side custom type. You can integrate those two steps also. > or a SQL function? Again a SQL function you're going to be using PG's PL/pgSQL or other embedded language to create it, so you roll the CREATE FUNCTION yourself, then call it on the Python side using func: http://www.sqlalchemy.org/docs/core/tutorial.html#functions http://www.sqlalchemy.org/docs/core/tutorial.html#functions
- msluyter 15y ago
  Fair enough. I'll check it out.

ghotli 15y ago

SQL that is hundreds of lines long can be hard to follow and harder to maintain. SQL is probably the fastest path to execution but perhaps not the most testable and maintainable. Perhaps that's the lens to view it through.

zzzeek 15y ago

I wrote SQLAlchemy after spending about eight years in three all-Oracle jobs one after the next, and I wrote it specifically around the use case of being able to compose hundreds-of-lines queries from smaller pieces.

jaylevitt 15y ago
Can SQLAlchemy core do arbitrary, non-ANSI things, like create a Postgres user-defined type or a SQL function? And if so, can I use SQLAlchemy as a standalone pre-processor? My app's in Rails so I can't exactly go integrate it..
- zzzeek 15y ago
  > Can SQLAlchemy core do arbitrary, non-ANSI things yes. many DB specific constructs are included and you can make your own: http://www.sqlalchemy.org/docs/core/compiler.html http://www.sqlalchemy.org/docs/core/compiler.html philosophically SQLA does not consider "abstraction" to be "hiding", so ultimately no features of the backend store are concealed > like create a Postgres user-defined type sure, the ENUM type does this right now automatically (i.e. CREATE TYPE...AS ENUM). For other kinds of PG types, you can emit the CREATE TYPE yourself then link to it with a SQLAlchemy-side custom type. You can integrate those two steps also. > or a SQL function? Again a SQL function you're going to be using PG's PL/pgSQL or other embedded language to create it, so you roll the CREATE FUNCTION yourself, then call it on the Python side using func: http://www.sqlalchemy.org/docs/core/tutorial.html#functions http://www.sqlalchemy.org/docs/core/tutorial.html#functions
msluyter 15y ago
Fair enough. I'll check it out.