As someone unfamiliar with Python, I've often heard a lot of praise for SQLAlchemy. So I'd like to understand:
What does it offer compared to "type-safe SQL builders" like jOOQ or QueryDSL?
Are there closer equivalents to it in Java (or Scala) world? I've seen Apache Empire-DB mentioned in this respect...
One of the notable things about SQLAlchemy is that it makes tables first class objects. Thus the core API is really written around table objects, and the API therefore is essentially relational in nature. Thus at this level even if the API is OO, it is essentially reflecting RDBMS objects or functions such as Tables, Columns, Relationships, Joins, Aliases etc. At this level SQLAlchemy gives you essentially a OOSQL where SQL and Relational Databases are not given the second class treatment. Its also at this that SQLAlchemy really shines since this level of abstraction gives you an ability to step down to a bit of a "raw" relational level and thus gain enormous amounts of flexibility which I really haven't seen any other ORM offer. Interestingly some of the underlying capabilities that are required to model class inheritance in ORMs are implemented at this layer eg. Joined Table Inheritance http://docs.sqlalchemy.org/en/rel_0_7/orm/inheritance.html#joined-table-inheritance
The API that is more often used (at least lately) is the declarative API which is really a lot more OO and maps objects in the business domain to the objects I referred to above (in most cases transparently). Here's where the ORM functionality comes in and the API is a little bit more similar to other ORM APIs where one works with domain objects and these actions get translated into the underlying table actions directly.
To the best of my awareness, ORMs in Scala are still playing catch-up to what is easily available in Java (eg. inheritance) even as they offer other capabilities (eg. type safety, LINQ like constructs), even as they struggle with some serious issues like 22 column limitations. (I've read comments where few have wondered why anyone would need more than 22 columns, and at least in my experience there are situations I wouldn't call rare, where one needs a multiple of that).
ORMs in scala (even as they take on a different flavour from Java) I think are still catching up to whats required. With respect to SQLAlchemy, is there a close enough equivalent to it I've seen in Java or Scala? I haven't seen any.
EDIT: One thing I forgot to add, is that even if one uses the declarative API, SQLAlchemy still gives you direct access to the underlying objects. So if "class Foo" is mapped declaratively, Foo.__table__ is the table object that you can directly use if you so desire.
Squeryl provides composability similar to what they talk about in "SQLALCHEMY'S PHILOSOPHY" on the libraries home page. You can query a query.
val query = from(table)(t => where(t.a === 1) select(t))
val composed = from(query)(q => where(q.b === 2) select(q))
It also shares a good portion of the design philosophy, primarily that when things get complicated the library should "get out of the way" and allow the developer to tune things themselves. While Squeryl does object mapping, I consider it more of a DSL than an ORM.
Some of the features it doesn't share from a quick glance at the SQLAlchemy feature list:
Of course, Squeryl also offers what I consider a major feature that a Python library can't, which is compiler checked type safety of your queries.
ScalaQuery (see the note about "slick" at the bottom) can do the simple:
for{
a <- Article
if a.dateCreated between(start, end)
_ <- Query groupBy a.reporterID orderBy a.dateCreated.desc
} yield(a)
or the arbitrarily complex via composition:
val team = for{
t <- Team
s <- School if t.schoolID is s.id
} yield (t,s)
val player = for{
r <- Roster
p <- Player if r.playerID is p.id
} yield (r, p)
val playerDetail = for{
(r, p) <- player
} yield (p.id, p.firstName, p.lastName, r.jerseyID, r.position, r.gamesPlayed)
val scoring = for{
(r, p) <- player
s <- Scoring if p.id is s.playerID
detail <- playerDetail
} yield (r, p, s, detail)
val scoringDetail = for{
(r, p, s, detail) <- scoring
val (total, goals, assists) =
(s.playerID.count, s.goal.sum, (s.assist1.sum + s.assist2.sum))
val ppg = (s.playerID.count / r.gamesPlayed)
} yield (goals, assists, total, ppg)
Here's how to get team stats (could modify for league or single player view):
val forScoring = for{
start ~ end ~ teamID <- Parameters[JodaTime,JodaTime,Int]
(r,p,s,player) <- scoring if r.teamID is teamID
comp <- bindDate(start, end) if s.gameID is comp.id
(goals, assists, total, ppg) <- scoringDetail
_ <- Query groupBy p.id orderBy ( ppg.desc, total.asc, goals.desc )
} yield (player, goals, assists, total, ppg)
def getScoring(start: JodaTime, end: JodaTime, id: Int): List[TeamScoring] = {
forScoring(start, end, id).list
}
I did not think it would be possible to generate strongly typed complex queries in Scala, and had resigned myself to just porting around tried & true hand written SQL; that is, until I came across ScalaQuery, which has been a revelation, much like the Scala language itself.
Anyway, you have options, Squeryl may be more in-line with SQL Alchemy, don't know, explore a bit, you'll likely not be disappointed, there is so much Scala goodness on offer, it's hard not to feel giddy in the here, now, and after ;-)
p.s. a great talk by Zeiger and Vogt at Scala Days Skills Matters on SLICK, the next evolution of ScalaQuery
It's a Java implementation of the "Active Record" design pattern. It was inspired by ActiveRecord ORM from Ruby on Rails. It might fit your need.
https://javalite.io/activejdbc
or
A SQL convenience library for Java. It attempts to expose relational database access in idiommatic Java, using collections, beans, and so on, while maintaining the same level of detail as JDBC.
