Query postgresql database without cats

Question
1

is there an “easy” library i.e. not using cats to query a postgresql.
By preference with demo code for postgresql specific.

2

Here’s a nice list of comparisons, although it’s a bit old.

1 Like
3

Check also magnum, you can also use plain JDBC.

Finally, you may use any effect library and just call unsafeRunSync everywhere.

4

That’s still just a fraction – there are a lot of database libraries written in Scala, even before you get to all the JVM libraries you can use.

Relatively new (but Haoyi has a habit of producing solid and relatively easy to use libraries) is scalasql.

2 Likes
5

In F# i had something working and quick.
With scala nothing seems to work for me .
I’m in the need for a clear tutorial , how to have a good sbt or mill file , how to make the database connection with real values/parameters, how to run & print a query.

Note : I tried slick but i receive errors

[info] compiling 1 Scala source to /mnt/xxx_source/Languages_ok/scalatut/99_slick_error/target/scala-3.4.0/classes …
[error] – [E006] Not Found Error: /mnt/xxx_source/Languages_ok/scalatut/99_slick_error/src/main/scala/com/alain/Main.scala:4:18
[error] 4 |val jdbcProfile = PostgresProfile
[error] | ^^^^^^^^^^^^^^^
[error] | Not found: PostgresProfile

These are my two files :
build.sbt


artix:[x]:~/Languages_ok/scalatut/99_slick_error$ cat build.sbt 
scalaVersion := "3.4.0"
version := "1.0"
name := "mytest"
organization := "com.alain"
val slickVersion = "3.5.2"
libraryDependencies ++=  Seq("com.lihaoyi" %% "fansi" % "0.5.0" ,
                                                      "org.postgresql" % "postgresql" % "42.5.0" ,
                                                      "com.typesafe.slick" %% "slick" % slickVersion ,
                                                      "com.typesafe.slick" %% "slick-codegen" % slickVersion ,
                                                      "org.slf4j" % "slf4j-nop" % "1.6.4" ,
                                                      "com.typesafe.slick" %% "slick-hikaricp" % slickVersion ,
                                                      "org.postgresql" % "postgresql" % "9.4-1206-jdbc42" 
                                                      //org.postgresql.ds.PGSimpleDataSource dependency
                                                      )
6

Main.scala

package com.alain 

val db = Database.forURL("jdbc:postgresql:x", driver = "org.postgresql.Driver")

import slick.jdbc.PostgresProfile._

val jdbcProfile = PostgresProfile
import slick.jdbc.PostgresProfile.api._

// First declare our Scala object
final case class Person(firstname: String, lastname: String)

// Next define how Slick maps from a database table to Scala objects
class Persons(tag: Tag) extends Table[Person](tag, "Persons") {
  def firstname  = column[String]("firstname")
  def lastname = column[String]("lastname")
  def * = (firstname, lastname).mapTo[Person]
}
object Main {
    val fansiStr:fansi.Str = fansi.Color.Red("This should be red")
    def main(args:Array[String]):Unit= 
        println(fansiStr)
        // The `TableQuery` object gives us access to Slick's rich query API
        val persons = TableQuery[Persons]

        // Inserting is done by appending to our query object
        // as if it were a regular Scala collection
        // SQL: insert into COFFEES (NAME, PRICE) values ('Latte', 2.50)
        persons += Person("Jan","DeMol")

        // Fetching data is also done using the query object
        // SQL: select NAME from COFFEES
        var z=persons.map(_.firstname)

        println(z)

        // More complex queries can be chained together
        // SQL: select NAME, PRICE from COFFEES where PRICE < 10.0 order by NAME
        //coffees.filter(_.price < 10.0).sortBy(_.name)
}
7

That first line, with the trailing ._, imports everything contained inside PostgresProfile, but not PostgresProfile itself. That’s why the second line is erroring: you haven’t actually imported the thing that you’re referencing.

1 Like
8

I was able to make slick work. Nice.

I wonder how it compares to doobie ? pros/cons ?

3 Likes
9

Glad to see you got it working! Underscore’s books are generally very useful resources.

Very different attitudes. Slick encodes the usual SQL operators as standard Scala methods; Doobie feels more like working in SQL itself. Slick comes with its own DBIO effect, which you have to weld to whatever effect your application is using; Doobie is “bring your own effect”, so melds directly with any effect that follows the cats-effect 3 typeclasses.

They’re both solidly good libraries, but there’s a lot of matter-of-taste involved. In practice, what feels better to you and works better in the structure of your application is mainly what it comes down to. In practice, applications built with cats-effect tend to use Doobie, since it slides more easily into that architecture; applications that are Future-based are (I think) more likely to use Slick.

1 Like
10

Try Squery - Squery
It is very simple, raw sql only library for scala 3.
Contains a mill source code generator, if you already have your tables ready.