Scala and databases

712 views
Skip to first unread message

Cecil Westerhof

unread,
Sep 6, 2012, 8:14:31 AM9/6/12
to scala...@googlegroups.com
I want to work with databases in scala. I want to start with sqlite3, but in the future I could switch (to for example H2). Anyone good starting points?
--
Cecil Westerhof

Dinotzar Tzar

unread,
Sep 6, 2012, 8:32:45 AM9/6/12
to Cecil Westerhof, scala...@googlegroups.com
I have only good things to say about Squeryl (http://squeryl.org/) --- it supports H2 (but currently not sqlite3, but you could try to write an adapter).

D.


Cecil Westerhof

unread,
Sep 6, 2012, 8:50:21 AM9/6/12
to scala...@googlegroups.com
2012/9/6 Dinotzar Tzar <dino...@gmail.com>
On Thu, Sep 6, 2012 at 2:14 PM, Cecil Westerhof <cldwes...@gmail.com> wrote:
I want to work with databases in scala. I want to start with sqlite3, but in the future I could switch (to for example H2). Anyone good starting points?

I have only good things to say about Squeryl (http://squeryl.org/) --- it supports H2 (but currently not sqlite3, but you could try to write an adapter).

That was one (of many) that I saw. I will try it and just switch to H2 immediately. ;-)

I understood that sbt is depreciated, so I will do the manual install.

--
Cecil Westerhof

Nils Kilden-Pedersen

unread,
Sep 6, 2012, 8:55:30 AM9/6/12
to Cecil Westerhof, scala...@googlegroups.com
I would suggest O/R Broker, which supports any JDBC database, which would include sqlite.

Jonas Bonér

unread,
Sep 6, 2012, 10:58:03 AM9/6/12
to Nils Kilden-Pedersen, Cecil Westerhof, scala...@googlegroups.com
Slick is pretty slick: http://slick.typesafe.com/
--
Jonas Bonér
CTO
Typesafe - Enterprise-Grade Scala from the Experts
Phone: +46 733 777 123
Twitter: @jboner

Alec Zorab

unread,
Sep 6, 2012, 12:10:35 PM9/6/12
to Jonas Bonér, Nils Kilden-Pedersen, scala...@googlegroups.com, Cecil Westerhof

I'd second SLICK. I'm using it in it's 2.9 incarnation as scalaquery and it's excellent.

What's this about sbt being deprecated though?

pagoda_5b

unread,
Sep 6, 2012, 12:37:39 PM9/6/12
to scala...@googlegroups.com, Nils Kilden-Pedersen, Cecil Westerhof
Is SLICK production-ready?
I deemed it safer to wait for the final release of scala 2.10 to use it.

Any thoughts about this?

Ivano

Alec Zorab

unread,
Sep 6, 2012, 1:17:57 PM9/6/12
to pagoda_5b, scala...@googlegroups.com, Nils Kilden-Pedersen, Cecil Westerhof
Well, I've been using it in production for a couple of months without
issue on 2.9.2, but I'm dealing with small-ish databases under
relatively low load, so your mileage may vary.

Stefan Zeiger

unread,
Sep 6, 2012, 2:48:21 PM9/6/12
to scala...@googlegroups.com
On 2012-09-06 18:37, pagoda_5b wrote:
> Is SLICK production-ready?
> I deemed it safer to wait for the final release of scala 2.10 to use it.
>
> Any thoughts about this?

I'd say ScalaQuery 0.10-M1 is production-ready. API-wise it's the
closest to Slick and it has seen lots of development since the early 0.9
releases. It does have some compositionality issues though which can
show up in complex queries.

Slick uses a completely new query compiler that should solve these
problems but it hasn't undergone as much real-world testing and it
requires Scala 2.10. I think it is ready to use for development (if you
can get all the Scala dependencies you need for the same Scala 2.10
milestone) but I probably wouldn't use a Scala milestone release on a
production system.

Previous Slick releases were tied closely to a specific milestone
because the reflection and macros APIs changed a lot between milestones
and we had to adapt Slick to those changes. I expect this to be much
less of a problem now that we're getting closer to a Scala 2.10 RC so we
should be able to provide new Slick builds with bug-fixes not just for
the latest milestone/RC but also for the older ones.

-sz

Cecil Westerhof

unread,
Sep 6, 2012, 3:54:14 PM9/6/12
to scala...@googlegroups.com
2012/9/6 Alec Zorab <alec...@googlemail.com>

I'd second SLICK. I'm using it in it's 2.9 incarnation as scalaquery and it's excellent.

What's this about sbt being deprecated though?

I mixed things up. It was sbaz. I'll install sbt and then one or more database wrappers.

--
Cecil Westerhof

Naftoli Gugenheim

unread,
Sep 10, 2012, 5:56:52 PM9/10/12
to Alec Zorab, Jonas Bonér, Nils Kilden-Pedersen, scala...@googlegroups.com, Cecil Westerhof
+1 for Slick -- I'm using it with Scala 2.10 milestone builds.

Jacobus

unread,
Oct 31, 2012, 3:47:50 PM10/31/12
to scala...@googlegroups.com
Hi,

I have done quite a bit of research on Slick and other solutions, e.g. Lift Mapper, and came to the conclusion that Slick is the best solution, and seems solid enough for new development opportunities. Now that Scala is in 2.10-RC1, I would guess Slick 0.11.2 is close to being production ready also.

Does anyone know of open-source projects using Slick, that may serve as examples for good system architectures that rely on Slick? I don't believe that a DAO pattern makes best use of the composability offered by Scala and Slick and would like to consider more functional approaches.

Cheers,
Jacobus

Kostas kougios

unread,
Oct 31, 2012, 7:44:59 PM10/31/12
to scala...@googlegroups.com
If you are looking for an ORM library then you can try mapperdao: https://code.google.com/p/mapperdao/ . sqllite3 not supported but H2 is. You can map your objects using scala code and you can then use a DSL to query. Wiki, pdf tutorial and examples are available in the home page of the library. I use it for a heavy-traffic web site.

Jacobus

unread,
Nov 1, 2012, 3:27:01 AM11/1/12
to Kostas kougios, scala...@googlegroups.com
I had a look at your library and it look very interesting. Well done :-)

