I found this gem of a comment in the code that perfectly described what I ended up having to painfully learn on my own:
# SQL ORM Missive:
#
# Don't use models to automatically generate schemas. After iterating several
# times with SQLAlchemy (and nearly every other ORM from frameworks both long
# since dead and still trendy), to get a schema "just right" requires
# entirely too much fiddling in the ORM. My hard earned lesson: SQL is the
# right dialect to describe your database's structure (read: do not use an
# ORM to generate DDL). Other than portability, what's the advantage of
# describing your schema in SQLAlchemy?
#
# For as fantastic as SQLAlchemy is, using SQLAlchemy to generate schema is
# the equivalent of giving yourself a lobotomy while in the middle of
# attempting to write a Pulitzer Prize article. Why handicap yourself? Use
# SQLAlchemy for what it excels at: generating efficient SQL based on
# mappings between Python classes and database tables. Manually generated
# schema results in a few extra lines of code in a tiny number of files, but
# it goes a long ways towards clarity, predictability and
# explicitness. Automatic schema migrations, you say? l2dba or gtfo.
#
# Re: PostgreSQL vs other RDBMS'es. Here's another piece of hard earned
# knowledge from my last 14yrs of dorking with countless websites and
# databases: portability to other relational databases is a straw man
# argument. PostgreSQL is faster, more stable, the most standards conformant
# relational database, is ridiculously feature rich and cheaper to operate
# than its alternatives (both in terms of operations and efficiency on
# hardware), and that's the short list of reasons. If you are building a
# website, I am unable to produce a single fact based, data driven argument
# to use MySQL[1]. Hiring? Training? Replication? Feh, that's FUD. Please
# stop perpetrating harm on your own organization and applications by
# succumbing to unfounded beliefs and lemmingism.
#
# [1] There are three situations where I would use a different relational
# database. The first two situations are: 1) if my target hosting platform
# did not support SystemV shared memory, or 2) I am architecting something
# for an embedded or mobile device. In the former case, I'd change hosting
# providers or would fire my system administrator, and in the latter, I'd use
# SQLite. What's the third case? The answer is DB2, but what's the question?
I also wish I had read that. I remember thinking it through, as I had kind of a hard time switching from Django ORM to SQLAlchemy, and ultimately I made the decision to use SQLAlchemy for everything because it minimized the possibility of me creating a schema that didn't perfectly match the ORM schema.
I worried that this would lead to having problems that weren't easily identifiable, and I was just learning Flask and SQLAlchemy at the time, I was making plenty of other mistakes on my own, and didn't need the added frustration.
Of course, as he says, it's a very fiddly process, and I either over or under-fiddled enough times to negate whatever frustration savings I might have otherwise avoided.
Gosu [1] has a neat ORM [2][3] that actually parses your SQL .ddl file and creates types based on that. It uses Gosu's "open type system" to create types without any code generation.
I really liked using it over the summer even though it was a little unpolished and lacking some functionality. I suspect it has improved since then; it was being actively developed while I was using it.
It thankfully didn't take two years, but I've brought sanity to a messy database by:
1. reverse-engineering its schema into code
2. simplifying that code, and migrating the data regularly
Code has been pretty malleable, making it easy to factor in common patterns, erase accidental complexity, and generally bring commonality where there was discordance. It is also easy to version control, and to generate migrations from.
(I have no argument that PostgreSQL is the only sane database, but portable ORMs can be used to ease migration towards Postgres, too)
Why is SQLAlchemy not recommended? Or are you referring to complex and extensive schemas? Having used it in some medium-sized projects with much success, I'm unsure why people seem to be regretting using it..
I think you have a valid point but from the yes/no discussion that is happening here do you have any recommendations for alternatives? in terms of best practices for Flask App Structure
I'm also a Flask user, and I honestly don't see anything that might cause alarm, though I've only scanned the code so far.
In fact, it looks much more structured than the majority of my own Flask apps, but usually, by the time an app gets to the size that this already is, I'm either moving or considering moving it all to Django.
Actually, yeah, I take it back. I don't know that I'd swear it off altogether, but the INSTALL document is really quite convoluted.
While I appreciate the author's statement on avoiding SQLAlchemy to create models, I know that it can be done, and generally, that's what a person looks for from a framework like this -- to get the fiddly bits right.
It might work or it might not, but it will definitely take awhile, and the comments in the INSTALL doc are seriously snarky.
Sure he had fun with the install doc but it looks complete. I would much rather have some snark and complete instructions than an incomplete quick start tutorial.
