DEV Community

Cover image for Quill SQL Server: Returning via OUTPUT
Juliano Alves
Juliano Alves

Posted on • Edited on • Originally published at juliano-alves.com

2 2

Quill SQL Server: Returning via OUTPUT

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
Enter fullscreen mode Exit fullscreen mode

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 (?, ?, ?)
Enter fullscreen mode Exit fullscreen mode

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 (?, ?, ?)
Enter fullscreen mode Exit fullscreen mode

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

Image of Timescale

🚀 pgai Vectorizer: SQLAlchemy and LiteLLM Make Vector Search Simple

We built pgai Vectorizer to simplify embedding management for AI applications—without needing a separate database or complex infrastructure. Since launch, developers have created over 3,000 vectorizers on Timescale Cloud, with many more self-hosted.

Read more

Top comments (0)

Postmark Image

Speedy emails, satisfied customers

Are delayed transactional emails costing you user satisfaction? Postmark delivers your emails almost instantly, keeping your customers happy and connected.

Sign up

👋 Kindness is contagious

Discover a treasure trove of wisdom within this insightful piece, highly respected in the nurturing DEV Community enviroment. Developers, whether novice or expert, are encouraged to participate and add to our shared knowledge basin.

A simple "thank you" can illuminate someone's day. Express your appreciation in the comments section!

On DEV, sharing ideas smoothens our journey and strengthens our community ties. Learn something useful? Offering a quick thanks to the author is deeply appreciated.

Okay