Why did you decide to write your own library instead of using Slick or
one of the other alternatives?

Konstantinos Kougios

unread,
Nov 1, 2012, 6:49:52 AM11/1/12
to Jacobus, scala...@googlegroups.com
Slick looks great but it is not an ORM tool (well, didn't use it but had
a look at some examples and it doesn't seem to be mapping domain classes
to tables). MapperDao handles related data and collections for i.e.
many-to-many relationships. There are benefits of Slick over MapperDao
and vice versa, depending on how an app is structured. MapperDao offers
a very good API for domain model centric applications.

There doesn't seem to be other ORM tools for Scala. And then there is
hibernate which is a proven and reliable Java ORM library but doesn't
take advantage of any of Scala language features. And the limitations of
Java are visible to hibernate code.

I explain some of the benefits of mapperdao at

http://code.google.com/p/mapperdao/wiki/Summary

It is, in my opinion, a nice ORM library and I use it at work where we
have very rich domain model and we map them to sql server tables. We do
40 mil page impressions per day.

Cheers

Rafał Krzewski

unread,
Nov 1, 2012, 8:52:01 AM11/1/12
to scala...@googlegroups.com, Jacobus, kostas....@googlemail.com
W dniu czwartek, 1 listopada 2012 11:50:07 UTC+1 użytkownik Konstantinos Kougios napisał:
There doesn't seem to be other ORM tools for Scala. 

Well, there is at least one other: https://github.com/fwbrasil/activate :)

Cheers,
Rafał

Konstantinos Kougios

unread,
Nov 1, 2012, 5:43:46 PM11/1/12
to Rafał Krzewski, scala...@googlegroups.com, Jacobus
I had a look at activate, I couldn't find a complete example with say a many-to-many relationship between 2 entities. From the table DDL to mappings (if any) and some queries. Do you have a link?

Tim Pigden

unread,
Nov 1, 2012, 5:57:48 PM11/1/12
to Konstantinos Kougios, Rafał Krzewski, scala-user, Jacobus
I'm using MapperDao and have found it very flexible for a wide range of domain types - making extensive use of natural keys on my database. Important for me is an easy way to drop down into the underlying query language - because I'm using PostGIS for geographical queries. The important thing here is to query domain models with geographic characteristics but to maintain a consistent object usage model whether with gis queries or normal ones. 

mapperdao has provided support for this. I'm not sure whether the same would work through the other frameworks which seem to be taking me too far from the underlying database. Mapperdao is perhaps a lower level abstraction but works well in this sort of arena.

Can the other frameworks cope with this sort of thing?

