ScalaQuery vs Squeryl

A few months ago I was asked what my take on Squeryl was. Here's what I came up with, from my limited knowledge of Squeryl and expert knowledge of ScalaQuery. Feel free to correct any mistakes you find.

• Both provide typed relational queries, are based on Scala 2.8.0, and built with sbt.

• Squeryl allows tables to be defined by POSOs (with optional parameter annotations for non-default column names and probably other features). ScalaQuery always requires a table object to describe a table as a typed tuple. It can either operate directly on tuples or map the table to a POSO (or something different, with user-defined mapping functions). Squeryl's approach is easier for simple cases but AFAICT it requires cglib for generating dynamic proxies, which makes it unsuitable for certain environments such as Android. Operating on primitive values also necessitates the "" operator to force certain expressions to be lifted to computations in the query (e.g. "where(s.year. > 1965)").

• Squeryl supports a predefined set of basic JDBC types which are mapped to the corresponding Java/Scala types. Custom types can be implemented based on the basic types and used in the table POSOs (but this leaks Squeryl-specific code into the POSOs). ScalaQuery comes with support for a basic set of JDBC types and can be extended with DBMS- or application-specific types. This is done through a typeclass (using implicits) to keep ScalaQuery from leaking into POSOs.

• Squeryl has some ORM features (cf. Object Persistence, Stateful Relations and Optimistic Concurrency Control in the Squeryl documentation). There are no equivalents in ScalaQuery which deliberately avoids possibly leaky ORM abstractions (but Squeryl also makes them rather explicit so it seems to be less leaky than a traditional ORM).

• Constructing queries: ScalaQuery uses a query monad abstraction to make use of Scala's for-comprehension syntax for query comprehensions. A query can be used to select, insert, update and delete. Squeryl uses a method-based syntax with methods for the individual parts of a SQL query. "select", "update" and "deleteWhere" are separate methods. This looks closer to SQL but allows less reuse. Inserts from queries are not supported by Squeryl AFAICT.

• "From" clauses are explicit in Squeryl. It has the "inhibitWhen" feature to dynamically include or exclude a table from a query. This is not necessary with ScalaQuery's query monad. Queries can be composed using any standard Scala features. The set of required tables for the "from" clause is deducted automatically instead of being tracked by a query.

• Both support multiple DBMSs (Squeryl: MySQL, PostgreSQL, H2, Oracle, DB2; ScalaQuery: MySQL, PostgreSQL, H2, Derby, SQLite). Squeryl has no DBMS-adapter-specific type constraints: Adapters exist only at run-time and do not influence the feature set that is available for the construction of entities and queries at compile-time. ScalaQuery's DBMS drivers have runtime types composed from different profiles and DBMS-specific functionality so they statically allow or disallow certain features (currently in the form of BasicProfile with BasicDriver and ExtendedProfile with the DBMS-specific drivers; More diverse profiles are planned after release 1.0).

• Squeryl supports batched updates and inserts. ScalaQuery has the requried abstractions but does not use JDBC batch features for them yet. (This has changed in the meantime: ScalaQuery does JDBC batch inserts)

• I wasn't able to find equivalents for ScalaQuery's query templates (parameterized queries which do not need to be compiled to SQL for every invocation), simple queries (text-based, with user-supplied parameter and result types and automatic mapping of parameters and ResultSets) and meta-data abstractions in Squeryl.

• Squeryl has the cooler name. Too bad I didn't think of it when I needed to find a new name for SQuery :)