About Our First Remote Pairing Open Source Blogging Session

Contributing to Quill, a Pairing Session As one of ’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? Quill 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 , meaning it’s mostly about and . Let’s walk through this. Add SQL Server Support for returning via OUTPUT quill-sql quill-jdbc Being a SQL Server feature, can be a good starting point. However, there aren’t any tests regarding returning. Still thinking about Sql Server specifically, it’s worth checking as well. There we can find something: SQLServerDialectSpec sqlserver/JdbcContextSpec in { inserted = testContext.run { qr4.insert(lift( ( ))).returningGenerated(_.i) } testContext.run(qr4.filter(_.i == lift(inserted))).head.i mustBe inserted } in { inserted = testContext.run { qr4Emb.insert(lift( ( ( )))).returningGenerated(_.emb.i) } testContext.run(qr4Emb.filter(_.emb.i == lift(inserted))).head.emb.i mustBe inserted } "Insert with returning with single column table" val TestEntity4 0 "Insert with returning with multiple columns and query embedded" val TestEntity4Emb EmbSingle 0 Let’s try to change these tests to use : .returning [error] .../quill/quill-jdbc/src/ /scala/io/getquill/context/jdbc/sqlserver/JdbcContextSpec.scala:57:49: The clause is not supported by the io.getquill.SQLServerDialect idiom. Use instead. [error] qr4.insert(lift(TestEntity4(0))).returning(_.i) test 'returning' 'returningGenerated' Maybe we can search for the error message. That takes us to : Parsing idiomReturnCapability { | => => c.fail( ) => c.fail( ) } match case ReturningMultipleFieldSupported ReturningClauseSupported case ReturningSingleFieldSupported s"The 'returning' clause is not supported by the idiom. Use 'returningGenerated' instead." ${currentIdiom.getOrElse("specified")} case ReturningNotSupported s"The 'returning' or 'returningGenerated' clauses are not supported by the idiom." ${currentIdiom.getOrElse("specified")} 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 can tell us something. The trait’s signature is: SQLServerDialect 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 , which has the definitions for all existing returning behaviours. ReturningCapability is returned by from trait and its descendants: ReturningCapability idiomReturningCapability Capabilities { : } { : = } { : = } ... 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 r @ ( (table: , ), alias, prop) => idiomReturningCapability { ( .byType[ ](prop).nonEmpty) => .withActionAlias( , r) => other => } r @ (action, alias, prop) => idiomReturningCapability { ( .byType[ ](prop).nonEmpty) => .withActionAlias( , r) => other => } case ReturningAction Insert Entity Nil 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 Entity SqlIdiom this case ReturningClauseSupported stmt"INSERT INTO RETURNING " ${table.token} ${defaultAutoGeneratedToken(prop.token)} ${returnListTokenizer.token(ExpandReturning(r)(this, strategy).map(_._1))} case stmt"INSERT INTO " ${table.token} ${defaultAutoGeneratedToken(prop.token)} case ReturningAction 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 Entity SqlIdiom this case ReturningClauseSupported stmt" RETURNING " ${action.token} ${returnListTokenizer.token(ExpandReturning(r)(this, strategy).map(_._1))} case stmt" " ${action.token} It starts to get complex. Before we carry on, we should summarise what we’ve learned so far: extends , in order to support ; SQLServerDialect CanReturnField returningGenerated only ensures this behaviour, defining that returns a ; CanReturnField idiomReturningCapability ReturningSingleFieldSupported is a , mother of all the return behaviours; ReturningSingleFieldSupported ReturningCapability verifies the type of the returning clause and ; Parsing fails the compilation if necessary decides how to generate the return statement after checking the current SqlIdiom 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, returns a : CanReturnField.idiomReturnCapability 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 is extended by as well: CanReturnClause MirrorSqlDialectWithReturnClause trait MirrorSqlDialectWithReturnClause extends SqlIdiom with QuestionMarkBindVariables with ConcatSupport with CanReturnClause Which is used for tests in , for and : SqlActionMacroSpec returning returningGenerated in testContext.withDialect( ) { ctx => ctx._ q = quote { qr1.insert(lift( ( , , L, ))).returning(_.l) } mirror = ctx.run(q) mirror.string mustEqual mirror.returningBehavior mustEqual } "returning clause - single" MirrorSqlDialectWithReturnClause import val TestEntity "s" 0 1 None val "INSERT INTO TestEntity (s,i,l,o) VALUES (?, ?, ?, ?) RETURNING l" ReturnRecord Now we know enough to set up a plan of action. Development plan - We need a new , exposed by a new via ; ReturningCapability Capabilities idiomReturningCapability - has to accommodate the new generating the clause; SqlIdiom ReturningCapability OUTPUT - needs to allow the new code to compile; Parsing - will extend the new SQLServerDialect 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 extending : MirrorSqlDialect CanOutputClause { (string: ) = string } trait MirrorSqlDialectWithOutputClause extends SqlIdiom with QuestionMarkBindVariables with ConcatSupport with CanOutputClause object MirrorSqlDialectWithOutputClause extends MirrorSqlDialectWithOutputClause override def prepareForProbing String So far so good… or maybe not. This change already breaks the code: [ ] /Users/juliano.alves/development/opensource/quill/quill-core/src/main/scala/io/getquill/norm/ExpandReturning.scala:23:11: match may be exhaustive. [ ] It would fail on the following input: OutputClauseSupported [ ] idiom.idiomReturningCapability match { [ ] ^ [ ] /Users/juliano.alves/development/opensource/quill/quill-core/src/main/scala/io/getquill/quotation/Parsing.scala:831:38: match may be exhaustive. [ ] It would fail on the following input: OutputClauseSupported [ ] def verifyAst(returnBody: Ast) = capability match { [ ] ^ [ ] /Users/juliano.alves/development/opensource/quill/quill-core/src/main/scala/io/getquill/quotation/Parsing.scala:875:7: match may be exhaustive. [ ] It would fail on the following input: OutputClauseSupported [ ] idiomReturnCapability match { [ ] ^ [ ] three errors found error not error error error error not error error error error not error error error error Let’s take care of first. The error happens because is not being taken in consideration during the pattern matching. We are basing the new implementation on , so it’s reasonable to simply mirror its behaviour: ExpandReturning OutputClauseSupported ReturningClauseSupported idiom.idiomReturningCapability { | => . => ... // line 23 match case ReturningClauseSupported OutputClauseSupported ReturnAction ReturnRecord case ReturningMultipleFieldSupported Similar changes in , for both errors: Parsing (returnBody: ) = capability { => => => returnBody { ... idiomReturnCapability { | | => => c.fail( ) => c.fail( ) } // line 831 def verifyAst Ast 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 match // line 875 match case ReturningMultipleFieldSupported ReturningClauseSupported OutputClauseSupported case ReturningSingleFieldSupported s"The 'returning' clause is not supported by the idiom. Use 'returningGenerated' instead." ${currentIdiom.getOrElse("specified")} case ReturningNotSupported s"The 'returning' or 'returningGenerated' clauses are not supported by the idiom." ${currentIdiom.getOrElse("specified")} There is something in this snippet that deserves attention, the being matched. It is defined in as well: idiomReturnCapability Parsing [getquill] : = { returnAfterInsertType = currentIdiom .toSeq .flatMap(_.members) .collect { ms: (ms.name.toString == ) => (ms.returnType) } .headOption .flatten returnAfterInsertType { (returnType) (returnType =:= typeOf[ ]) => (returnType) (returnType =:= typeOf[ ]) => (returnType) (returnType =:= typeOf[ ]) => (returnType) (returnType =:= typeOf[ ]) => other => } } private def idiomReturnCapability ReturningCapability val case MethodSymbol if "idiomReturningCapability" Some match case Some if ReturningClauseSupported ReturningClauseSupported case Some if ReturningSingleFieldSupported ReturningSingleFieldSupported case Some if ReturningMultipleFieldSupported ReturningMultipleFieldSupported case Some if 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 ReturningSingleFieldSupported Long story short, has the type information about being used by the idiom. Then, the pattern matching returns the corresponding of that type, or a otherwise. So has to be included as an option: returnAfterInsertType ReturningCapability object ReturningSingleFieldSupported OutputClauseSupported returnAfterInsertType { (returnType) (returnType =:= typeOf[ ]) => (returnType) (returnType =:= typeOf[ ]) => ... match case Some if ReturningClauseSupported ReturningClauseSupported case Some if 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 but generating an clause: returning clause - single OUTPUT in testContext.withDialect( ) { ctx => ctx._ q = quote { qr1.insert(lift( ( , , L, ))).returning(_.l) } mirror = ctx.run(q) mirror.string mustEqual mirror.returningBehavior mustEqual } "output clause - single" MirrorSqlDialectWithOutputClause import val TestEntity "s" 0 1 None val "INSERT INTO TestEntity (s,i,l,o) OUTPUT INSERTED.l VALUES (?, ?, ?, ?)" ReturnRecord It fails, for obvious reasons. Time to work on and figure how to generate the expected result. Remember ? SqlIdiom those two pattern clauses related to idiomReturningCapability r @ ( (table: , ), alias, prop) => idiomReturningCapability { ... r @ (action, alias, prop) => idiomReturningCapability { ... case ReturningAction Insert Entity Nil match case ReturningAction match In order to identify which of the clauses we should look at, we will start dealing with Quill’s ASTs! ReturningActions Quill’s Abstract Syntax Trees This is my favourite part of the process, using the console to explore the . In the : AST sbt console scala> io.getquill._ scala> io.getquill.ast._ scala> val ctx = new SqlMirrorContext(MirrorSqlDialectWithOutputClause, Literal) scala> ctx._ scala> ( [ ]) > = { query[TestEntity] } scala> val q = quote { qr1.insert(lift(TestEntity( , , L, 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( , , L, scala.None).s: io.getquill.quotation.ScalarValueLifting[String,String]; val TestEntity.apply( , , L, scala.None).i: io.getquill.quotation.ScalarValueLifting[Int,Int]; val TestEntity.apply( , , L, scala.None).l: io.getquill.quotation.ScalarValueLifting[Long,Long]; val TestEntity.apply( , , L, scala.None).o: io.getquill.quotation.ScalarValueLifting[Option[Int],Option[Int]]}} = $anon$ b import import import case class TestEntity s: , : , : , : String i Int l Long o Option Int scala val qr1 quote "s" 0 1 "s" 0 1 "s" 0 1 "s" 0 1 "s" 0 1 1@ 374e427 To make visualisation of the AST easy, Quill integrates the awesome : PPrint module scala> pprint.pprintln(q.ast, ) ( ( ( , ()), ( ( ( ), ( ( ), ), ( , , ( ))), ( ( ), ( ( ), ), ( , , ( ))), ( ( ), ( ( ), ), ( , L, ( ))), ( ( ), ( ( ), ), ( , , ( ))) ) ), ( ), ( ( ), ) ) 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 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 Assignment Ident "v" Property Ident "v" "l" ScalarValueLift "$line9.$read.$iw.$iw.$iw.$iw.$iw.$iw.TestEntity.apply(\"s\", 0, 1L, scala.None).l" 1 MirrorEncoder 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 Ident "x1" Property Ident "x1" "l" Apparently we are dealing with the second of those two cases. Let’s double check: scala> q.ast { ( (entity: , ), _, prop) => (action, alias, prop) => } res18: = second match case ReturningAction Insert Entity Nil "first" case ReturningAction "second" Boolean Changing SqlIdiom Following the same approach as , but using instead of , we have: ReturningClauseSupported OUTPUT RETURNING r @ (action, alias, prop) => idiomReturningCapability { ... => => case ReturningAction match case ReturningClauseSupported stmt" RETURNING " ${action.token} ${returnListTokenizer.token(ExpandReturning(r)(this, strategy).map(_._1))} case OutputClauseSupported stmt" OUTPUT " ${action.token} ${returnListTokenizer.token(ExpandReturning(r)(this, strategy).map(_._1))} That change generates: scala> ctx.run(q).string : : (s,i,l,o) (?, ?, ?, ?) l 23 INSERT INTO TestEntity VALUES OUTPUT Okay, becomes , so it’s necessary to extract some information from action in order to generate the insert clause. Quill : ${action.token} INSERT INTO TestEntity (s,i,l,o) VALUES (?, ?, ?, ?) already does that (entity: , assignments) => table = insertEntityTokenizer.token(entity) columns = assignments.map(_.property.token) values = assignments.map(_.value) ).getOrElse( )} (${columns.mkStmt( )}) (${values.map(scopedTokenizer(_)).mkStmt( )}) // line 432 case Insert Entity val val val stmt"INSERT " $table ${actionAlias.map(alias => stmt" AS ${alias.token} stmt"" "," VALUES ", " " Combining this code with our knowledge about the existing clause to generate the clause, we have: RETURNING OUTPUT => action { (entity: , assignments) => table = insertEntityTokenizer.token(entity) columns = assignments.map(_.property.token) values = assignments.map(_.value) ).getOrElse( )} (${columns.mkStmt( )}) ${returnListTokenizer.token( (r)( , strategy).map(_._1))} (${values.map(scopedTokenizer(_)).mkStmt( )}) ast can be translated to sql: '$other' case OutputClauseSupported match case Insert Entity val val val stmt"INSERT " $table ${actionAlias.map(alias => stmt" AS ${alias.token} stmt"" "," OUTPUT ExpandReturning this VALUES ", " " case other => fail(s" Action 't ") } Back to : sbt console scala> ctx.run(q).string : : (s,i,l,o) l (?, ?, ?, ?) 20 INSERT INTO TestEntity OUTPUT VALUES Almost there! We now have to find a way to include . before every single element of . is the object handling that: INSERTED returnListTokenizer ExpandReturning (returning: )(idiom: , naming: ): [( , )] = { (_, alias, properties) = returning dePropertized = (properties) { `alias` => (alias.name) } ... def apply ReturningAction Idiom NamingStrategy List Ast Statement val ReturningAction val Transform case ExternalIdent 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 . I actually before ’s review, but it hadn’t used the code already in place to solve that problem - and I knew that. Living and learning! And that’s what I did came up with a solution deusaquilus (returning: , renameAlias: [ ] = )(idiom: , naming: ): [( , )] = { (_, alias, properties) = returning dePropertized = renameAlias { (newName) => (properties, alias -> (newName)) => (properties, alias -> (alias.name)) } ... def apply ReturningAction Option String None Idiom NamingStrategy List Ast Statement val ReturningAction val match case Some BetaReduction Ident case None BetaReduction ExternalIdent My knowledge regarding is limited, so let’s trust deusaquilus advice here. Let’s make use of the new resource in , but we should extract the duplicated code first: BetaReduction SqlIdiom (entity: , assignments) => (table, columns, values) = insertInfo(insertEntityTokenizer, entity, assignments) ).getOrElse( )} (${columns.mkStmt( )}) (${values.map(scopedTokenizer(_)).mkStmt( )}) case Insert Entity val stmt"INSERT " $table ${actionAlias.map(alias => stmt" AS ${alias.token} stmt"" "," VALUES ", " " ... 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 => action { (entity: , assignments) => (table, columns, values) = insertInfo(insertEntityTokenizer, entity, assignments) ).getOrElse( )} (${columns.mkStmt( )}) ${returnListTokenizer.token( (r, ( ))( , strategy).map(_._1))} (${values.map(scopedTokenizer(_)).mkStmt( )}) ast can be translated to sql: '$other' case OutputClauseSupported match case Insert Entity val stmt"INSERT " $table ${actionAlias.map(alias => stmt" AS ${alias.token} stmt"" "," OUTPUT ExpandReturning Some "INSERTED" this VALUES ", " " case other => fail(s" Action 't ") } Now, in the console we have: scala> ctx.run(q).string : : (s,i,l,o) .l (?, ?, ?, ?) 20 INSERT INTO TestEntity OUTPUT INSERTED VALUES And the tests are green again. Let’s cover the other possibilities for : returning in testContext.withDialect( ) { ctx => ctx._ q = quote { qr1.insert(lift( ( , , L, ))).returning(r => (r.i, r.l)) } mirror = ctx.run(q) mirror.string mustEqual mirror.returningBehavior mustEqual } in testContext.withDialect( ) { ctx => ctx._ q = quote { qr1.insert(lift( ( , , L, ))).returning(r => (r.i, r.l + )) } mirror = ctx.run(q) mirror.string mustEqual } in testContext.withDialect( ) { ctx => ctx._ q = quote { qr1.insert(lift( ( , , L, ))).returning(r => r) } mirror = ctx.run(q) mirror.string mustEqual mirror.returningBehavior mustEqual } - { in testContext.withDialect( ) { ctx => ctx._ q = quote { qr1Emb.insert(lift( ( ( , ), L, ))).returning(_.emb.i) } mirror = ctx.run(q) mirror.string mustEqual mirror.returningBehavior mustEqual } in testContext.withDialect( ) { ctx => ctx._ q = quote { qr1Emb.insert(lift( ( ( , ), L, ))).returning(r => (r.emb.i, r.emb.s)) } mirror = ctx.run(q) mirror.string mustEqual mirror.returningBehavior mustEqual } } "output clause - multi" MirrorSqlDialectWithOutputClause import val TestEntity "s" 0 1 None val "INSERT INTO TestEntity (s,i,l,o) OUTPUT INSERTED.i, INSERTED.l VALUES (?, ?, ?, ?)" ReturnRecord "output clause - operation" MirrorSqlDialectWithOutputClause import val TestEntity "s" 0 1 None 1 val "INSERT INTO TestEntity (s,i,l,o) OUTPUT INSERTED.i, INSERTED.l + 1 VALUES (?, ?, ?, ?)" "output clause - record" MirrorSqlDialectWithOutputClause import val TestEntity "s" 0 1 None val "INSERT INTO TestEntity (s,i,l,o) OUTPUT INSERTED.s, INSERTED.i, INSERTED.l, INSERTED.o VALUES (?, ?, ?, ?)" ReturnRecord "output clause - embedded" "embedded property" MirrorSqlDialectWithOutputClause import val TestEntityEmb Emb "s" 0 1 None val "INSERT INTO TestEntity (s,i,l,o) OUTPUT INSERTED.i VALUES (?, ?, ?, ?)" ReturnRecord "two embedded properties" MirrorSqlDialectWithOutputClause import val TestEntityEmb Emb "s" 0 1 None val "INSERT INTO TestEntity (s,i,l,o) OUTPUT INSERTED.i, INSERTED.s VALUES (?, ?, ?, ?)" ReturnRecord Great, everything works! Now we have to handle a fundamental difference between and . OUTPUT RETURNING Stopping invalid actions The next test is , with this code: "with returning clause - query" q = quote { qr1 .insert(lift( ( , , L, ))) .returning(r => (query[ ].map(d => d.i).max)) } val TestEntity "s" 0 1 None Dummy Unlike Postgres, , meaning that our implementation has to fail the compilation when a query is present. Let’s start with a test: SQL Server doesn’t allow an output clause in a select statement in testContext.withDialect( ) { ctx => mustNot compile } "output clause - should fail on query" MirrorSqlDialectWithOutputClause "" "import ctx._; quote { qr4.insert(lift(TestEntity4(1L))).returning(r => query[TestEntity4].filter(t => t.i == r.i)) }" "" Recapping the beginning of our session: verifies the type of the returning clause and can fail the compilation if necessary Parsing That is exactly what we need: (returnBody: ) = capability { => => => returnBody { ... // line 831 def verifyAst Ast 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 match In order to figure the right moment to stop the compilation, we need to understand that AST better. Back to the console: q = quote { qr4.insert(lift( ( L))).returning(r => query[ ].filter(t => t.i == r.i)) } scala> pprint.pprintln(q.ast, ) ( ( ( , ()), ( ( ( ), ( ( ), ), ( , L, ( )))) ), ( ), ( ( , ()), ( ), ( ( ( ), ), ==, ( ( ), ))) ) val TestEntity4 1 TestEntity4 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" 1 MirrorEncoder Ident "r" Filter Entity "TestEntity4" List Ident "t" BinaryOperation Property Ident "t" "i" Property Ident "r" "i" The last line is the we are looking for. Having instead of it generates: returnBody .map .filter q2 = quote { qr4.insert(lift( ( L))).returning(r => query[ ]).map(t => t.i) } scala> pprint.pprintln(q2.ast, ) ( ... ( ( , ()), ( ), ( ( ), )) ) val TestEntity4 1 TestEntity4 200 Returning Map Entity "TestEntity4" List Ident "x1" Property Ident "x1" "i" None of these can be allowed to compile. Many similar operations extend , so let’s consider that to define a limitation in : Query Parsing { (returnBody: ) = capability { => returnBody { _: => c.fail( ) _ => } => ... implicit ( ) class InsertReturnCapabilityExtension capability: ReturningCapability def verifyAst Ast match case OutputClauseSupported match case Query s" not allow queries in 'returning' clauses." ${currentIdiom.map(n => s"The dialect $n does").getOrElse("Unspecified dialects do")} case case ReturningClauseSupported After this change, the tests are happy and green again. , but the good news are that we need a quite similar change, which is trivial at this point: We need the same tests for .returningGenerated r @ ( (table: , ), alias, prop) => idiomReturningCapability { ... => => ... // line 450 case ReturningAction Insert Entity Nil match case ReturningClauseSupported stmt"INSERT INTO RETURNING " ${table.token} ${defaultAutoGeneratedToken(prop.token)} ${returnListTokenizer.token(ExpandReturning(r)(this, strategy).map(_._1))} case OutputClauseSupported stmt"INSERT INTO OUTPUT " ${table.token} ${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 from now on: CanOutputClause { 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 in { testContext.run(qr1.delete) inserted = testContext.run { qr1.insert(lift( ( , , L, ( )))).returning(r => (r.i, r.s, r.o)) } ( , , ( )) mustBe inserted } in { testContext.run(qr1.delete) inserted = testContext.run { qr1.insert(lift( ( , , L, ( )))).returning(r => (r.i + , r.s, r.o.map(_ + ))) } ( + , , ( + )) mustBe inserted } in { testContext.run(qr1Emb.delete) testContext.run(qr1Emb.insert(lift( ( ( , ), L, ( ))))) inserted = testContext.run { qr1Emb.insert(lift( ( ( , ), L, ( )))).returning(r => (r.emb.i, r.o)) } ( , ( )) mustBe inserted } in { testContext.run(qr1.delete) inserted = testContext.run { qr1.insert(lift( ( , , L, ( )))).returning(r => (r.i, r.s, r.o)) } ( , , ( )) mustBe inserted } "Insert with returning with multiple columns" val TestEntity "foo" 1 18 Some 123 1 "foo" Some 123 "Insert with returning with multiple columns and operations" val TestEntity "foo" 1 18 Some 123 100 100 1 100 "foo" Some 123 100 "Insert with returning with multiple columns and query embedded" TestEntityEmb Emb "one" 1 18 Some 123 val TestEntityEmb Emb "two" 2 18 Some 123 2 Some 123 "Insert with returning with multiple columns - case class" case ( ) class Return id: , str: , opt: [ ] Int String Option Int val TestEntity "foo" 1 18 Some 123 Return Return 1 "foo" Some 123 What can go wrong? [info] - returning multiple columns *** *** [info] com.microsoft.sqlserver.jdbc. : result set was generated update. [info] at com.microsoft.sqlserver.jdbc. .makeFromDriverError( .java: ) [info] at com.microsoft.sqlserver.jdbc. .doExecutePreparedStatement( .java: ) [info] at com.microsoft.sqlserver.jdbc. $ .doExecute( .java: ) [info] at com.microsoft.sqlserver.jdbc. .execute( .java: ) [info] at com.microsoft.sqlserver.jdbc. .executeCommand( .java: ) [info] at com.microsoft.sqlserver.jdbc. .executeCommand( .java: ) [info] at com.microsoft.sqlserver.jdbc. .executeStatement( .java: ) [info] at com.microsoft.sqlserver.jdbc. .executeUpdate( .java: ) [info] at com.zaxxer.hikari.pool. .executeUpdate( .java: ) [info] at com.zaxxer.hikari.pool. .executeUpdate( .java) Insert with with FAILED SQLServerException A for SQLServerException SQLServerException 227 SQLServerPreparedStatement SQLServerPreparedStatement 592 SQLServerPreparedStatement PrepStmtExecCmd SQLServerPreparedStatement 508 TDSCommand IOBuffer 7233 SQLServerConnection SQLServerConnection 2869 SQLServerStatement SQLServerStatement 243 SQLServerStatement SQLServerStatement 218 SQLServerPreparedStatement SQLServerPreparedStatement 461 ProxyPreparedStatement ProxyPreparedStatement 61 HikariProxyPreparedStatement HikariProxyPreparedStatement This is an interesting error, which actually happens in as well. Luckily, : ProductJdbcSpec it has been reported in the issue Also note that SQL Server requires instead of a combination of and prep.executeQuery() preparedStatement.executeUpdate() preparedStatement.getGeneratedKeys() Good to know. is the responsible for handling jdbc connections. According to the description above, the method we are looking for is : JdbcContextBase executeActionReturning [ ](sql: , prepare: = identityPrepare, extractor: [ ], returningBehavior: ): [ ] = withConnectionWrapped { conn => (params, ps) = prepare(prepareWithReturning(sql, conn, returningBehavior)) logger.logQuery(sql, params) ps.executeUpdate() handleSingleResult(extractResult(ps.getGeneratedKeys, extractor)) } def executeActionReturning O String Prepare Extractor O ReturnAction Result O val extends , defining as : SqlServerJdbcContextBase JdbcContextBase SQLServerDialect idiom { idiom = } [ <: ] [ , ] trait SqlServerJdbcContextBase N NamingStrategy extends JdbcContextBase SQLServerDialect N with BooleanObjectEncoding with UUIDStringEncoding val SQLServerDialect SQL Server demands an exceptional behaviour regarding , so we need to override : preparedStatement executeActionReturning { idiom = [ ](sql: , prepare: = identityPrepare, extractor: [ ], returningBehavior: ): [ ] = withConnectionWrapped { conn => (params, ps) = prepare(prepareWithReturning(sql, conn, returningBehavior)) logger.logQuery(sql, params) handleSingleResult(extractResult(ps.executeQuery, extractor)) } } [ <: ] [ , ] trait SqlServerJdbcContextBase N NamingStrategy extends JdbcContextBase SQLServerDialect N with BooleanObjectEncoding with UUIDStringEncoding val SQLServerDialect override def executeActionReturning O String Prepare Extractor O ReturnAction Result O val Obviously we have to , but this post is already long enough to paste those tests here. With that we finish our contribution to Quill. add more tests to SQLServerDialectSpec That was fun, maybe you should be the pilot next time! Let me know when we should have the next session in the comments!