Tim
--
Tim Pigden
Optrak Distribution Software Limited
+44 (0)1992 517100
http://www.linkedin.com/in/timpigden
http://optrak.com
Optrak Distribution Software Ltd is a limited company registered in England and Wales.
Company Registration No. 2327613 Registered Offices: Orland House, Mead Lane, Hertford, SG13 7AT England 
This email and any attachments to it may be confidential and are intended solely for the use of the individual to whom it is addressed. Any views or opinions expressed are solely those of the author and do not necessarily represent those of Optrak Distribution Software Ltd. If you are not the intended recipient of this email, you must neither take any action based upon its contents, nor copy or show it to anyone. Please contact the sender if you believe you have received this email in error.

Flávio W. Brasil

unread,
Nov 2, 2012, 4:06:42 PM11/2/12
to Konstantinos Kougios, Rafał Krzewski, scala...@googlegroups.com, Jacobus
Hello.

Activate is designed to be simple. OO is straightforward to map data and relations. Instead of trying to create a magical relational many-to-many concept, you do as should do in OO: create a relation entity.

class Stock(var code: String) extends Entity {
def categories = select[StockCategory].where(_.stock :== this).map(_.category)
}
class Category(var name: String) extends Entity {
def stocks = select[StockCategory].where(_.category :== this).map(_.stock)
}
case class StockCategory(stock: Stock, category: Category) extends Entity

To map the same domain using separate entity declaration and "implicit" many-to-many relation you have to produce much more code. Activate 1.1 version (to be released this month) has support for Lists too:

class Person(var name: String, var contacts: List[Contact]) extends Entity

There are characteristics that makes Activate different from Slick, Mapperdao, Squeryl, Hibernate, etc.
For example: transparent persistence, optimistic locking with read and write validation, nested transactions, efficient memory usage, design by contract, migrations dsl,  relational and non relational database support, mass update/delete, play framework support. 
It's a new way to do persistence: simple, highly scalable and consistent. 

-- 
Flávio W. Brasil
{persistence as it should be}

Konstantinos Kougios

unread,
Nov 2, 2012, 5:58:48 PM11/2/12
to "Flávio W. Brasil", Rafał Krzewski, scala...@googlegroups.com, Jacobus
Thanks for the example Flavio, ok I see. Why the emphasis on transactional memory and efficient memory usage? What apps are you targeting?  Also what's your naming conventions for mapping to the tables?

mapperdao uses a different approach and your example would look like

class Stock(val code:String,val categories:Set[Category]) // or List[Category] etc
class Category(val name:String,val stocks:Set[Stock])

You can too use an intermediate StockCategory entity if you prefer.

MapperDao gives direct control on persistence and transactions to client code, i.e.

val tx = Transaction.get(txManager, Propagation.Nested, Isolation.ReadCommited, -1)
// will insert category and insert/update related Stock in 1 transaction
val inserted = tx { () =>
    mapperDao.insert(CategoryEntity, category)
}

MapperDao supports nested transactions too along with updates/deletes. All web frameworks are supported as mapperdao only needs a DataSource to work. A major feature of mapperdao is support for immutable classes, something that i.e. hibernate doesn't support, along with a typesafe DSL for querying.

Flávio W. Brasil

unread,
Nov 5, 2012, 5:55:01 PM11/5/12
to Konstantinos Kougios, Rafał Krzewski, scala...@googlegroups.com, Jacobus
Hello Konstantinos,

Thanks for the example Flavio, ok I see. Why the emphasis on transactional memory and efficient memory usage? What apps are you targeting?
There isn't a specific type of application that is the Activate target. It can be used from simple embedded systems to high performance environments.

The STM provides a powerful consistency control that is absent in regular ORMs, including read validation. It uses optimistic locking that permit higher transaction throughput comparing to traditional database pessimistic approach. There are other benefits of STM that I will not list here.

A common ORM performance bottleneck is marshal/unmarshal entities from/to database. The usual solution for this problem is to use caches. Normally ORMs cache implementations fail in aspects like inconsistent reads/writes and (believe or not) performance issues. Activate simple uses the great JVM garbage collector to decide what entities to maintain in memory by using soft references. The STM assures that the in-memory information is consistent.

Also what's your naming conventions for mapping to the tables?
Entities are mapped to the storage using their simple class name and properties names. The 1.1 version will have annotations that permit custom names mapping.

mapperdao uses a different approach and your example would look like

class Stock(val code:String,val categories:Set[Category]) // or List[Category] etc
class Category(val name:String,val stocks:Set[Stock])

You can too use an intermediate StockCategory entity if you prefer.
I'm assuming that there is a separate entity declaration that is not in your email, right?

MapperDao gives direct control on persistence and transactions to client code, i.e.

