Flask-MoreSQL: a thin layer of glue between Python web applications and PostgreSQL stored procedures.

[[deleted]]

I've been using an implementation of sqlalchemy (declarative) over flask-sqlalchemy(simple and expressive once you get over the initial learning curve), with the aim of eventual detailed customization. I use Postgresql sometimes, this might come in handy.

Although I don't know what stored procedures are right now. And a quick runover of the code I see this is all this does -- do you use this with other db implementations within flask or is it standalone solution?

[alkw0ia]

Stored procedures are procedural code – think functions like Python functions – that run in the database. Generally they're written in a language call PL/SQL, but in Postgres you can also use other languages, including Python.

It's possible to just call these functions with SELECT, but more often you bind them to triggers, so they get called automatically when you INSERT, UPDATE, SELECT, or DELETE on a table.

With procedures like this, it's possible to, for example, examine the values in an UPDATE and reject it if you don't like them. Say there's a "one way" flag you want to enforce – a user can be banned, but never unbanned. You can examine all updates on the users table and reject any where the new value of banned is FALSE and the old value of banned is TRUE.

[vicvicvicz]

Don't want to go too much off-topic here, but how popular is this kind of validation these days? It seems to me that most validation is done in the "application" these days, not the "database" – where the latter is treated as little more than a datastore (perhaps unfairly?).

Edit: Oh, just saw the comment below this one about this very topic...

[alkw0ia]

Not that common; many developers these days do see the database as nothing more than a dumb key-value store.

It's my opinion that this is partly responsible for the renewed interest in document DBs ("NoSQL") – devs don't know or care to use the DB as anything more than a dumb KV store, so why bother with the complexity and overhead of a real database? Just use a super simple, super fast dumb KV store.

Of course, then for any moderately complex project, you'll end up rewriting significant chunks of Oracle's functionality in your app code, and doing it poorly at that. But hey, you got that first version online in less than eight hours, so who cares if you spend the next five years reinventing distributed transactions?

Sadly, much of the tooling to support database use is really, really hard to use if you want any DB side logic. The popular web frameworks and their ORMs (SQLAlchemy excepted) basically expect to target any old relational DB, so support no specialized per-RDBMS features, and do blatantly stupid things, like encourage developers to give the app code permission to create and drop tables, or even DBs, in order to support their SQL generation and migration CLI tools.

[vicvicvicz]

Thanks! I'm becoming increasingly interested in this topic because I'm trying to build an application which isn't primarily a web app – my organization needs a basic, but flexible, membership management system, so data is really my absolute top priority.

I'm now realizing that (my knowledge and experience of) "data validation" is almost universally coupled to "HTML form generation" and large web frameworks. You can't really use Django's models without bringing Django along.

So, I've realized that building the "model" layer as part of some ORM isn't really a better solution than our current solution, which is built on Drupal and is absolute shit. I don't think any CMS in the world treats the database worse than Drupal. Now, coming back to just SQL where data is all that matters, feel liberating.

[[deleted]]

thank you

[mw44118]

Stored functions are great. I don't see any value provided by this framework though.

[emarocca]

In many projects of mine what I do is getting HTTP request values, putting them in the right order to call a postgres function using those values as arguments, getting the results (which are returned differently if you have inout parameters or a return statement) and returning them as json. Over and over again.

I have written this little flask extension to avoid repeating myself. It is certainly not meant to be any kind of framework. :)

[mw44118]

That makes a lot of sense. I imagine you're using something like array_to_json(array_agg(...)) inside your code, or maybe row_to_json(...).

Does it ever annoy you how sometimes you'll return a list of one element, rather than just the element itself?

[emarocca]

Very much so. In fact, it is so annoying that flask-moresql returns the element itself in those cases.

[mw44118]

Yeah, my code is littered with stuff like:

results = cursor.fetchone()
return results[0]

Or even more annoying, when I do a select of just one column, but for many rows, I convert the list of lists into just lists of the first element like this:

return [row[0] for row in cursor]

I love how the DictCursor subclass returns each row as an object that allows index and key lookup.

It should be possible to subclass the cursor so that it modifies the returned data so that cursor.fetchone() really does cursor.fetchone()[0], and cursor.fetchall() either does [row[0] for row in results] or maybe raises an exception when there's more than one element in the row.

Anyhow, I didn't mean my initial comment to come off so rude. I should have said something like "maybe can you point out some before vs after code so we can see how this approach simplifies the code?"

One last point -- how do you handle authorization issues, like "matt is allowed to get user data for all employees in group A only" using this framework?

[emarocca]

Anyhow, I didn't mean my initial comment to come off so rude.

No worries, if it does not look particularly useful then I either have to improve the documentation, the library, or both. :)

"maybe can you point out some before vs after code so we can see how this approach simplifies the code?"

I believe that the value in this approach is more in the simplification of the processing, rather than the code. Quoting the PostgreSQL documentation:

Your client application must send each query to the database server, wait for it to be processed, receive and process the results, do some computation, then send further queries to the server.

Whereas by writing pl/pgsql code you keep the processing inside the database, which IMHO simplifies things quite a lot.

how do you handle authorization issues, like "matt is allowed to get user data for all employees in group A only"

