All that being said, I've now been burned 3x because small "hobby" projects I started using sqlite have had major problems when they became "real". This is because sqlite is really awful when it comes to concurrency...they're NOT kidding about that in the docs! In one extreme case, I even saw major corruption of the database with heavy concurrent usage. In two of those cases, I've migrated to postgresql, and in one case to redis (a non-sql awesome database that's super-fast).
Migrating to a different SQL solution (e.g., postgresql) shouldn't be a big problem if your database adapter is built well. I'm not really familiar with Flask, but hopefully it should be fine. The things you have to watch out for:
- SQLite has dynamic typing and is very forgiving -- so it will save whatever invalid formats you give it as-is. This can really hurt you when you try to migrate to something more correct, like postgresql.
- Every SQL system has quite different date and time functions, which is really annoying
- Pragma's will of course be different, which might be an issue
- In general, any sort of "advanced" feature will be an issue.
Hope this helps, and good luck!
If you use sqlalchemy and don't import dialects, it should be a one line change.
I can recommend SQLAlchemy. It's very flexible (unlike a lot of simpler ORMs it doesn't limit what you can do) and supports sqlite, mysql, postgres and others.