46 Database queries with Quill

 

After reading this lesson, you will be able to:

  • Connect to a database and execute SQL queries asynchronously using the Quill library
  • Match database tables to case classes
  • Write code to generate and run queries to select, insert, update and delete records

After learning about the type Future, you’ll discover how to connect and asynchronously perform queries to a database using a popular library called Quill. In particular, you’ll see how to start a test PostgreSQL database instance and connect to it. You’ll see how to execute SQL queries. You’ll master how to define case classes that correspond to its tables and write code that generates SQL queries to retrieve, insert, update, and delete its records. In the capstone, you’ll use a database to store the questions and user information for a quiz application.

Consider this

Suppose you are developing software to manage orders and bookings in a restaurant that uses a database to read and write data. How would you connect to it, retrieve and save records?

46.1  Project Setup

Rather than creating an sbt project from scratch, let’s download a base project as your starting point: the following subsections provide instructions on how to do this.

46.1.1    Download the base project

First, you need to create an sbt project and download its external dependencies. Navigate to an empty folder and use the git command to download the base project for this lesson:

46.1.2    Starting the PostgreSQL server

46.2  Connecting to the PostgreSQL Server

46.3  Executing queries

46.4  Running generated queries

46.5  Summary

46.6  Answers to Quick Checks