Beets: adopt sqlalchemy?

[jrobeson]

I know this was mentioned before, but I can't remember if we came to a decision on it. I'd like to start by using sqlalchemy core only, and not the ORM. The ORM can come later.

[sampsyo]

I'd be interested in talking about it in more detail. What benefits do you anticipate from switching to SQLAlchemy?

I actually like SQLAlchemy and I use it all the time in web projects, but I personally haven't missed it at all (neither the core nor the ORM) in beets development.

• Our interaction with SQL is quite simple, so the additional tools for constructing queries and such haven't seemed very useful.
• We rely heavily on an expanding list of fixed fields, which are a 1-1 mapping to the MediaFile fields. That's not a great match for typical declarative ORM-style schema setups.
• dbcore already serves as a kind of ORM, which comes with extra magic that defines the essence of beets: extensible types, string formatting, a terse query language, and flexible attributes.

That's why I've never pursued it. I'd love to hear others' opinions either way.

[jrobeson]

I propose it for a few reasons:

• deals with stuff that's not really in the problem domain of beets
  
  
  
  • transactions
  
  
  • threading
  
  
  • locking
  
  
  • DDL
  
  
  • logging
  
  
  • introspection
  
  
  • profiling
  
  
• makes it easier to offer postgres/mysql support for those who demand it.
• less code to maintain
• makes it easier to turn beets into a safe webapp
  
  
  
  • caching
  
  
  • escaping
  
  

[dokterbob]

+1

As queries are darn slow on my rather large music collection, I am interested whether having a 'proper' database might speed things up a bit. Plus all the other reasons mentioned. ;)

[jrobeson]

we're still missing basic stuff like indexes. that'd need to be solved with or without sqalchemy.

[sampsyo]

On the topic of "proper" databases, I tend to agree with @jrobeson that there's lower-hanging fruit for making beets faster, such as indices. Here's a blog post I wrote on this topic: http://beets.io/blog/sqlite-performance.html

[jrobeson]

I was never personally interested in using other databases for normal beets use cases, but for other usages, like shared library as a webapp.

[billop]

We rely heavily on an expanding list of fixed fields, which are a 1-1 mapping to the MediaFile fields. That's not a great match for typical declarative ORM-style schema setups.

Curious because I've had the exact same thought before, is there a rebuttal for this in favor of the ORM style of tracking the schema?