DEV Community: Juliano Alves

Sttp: An Extensible API Client

Juliano Alves — Mon, 07 Nov 2022 10:00:00 +0000

A while back I blogged about how to write an api client using ZIO and http4s client. Revisiting it is interesting, so much changed since then! The post was a nice example at the time, but thinking about that solution as a real world implementation, it suffers from a couple of problems:

it's not extensible;
it's highly coupled - what is not necessarily a problem, when it's part of the domain. But it would be nice to have.

Simply making changes according to these two topics wouldn't evolve the idea that much though, so here's the new proposition: let's implement an extensible client, properly!

What's wrong with the previous client?

Having client.getThis and client.getThat implicates in changing the client itself whenever we need to add a new request. Besides, the request / response strategy could be better and shapeless should be removed.

Let's start with a base definition for our new requests:

Request[Response]

This is a brilliant approach that I "borrowed" from sttp, where the request carries the information about the response. In principle, we could define a trait with one single method:

def send[A](request: Request[A]): F[A]

For the new, modern, super-duper awesome client, let's keep the effect system generic. Just for fun 😉

What is the plan?

We need a public API to consume, so we will build a client to the pokeapi.co. If you haven't watched Pokemon, don't tell me. That would make me feel old!

Let's use the already mentioned sttp to send requests, and for JSON parsing we'll be using zio-json.

Let's catch them all!

First things first, an api host:

import sttp.model.Uri
import sttp.model.Uri.UriContext

final case class ApiHost private (uri: Uri)

object ApiHost:
  final val default = ApiHost(uri"https://pokeapi.co/api/v2")

Now let's define the generic request to consume the Pokemon Api. I know it's not so obvious, but what we need is the...

PokeRequest (Best. Name. Ever.)

Most resources available via pokeapi.co can be requested using the following pattern:

https://pokeapi.co/api/v2/{resource}/{id or name}/

Let's abstract the path parameters and make it easy for the user to create a request in the following format, so it can be sent via sttp:

Request[Either[ResponseException[String, String], A], Any]

A request that can fail with a ResponseException or succeed with A... 🤔 this is not that readable, is it? I think a couple of type aliases will be handy here:

type FailureResponse = ResponseException[String, String]
type SttpRequest[A]  = Request[Either[FailureResponse, A], Any]

That's better, with that we can have a makeRequest that returns a SttpRequest[A]. It will receive the complete uri and a JsonDecoder[A] implicitly:

import sttp.client3.ziojson.asJson
import sttp.model.{ MediaType, Uri }
import zio.json.JsonDecoder

def makeRequest[A](uri: Uri)(using JsonDecoder[A]): SttpRequest[A] =
  basicRequest
    .get(uri)
    .readTimeout(10.seconds)
    .contentType(MediaType.ApplicationJson)
    .response(asJson[A])

The complete way to build requests using sttp is very well documented here. makeRequest returns the description of a simple GET request that parses the result into json using JsonDecoder.

Now we have all we need to create the trait PokeRequest:

trait PokeRequest[A](id: String | Long):
  val resource: String

  def sttpRequest(host: ApiHost)(using JsonDecoder[A]): SttpRequest[A] =
    makeRequest(host.uri.addPath(resource, id.toString))

The new trait is decoupled from the client, and easily customizable, well done us! Before creating a concrete request we need the response well defined (the generic A in the request), so let's define a Berry:

import zio.json.{ jsonField, DeriveJsonDecoder, JsonDecoder }

final case class Berry(
    id: Int,
    name: String,
    @jsonField("growth_time") growthTime: Int,
    @jsonField("max_harvest") maxHarvest: Int,
    @jsonField("natural_gift_power") naturalGiftPower: Int,
    size: Int,
    smoothness: Int,
    @jsonField("soil_dryness") soilDryness: Int,
    firmness: NamedAPIResource,
    flavors: List[BerryFlavorMap],
    item: NamedAPIResource,
    @jsonField("natural_gift_type") naturalGiftType: NamedAPIResource
)

object Berry:
  given JsonDecoder[Berry] = DeriveJsonDecoder.gen

The pokeapi response has all its field names in snake case, so jsonField is used when the names don't match. The most important part is the creation of the JsonDecoder using the code generator. All in place to write BerryRequest:

final case class BerryRequest(id: String | Long) extends PokeRequest[Berry](id):
  val resource = "berry"

Having the response, writting the request is that simple! What do we need now to send request? You won't see that coming, dear reader! We need a...

PokeApiClient

I am a master naming stuff

Cool, now let's get to business. Sttp gives us the generic structures we are looking for, we just need to adapt it to our domain, making it more restrict. Our PokeApiClient will be defined using an SttpBackend:

import sttp.client3.*
import sttp.monad.MonadError

case class PokeApiClient[F[_], +P](host: ApiHost)(using
    backend: SttpBackend[F, P]
):
  given monadError: MonadError[F] = backend.responseMonad

The backend wraps the real http client, F is the effect type in use (ZIO, Future, etc) and P indicates extra capabilities like streaming or websockets, so don't worry about it, we are not using any of those. The last important bit is the given monadError: it's the way to inform if the request was successful or not.

The client will have only one public method send that receives the request, the JsonDecoder and return a F[A]. However, splitting it in two makes it easier to understand:

def send[A](request: PokeRequest[A])(using JsonDecoder[A]): F[A] =
  doSend(request).flatMap {
    case Right(value) => monadError.unit(value)
    case Left(error) =>  monadError.error(error)
  }

private def doSend[A](
    request: PokeRequest[A]
)(using JsonDecoder[A]): F[Either[FailureResponse, A]] =
  request
    .sttpRequest(host)
    .send(backend)
    .map(_.body)

doSend provides the request with a host and effectively sends the request using the backend, returning the response body. send will then flatMap it and use the appropriate channels of monadError to return the result, unit for success and error for failure.

This last line some complexity in it, but honestly, don't think about it. Let's focus on what's matter: we have a generic, extensible api client!

Using the PokeApiClient

All the sttp backends published for Scala 3 are supported. An example with Futures:

given backend: SttpBackend[Future, Any] = HttpClientFutureBackend()
val client = PokeApiClient()

client.send(ContestTypeRequest(1)).onComplete {
    case Success(contest) => println(contest.names)
    case Failure(t)       => println(s"Failed with: $t")
}

or with ZIO:

val client = AsyncHttpClientZioBackend().map(implicit backend => PokeApiClient())

val zio = client.flatMap(_.send(PokemonRequest("bulbasaur")))
val pokemon = Unsafe.unsafeCompat { implicit u =>
  Runtime.default.unsafe.run(zio).getOrThrowFiberFailure()
}
print(pokemon.id)

You can find more examples using different backends, and the complete code in the Github repo.

Summary

Revisiting our own code is a stimulating experience - always good to see how the perspective changed, and what we've learned. Sttp is a powerful tool, and the knowledge acquired studying it made it easy to create an extensible api client, that allows the user to decide which effect system they want to use.

The final outcome is in the list of wrapper libraries of the PokeApi. Can you see possible improvements? Just let me know!

References

Originally published at https://juliano-alves.com on October, 2022.

Shapeless: a real world use case

Juliano Alves — Mon, 20 Apr 2020 09:32:31 +0000

Shapeless is a library for generic programming in Scala, largely present in the ecosystem, but mostly behind the scenes; it is likely shapeless powers some of the libraries in your project, even though you don't use it directly.

Trying to solve an everyday problem, I've found an use case which I could solve using shapeless. This post doesn't intend to explain how shapeless work (there is a whole book about it here), but to provide a taste of it instead.

The Challenge

We need to write a REST API consumer using ZIO and http4s. Here is the service definition:

import io.circe.Decoder
import zio.{Has, RIO, Task}

object HttpClient {
  type HttpClient = Has[Service]

  trait Service {
    protected final val rootUrl = "http://localhost:8080"

    def get[T](uri: String, parameters: Map[String, String])
              (implicit d: Decoder[T]): Task[List[T]]
  }

