Mapper vs. Record / Squeryl
I am about to start my first project within Lift and I have to decide which persistence library to choose. I'm going to use a relational backend so that both Mapper and Record can appear in the game.
In the case of Mapper - what I missed the most was the ability to manage the queries sent to the DBMS, especially when it comes to more complex queries that will be solved by joins, aggregations, etc. in SQL. Let's take the following example: let's have the following two objects:
class BaseProduct extends LongKeyedMapper[BaseProduct] with IdPK {
// some fields
}
object BaseProduct extends BaseProduct with LongKeyedMetaMapper[BaseProduct]
class MyProduct extends LongKeyedMapper[MyProduct] with IdPK {
// some fields
object base extends MappedLongForeignKey(this, BaseProduct)
}
object MyProduct extends MyProduct with LongKeyedMetaMapper[MyProduct]
Where MyProduct
is one of the object's specializations BaseProduct
. It is obvious that there is a one-to-one relationship between these entities. However, the best option I came up with to query the exact one MyProduct
along with mine BaseProduct
was a query like this:
MyProduct.findAll(PreCache(MyProduct.base))
Which is causing two requests (moreover, I'm afraid I can't control which fields of the object MyProduct
I want to select.
Bad enough for the Mapper library. My main concern with the Record / Squeryl API is that it lacks all those Proto
classes that are present in the Mapper API. Is there something close to the functionality of these classes for writing? Is it possible to access database features in Squeryl (e.g. geometric queries in PostgreSQL)?
Are there any other pros and cons for any of these layers? Or is there another level of abstraction that deserves attention if I want to have decent encapsulation of the database communication and that provides decent control over the queries (I was used to issue queries directly using the PDO layer in PHP-I I don't need such a direct query interface, but there can be a lot of control over the queries). The integration with the Lift frame is definitely an advantage.
Thank!
source to share
We have been using Squeryl with Lift for a long time and were very happy with it. Based on your case above, in the current version of Squeryl (0.9.5), you can do something like:
class BaseProduct(id:Long, some more fields) extends KeyedEntity[Long] {
}
class MyProduct(id:Long, some more fields) extends KeyedEntity[Long] {
}
Then you will have a schema that defines the relationship as (I assume they are concatenated into an ID field):
val baseProducts = Table[BaseProduct]("base_products")
val myProducts = Table[MyProduct]("my_products")
val myProductsToBaseProducts =
oneToManyRelation(myProducts, baseProducts).via((mp, bp) =>
mp.id === bp.id)
To query for both records, you would do something like:
from(myProducts, baseProducts) ( (mp, bp) =>
where(mp.id === bp.id and mp.id === LOOKUPVAL)
select(bp, mp) )
The query above will return a tuple (BaseProduct, MarketProduct) from a single SQL selection.
You can also use a relation to retrieve the related item, for example by adding this method to MyProduct
:
def baseProduct = myProductsToBaseProducts.left(this)
However, just like your example from Mapper, it issues a second request. As far as queries against specific databases are concerned, there is an operator &
that will allow you to evaluate expressions on the server. If a function is not available in Squeryl, you can create custom functions .
Overall, I found Squeryl to be a very flexible and great hybrid between ORM and straight SQL. It performed remarkably well, and I didn't find too many places where Squeryl prevented me from easily getting the necessary database functionality. It gets even easier in the next version as 0.9.6 will have more flexibility with custom types .
source to share