{"id":2284,"date":"2012-02-16T00:14:36","date_gmt":"2012-02-15T23:14:36","guid":{"rendered":"http:\/\/www.gamlor.info\/wordpress\/?p=2284"},"modified":"2021-07-03T13:35:28","modified_gmt":"2021-07-03T12:35:28","slug":"scalaquery-a-small-database-library","status":"publish","type":"post","link":"https:\/\/www.gamlor.info\/wordpress\/2012\/02\/scalaquery-a-small-database-library\/","title":{"rendered":"ScalaQuery, a Small Database Library"},"content":{"rendered":"<p>On this blog I usually talk about no relational databases, like RavenDB or db4o. But guess what, I still like regular relational databases.<\/p>\n<div id=\"attachment_2291\" style=\"width: 310px\" class=\"wp-caption aligncenter\"><a href=\"https:\/\/www.gamlor.info\/wordpress\/wp-content\/uploads\/2012\/02\/relational-scala.png\"><img loading=\"lazy\" decoding=\"async\" aria-describedby=\"caption-attachment-2291\" src=\"https:\/\/www.gamlor.info\/wordpress\/wp-content\/uploads\/2012\/02\/relational-scala-300x182.png\" alt=\"Relational Stuff fits Scala well\" title=\"relational-scala\" class=\"size-medium wp-image-2291\" width=\"300\" height=\"182\" srcset=\"https:\/\/www.gamlor.info\/wordpress\/wp-content\/uploads\/2012\/02\/relational-scala-300x182.png 300w, https:\/\/www.gamlor.info\/wordpress\/wp-content\/uploads\/2012\/02\/relational-scala.png 1000w\" sizes=\"(max-width: 300px) 100vw, 300px\" \/><\/a><p id=\"caption-attachment-2291\" class=\"wp-caption-text\">Relational Stuff fits Scala well<\/p><\/div>\n<p>A while back I needed to read some data from a relational database in Scala. What I wanted was a small library, which removes most of the JDBC boiler plate, but not a full blown ORM. I ended up using is <a href=\"http:\/\/scalaquery.org\/\">ScalaQuery<\/a>.<\/p>\n<p>This library gives you a thin, rational layer on top of JDBC. This is how it works. First you define a &#8216;table&#8217;. This is done extending a table class and adding field definitions to it:<br \/>\n<script src=\"https:\/\/gist.github.com\/1839338.js?file=TableDefinition.scala\"><\/script><noscript><pre><code class=\"language-scala scala\">val ApiKeys = new ExtendedTable[(String, String)](&quot;googleApiKeys&quot;) {\n\tdef deviceId = column[String](&quot;deviceId&quot;, O.PrimaryKey)\n\n\tdef apiKey = column[String](&quot;apiKey&quot;)\n\n\tdef * = deviceId ~ apiKey\n}<\/code><\/pre><\/noscript><\/p>\n<p>Afterwards you need to create a database instance:<br \/>\n<script src=\"https:\/\/gist.github.com\/1839338.js?file=DatabaseInstance.scala\"><\/script><noscript><pre><code class=\"language-scala scala\">val database: Database = Database.forURL(&quot;jdbc:h2:~\/apiKeyStore&quot;, driver = &quot;org.h2.Driver&quot;)<\/code><\/pre><\/noscript><\/p>\n<p>To do an operation we need a transaction \/ session. On way to create such a session is to use the withSession method. Basically all operations require a session. We can manually pass the session to the methods. Or we can declare an implicit session which is used. For example the built in thread local session:<br \/>\n<script src=\"https:\/\/gist.github.com\/1839338.js?file=SessionManagement.scala\"><\/script><noscript><pre><code class=\"language-scala scala\">\/\/ We use the thread local session management\n\/\/ So we import this implicit parameter which returns the current session\n\/\/ Of course we pass any other session \/ use our own implicits\nimport org.scalaquery.session.Database.threadLocalSession\n\n\n\/\/ and we do stuff in our session\ndatabase withSession {\n  \/\/ do stuff\n}<\/code><\/pre><\/noscript><\/p>\n<p>For example we can create the database schema:<br \/>\n<script src=\"https:\/\/gist.github.com\/1839338.js?file=CreateSchema.scala\"><\/script><noscript><pre><code class=\"language-scala scala\">database withSession {\n    ApiKeys.ddl.create\n}<\/code><\/pre><\/noscript><\/p>\n<p>Now to the important part: We can query and update data with our table object. The cool thing is that we just can use the Scala for-construct to do so. The library will transform our code into the appropriate SQL statement. Everything lives in the &#8216;Scala&#8217; world, no strings and casts etc. are required. Also not that some imports are required for these API features:<br \/>\n<script src=\"https:\/\/gist.github.com\/1839338.js?file=QueryForAKey.scala\"><\/script><noscript><pre><code class=\"language-scala scala\">\/\/ Imports for the query API\nimport org.scalaquery.ql.extended.H2Driver.Implicit._\nimport org.scalaquery.session.{Session, Database}\nimport org.scalaquery.ResultSetInvoker\nimport org.scalaquery.simple.StaticQuery\nimport org.scalaquery.ql.Query\n\n\n\/\/ Query\nval devicesByApiKey = for {\n\ta &lt;- ApiKeys if a.deviceId.like(deviceId)\n} yield a.apiKey\n\n\/\/ Do stuff with the result\ndevicesByApiKey.foreach(i=&gt;doStuff(i))<\/code><\/pre><\/noscript><\/p>\n<p>We also can insert and update tables:<br \/>\n<script src=\"https:\/\/gist.github.com\/1839338.js?file=UpdateOrInsert.cs\"><\/script><noscript><pre><code class=\"language-c# c#\">val devicesByApiKey = for {\n\ta &lt;- ApiKeys if a.deviceId.like(deviceId)\n} yield a.apiKey\n\ndevicesByApiKey.firstOption match {\n\tcase None =&gt; {\n\t  ApiKeys.insert(deviceId, googleKey)\n\t}\n\tcase Some(_) =&gt; {\n\t  query.update(googleKey)\n\t}\n}<\/code><\/pre><\/noscript><\/p>\n<h2>My Opinion &amp; State of the Library<\/h2>\n<p>I really like the approach of this library. It is lightweight, close the SQL-metal and yet it blends perfectly with the Scala code. It really doesn&#8217;t feel like you entering into a new domain when talking to the database. It only surfaces operations which work in SQL and doesn\u2019t do any operations behind the scenes. For many use cases this works way better than heavy weight ORM frameworks.<\/p>\n<p>However I have to point out that many features are not there and there doesn&#8217;t seem any active development going on. For example you cannot check if a table already exists before calling the &#8216;create schema&#8217; method. I also miss a nice method for using raw SQL in case you need a missing feature.<\/p>\n<p>So right now I only would use it for pet projects, or when you are willing to invest your time to patch and improve things.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>On this blog I usually talk about no relational databases, like RavenDB or db4o. But guess what, I still like regular relational databases. A while back I needed to read&hellip; <\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":"","_links_to":"","_links_to_target":""},"categories":[15,17],"tags":[162,226,259],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.gamlor.info\/wordpress\/wp-json\/wp\/v2\/posts\/2284"}],"collection":[{"href":"https:\/\/www.gamlor.info\/wordpress\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.gamlor.info\/wordpress\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.gamlor.info\/wordpress\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.gamlor.info\/wordpress\/wp-json\/wp\/v2\/comments?post=2284"}],"version-history":[{"count":6,"href":"https:\/\/www.gamlor.info\/wordpress\/wp-json\/wp\/v2\/posts\/2284\/revisions"}],"predecessor-version":[{"id":3895,"href":"https:\/\/www.gamlor.info\/wordpress\/wp-json\/wp\/v2\/posts\/2284\/revisions\/3895"}],"wp:attachment":[{"href":"https:\/\/www.gamlor.info\/wordpress\/wp-json\/wp\/v2\/media?parent=2284"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.gamlor.info\/wordpress\/wp-json\/wp\/v2\/categories?post=2284"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.gamlor.info\/wordpress\/wp-json\/wp\/v2\/tags?post=2284"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}