News for hackers. Stuff that matters.

Ask HN: Postgresql or Sqlite for Python WSGI apps?

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?
( 6 comments | 11 points | on HN )
Re:Ask HN: Postgresql or Sqlite for Python WSGI apps? by apu on Monday May 30, 2011 @03:53AM (#2598815)
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!

Re:Ask HN: Postgresql or Sqlite for Python WSGI apps? by jamii on Monday May 30, 2011 @03:56AM (#2598826)
> 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.

Re:Ask HN: Postgresql or Sqlite for Python WSGI apps? by codeape on Monday May 30, 2011 @05:46AM (#2598956)
Never use SQLite in production if you expect more than one simultaneous user.
Re:Ask HN: Postgresql or Sqlite for Python WSGI apps? by ra on Monday May 30, 2011 @03:38AM (#2598795)
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.
Re:Ask HN: Postgresql or Sqlite for Python WSGI apps? by wladimir on Monday May 30, 2011 @05:24AM (#2598923)
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.

Re:Ask HN: Postgresql or Sqlite for Python WSGI apps? by gte910h on Monday May 30, 2011 @03:50AM (#2598809)
But I'd say: Really hold off a bit before you bother with the postgres setup...it hurts a bit if you're not familiar with it.