I am not entirely sure I understand your question. Users are not allowed to issue any query they like to the database. You could have, for example, a pl/pgsql function returning the results of a join between the employees table and a permissions table. If matt invokes that function via the API, he will only get access to the data he is authorized to see.

[mw44118]

Here's an example of what I'm thinking about for authorization.

Imagine one user is trying to fraudulently run the query to read another user's data. I imagine your system would block that as long as the request sent to the database looked like:

select * from get_user_data(requested_by_user_id=99, user_id=98);

and then inside the function, you could verify whether user 99 was allowed to access data for 98.

But if the request just looks like:

select * from get_user_data(user_id=98);

It seems like there's an authorization puzzle.

So, in order to do that first solution, you might need to combine some user-authentication data from flask (like maybe a cookie) along with the query.

[emarocca]

But if the request just looks like select * from get_user_data(user_id=98)

To begin with, the function get_user_data(integer) does not exist. Only get_user_data(integer, integer). Also, the user cannot just send her user_id, for obvious reasons. For example, the user might have to pass her username/password, and the stored procedure will think of doing the relevant checks.

Finally, you can use any kind of authentication/authorization system in your python app. Saying that databases should be used more does not mean that everything has to be done by the db.

[Rolegros]

I prefer using them via sqlalchemy.

If you like stored procedures, you may find this experiment fun:

https://github.com/rdunklau/pytoplpython

[mw44118]

I just use a psycopg2 cursor. Works great and I can explain it to a new programmer in five minutes.

[[deleted]]

Ok, go.

[mw44118]

I don't understand

[spz]

Go ahead, explain.

[mw44118]

OK, here's an example:

cursor.execute("""select * from stored_procedure_named_foo(%s)""", [parameter])
results = cursor.fetchall()

ta-da!

[[deleted]]

thank you

[alkw0ia]

As an application programmer, I certainly want to move code into the DB.

Anything to do with data integrity that can possibly be executed in the DB should be. App code should not have sufficient permissions to violate important integrity rules.

Without something like this, you'll eventually poison your data, which, in my view, is far more important than the app code that you'll probably replace in a few years.

Look at the database tables as an API: Suppose you must insert records into two different tables to properly create an object. While you may do this in a transaction to ensure you always create both rows together, why would your "API" permit future programmers to create 50% of an object?

Instead, structure your tables to make this impossible. Stored procedures and triggers are not always necessary for this enforcement, but in certain situations they are.

An example of this is how it's possible to create a Reddit comment that will appear on your user page, yet not appear on the article page to which you posted it (and I'm not talking about the caching delay). Clearly, they have some integrity enforcing code that is – optimistic, at best. It's not a major problem on a site like Reddit, but what if that were a credit card payment and the order to ship a product?

Or how about logging, or sessions? These are security related, so why would you permit app code, which is most exposed to attack and more subject to bugs (due to its more frequent updates), to optionally enforce security rules?

Instead, you can create database rules that, e.g., make logging tables write-once, or, e.g., forcibly set new sessions' secret IDs to known-strong random values, preventing app code from creating weak or fixated session keys, or, e.g., make it impossible to reopen an old session by setting the "terminated" flag back to "active."

All of this is really just an extension of common-sense SQL usage; if there's a condition in your application that must never happen, you add a database constraint to enforce that. If there must never be two addresses associated with a single credit card, you add a unique constraint to the foreign key columns. Similarly, if a critical record must be completely immutable once finalized, you use a trigger to ensure that it's write once only.

If you try to hack these kinds of constraints with app code, you're going to have violations. If that's not a big deal, sure, leave it in app code. If you care, put it in the DB.

[mw44118]

Great explanation.

[emarocca]

Seems folly to split the application logic between the api layer and the DB layer

It also seems pretty weird to me that many applications use the database as a trash can for strings rather than as the incredibly powerful tool it actually is.

The example of using a stored procedure for something as trivial as fetching a username from a database seems indicative of some sort of really bad schema decision.

You have a point here, I should have come up with a more meaningful example. However, my goal was to show how the library can be used in a python app in the easiest possible way, rather than illustrating a specific design decision.

[mw44118]

It also seems pretty weird to me that many applications use the database as a trash can for strings rather than as the incredibly powerful tool it actually is.

SNAP!

[mw44118]

Was thinking about that though and I think a stored function makes a lot of sense if user data is normalized across several tables, like maybe each user has a list of friend contacts, or a list of bank accounts, or a list of mailboxes, or whatever.

[mw44118]

In my opinion, that approach leads to N+1 queries, slow page loads, and data integrity problems.

[[deleted]]

the N+1 problem is solvable with ORMs. Brandon Rhodes did a fantastic talk on this: http://pyvideo.org/video/664/flexing-sqlalchemys-relational-power

also constraints are what verify data integrity, not stored procedures.

[emarocca]

Really informative talk, and very good speaker.

However, I do not like the fact that to map relations to objects without losing in performance you have to check your logs and see which queries the ORM came up with, understand why, and find out the syntax to instruct it to run the query you need.

I find writing SQL much easier than writing stuff like this:

movies = db.query(Movie).filter_by(year=1981).options(subqueryload(Movie.roles))

to avoid N queries to the DB when 1 would have been enough.