  def get[T](resource: String, parameters: Map[String, String])
            (implicit d: Decoder[T]): RIO[HttpClient, List[T]] =
    RIO.accessM[HttpClient](_.get.get[T](resource, parameters))
}

In case you are not familiar with ZIO (you should, it's awesome), what you need to know is:

every get requests returns a Task of List
the get function outside the Service is just a helper to access the environment of the effect (ZIO stuff)

Learn more about ZIO modules and layers here.

This is the HttpClient.Service implementation, using http4s:

import io.circe.Decoder
import org.http4s.Uri
import org.http4s.circe.CirceEntityCodec.circeEntityDecoder
import org.http4s.client.Client
import org.http4s.client.dsl.Http4sClientDsl
import zio._
import zio.interop.catz._

class Http4sClient(client: Client[Task])
  extends HttpClient.Service with Http4sClientDsl[Task] {

  def get[T](resource: String, parameters: Map[String, String])
            (implicit d: Decoder[T]): Task[List[T]] = {
    val uri = Uri(path = rootUrl + resource)
      .withQueryParams(parameters)

    client
      .expect[List[T]](uri.toString())
      .foldM(IO.fail(_), ZIO.succeed(_))
  }
}

Http4sClient.get adds resource to the uri and the parameters are the query string. Now, to represent the request call, we have a case class called OrganisationRequest:

case class OrganisationRequest(code: Option[String],
                               description: Option[String],
                               page: Integer = 1)

The Problem

Using the client (via get helper) is trivial, except for one detail:

import HttpClient.get

def organisations(request: OrganisationRequest):
  get[Organisation]("/organisations", ???)

We need to transform the request into Map[String, String], what is an easy task. However, there are many "request" objects, and writing toMap methods to every single one of them is a Java-ish solution. Here is the challenge: how can we build this generic transformation?

Spoiler: with shapeless.

A bit of shapeless

This section is a grasp of how shapeless works, so the solution will make more sense when we get there. Shapeless can create an Heterogenous List (or HList) as a generic representation of case classes. Let's do it using Generic:

scala> import shapeless._

scala> val org = OrganisationRequest(Some("acme"), None, 5)
org: OrganisationRequest = OrganisationRequest(Some(org),None,5)

scala> val gen = Generic[OrganisationRequest]
gen: shapeless.Generic[OrganisationRequest]
  {type Repr =
    Option[String]
    :: Option[String]
    :: Integer
    :: shapeless.HNil} = anon$macro$4$1@48f146f2

scala> gen.to(org)
res8: gen.Repr = Some(acme) :: None :: 5 :: HNil

The generic representation of OrganisationRequest is an HList of type Option[String] :: Option[String] :: Int :: HNil. We have the values, but we need the names of the fields for our Map. We need LabelledGeneric instead of Generic:

scala> val lgen = LabelledGeneric[OrganisationRequest]
lgen: shapeless.LabelledGeneric[OrganisationRequest]
  {type Repr =
    Option[String] with shapeless.labelled.KeyTag[Symbol with shapeless.tag.Tagged[String("code")],Option[String]]
    :: Option[String] with shapeless.labelled.KeyTag[Symbol with shapeless.tag.Tagged[String("description")],Option[String]]
    :: Integer with shapeless.labelled.KeyTag[Symbol with shapeless.tag.Tagged[String("page")],Integer]
    :: shapeless.HNil} = shapeless.LabelledGeneric$$anon$1@55f78c67

As you can see, with LabelledGeneric it's possible to retain the information about the field names as well.

The Solution

Luckily, we don't need to manipulate LabelledGeneric ourselves, shapeless provides us with plenty of useful type classes that can be found in the shapless.ops package. We will build our solution using ToMap:

scala> import shapeless.ops.product.ToMap

scala> val toMap = ToMap[OrganisationRequest]
toMap: shapeless.ops.product.ToMap[OrganisationRequest]
  {type K = Symbol
    with shapeless.tag.Tagged[_ >: String("page")
    with String("description")
    with String("code") <: String];
  type V = java.io.Serializable} =
    shapeless.ops.product$ToMap$$anon$5@3bccd311

scala> val map = toMap(org)
map: toMap.Out = Map('page -> 5,
                     'description -> None,
                     'code -> Some(acme))

We can make it even nicer using shapeless syntax:

scala> import shapeless.syntax.std.product._

scala> val map = org.toMap[Symbol, Any]
map: Map[Symbol,Any] = Map('page -> 5,
                           'description -> None,
                           'code -> Some(acme))

For the final solution, let's create an implicit class in order to add a parameters method to our request class. Besides, we should remove every entry with null or None values, flatten the Options and turn keys and values into String:

import shapeless.ops.product.ToMap
import shapeless.syntax.std.product._

implicit class RequestOps[A <: Product](val a: A) {
  def parameters(implicit toMap: ToMap.Aux[A, Symbol, Any]): Map[String, String] =
    a.toMap[Symbol, Any]
      .filter {
        case (_, v: Option[Any]) => v.isDefined
        case (_, v) => v != null
      }
      .map {
        case (k, v: Option[Any]) => k.name -> v.get.toString
        case (k, v) => k.name -> v.toString
      }
}

A few comments here:

A <: Product needs to be in place so we can use shapeless.ops.product. All case classes implement Product, it's just a matter of adding the constrain for implicit resolution;
the implicit parameter toMap is a ToMap.Aux instead of just ToMap. Long story short, shapeless defines the Aux alias in order to make some of its internal complexity more readable and usable. Just trust me here ;)

Finally, this brings us to an elegant solution:

import HttpClient.get
import RequestOps

def organisations(request: OrganisationRequest):
  get[Organisation]("/organisations", request.parameters)

Conclusion

Even though shapeless looks almost magical at the first glance, after dedicating myself to understand it better, I've figured it can be very useful in practical terms. Shapeless provides a broad range of typeclasses that can be used in all sort of ways, and spending time learning about how they work is a very interesting exercise, improving skills related to typeclasses, derivations and bringing clarity about how some popular libraries that use shapeless work, like circe.

I've heard that adding too much shapeless can properly affect the project's compile time. I'd like to hear more about it, if you have experience using shapeless directly, please share in the comments.

Originally published at https://juliano-alves.com on April 6, 2020.

Rust: Deserialize JSON with Serde

Juliano Alves — Tue, 31 Mar 2020 09:52:21 +0000

JSON can be a complicated format to manipulate, even though it's well structured text. When using static type languages - like Rust - we want the almighty compiler on our side, no one wants to work with pure text... but it happens :)

The following JSON has a list of profiles:

[
  {
    "id": 1,
    "type": "personal",
    "details": {
      "firstName": "Juliano",
      "lastName": "Alves",
      "primaryAddress": 7777777
    }
  },
  {
    "id": 2,
    "type": "business",
    "details": {
      "name": "Juliano Business",
      "companyRole": "OWNER",
      "primaryAddress": 8888888
    }
  }
]

They are not the same though. We have to handle two problems:

The field names should follow snake_case instead of camelCase;
The response has different fields

Let's see how we can parse this json into a strongly typed data structure using Serde.

The Data Structure

Both profiles have id and type fields, so instead of the similarities we should think about the differences between them first. Let's define PersonalDetails and BusinessDetails:

struct PersonalDetails {
    first_name: String,
    last_name: String,
    primary_address: i32
}

struct BusinessDetails {
    name: String,
    company_role: String,
    primary_address: i32
}

Now we have to make sure that serde will know how to use these structs.

The derive macro

Based on Rust's #[derive] mechanism, serde provides a handful macro that can be used to generate implementations of Serialize and Deserialize. We just need to deserialize, so let's add it. Once we are changing the code, we can derive Debug as well, to make it easier to print later:

#[derive(Deserialize, Debug)]
struct PersonalDetails {

#[derive(Deserialize, Debug)]
struct BusinessDetails {

Renaming fields

In order to customize fields, serde provides container attributes. To rename camelCase we can use #[serde(rename_all = "...")]:

#[derive(Deserialize, Debug)]
#[serde(rename_all = "camelCase")]
struct PersonalDetails {

#[derive(Deserialize, Debug)]
#[serde(rename_all = "camelCase")]
struct BusinessDetails {

Parsing different objects

The distinction between the profiles is given by the type attribute. When the field identifying which variant we are dealing with is inside the content, serde calls it internally tagged.

Let's define an enum Profile, with two profiles Personal and Business. Using tag we will tell serde to use the type field in order to decide between the variants:

#[derive(Deserialize, Debug)]
#[serde(tag = "type", rename_all = "camelCase")]
enum Profile {
    Personal {
        id: i32,
        details: PersonalDetails,
    },
    Business {
        id: i32,
        details: BusinessDetails,
    },
}

Now we can parse the json with serde_json:

let profiles: Vec<Profile> = serde_json::from_str(data)?;

Full example

Cargo.toml

[package]
name = "serde-example"
version = "0.1.0"
authors = ["Juliano Alves <von.juliano@gmail.com>"]
edition = "2018"

[dependencies]
serde = "1.0"
serde_derive = "1.0"
serde_json = "1.0"

main.rs

#[macro_use]
extern crate serde_derive;

use serde_json::Result;

fn main() -> Result<()>  {
    let data = r#"
    [
      {
        "id": 1,
        "type": "personal",
        "details": {
          "firstName": "Juliano",
          "lastName": "Alves",
          "primaryAddress": 7777777
        }
      },
      {
        "id": 2,
        "type": "business",
        "details": {
          "name": "Juliano Business",
          "companyRole": "OWNER",
          "primaryAddress": 8888888
        }
      }
    ]
    "#;

    let profiles: Vec<Profile> = serde_json::from_str(data)?;
    println!("{:#?}", profiles);

    Ok(())
}

#[derive(Deserialize, Debug)]
#[serde(rename_all = "camelCase")]
struct PersonalDetails {
    first_name: String,
    last_name: String,
    primary_address: i32
}

#[derive(Deserialize, Debug)]
#[serde(rename_all = "camelCase")]
struct BusinessDetails {
    name: String,
    company_role: String,
    primary_address: i32
}

#[derive(Deserialize, Debug)]
#[serde(tag = "type", rename_all = "camelCase")]
enum Profile {
    Personal {
        id: i32,
        details: PersonalDetails,
    },
    Business {
        id: i32,
        details: BusinessDetails,
    },
}

Originally posted on my blog

Quill SQL Server: Returning via OUTPUT

Juliano Alves — Tue, 10 Dec 2019 11:11:38 +0000

Besides NDBC Postgres module, the release of Quill 3.5.0 brings an interesting feature for Microsoft SQL Server users: returning via OUTPUT.

The `returning` feature

Quill has the returning feature, which behaves in different ways, according to the limitations of each database. Postgres has the largest set of functionalities around returning, given that Postgres dialect supports the INSERT ... RETURNING clause:

case class Product(id: Int, description: String, sku: Long)

val q = quote {
  query[Product].insert(lift(Product(1, "My Product", 1011L))).returning(r => (r.id, r.description))
}

ctx.run(q)
// INSERT INTO Product (id, description, sku) VALUES (?, ?, ?) RETURNING id, description

Quite useful. SQL Server supports a very similar feature via OUTPUT clause. The 3.5.0 release brings this feature to Quill as well.

While implementing this feature I've found the inspiration to write the post Contributing to Quill, a Pairing Session, so if you are interested in how this feature came to life, there you go :)

`INSERT ... OUTPUT`

The way to use the new feature in SQL Server is exactly the same:

val q = quote {
  query[Product].insert(lift(Product(1, "SQL Server", 1433L))).returning(r => (r.id, r.description))
}

ctx.run(q)
// INSERT INTO Product (id, description, sku) OUTPUT INSERTED.id, INSERTED.description VALUES (?, ?, ?)

As mentioned before, it is more limited than Postgres returning. While we can even query the return clause in Postgres, the best we can do on SQL Server are arithmetic operations:

val q = quote {
  query[Product].insert(lift(Product(1, "SQL Server", 1433L)))
}

ctx.run(q.returning(r => r.id + 42))
// INSERT INTO Product (id, description, sku) OUTPUT INSERTED.id + 42 VALUES (?, ?, ?)

This is a limitation of SQL Server, so there's not much Quill can do about it - except for making sure that invalid operations don't compile.

Do you use Quill with SQL Server? Give us feedback about the new feature!

Originally posted on my blog

Quill NDBC Postgres: A New Async Module

Juliano Alves — Mon, 02 Dec 2019 10:40:16 +0000

Quill 3.5.0 is out and this release brings an integration with the already mentioned NDBC Driver. This post is a brief summary about the new module quill-ndbc-postgres.

Motivation

Quill has an asynchronous module called quill-async which provides support to MySQL and Postgres databases. This module is built on top of mauricio/postgresql-async: a Netty based, database drivers for Postgres and MySQL written in Scala. It is a great solution, and we have been using it for a while now.

But all of sudden, this issue appeared:

Wait... what?

Well, it happens. A replacement is necessary.

NDBC: Non-blocking Database Connectivity

NDBC's goal is to provide a full asynchronous approach to handle databases. It is built using Netty and Trane.io Futures, a High-performance Future implementation for the JVM. Sounds's like a good replacement!

Took me some time to implement it, but a few days ago my pull request integrating NDBC got merged. Now we have an alternative to quill-async-postgres; async support via NDBC driver is available with Postgres database.

Quill + NDBC

To replace the deprecated driver just a few changes are necessary. First, add quill-ndbc-postgres to your dependencies:

libraryDependencies ++= Seq(
  "io.getquill" %% "quill-ndbc-postgres" % "3.4.11"
)

The application.properties needs to change as well:

ctx.ndbc.dataSourceSupplierClass=io.trane.ndbc.postgres.netty4.DataSourceSupplier
ctx.ndbc.host=database-host
ctx.ndbc.port=1234
ctx.ndbc.user=username
ctx.ndbc.password=password
ctx.ndbc.database=dbname

The main difference is the first line, NDBC needs to know which data source supplier class to use, for Postgres it is io.trane.ndbc.postgres.netty4.DataSourceSupplier.

Even though NDBC is written on top of Netty, we keep the api and implementation apart. This way we can build another implementation, on top of Akka, for instance.

Now we have everything in place to start using NdbcPostgresContext:

val ctx = new NdbcPostgresContext(Literal, "ctx")

That's all, fairly straightforward. When you start using it, let me know how it goes for you in the comments, or please open an issue at the project's github.

The next step is the MySQL implementation. I will let you know when it happens :)

Originally posted on my blog

Contributing to Quill, a Pairing Session

Juliano Alves — Mon, 25 Nov 2019 11:01:34 +0000

As one of Quill's maintainer and an open source enthusiast, I am always thinking of ways to spread the word and have more people contributing to the project. Pair programming is a great technique to share knowledge and introduce newcomers to a project, however, I can't pair with someone in the US, India or Brazil willing to contribute, can I?

Of course I can! Welcome to our your first remote pairing blog session! In this post I will have you as my co-pilot while implementing a new feature to Quill.

Think about it as a proper pairing session, where I am talking directly to you, speaking my mind. Sometimes it can feel like I am jumping around the code way too easily, but this is natural in a pairing session where one person has more experience that the other. And don't be ashamed, express your opinions and suggestions in the comments.

Getting started

The feature we will implement is Add SQL Server Support for returning via OUTPUT, meaning it's mostly about quill-sql and quill-jdbc. Let's walk through this.

Being a SQL Server feature, SQLServerDialectSpec can be a good starting point. However, there aren't any tests regarding returning. Still thinking about Sql Server specifically, it's worth checking sqlserver/JdbcContextSpec as well. There we can find something:

"Insert with returning with single column table" in {
  val inserted = testContext.run {
    qr4.insert(lift(TestEntity4(0))).returningGenerated(_.i)
  }
  testContext.run(qr4.filter(_.i == lift(inserted))).head.i mustBe inserted
}

"Insert with returning with multiple columns and query embedded" in {
  val inserted = testContext.run {
    qr4Emb.insert(lift(TestEntity4Emb(EmbSingle(0)))).returningGenerated(_.emb.i)
  }
  testContext.run(qr4Emb.filter(_.emb.i == lift(inserted))).head.emb.i mustBe inserted
}

Let's try to change these tests to use .returning:

[error] .../quill/quill-jdbc/src/test/scala/io/getquill/context/jdbc/sqlserver/JdbcContextSpec.scala:57:49: The 'returning' clause is not supported by the io.getquill.SQLServerDialect idiom. Use 'returningGenerated' instead.
[error]       qr4.insert(lift(TestEntity4(0))).returning(_.i)

Maybe we can search for the error message. That takes us to Parsing:

idiomReturnCapability match {
  case ReturningMultipleFieldSupported | ReturningClauseSupported =>
  case ReturningSingleFieldSupported =>
    c.fail(s"The 'returning' clause is not supported by the ${currentIdiom.getOrElse("specified")} idiom. Use 'returningGenerated' instead.")
  case ReturningNotSupported =>
    c.fail(s"The 'returning' or 'returningGenerated' clauses are not supported by the ${currentIdiom.getOrElse("specified")} idiom.")
}

That definitely doesn't help now, but let's keep it in mind, eventually we will get back to it.

Let's look for something more specific. Maybe SQLServerDialect can tell us something. The trait's signature is:

trait SQLServerDialect
  extends SqlIdiom
  with QuestionMarkBindVariables
  with ConcatSupport
  with CanReturnField

We are getting close, I can feel it! Following CanReturnField we will see that it extends ReturningCapability, which has the definitions for all existing returning behaviours.

ReturningCapability is returned by idiomReturningCapability from trait Capabilities and its descendants:

trait Capabilities {
  def idiomReturningCapability: ReturningCapability
}

trait CanReturnClause extends Capabilities {
  override def idiomReturningCapability: ReturningClauseSupported = ReturningClauseSupported
}

trait CanReturnField extends Capabilities {
  override def idiomReturningCapability: ReturningSingleFieldSupported = ReturningSingleFieldSupported
}
...

Following idiomReturningCapability we will finally find the place where the SQL is generated, in SqlIdiom:

case r @ ReturningAction(Insert(table: Entity, Nil), alias, prop) =>
  idiomReturningCapability match {
    // If there are queries inside of the returning clause we are forced to alias the inserted table (see #1509). Only do this as
    // a last resort since it is not even supported in all Postgres versions (i.e. only after 9.5)
    case ReturningClauseSupported if (CollectAst.byType[Entity](prop).nonEmpty) =>
      SqlIdiom.withActionAlias(this, r)
    case ReturningClauseSupported =>
      stmt"INSERT INTO ${table.token} ${defaultAutoGeneratedToken(prop.token)} RETURNING ${returnListTokenizer.token(ExpandReturning(r)(this, strategy).map(_._1))}"
    case other =>
      stmt"INSERT INTO ${table.token} ${defaultAutoGeneratedToken(prop.token)}"
  }

case r @ ReturningAction(action, alias, prop) =>
  idiomReturningCapability match {
    // If there are queries inside of the returning clause we are forced to alias the inserted table (see #1509). Only do this as
    // a last resort since it is not even supported in all Postgres versions (i.e. only after 9.5)
    case ReturningClauseSupported if (CollectAst.byType[Entity](prop).nonEmpty) =>
      SqlIdiom.withActionAlias(this, r)
    case ReturningClauseSupported =>
      stmt"${action.token} RETURNING ${returnListTokenizer.token(ExpandReturning(r)(this, strategy).map(_._1))}"
    case other =>
      stmt"${action.token}"
  }

It starts to get complex. Before we carry on, we should summarize what we've learned so far:

SQLServerDialect extends CanReturnField, in order to support returningGenerated only;
CanReturnField ensures this behaviour, defining that idiomReturningCapability returns a ReturningSingleFieldSupported;
ReturningSingleFieldSupported is a ReturningCapability, mother of all the return behaviours;
Parsing verifies the type of the returning clause and fails the compilation if necessary;
SqlIdiom decides how to generate the return statement after checking the current idiomReturningCapability

All this acquired knowledge is vital to implement the new feature. Like the Github issue says, we already have a dialect that does what we need, PostgresDialect:

trait PostgresDialect
  extends SqlIdiom
  with QuestionMarkBindVariables
  with ConcatSupport
  with OnConflictSupport
  with CanReturnClause

As we know, CanReturnField.idiomReturnCapability returns a ReturningClauseSupported:

/**
 * An actual `RETURNING` clause is supported in the SQL dialect of the specified database e.g. Postgres.
 * this typically means that columns returned from Insert/Update/etc... clauses can have other database
 * operations done on them such as arithmetic `RETURNING id + 1`, UDFs `RETURNING udf(id)` or others.
 * In JDBC, the following is done:
 * `connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS))`.
 */
sealed trait ReturningClauseSupported extends ReturningCapability

CanReturnClause is extended by MirrorSqlDialectWithReturnClause as well:

trait MirrorSqlDialectWithReturnClause
  extends SqlIdiom
  with QuestionMarkBindVariables
  with ConcatSupport
  with CanReturnClause

Which is used for tests in SqlActionMacroSpec, for returning and returningGenerated:

"returning clause - single" in testContext.withDialect(MirrorSqlDialectWithReturnClause) { ctx =>
  import ctx._
  val q = quote {
    qr1.insert(lift(TestEntity("s", 0, 1L, None))).returning(_.l)
  }

  val mirror = ctx.run(q)
  mirror.string mustEqual "INSERT INTO TestEntity (s,i,l,o) VALUES (?, ?, ?, ?) RETURNING l"
  mirror.returningBehavior mustEqual ReturnRecord
}

Now we know enough to set up a plan of action.

Development plan

We need a new ReturningCapability, exposed by a new Capabilities via idiomReturningCapability;
SqlIdiom has to accomodate the new ReturningCapability generating the OUTPUT clause;
Parsing needs to allow the new code to compile;
SQLServerDialect will extend the new Capabilities

And we can deal with the unexpected surprises along the way. The game is on!

`OutputClauseSupported` and `CanOutputClause`

Following the stablished convention, the names make sense:

sealed trait OutputClauseSupported extends ReturningCapability

object OutputClauseSupported extends OutputClauseSupported

trait CanOutputClause extends Capabilities {
  override def idiomReturningCapability: OutputClauseSupported = OutputClauseSupported
}

Now a new MirrorSqlDialect extending CanOutputClause:

trait MirrorSqlDialectWithOutputClause
  extends SqlIdiom
  with QuestionMarkBindVariables
  with ConcatSupport
  with CanOutputClause

object MirrorSqlDialectWithOutputClause extends MirrorSqlDialectWithOutputClause {
  override def prepareForProbing(string: String) = string
}

So far so good... or maybe not. This change already breaks the code:

[error] /Users/juliano.alves/development/opensource/quill/quill-core/src/main/scala/io/getquill/norm/ExpandReturning.scala:23:11: match may not be exhaustive.
[error] It would fail on the following input: OutputClauseSupported
[error]     idiom.idiomReturningCapability match {
[error]           ^
[error] /Users/juliano.alves/development/opensource/quill/quill-core/src/main/scala/io/getquill/quotation/Parsing.scala:831:38: match may not be exhaustive.
[error] It would fail on the following input: OutputClauseSupported
[error]     def verifyAst(returnBody: Ast) = capability match {
[error]                                      ^
[error] /Users/juliano.alves/development/opensource/quill/quill-core/src/main/scala/io/getquill/quotation/Parsing.scala:875:7: match may not be exhaustive.
[error] It would fail on the following input: OutputClauseSupported
[error]       idiomReturnCapability match {
[error]       ^
[error] three errors found

Let's take care of ExpandReturning first. The error happens because OutputClauseSupported is not being taken in consideration during the pattern matching. We are basing the new implementation on ReturningClauseSupported, so it's reasonable to simply mirror its behaviour:

// line 23
idiom.idiomReturningCapability match {
  case ReturningClauseSupported | OutputClauseSupported =>
    ReturnAction.ReturnRecord
  case ReturningMultipleFieldSupported =>
  ...

Similar changes in Parsing, for both errors:

// line 831
def verifyAst(returnBody: Ast) = capability match {
  case OutputClauseSupported =>
  case ReturningClauseSupported =>
  // Only .returning(r => r.prop) or .returning(r => OneElementCaseClass(r.prop1..., propN)) or .returning(r => (r.prop1..., propN)) (well actually it's prop22) is allowed.
  case ReturningMultipleFieldSupported =>
    returnBody match {
  ...

// line 875
idiomReturnCapability match {
  case ReturningMultipleFieldSupported | ReturningClauseSupported | OutputClauseSupported =>
  case ReturningSingleFieldSupported =>
    c.fail(s"The 'returning' clause is not supported by the ${currentIdiom.getOrElse("specified")} idiom. Use 'returningGenerated' instead.")
  case ReturningNotSupported =>
    c.fail(s"The 'returning' or 'returningGenerated' clauses are not supported by the ${currentIdiom.getOrElse("specified")} idiom.")
}

There is something in this snippet that deserves attention, the idiomReturnCapability being matched. It is defined in Parsing as well:

private[getquill] def idiomReturnCapability: ReturningCapability = {
  val returnAfterInsertType =
    currentIdiom
      .toSeq
      .flatMap(_.members)
      .collect {
        case ms: MethodSymbol if (ms.name.toString == "idiomReturningCapability") => Some(ms.returnType)
      }
      .headOption
      .flatten

  returnAfterInsertType match {
    case Some(returnType) if (returnType =:= typeOf[ReturningClauseSupported]) => ReturningClauseSupported
    case Some(returnType) if (returnType =:= typeOf[ReturningSingleFieldSupported]) => ReturningSingleFieldSupported
    case Some(returnType) if (returnType =:= typeOf[ReturningMultipleFieldSupported]) => ReturningMultipleFieldSupported
    case Some(returnType) if (returnType =:= typeOf[ReturningNotSupported]) => ReturningNotSupported
    // Since most SQL Dialects support returing a single field (that is auto-incrementing) allow a return
    // of a single field in the case that a dialect is not actually specified. E.g. when SqlContext[_, _]
    // is used to define `returning` clauses.
    case other => ReturningSingleFieldSupported
  }
}

Long story short, returnAfterInsertType has the type information about ReturningCapability being used by the idiom. Then, the pattern matching returns the corresponding object of that type, or a ReturningSingleFieldSupported otherwise. So OutputClauseSupported has to be included as an option:

  returnAfterInsertType match {
    case Some(returnType) if (returnType =:= typeOf[ReturningClauseSupported]) => ReturningClauseSupported
    case Some(returnType) if (returnType =:= typeOf[OutputClauseSupported]) => OutputClauseSupported
    ...

Code compiling, tests passing. Time to write some tests to the new feature.

Adapting the SQL Idiom

Let's start simple, adding a test similar to returning clause - single but generating an OUTPUT clause:

"output clause - single" in testContext.withDialect(MirrorSqlDialectWithOutputClause) { ctx =>
  import ctx._
  val q = quote {
    qr1.insert(lift(TestEntity("s", 0, 1L, None))).returning(_.l)
  }

  val mirror = ctx.run(q)
  mirror.string mustEqual "INSERT INTO TestEntity (s,i,l,o) OUTPUT INSERTED.l VALUES (?, ?, ?, ?)"
  mirror.returningBehavior mustEqual ReturnRecord
}

It fails, for obvious reasons. Time to work on SqlIdiom and figure how to generate the expected result. Remember those two pattern clauses related to idiomReturningCapability?

case r @ ReturningAction(Insert(table: Entity, Nil), alias, prop) =>
  idiomReturningCapability match {
  ...

case r @ ReturningAction(action, alias, prop) =>
  idiomReturningCapability match {
  ...

In order to identify which of the ReturningActions clauses we should look at, we will start dealing with Quill's ASTs!

Quill's Abstract Syntax Trees

This is my favourite part of the process, using the console to explore the AST. In the sbt console:

scala> import io.getquill._
scala> import io.getquill.ast._
scala> val ctx = new SqlMirrorContext(MirrorSqlDialectWithOutputClause, Literal)
scala> import ctx._

scala> case class TestEntity(s: String, i: Int, l: Long, o: Option[Int])
scala> val qr1 = quote { query[TestEntity] }

scala> val q = quote {
         qr1.insert(lift(TestEntity("s", 0, 1L, None))).returning(_.l)
       }
q: ctx.Quoted[ctx.ActionReturning[TestEntity,Long]]{def quoted: io.getquill.ast.Returning; def ast: io.getquill.ast.Returning; def id380689071(): Unit; val liftings: AnyRef{val TestEntity.apply("s", 0, 1L, scala.None).s: io.getquill.quotation.ScalarValueLifting[String,String]; val TestEntity.apply("s", 0, 1L, scala.None).i: io.getquill.quotation.ScalarValueLifting[Int,Int]; val TestEntity.apply("s", 0, 1L, scala.None).l: io.getquill.quotation.ScalarValueLifting[Long,Long]; val TestEntity.apply("s", 0, 1L, scala.None).o: io.getquill.quotation.ScalarValueLifting[Option[Int],Option[Int]]}} = $anon$1@374e427b

To make visualization of the AST easy, Quill integrates the awesome PPrint module:

scala> pprint.pprintln(q.ast, 200)
Returning(
  Insert(
    Entity("TestEntity", List()),
    List(
      Assignment(Ident("v"), Property(Ident("v"), "s"), ScalarValueLift("$line9.$read.$iw.$iw.$iw.$iw.$iw.$iw.TestEntity.apply(\"s\", 0, 1L, scala.None).s", "s", MirrorEncoder(<function3>))),
      Assignment(Ident("v"), Property(Ident("v"), "i"), ScalarValueLift("$line9.$read.$iw.$iw.$iw.$iw.$iw.$iw.TestEntity.apply(\"s\", 0, 1L, scala.None).i", 0, MirrorEncoder(<function3>))),
      Assignment(Ident("v"), Property(Ident("v"), "l"), ScalarValueLift("$line9.$read.$iw.$iw.$iw.$iw.$iw.$iw.TestEntity.apply(\"s\", 0, 1L, scala.None).l", 1L, MirrorEncoder(<function3>))),
      Assignment(Ident("v"), Property(Ident("v"), "o"), ScalarValueLift("$line9.$read.$iw.$iw.$iw.$iw.$iw.$iw.TestEntity.apply(\"s\", 0, 1L, scala.None).o", None, MirrorEncoder(<function3>)))
    )
  ),
  Ident("x1"),
  Property(Ident("x1"), "l")
)

Apparently we are dealing with the second of those two cases. Let's double check:

scala> q.ast match {
         case ReturningAction(Insert(entity: Entity, Nil), _, prop) => "first"
         case ReturningAction(action, alias, prop) => "second"
       }
res18: Boolean = second

Changing `SqlIdiom`

Following the same approach as ReturningClauseSupported, but using OUTPUT instead of RETURNING, we have:

case r @ ReturningAction(action, alias, prop) =>
  idiomReturningCapability match {
    ...
    case ReturningClauseSupported =>
      stmt"${action.token} RETURNING ${returnListTokenizer.token(ExpandReturning(r)(this, strategy).map(_._1))}"
    case OutputClauseSupported =>
      stmt"${action.token} OUTPUT ${returnListTokenizer.token(ExpandReturning(r)(this, strategy).map(_._1))}"

That change generates:

scala> ctx.run(q).string
<console>:23: INSERT INTO TestEntity (s,i,l,o) VALUES (?, ?, ?, ?) OUTPUT l

Okay, ${action.token} becomes INSERT INTO TestEntity (s,i,l,o) VALUES (?, ?, ?, ?), so it's necessary to extract some information from action in order to generate the insert clause. Quill already does that:

// line 432
case Insert(entity: Entity, assignments) =>
  val table = insertEntityTokenizer.token(entity)
  val columns = assignments.map(_.property.token)
  val values = assignments.map(_.value)
  stmt"INSERT $table${actionAlias.map(alias => stmt" AS ${alias.token}").getOrElse(stmt"")} (${columns.mkStmt(",")}) VALUES (${values.map(scopedTokenizer(_)).mkStmt(", ")})"

Combining this code with our knowledge about the existing RETURNING clause to generate the OUTPUT clause, we have:

case OutputClauseSupported => action match {
  case Insert(entity: Entity, assignments) =>
    val table = insertEntityTokenizer.token(entity)
    val columns = assignments.map(_.property.token)
    val values = assignments.map(_.value)
    stmt"INSERT $table${actionAlias.map(alias => stmt" AS ${alias.token}").getOrElse(stmt"")} (${columns.mkStmt(",")}) OUTPUT ${returnListTokenizer.token(ExpandReturning(r)(this, strategy).map(_._1))} VALUES (${values.map(scopedTokenizer(_)).mkStmt(", ")})"
  case other =>
    fail(s"Action ast can't be translated to sql: '$other'")
}

Back to sbt console:

scala> ctx.run(q).string
<console>:20: INSERT INTO TestEntity (s,i,l,o) OUTPUT l VALUES (?, ?, ?, ?)

Almost there! We now have to find a way to include INSERTED. before every single element of returnListTokenizer. ExpandReturning is the object handling that:

def apply(returning: ReturningAction)(idiom: Idiom, naming: NamingStrategy): List[(Ast, Statement)] = {
  val ReturningAction(_, alias, properties) = returning

  val dePropertized =
    Transform(properties) {
      case `alias` => ExternalIdent(alias.name)
    }
  ...

And I'll be entirely honest here, I have no idea what to do. I've never seen this part of the code.

Asking for help

When contributing to open source, never be afraid to ask for help

And that's what I did. I actually came up with a solution before deusaquilus's review, but it hadn't used the code already in place to solve that problem - and I knew that. Living and learning!

def apply(returning: ReturningAction, renameAlias: Option[String] = None)(idiom: Idiom, naming: NamingStrategy): List[(Ast, Statement)] = {
  val ReturningAction(_, alias, properties) = returning

  val dePropertized = renameAlias match {
    case Some(newName) =>
      BetaReduction(properties, alias -> Ident(newName))
    case None =>
      BetaReduction(properties, alias -> ExternalIdent(alias.name))
  }
  ...

My knowledge regarding BetaReduction is limited, so let's trust deusaquilus advice here. Let's make use of the new resource in SqlIdiom, but we should extract the duplicated code first:

case Insert(entity: Entity, assignments) =>
  val (table, columns, values) = insertInfo(insertEntityTokenizer, entity, assignments)
  stmt"INSERT $table${actionAlias.map(alias => stmt" AS ${alias.token}").getOrElse(stmt"")} (${columns.mkStmt(",")}) VALUES (${values.map(scopedTokenizer(_)).mkStmt(", ")})"
...

private def insertInfo(insertEntityTokenizer: Tokenizer[Entity], entity: Entity, assignments: List[Assignment])(implicit astTokenizer: Tokenizer[Ast]) = {
  val table = insertEntityTokenizer.token(entity)
  val columns = assignments.map(_.property.token)
  val values = assignments.map(_.value)
  (table, columns, values)
}

And finally, let's introduce INSERTED:

case OutputClauseSupported => action match {
  case Insert(entity: Entity, assignments) =>
    val (table, columns, values) = insertInfo(insertEntityTokenizer, entity, assignments)
    stmt"INSERT $table${actionAlias.map(alias => stmt" AS ${alias.token}").getOrElse(stmt"")} (${columns.mkStmt(",")}) OUTPUT ${returnListTokenizer.token(ExpandReturning(r, Some("INSERTED"))(this, strategy).map(_._1))} VALUES (${values.map(scopedTokenizer(_)).mkStmt(", ")})"
  case other =>
    fail(s"Action ast can't be translated to sql: '$other'")
}

Now, in the console we have:

scala> ctx.run(q).string
<console>:20: INSERT INTO TestEntity (s,i,l,o) OUTPUT INSERTED.l VALUES (?, ?, ?, ?)

And the tests are green again. Let's cover the other possibilities for returning:

"output clause - multi" in testContext.withDialect(MirrorSqlDialectWithOutputClause) { ctx =>
  import ctx._
  val q = quote {
    qr1.insert(lift(TestEntity("s", 0, 1L, None))).returning(r => (r.i, r.l))
  }
  val mirror = ctx.run(q)
  mirror.string mustEqual "INSERT INTO TestEntity (s,i,l,o) OUTPUT INSERTED.i, INSERTED.l VALUES (?, ?, ?, ?)"
  mirror.returningBehavior mustEqual ReturnRecord
}
"output clause - operation" in testContext.withDialect(MirrorSqlDialectWithOutputClause) { ctx =>
  import ctx._
  val q = quote { qr1.insert(lift(TestEntity("s", 0, 1L, None))).returning(r => (r.i, r.l + 1)) }
  val mirror = ctx.run(q)

  mirror.string mustEqual "INSERT INTO TestEntity (s,i,l,o) OUTPUT INSERTED.i, INSERTED.l + 1 VALUES (?, ?, ?, ?)"
}
"output clause - record" in testContext.withDialect(MirrorSqlDialectWithOutputClause) { ctx =>
  import ctx._
  val q = quote {
    qr1.insert(lift(TestEntity("s", 0, 1L, None))).returning(r => r)
  }
  val mirror = ctx.run(q)
  mirror.string mustEqual "INSERT INTO TestEntity (s,i,l,o) OUTPUT INSERTED.s, INSERTED.i, INSERTED.l, INSERTED.o VALUES (?, ?, ?, ?)"
  mirror.returningBehavior mustEqual ReturnRecord
}
"output clause - embedded" - {
  "embedded property" in testContext.withDialect(MirrorSqlDialectWithOutputClause) { ctx =>
    import ctx._
    val q = quote {
      qr1Emb.insert(lift(TestEntityEmb(Emb("s", 0), 1L, None))).returning(_.emb.i)
    }
    val mirror = ctx.run(q)
    mirror.string mustEqual "INSERT INTO TestEntity (s,i,l,o) OUTPUT INSERTED.i VALUES (?, ?, ?, ?)"
    mirror.returningBehavior mustEqual ReturnRecord
  }
  "two embedded properties" in testContext.withDialect(MirrorSqlDialectWithOutputClause) { ctx =>
    import ctx._
    val q = quote {
      qr1Emb.insert(lift(TestEntityEmb(Emb("s", 0), 1L, None))).returning(r => (r.emb.i, r.emb.s))
    }
    val mirror = ctx.run(q)
    mirror.string mustEqual "INSERT INTO TestEntity (s,i,l,o) OUTPUT INSERTED.i, INSERTED.s VALUES (?, ?, ?, ?)"
    mirror.returningBehavior mustEqual ReturnRecord
  }
}

Great, everything works! Now we have to handle a fundamental difference between OUTPUT and RETURNING.

Stopping invalid actions

The next test is "with returning clause - query", with this code:

val q = quote {
  qr1
    .insert(lift(TestEntity("s", 0, 1L, None)))
    .returning(r => (query[Dummy].map(d => d.i).max))
}

Unlike Postgres, SQL Server doesn't allow an output clause in a select statement, meaning that our implementation has to fail the compilation when a query is present. Let's start with a test:

"output clause - should fail on query" in testContext.withDialect(MirrorSqlDialectWithOutputClause) { ctx =>
  """import ctx._; quote { qr4.insert(lift(TestEntity4(1L))).returning(r => query[TestEntity4].filter(t => t.i == r.i)) }""" mustNot compile
}

Recapping the beginning of our session:

Parsing verifies the type of the returning clause and can fail the compilation if necessary

That is exactly what we need:

// line 831
def verifyAst(returnBody: Ast) = capability match {
  case OutputClauseSupported =>
  case ReturningClauseSupported =>
  // Only .returning(r => r.prop) or .returning(r => OneElementCaseClass(r.prop1..., propN)) or .returning(r => (r.prop1..., propN)) (well actually it's prop22) is allowed.
  case ReturningMultipleFieldSupported =>
    returnBody match {
  ...

In order to figure the right moment to stop the compilation, we need to understand that AST better. Back to the console:

val q = quote { qr4.insert(lift(TestEntity4(1L))).returning(r => query[TestEntity4].filter(t => t.i == r.i)) }

scala> pprint.pprintln(q.ast, 200)
Returning(
  Insert(
    Entity("TestEntity4", List()),
    List(Assignment(Ident("v"), Property(Ident("v"), "i"), ScalarValueLift("$line8.$read.$iw.$iw.$iw.$iw.$iw.$iw.TestEntity4.apply(1L).i", 1L, MirrorEncoder(<function3>))))
  ),
  Ident("r"),
  Filter(Entity("TestEntity4", List()), Ident("t"), BinaryOperation(Property(Ident("t"), "i"), ==, Property(Ident("r"), "i")))
)

The last line is the returnBody we are looking for. Having .map instead of .filter it generates:

val q2 = quote { qr4.insert(lift(TestEntity4(1L))).returning(r => query[TestEntity4]).map(t => t.i) }

scala> pprint.pprintln(q2.ast, 200)
Returning(
  ...
  Map(Entity("TestEntity4", List()), Ident("x1"), Property(Ident("x1"), "i"))
)

None of these can be allowed to compile. Many similar operations extend Query, so let's consider that to define a limitation in Parsing:

implicit class InsertReturnCapabilityExtension(capability: ReturningCapability) {
  def verifyAst(returnBody: Ast) = capability match {
    case OutputClauseSupported =>
      returnBody match {
        case _: Query =>
          c.fail(s"${currentIdiom.map(n => s"The dialect $n does").getOrElse("Unspecified dialects do")} not allow queries in 'returning' clauses.")
        case _ =>
      }
    case ReturningClauseSupported =>
    ...

After this change, the tests are happy and green again. We need the same tests for .returningGenerated, but the good news are that we need a quite similar change, which is trivial at this point:

// line 450
case r @ ReturningAction(Insert(table: Entity, Nil), alias, prop) =>
  idiomReturningCapability match {
    ...
    case ReturningClauseSupported =>
      stmt"INSERT INTO ${table.token} ${defaultAutoGeneratedToken(prop.token)} RETURNING ${returnListTokenizer.token(ExpandReturning(r)(this, strategy).map(_._1))}"
    case OutputClauseSupported =>
      stmt"INSERT INTO ${table.token} OUTPUT ${returnListTokenizer.token(ExpandReturning(r, Some("INSERTED"))(this, strategy).map(_._1))} ${defaultAutoGeneratedToken(prop.token)}"
    ...

It concludes the changes around the sql idiom.

Changing `SQLServerDialect`

The dialect will extend CanOutputClause from now on:

trait SQLServerDialect
  extends SqlIdiom
  with QuestionMarkBindVariables
  with ConcatSupport
  with CanOutputClause {

We have to ensure that the SQL generated is valid against the database. Let's add some tests to JdbcContextSpec:

"Insert with returning with multiple columns" in {
  testContext.run(qr1.delete)
  val inserted = testContext.run {
    qr1.insert(lift(TestEntity("foo", 1, 18L, Some(123)))).returning(r => (r.i, r.s, r.o))
  }
  (1, "foo", Some(123)) mustBe inserted
}

"Insert with returning with multiple columns and operations" in {
  testContext.run(qr1.delete)
  val inserted = testContext.run {
    qr1.insert(lift(TestEntity("foo", 1, 18L, Some(123)))).returning(r => (r.i + 100, r.s, r.o.map(_ + 100)))
  }
  (1 + 100, "foo", Some(123 + 100)) mustBe inserted
}

"Insert with returning with multiple columns and query embedded" in {
  testContext.run(qr1Emb.delete)
  testContext.run(qr1Emb.insert(lift(TestEntityEmb(Emb("one", 1), 18L, Some(123)))))
  val inserted = testContext.run {
    qr1Emb.insert(lift(TestEntityEmb(Emb("two", 2), 18L, Some(123)))).returning(r =>
      (r.emb.i, r.o))
  }
  (2, Some(123)) mustBe inserted
}

"Insert with returning with multiple columns - case class" in {
  case class Return(id: Int, str: String, opt: Option[Int])
  testContext.run(qr1.delete)
  val inserted = testContext.run {
    qr1.insert(lift(TestEntity("foo", 1, 18L, Some(123)))).returning(r => Return(r.i, r.s, r.o))
  }
  Return(1, "foo", Some(123)) mustBe inserted
}

What can go wrong?

[info] - Insert with returning with multiple columns *** FAILED ***
[info]   com.microsoft.sqlserver.jdbc.SQLServerException: A result set was generated for update.
[info]   at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:227)
[info]   at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:592)
[info]   at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:508)
[info]   at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7233)
[info]   at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2869)
[info]   at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:243)
[info]   at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:218)
[info]   at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeUpdate(SQLServerPreparedStatement.java:461)
[info]   at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeUpdate(ProxyPreparedStatement.java:61)
[info]   at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeUpdate(HikariProxyPreparedStatement.java)

This is an interesting error, which actually happens in ProductJdbcSpec as well. Luckly, it has been reported in the issue:

Also note that SQL Server requires prep.executeQuery() instead of a combination of preparedStatement.executeUpdate() and preparedStatement.getGeneratedKeys()

Good to know. JdbcContextBase is the responsible for handling jdbc connections. According to the description above, the method we are looking for is executeActionReturning:

def executeActionReturning[O](sql: String, prepare: Prepare = identityPrepare, extractor: Extractor[O], returningBehavior: ReturnAction): Result[O] =
  withConnectionWrapped { conn =>
    val (params, ps) = prepare(prepareWithReturning(sql, conn, returningBehavior))
    logger.logQuery(sql, params)
    ps.executeUpdate()
    handleSingleResult(extractResult(ps.getGeneratedKeys, extractor))
  }

SqlServerJdbcContextBase extends JdbcContextBase, defining SQLServerDialect as idiom:

trait SqlServerJdbcContextBase[N <: NamingStrategy] extends JdbcContextBase[SQLServerDialect, N]
  with BooleanObjectEncoding
  with UUIDStringEncoding {

  val idiom = SQLServerDialect
}

SQL Server demands an exceptional behaviour regarding preparedStatement, so we need to override executeActionReturning:

trait SqlServerJdbcContextBase[N <: NamingStrategy] extends JdbcContextBase[SQLServerDialect, N]
  with BooleanObjectEncoding
  with UUIDStringEncoding {

  val idiom = SQLServerDialect

  override def executeActionReturning[O](sql: String, prepare: Prepare = identityPrepare, extractor: Extractor[O], returningBehavior: ReturnAction): Result[O] =
    withConnectionWrapped { conn =>
      val (params, ps) = prepare(prepareWithReturning(sql, conn, returningBehavior))
      logger.logQuery(sql, params)
      handleSingleResult(extractResult(ps.executeQuery, extractor))
    }
}

Obviously we have to add more tests to SQLServerDialectSpec, but this post is already long enough to paste those tests here. With that we finish our contribution to Quill.

That was fun, maybe you should be the pilot next time! Let me know when we should have the next session in the comments!

Originally posted on my blog

The Journey of an Open Source Developer

Juliano Alves — Mon, 04 Nov 2019 11:23:25 +0000

Originally posted on my blog

Hacktoberfest is an awesome initiative that invites developers from around the world to participate and contribute to Open Source. This is the second year in a row that I managed to beat the challenge, and inspired by it I decided to write a post about how much I learned since I first joined that community.

A long time ago in a galaxy far, far away...

The year was 2010, where the universe made his move to make me cross paths with my good friend Jonas Abreu. Jonas is the creator of Mirror, a simple DSL layer over Java Reflection API, making meta programming easier. There was a feature request for a proxy creation capability open, and Jonas asked me if I was interested in implementing it. I accepted the challenge.

I suffered for three days, dealing with code that I didn't even understand... until I did. And I still remember the feeling that my triumph brought me that day. That pain was the knowledge getting inside my brain!

My very first contribution to open source. Here's what I learned contributing to Mirror:

Lots about Java meta programming
Proxy classes, Javassist and cglib
Receiving and providing useful/respectful feedback about code
Designing a tool that will be used by others
Some interesting dark magic

I loved the idea of contributing to the greater good, while challenging myself and working with brilliant engineers, way smarter than me.

I started taking part community initiatives, like running coding dojos. Doing that I met more great devs and we started a small group. At some point, that group decided to learn Scala. We where studying, coding together and eventually started Scaladores, the Scala user group of São Paulo.

Eventually, Jonas started talking about a different approach for learning he had been studying, called deliberate practice.

What if we could put all these recently acquired knowledge altogether?

Gamification + Deliberate Practice = Aprenda

The result was Aprenda, a learning platform that mix gamification and deliberate practice, making it easier to learn html, regex or git.

What do you want to learn today?

This is what I learned working on Aprenda:

Deliberate Practice
Gamification
Design a system using Actors
Parser Combinators

Entirely different topics this time, technically and conceptually. However I couldn't dedicate a good amount of time to this project. I had other problems to deal with, what brew some new ideas.

I had finished working in a Ruby on Rails project, and moved to a team working with Java and Spring. Many CRUDs, a lot of repetition, what gave me an idea. So I started a new project.

Writing my own Gem

The idea was clear, I wanted to generate that similar code the same way one can do with rails g scaffold. Using Thor, the same gem powering Rails generators, I created Spring MVC Scaffold. Now, everything I needed to do to create a CRUD was:

springmvc scaffold product name:string value:double active:boolean

Even though it's not being maintained anymore, it's still available in Rubygems. Here are my takeaways from my first public tool:

Generating code/files with Thor
Defining commands for a cli
Organising code of a ruby lib
Creating a ruby gem
Travis for opensource
Publishing to Rubygems

After solving a common problem, think about making that solution available. Most likely, other people have similar problems.

And I tell you what, after that project, I went to work with a tech environment full of problems.

Working with Microsoft Tech

While working with Microsoft .NET, I found a few issues that other communities had already solved. My new opportunity to contribute was a matter of porting those solutions.

Selenia

In my opinion, Selenium API has always been pretty bad. Selenia is a DSL to write concise UI tests in C#, so instead of having:

IWebDriver driver;
ChromeOptions options = new ChromeOptions();
options.addExtensions(new File("/path/to/extension.crx"));
ChromeDriver driver = new ChromeDriver(options);
driver = new ChromeDriver();
driver.Navigate().GoToUrl("http://www.google.com/ncr");

IWebElement query = driver.FindElement(By.Name("q"));
query.SendKeys("Selenium");
query.Submit();

driver.Quit();

We can write

Open("http://www.google.com/");
S(By.Name("q").Value("Selenia").Enter();

If you are wondering, the answer is no, it's not necessary to close the driver yourself.

CSharp.Fun

When building reports, it's common to navigate the hierarchy of objects, and even more common is to check if the current node is not null before the next step. My friend Breno and I implemented a few Monadic Types to deal with that pain, like Option and Try:

// Option
var option = Option.From<string>(null);

option.Should().Be(Option.None);

// Try
var failure = Try.From<int>(() => { throw new Exception(); });
var success = failure.Recover((Exception ex) => 1);

success.Value.Should().Be(1);

Ioget

Immutability makes a developer's life easy. Back in the day, asp.net mvc would disagree, the only way to instantiate objects was via setters. I had to do something about it.

I built Ioget to to help with unmarshalling request parameters in Web applications. HTTP request parameters are strings, so Ioget looks for the best way to parse those paams according to the given class, instantiating objects via their constructors, therefore making them immutable.

Even though I published it to nuget, I've never managed to integrate it with asp.net, at the time I stoped working with Microsoft technology. Here are my learnings from the three projects:

Reification
Internals of asp.net mvc
Implementing monads
Internals of .net framework

Regarding internals, I really like this snippet, used in Selenia to close the driver:

private void MarkForAutoClose(IWebDriver driver) =>
  AppDomain.CurrentDomain.DomainUnload += (s, e) => driver.Quit();

There and Back Again

I moved to London in 2016, what paused my contributions for a while. Eventually, I watched a talk from Gustavo Amigo about his project quill-pgsql, an extension to support Postgres data types with Quill. He mentioned that the project was in its early moments, ideal for someone to join. And was missing writing some Scala.

After a few pull requests, I decided to contribute with the main project.

Quill is a Compile-time Language Integrated Queries for Scala. I consider it the most challenging (and interesting) project I have ever contributed to. It's been a few years since I started, and nowadays I am one of the maintainers. Here's what I learned working on Quill:

Abstract Syntactic Trees
The Dark Arts also known as Scala Macros
How to make code extensible via implicits
Exclusive/weird SQL rules

The next project is strongly related. We have a module quill-async, which uses an async db driver that is not being being maintained anymore. Quill's creator Flavio Brasil suggested we could write a new async driver. That's how NDBC started.

Non-Blocking Database Connectivity (NDBC) is a fully async alternative to JDBC. It's architecture was designed to provide a high performance, non-blocking connectivity to the database on top of Trane.io Futures and Netty 4.

// Create a Config with an Embedded Postgres
Config config = Config.create("io.trane.ndbc.postgres.netty4.DataSourceSupplier", "localhost", 0, "user")
                      .database("test_schema")
                      .password("test")
                      .embedded("io.trane.ndbc.postgres.embedded.EmbeddedSupplier");

// Create a DataSource
DataSource<PreparedStatement, Row> ds = DataSource.fromConfig(config);

// Define a timeout
Duration timeout = Duration.ofSeconds(10);

// Send a query to the db defining a timeout and receiving back a List
List<Row> rows = ds.query("SELECT 1 AS value").get(timeout);

// iterate over awesome strongly typed rows
rows.forEach(row -> System.out.println(row.getLong("value")));

More knowledge acquired:

Binary protocols
Netty 4
Weaknesses of the Java type system
Fully implementing functional structures

That's where I am at the moment. I share my attention between Quill and Ndbc, trying to make them work together.

Conclusion

This almost 10 years open source journey taught me something extremely valuable:

Open Source is about sharing knowledge. When I am solving an issue I am acquiring knowledge. When I send a pull request, I am spreading that knowledge. Knowledge brings more knowledge!

I strongly recommend you to become part of the open source community. It can be difficult, specially in the beginning, but everything you will learn will make you a better developer. I promise!

Plans for the future

The contributions I have in mind for the near future are:

Implementing array support in Finagle Postgres
Clojure wrapper for NDBC
ndbc-spring module
cli in Rust

Any suggestion? Interesting ideas? Let me know in the comments!

DEV Community: Juliano Alves

Sttp: An Extensible API Client

What's wrong with the previous client?

What is the plan?

Let's catch them all!

PokeRequest (Best. Name. Ever.)

PokeApiClient

Using the PokeApiClient

Summary

References

Shapeless: a real world use case

The Challenge

The Problem

A bit of shapeless

The Solution

Conclusion

Rust: Deserialize JSON with Serde

The Data Structure

The derive macro

Renaming fields

Parsing different objects

Full example

Quill SQL Server: Returning via OUTPUT

The returning feature

INSERT ... OUTPUT

Quill NDBC Postgres: A New Async Module

Motivation

NDBC: Non-blocking Database Connectivity

Quill + NDBC

Contributing to Quill, a Pairing Session

Getting started

Development plan

OutputClauseSupported and CanOutputClause

Adapting the SQL Idiom

Quill's Abstract Syntax Trees

Changing SqlIdiom

Asking for help

Stopping invalid actions

Changing SQLServerDialect

The Journey of an Open Source Developer

A long time ago in a galaxy far, far away...

Gamification + Deliberate Practice = Aprenda

Writing my own Gem

Working with Microsoft Tech

Selenia

CSharp.Fun

Ioget

There and Back Again

Conclusion

Plans for the future

The `returning` feature

`INSERT ... OUTPUT`

`OutputClauseSupported` and `CanOutputClause`

Changing `SqlIdiom`

Changing `SQLServerDialect`