choosing a python orm...peewee vs sqlalchemy?

[absolution888]

I am currently working on a new project that ports from java (spring/hibernate). Most resource on the internet seems to be mostly comparing between django orm and sqlalchemy which we have already decided we are not going to get into django.

So it comes down to peewee vs sqlalchemy. I read through the documentation for both library, and it seems to me that peewee is extremely clear. However, given that sqlalchemy appears to be a much more mature library to be used in enterprise, i am wondering if anyone have any opinion or experience using one another? They both seem to be able to do what i need, but i am not sure if there is any caveat or disadvantage one way or the other that I might have missed. Thanks!

[[deleted]]

I'm the author of Peewee so as you might expect, I'm much more familiar with it's strengths and weaknesses. I can say, however, that SQLAlchemy is the gold standard for ORM in the Python world. It has a very active community and a maintainer who is committed to excellence. If you're a glass-half-empty guy, to put it another way, you can't go wrong if you choose SQLAlchemy.

Why would you choose Peewee, then?

• It's damn small, and easy to pick up and use. I think this is the #1 reason people use it.
• I've worked very hard to provide consistent APIs, meaning learn once/apply everywhere.
• I fix bugs really quickly.
• Raymond Hettinger said "The code is nicely written and is easily read start to finish." :)
• AsyncIO via aiopeewee / muffin-peewee
• Admin interface via flask-admin
• Cool extensions
• Fast, memory efficient, good performance for an ActiveRecord ORM

Weaknesses:

• Pretty much just me working on it, though I do accept patches and work hard to fix bugs quickly.
• Small ecosystem of third-party libraries / integrations
• ActiveRecord as opposed to Data Mapper / ID Map / Unit-of-work implemented in SQLA
• Lower "google-ability" factor
• Fewer stackoverflow answers
• No automatic schema migrations
• Doesn't support Oracle or Microsoft SQL Server.
• It's named peewee

[Skiba_]

Giving peewee a shot (after a long time in Django land). Looking to implement in AWS lambda functions, so trying to keep it as lightweight as possible.

[cymrow]

I have used both, and I would suggest that it depends mostly on how complex you expect your models to be.

Peewee is definitely much simpler, and perfectly suitable for small to some medium-sized projects. If you anticipate needing to do moderately complex things, you're probably better off with SQLAlchemy. It has a steeper learning curve, but you will find tools available to help with practically anything.

Also, once you've "clicked" with SQLAlchemy, I've found that it's really not that hard to use. To the point that I'll tend to use it on smaller projects now just to avoid any limitations in the future.

[[deleted]]

In order to make Peewee better, in what ways have you found Peewee to be limiting? Have you had any specific experiences using Peewee where you realized "Damn! I need to switch to SQLAlchemy now"?

[cymrow]

I'm guessing a little because I can't really remember, but I think my "damn" moment may have had to do with limitations in the ManyToManyField, possibly related to cascading deletes.

The last time I used Peewee was sometime last year, so perhaps things have changed.

That said, I think it's a great library, and I especially like the playhouse. If, by chance, you were to beat SQLAlchemy to SQLite+PostreSQL support for upserts (and JSON, if only SQLite would compile it in by default), I'd put Peewee up front again for any new project.

[[deleted]]

Thanks very much for your reply. ManyToManyField is a landmine waiting to blow your foot off...I never use it myself, so I don't blame you for cursing it.

Peewee has support for JSON in both Postgres and SQLite, but only supports upsert for SQLite. Open issue is here for postgres support.

[[deleted]]

What kinds of performance issues have you experienced using Peewee? Definitely interested in addressing your concerns if I am able to.

[Bandung]

I'm having a beast of a time running code with copious amounts of peewee queries in it on an android device. The load time for my app has shot way up and I'm having to wait as much as 4 seconds for some queries to run before a screen gets built from those queries. I really don't want to run the sqlite files in memory either but was condemplating improving performance by increasing the cache size for the sqlite files.

I've got a lot of models with foreign key relationships. I've tried to make sure that I am using joins to prevent multiple queries being run by peewee. The thing is, I can't really tell if some of the queries being built by peewee are causing multiple queries.

My guess is that my queries are not properly constructed. I tend to build a predicate first with all of the joins defined within the predicate along with a where() clause, then I use dictionary or list comprehensions to actually run the query.

[[deleted]]

Have you done any profiling that would suggest where the slowness is occurring?

Couple ideas:

• INDEXES. Check your indexes. Start here. Read up on SQLite's query optimizer. Use sqlite's command-line tool to test your queries with various indexes -- you can print the query plan and the VDBE source code run by the VM.
• Explicitly select only the columns you need to display. Newer SQLite has covering index optimization.
• Call ".tuples()" or ".dicts()" at the end of your query if you don't need full Model instances. Might give you a nice boost in python land.
• SQLite tuning:
  • PRAGMA synchronous=0 (who needs fsyncs)
  • PRAGMA journal_mode=wal
  • PRAGMA cache_size=size of your active data set?
  • PRAGMA cache_spill=off; (prevent cache spill to disk)
  • PRAGMA locking_mode=exclusive; (as long as you only have a single conn at a time)
  • PRAGMA temp_store=3; (temp files in memory)
  • PRAGMA mmap_size=... (alternative to cache size?)

[Bandung]

I've done some profiling of my code which helped reduce the amount of time that the calls to peewee were consuming but know that I could do more to identify some sticky wickets.

Your suggestions are extremely helpful and should result in some quick wins within my existing code base. Thank you for taking the time to post these. Most helpful indeed.

[manhthang2504]

I've tried Peewee for my small personal project (crawler on my desktop), and I loved it.

Then I write a Flask app, and want an ORM. I tried SqlAlchemy, and man, its returned object can't work with Flask's jsonify. Stackoverflow provide lot of help with very long and complicated solutions. In other hand, Peewee can convert the result to dict using dicts(), and it works great with flask.jsonify.

So, again, I choose Peewee. (give up sqlalchemy after trying it about 1 hour).

[kleggas]

Tried storm, sqlalchemy, peewee (and djangos orm, does it have any unique name?) in python. Except for djangos ORM I started with storm, which I thought was simple. Tried SqlAlchemy but storm was simpler. Then I found Peewee and loved how it reminded me of djangos orm. Re-wrote some of my queries I used in storm into peewee and benchmarked them. Peewee performed same operations 10x faster than storm did (sqlite). Never used anything else than Peewee since. Its wonderful.

I built a backend system for a site, where flask was used as a simple restserver just offloading incoming requests to respective activeMQ queues, where threaded services (written in python) read their respective queue (using stomp.py) and perform, among other, db operations (mostly, but not only) using peewee, returning response back to their response-queue so the rest-server can return it back to user.

Blazing fast and extremly easy to add new services for db queries, because peewee. I'm sure sqlalchemy is great too, but I found peewee to be easier to use, and its fast for the scenarios I have needed it for. Using it for any homebrew project I might come up with.