Writing SQL in Play with Scala?

Question

I'm looking at the documentation for the Play framework and it appears as though SQL is being written in the framework.

Coming from Rails, I know this as a bad practice. Mainly because developers require the need to switch databases as they scale.

What are the practices in Play to allow for developers to implement conventions and to work with databases without having to hard code SQL?

Answer 1

One of the feature/defects (depending on who you ask) of Play is the there is no ORM. (An ORM is an object-relational mapper/mapping; it is the part of Rails, Django, etc. that writes your SQL for you.)

• Pro-ORM: You don't have to write any SQL.

  • Ease-of-use: Some developers unused to SQL will find this easier.

  • Code resuse: Tables are usually based on your classes; there is less duplication of code.

  • Portability: ORMs try smooth over any differences between DMBS vendors.

• No-ORM: You get to write your own SQL, and not rely on unseen ORM (black)magic.

  • Performance: I work for a company which produces high-traffic web applications. With millions of visitors, you need to know exactly what queries you are running, and exactly what indicies you are using. Otherwise, the code that worked so well in dev will crash production.

  • Flexibility: ORMs often do not have the full range of expression that a domain-specific language like SQL does. Some more complex sub-selects and aggregation queries will be difficult/impossible to write with ORMs.

  • While you may think "developers require the need to switch databases as they scale", if you scale enough to change your DBMS, changing query syntax will be the least of your scalability issues. Often, the queries themselves will have to be rewritten to use sharding, etc., at which point the ORM is dead.

It is a tradeoff; one that in my experience has often favored no ORM.

See the Anorm page for Play's justification of this decision:

You don’t need another DSL to access relational databases

SQL is already the best DSL for accessing relational databases. We don’t need to invent something new.

...

---

Play developers will typically write their own SQL (much the same way they will write in other languages, like HTML), use Anorm, and follow common SQL conventions.

If portability is a requirement, use only ANSI SQL (no vendor-specific featues). It is generally well supported.

---

EDIT: Or if you are really open-minded, you might have a look at NoSQL databases, like Mongo. They are inherently object-based, so object-oriented Ruby/Python/Scala can be used as the API naturally.

Answer 2

In addition to Paul Draper's excellent answer, this post is meant to tell you about what Play developers usually do in practice.

TL;DR: use Slick

Play is less opinionated than Rails and gives the user many more choices for their data storage backend. Many people use Play as a web layer for very complex existing backend systems. Many people use Play with a NoSQL storage backend (e.g. MongoDB). Then there's also people using Play for traditional web-service-with-SQL applications. Generalizing a bit too much, one can recognize two people using Play with relational databases.

"Traditional web developers" They are used to standard Java technologies or are part of an organization that uses them. The Java Persistence API and its implementations (Hibernate, EclipseLink, etc...) are their ORM. You can do so too. There are also appear to be Scala ORMs, but I'm less familiar with those.

Note that Java/Scala ORMs are still different ORMs in style when compared to Rails' ActiveRecord. Ruby is a dynamic language that allows/promotes loads of monkey patching and method_missing stuff, so there is MyObject.finder_that_I_didnt_necessarily_have_to_write_myself(). This style of ORM is called the active record pattern. This style is impossible to accomplish in pure Java and discouraged in Scala (as it violates type safety), so you have to get used to writing a more traditional style using service layers and data access objects.

"Scala web developers" Many Scala people think that ORMs are a bad abstraction for database access. They also agree that using raw SQL is a bad idea, and that an abstraction is still in order. Luckily Scala is an expressive compiled language, and people have found a way to abstract database access that does not rely on the object oriented language paradigm as ORMs do, but mostly on the functional language paradigm. This is quite a similar idea to the LINQ query language Microsoft has made for their .NET framework languages. The core idea is that you don't have an ORM to perform query magic, nor write queries in SQL, but write them in Scala itself. The advantages of this approach are twofold:

1. You get a more fine grained control over what your queries actually execute when compared to ORMs.
2. Queries are checked for validity by the Scala compiler, so you don't get runtime errors for invalid SQL you wrote yourself. If it is valid Scala, it is translated to a valid SQL statement for you.

Two major libraries exist for accomplishing this. The first is Squeryl. The second is Slick. Slick appears to be the most popular one, and there are some examples floating around the web that show how you are supposed to make it work with Play. Also check out this video that serves as an introduction to Slick and which compares it to the ORM approach.

Answer 3

FWIW, I wrote a short summary of the state of database access in Scala some months ago. I personally found that using Slick in combination with its plain SQL querying capabilities to be quite convenient.