

Ask HN: Postgresql or Sqlite for Python WSGI apps? - samrat

Which database should I learn Postgresql or SQLite, or something else? I'm learning  Flask(http://flask.pocoo.org/) but I don't have any ideas yet about the type of apps I'll be developing. So, which is worth learning more?
======
apu
SQLite is awesome because it's so easy to get started with and because it's a
single file. This makes it trivial to move around to other
machines/backups/etc. You also don't need to setup any server, etc.

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!

~~~
jamii
> Migrating to a different SQL solution (e.g., postgresql) shouldn't be a big
> problem if your database adapter is built well.

If you use sqlalchemy and don't import dialects, it should be a one line
change.

------
codeape
Never use SQLite in production if you expect more than one simultaneous user.

------
ra
For web apps in general if you are using an ORM that supports both (and you
should be), SQLite is great for development, but Postgres is really the only
choice for production.

~~~
wladimir
Agreed! He should use an ORM and not bother with DB implementation details.

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.