val tx = Transaction.get(txManager, Propagation.Nested, Isolation.ReadCommited, -1) 
// will insert category and insert/update related Stock in 1 transaction
val inserted = tx { () => 
    mapperDao.insert(CategoryEntity, category) 


MapperDao supports nested transactions too along with updates/deletes. 
Interesting, you are using spring implementation that uses savepoints to do nested transactions. The only difference from Activate is that the mapperdao nested transaction will use the database pessimistic locking approach.

All web frameworks are supported as mapperdao only needs a DataSource to work.
Activate also supports all web frameworks. There is a Play module that has some glue code to make the integration easier.

A major feature of mapperdao is support for immutable classes, something that i.e. hibernate doesn't support, along with a typesafe DSL for querying.
Seems to me that Mapperdao immutable entities hide a "veiled" mutability. An example using Mapperdao:

case class Product(val title: String, val description: String)
val inserted=mapperDao.insert(ProductEntity,Product(“title”,”desc”))
mapperDao.update(ProductEntity,inserted,Product(“newv”,”newv”))

The Product class is immutable, but the Product entity instance isn't. The "inserted" entity is modified to have values "newv". If there is a concurrent transaction the entity instance can stay inconsistent. This veiled mutability looks dangerous to me.

-- 
Flávio W. Brasil
{persistence as it should be}

Konstantinos Kougios

unread,
Nov 6, 2012, 10:11:38 AM11/6/12
to "Flávio W. Brasil", Rafał Krzewski, scala...@googlegroups.com, Jacobus
Ok, I see.

To answer the original post and regarding an ORM library, I think the mapperdao tutorial is the best link : https://mapperdao-examples.googlecode.com/files/tutorial-1.0.0-rc15.pdf , which includes the entity declarations.

Regarding entity declarations that might seem an overhead, I've got a f.a.q page: http://code.google.com/p/mapperdao/wiki/FAQ . They are very useful for i.e. querying i.e. (select from pe where pe.price>10).toSet(queryDao) , compared to the String-based HQL.

Flavio, mapperdao doesn't mutate any instances, i.e.

val updated=mapperDao.update(ProductEntity,inserted,Product(“newv”,”newv”))

updated , inserted and Product(“newv”,”newv”) are 3 separate instances, none of them is mutated. mapperdao can be used in a functional way, i.e.

val l = List(...products)
val persistedL=l.map(mapperDao.insert(ProductEntity,_)) // list of persisted products i.e. Product with SurrogateIntId

l and persistedL contain different instances.

Flávio W. Brasil

unread,
Nov 6, 2012, 10:36:14 AM11/6/12
to Konstantinos Kougios, Rafał Krzewski, scala...@googlegroups.com, Jacobus
The point about using immutable classes in functional programming is avoid information mutability, so you don't have to worry about concurrency issues. 
Mapperdao classes are immutable, but the "real" entity (information) is mutable and can produce concurrency issues. You have a sensation of using immutable data, but will have the same issues of mutable classes.

-- 
Flávio W. Brasil
{persistence as it should be}

Tim Pigden

unread,
Nov 6, 2012, 10:39:36 AM11/6/12
to scala-user

Flavio - why do you say the real data is mutable (other than in the database)?
--
Tim Pigden
Optrak Distribution Software Limited
+44 (0)1992 517100
http://www.linkedin.com/in/timpigden
http://optrak.com
Optrak Distribution Software Ltd is a limited company registered in England and Wales.
Company Registration No. 2327613 Registered Offices: Orland House, Mead Lane, Hertford, SG13 7AT England 
This email and any attachments to it may be confidential and are intended solely for the use of the individual to whom it is addressed. Any views or opinions expressed are solely those of the author and do not necessarily represent those of Optrak Distribution Software Ltd. If you are not the intended recipient of this email, you must neither take any action based upon its contents, nor copy or show it to anyone. Please contact the sender if you believe you have received this email in error.

Konstantinos Kougios

unread,
Nov 6, 2012, 10:47:03 AM11/6/12
to "Flávio W. Brasil", scala...@googlegroups.com
Sorry, I don't understand what is mutable, can you give an other example
with what you believe to be mutating (in terms of fields of an instance)?

By all means, mapperdao doesn't mutate immutable entities not even
mutable ones. Ofcourse it occasionally has internal mutable state but
externally it is thread safe and doesn't mutate client instances.

Kostas kougios

unread,
Nov 6, 2012, 5:34:30 PM11/6/12
to scala...@googlegroups.com, Flávio W. Brasil, kostas....@googlemail.com
to be continued at https://groups.google.com/forum/?fromgroups=#!topic/scala-user/Au3MA-BUqIw to avoid hijacking this thread.

:D
Reply all
Reply to author
Forward
0 new messages