Skip to content

More complex SELECTs

We've seen how to create simple queries from our schema. Beam supports other clauses in the SQL SELECT statement.

For these examples, we're going to use the beam-sqlite backend with the provided sample Chinook database. The Chinook database schema is modeled after a fictional record store. It provides several tables containing information on the music as well as the billing operations. Thus, it provides a good 'real-world' demonstration of beam's capabalities.

First, create a SQLite database from the included example.

$ sqlite3 chinook.db < beam-sqlite/examples/chinook.sql

Now, load the chinook database schema in GHCi.

Prelude Database.Beam.Sqlite> :load beam-sqlite/examples/Chinook/Schema.hs
Prelude Chinook.Schema> chinook <- open "chinook.db"

One more thing, before we see more complex examples, let's define a quick utility function.

Prelude Chinook.Schema> let withConnectionTutorial = runBeamSqliteDebug putStrLn chinook

Let's test it!

We can run all our queries like:

withConnectionTutorial $ runSelectReturningList $ select $ <query>

Let's select all the tracks.

withConnectionTutorial $ runSelectReturningList $ select $ all_ (track chinookDb)

For the rest of the guide, we will also show the generated SQL code for both sqlite and postgres.

all_ (track chinookDb)

SELECT "t0"."TrackId" AS "res0",
       "t0"."Name" AS "res1",
       "t0"."AlbumId" AS "res2",
       "t0"."MediaTypeId" AS "res3",
       "t0"."GenreId" AS "res4",
       "t0"."Composer" AS "res5",
       "t0"."Milliseconds" AS "res6",
       "t0"."Bytes" AS "res7",
       "t0"."UnitPrice" AS "res8"
FROM "Track" AS "t0"

SELECT "t0"."TrackId" AS "res0",
       "t0"."Name" AS "res1",
       "t0"."AlbumId" AS "res2",
       "t0"."MediaTypeId" AS "res3",
       "t0"."GenreId" AS "res4",
       "t0"."Composer" AS "res5",
       "t0"."Milliseconds" AS "res6",
       "t0"."Bytes" AS "res7",
       "t0"."UnitPrice" AS "res8"
FROM "Track" AS "t0";

-- With values: []

Returning a subset of columns

Oftentimes we only care about the value of a few columns, rather than every column in the table. Beam fully supports taking projections of tables. As said before, Q is a Monad. Thus, we can use monadic do notation to only select a certain subset of columns. For example, to fetch only the name of every track:

do tracks <- all_ (track chinookDb)
   pure (trackName tracks)

SELECT "t0"."Name" AS "res0"
FROM "Track" AS "t0"

SELECT "t0"."Name" AS "res0"
FROM "Track" AS "t0";

-- With values: []

Notice that beam has properly written the SELECT projection to only include the Name field.

We can also return multiple fields, by returning a tuple. Perhaps we would also like to know the composer:

do tracks <- all_ (track chinookDb)
   pure (trackName tracks, trackComposer tracks)

SELECT "t0"."Name" AS "res0",
       "t0"."Composer" AS "res1"
FROM "Track" AS "t0"

SELECT "t0"."Name" AS "res0",
       "t0"."Composer" AS "res1"
FROM "Track" AS "t0";

-- With values: []

You can also return arbitrary expressions in the projection. For example to return the name, composer, unit price, and length in seconds (where the database stores it in milliseconds):

do tracks <- all_ (track chinookDb)
   pure (trackName tracks, trackComposer tracks, trackMilliseconds tracks `div_` 1000)

SELECT "t0"."Name" AS "res0",
       "t0"."Composer" AS "res1",
       ("t0"."Milliseconds") / (1000) AS "res2"
FROM "Track" AS "t0"

SELECT "t0"."Name" AS "res0",
       "t0"."Composer" AS "res1",
       ("t0"."Milliseconds") / (?) AS "res2"
FROM "Track" AS "t0";

-- With values: [SQLInteger 1000]

Beam includes instances to support returning up to 8-tuples. To return more, feel free to nest tuples. As an example, we can write the above query as

do tracks <- all_ (track chinookDb)
   pure ((trackName tracks, trackComposer tracks), trackMilliseconds tracks `div_` 1000)

SELECT "t0"."Name" AS "res0",
       "t0"."Composer" AS "res1",
       ("t0"."Milliseconds") / (1000) AS "res2"
FROM "Track" AS "t0"

SELECT "t0"."Name" AS "res0",
       "t0"."Composer" AS "res1",
       ("t0"."Milliseconds") / (?) AS "res2"
FROM "Track" AS "t0";

-- With values: [SQLInteger 1000]

Notice that the nesting of tuples does not affect the generated SQL projection. The tuple structure is only used when reading back the row from the database.

The Q monad is perfectly rule-abiding, which means it also implements a valid Functor instance. Thus the above could more easily be written.

fmap (\tracks -> (trackName tracks, trackComposer tracks, trackMilliseconds tracks `div_` 1000)) $
all_ (track chinookDb)

SELECT "t0"."Name" AS "res0",
       "t0"."Composer" AS "res1",
       ("t0"."Milliseconds") / (1000) AS "res2"
FROM "Track" AS "t0"

SELECT "t0"."Name" AS "res0",
       "t0"."Composer" AS "res1",
       ("t0"."Milliseconds") / (?) AS "res2"
FROM "Track" AS "t0";

-- With values: [SQLInteger 1000]

WHERE clause

We've seen how to use all_ to select all rows of a table. Sometimes, you would like to filter results based on the result of some condition. For example, perhaps you would like to fetch all customers whose names start with "Jo". We can filter over results using the filter_ function.

filter_ (\customer -> customerFirstName customer `like_` "Jo%") $
all_ (customer chinookDb)

SELECT "t0"."CustomerId" AS "res0",
       "t0"."FirstName" AS "res1",
       "t0"."LastName" AS "res2",
       "t0"."Company" AS "res3",
       "t0"."Address" AS "res4",
       "t0"."City" AS "res5",
       "t0"."State" AS "res6",
       "t0"."Country" AS "res7",
       "t0"."PostalCode" AS "res8",
       "t0"."Phone" AS "res9",
       "t0"."Fax" AS "res10",
       "t0"."Email" AS "res11",
       "t0"."SupportRepId" AS "res12"
FROM "Customer" AS "t0"
WHERE ("t0"."FirstName") LIKE ('Jo%')

SELECT "t0"."CustomerId" AS "res0",
       "t0"."FirstName" AS "res1",
       "t0"."LastName" AS "res2",
       "t0"."Company" AS "res3",
       "t0"."Address" AS "res4",
       "t0"."City" AS "res5",
       "t0"."State" AS "res6",
       "t0"."Country" AS "res7",
       "t0"."PostalCode" AS "res8",
       "t0"."Phone" AS "res9",
       "t0"."Fax" AS "res10",
       "t0"."Email" AS "res11",
       "t0"."SupportRepId" AS "res12"
FROM "Customer" AS "t0"
WHERE ("t0"."FirstName") LIKE (?);

-- With values: [SQLText "Jo%"]

You can use (&&.) and (||.) to combine boolean expressions, as you'd expect. For example, to select all customers whose first name begins with "Jo", last name begins with "S", and who live in either California or Washington:

filter_ (\customer -> ((customerFirstName customer `like_` "Jo%") &&. (customerLastName customer `like_` "S%")) &&.
                      (addressState (customerAddress customer) ==. just_ "CA" ||. addressState (customerAddress customer) ==. just_ "WA")) $
        all_ (customer chinookDb)

SELECT "t0"."CustomerId" AS "res0",
       "t0"."FirstName" AS "res1",
       "t0"."LastName" AS "res2",
       "t0"."Company" AS "res3",
       "t0"."Address" AS "res4",
       "t0"."City" AS "res5",
       "t0"."State" AS "res6",
       "t0"."Country" AS "res7",
       "t0"."PostalCode" AS "res8",
       "t0"."Phone" AS "res9",
       "t0"."Fax" AS "res10",
       "t0"."Email" AS "res11",
       "t0"."SupportRepId" AS "res12"
FROM "Customer" AS "t0"
WHERE ((("t0"."FirstName") LIKE ('Jo%'))
       AND (("t0"."LastName") LIKE ('S%')))
  AND ((("t0"."State") IS NOT DISTINCT
        FROM ('CA'))
       OR (("t0"."State") IS NOT DISTINCT
           FROM ('WA')))

SELECT "t0"."CustomerId" AS "res0",
       "t0"."FirstName" AS "res1",
       "t0"."LastName" AS "res2",
       "t0"."Company" AS "res3",
       "t0"."Address" AS "res4",
       "t0"."City" AS "res5",
       "t0"."State" AS "res6",
       "t0"."Country" AS "res7",
       "t0"."PostalCode" AS "res8",
       "t0"."Phone" AS "res9",
       "t0"."Fax" AS "res10",
       "t0"."Email" AS "res11",
       "t0"."SupportRepId" AS "res12"
FROM "Customer" AS "t0"
WHERE ((("t0"."FirstName") LIKE (?))
       AND (("t0"."LastName") LIKE (?)))
  AND ((CASE
            WHEN (("t0"."State") IS NULL)
                 AND ((?) IS NULL) THEN ?
            WHEN (("t0"."State") IS NULL)
                 OR ((?) IS NULL) THEN ?
            ELSE ("t0"."State")=(?)
        END)
       OR (CASE
               WHEN (("t0"."State") IS NULL)
                    AND ((?) IS NULL) THEN ?
               WHEN (("t0"."State") IS NULL)
                    OR ((?) IS NULL) THEN ?
               ELSE ("t0"."State")=(?)
           END));

-- With values: [SQLText "Jo%",SQLText "S%",SQLText "CA",SQLInteger 1,SQLText "CA",SQLInteger 0,SQLText "CA",SQLText "WA",SQLInteger 1,SQLText "WA",SQLInteger 0,SQLText "WA"]

Note

We had to use the just_ function above to compare addressState (customerAddress customer). This is because addressState (customerAddress customer) represents a nullable column which beam types as Maybe Text. Just as in Haskell, we need to explicitly unwrap the Maybe type. This is an example of beam offering stronger typing than SQL itself.

LIMIT/OFFSET support

The limit_ and offset_ functions can be used to truncate the result set at a certain length and fetch different portions of the result. They correspond to the LIMIT and OFFSET SQL constructs.

limit_ 10 $ offset_ 100 $
filter_ (\customer -> ((customerFirstName customer `like_` "Jo%") &&. (customerLastName customer `like_` "S%")) &&.
                      (addressState (customerAddress customer) ==. just_ "CA" ||. addressState (customerAddress customer) ==. just_ "WA")) $
        all_ (customer chinookDb)

SELECT "t0"."CustomerId" AS "res0",
       "t0"."FirstName" AS "res1",
       "t0"."LastName" AS "res2",
       "t0"."Company" AS "res3",
       "t0"."Address" AS "res4",
       "t0"."City" AS "res5",
       "t0"."State" AS "res6",
       "t0"."Country" AS "res7",
       "t0"."PostalCode" AS "res8",
       "t0"."Phone" AS "res9",
       "t0"."Fax" AS "res10",
       "t0"."Email" AS "res11",
       "t0"."SupportRepId" AS "res12"
FROM "Customer" AS "t0"
WHERE ((("t0"."FirstName") LIKE ('Jo%'))
       AND (("t0"."LastName") LIKE ('S%')))
  AND ((("t0"."State") IS NOT DISTINCT
        FROM ('CA'))
       OR (("t0"."State") IS NOT DISTINCT
           FROM ('WA')))
LIMIT 10
OFFSET 100

SELECT "t0"."CustomerId" AS "res0",
       "t0"."FirstName" AS "res1",
       "t0"."LastName" AS "res2",
       "t0"."Company" AS "res3",
       "t0"."Address" AS "res4",
       "t0"."City" AS "res5",
       "t0"."State" AS "res6",
       "t0"."Country" AS "res7",
       "t0"."PostalCode" AS "res8",
       "t0"."Phone" AS "res9",
       "t0"."Fax" AS "res10",
       "t0"."Email" AS "res11",
       "t0"."SupportRepId" AS "res12"
FROM "Customer" AS "t0"
WHERE ((("t0"."FirstName") LIKE (?))
       AND (("t0"."LastName") LIKE (?)))
  AND ((CASE
            WHEN (("t0"."State") IS NULL)
                 AND ((?) IS NULL) THEN ?
            WHEN (("t0"."State") IS NULL)
                 OR ((?) IS NULL) THEN ?
            ELSE ("t0"."State")=(?)
        END)
       OR (CASE
               WHEN (("t0"."State") IS NULL)
                    AND ((?) IS NULL) THEN ?
               WHEN (("t0"."State") IS NULL)
                    OR ((?) IS NULL) THEN ?
               ELSE ("t0"."State")=(?)
           END))
LIMIT 10
OFFSET 100;

-- With values: [SQLText "Jo%",SQLText "S%",SQLText "CA",SQLInteger 1,SQLText "CA",SQLInteger 0,SQLText "CA",SQLText "WA",SQLInteger 1,SQLText "WA",SQLInteger 0,SQLText "WA"]

Note

Nested limit_s and offset_s compose in the way you'd expect without generating extraneous subqueries.

Warning

Note that the order of the limit_ and offset_ functions matter. Offseting an already limited result is not the same as limiting an offseted result. For example, if you offset three rows into a limited set of five results, you will get at most two rows. On the other hand, if you offset three rows and then limit the result to the next five, you may get up to five. Beam will generate exactly the query you specify. Notice the difference below, where the order of the clauses made beam generate a query that returns no results.

offset_ 100 $ limit_ 10 $
filter_ (\customer -> ((customerFirstName customer `like_` "Jo%") &&. (customerLastName customer `like_` "S%")) &&.
                      (addressState (customerAddress customer) ==. just_ "CA" ||. addressState (customerAddress customer) ==. just_ "WA")) $
        all_ (customer chinookDb)

SELECT "t0"."CustomerId" AS "res0",
       "t0"."FirstName" AS "res1",
       "t0"."LastName" AS "res2",
       "t0"."Company" AS "res3",
       "t0"."Address" AS "res4",
       "t0"."City" AS "res5",
       "t0"."State" AS "res6",
       "t0"."Country" AS "res7",
       "t0"."PostalCode" AS "res8",
       "t0"."Phone" AS "res9",
       "t0"."Fax" AS "res10",
       "t0"."Email" AS "res11",
       "t0"."SupportRepId" AS "res12"
FROM "Customer" AS "t0"
WHERE ((("t0"."FirstName") LIKE ('Jo%'))
       AND (("t0"."LastName") LIKE ('S%')))
  AND ((("t0"."State") IS NOT DISTINCT
        FROM ('CA'))
       OR (("t0"."State") IS NOT DISTINCT
           FROM ('WA')))
LIMIT 0
OFFSET 100

SELECT "t0"."CustomerId" AS "res0",
       "t0"."FirstName" AS "res1",
       "t0"."LastName" AS "res2",
       "t0"."Company" AS "res3",
       "t0"."Address" AS "res4",
       "t0"."City" AS "res5",
       "t0"."State" AS "res6",
       "t0"."Country" AS "res7",
       "t0"."PostalCode" AS "res8",
       "t0"."Phone" AS "res9",
       "t0"."Fax" AS "res10",
       "t0"."Email" AS "res11",
       "t0"."SupportRepId" AS "res12"
FROM "Customer" AS "t0"
WHERE ((("t0"."FirstName") LIKE (?))
       AND (("t0"."LastName") LIKE (?)))
  AND ((CASE
            WHEN (("t0"."State") IS NULL)
                 AND ((?) IS NULL) THEN ?
            WHEN (("t0"."State") IS NULL)
                 OR ((?) IS NULL) THEN ?
            ELSE ("t0"."State")=(?)
        END)
       OR (CASE
               WHEN (("t0"."State") IS NULL)
                    AND ((?) IS NULL) THEN ?
               WHEN (("t0"."State") IS NULL)
                    OR ((?) IS NULL) THEN ?
               ELSE ("t0"."State")=(?)
           END))
LIMIT 0
OFFSET 100;

-- With values: [SQLText "Jo%",SQLText "S%",SQLText "CA",SQLInteger 1,SQLText "CA",SQLInteger 0,SQLText "CA",SQLText "WA",SQLInteger 1,SQLText "WA",SQLInteger 0,SQLText "WA"]

Backends often differ as to how they implement LIMIT/OFFSET. For example, SQLite requires that LIMIT always be given if an OFFSET is provided. Beam correctly handles this behavior.

offset_ 100 $
filter_ (\customer -> ((customerFirstName customer `like_` "Jo%") &&. (customerLastName customer `like_` "S%")) &&.
                      (addressState (customerAddress customer) ==. just_ "CA" ||. addressState (customerAddress customer) ==. just_ "WA")) $
        all_ (customer chinookDb)

SELECT "t0"."CustomerId" AS "res0",
       "t0"."FirstName" AS "res1",
       "t0"."LastName" AS "res2",
       "t0"."Company" AS "res3",
       "t0"."Address" AS "res4",
       "t0"."City" AS "res5",
       "t0"."State" AS "res6",
       "t0"."Country" AS "res7",
       "t0"."PostalCode" AS "res8",
       "t0"."Phone" AS "res9",
       "t0"."Fax" AS "res10",
       "t0"."Email" AS "res11",
       "t0"."SupportRepId" AS "res12"
FROM "Customer" AS "t0"
WHERE ((("t0"."FirstName") LIKE ('Jo%'))
       AND (("t0"."LastName") LIKE ('S%')))
  AND ((("t0"."State") IS NOT DISTINCT
        FROM ('CA'))
       OR (("t0"."State") IS NOT DISTINCT
           FROM ('WA')))
  OFFSET 100

SELECT "t0"."CustomerId" AS "res0",
       "t0"."FirstName" AS "res1",
       "t0"."LastName" AS "res2",
       "t0"."Company" AS "res3",
       "t0"."Address" AS "res4",
       "t0"."City" AS "res5",
       "t0"."State" AS "res6",
       "t0"."Country" AS "res7",
       "t0"."PostalCode" AS "res8",
       "t0"."Phone" AS "res9",
       "t0"."Fax" AS "res10",
       "t0"."Email" AS "res11",
       "t0"."SupportRepId" AS "res12"
FROM "Customer" AS "t0"
WHERE ((("t0"."FirstName") LIKE (?))
       AND (("t0"."LastName") LIKE (?)))
  AND ((CASE
            WHEN (("t0"."State") IS NULL)
                 AND ((?) IS NULL) THEN ?
            WHEN (("t0"."State") IS NULL)
                 OR ((?) IS NULL) THEN ?
            ELSE ("t0"."State")=(?)
        END)
       OR (CASE
               WHEN (("t0"."State") IS NULL)
                    AND ((?) IS NULL) THEN ?
               WHEN (("t0"."State") IS NULL)
                    OR ((?) IS NULL) THEN ?
               ELSE ("t0"."State")=(?)
           END))
LIMIT -1
OFFSET 100;

-- With values: [SQLText "Jo%",SQLText "S%",SQLText "CA",SQLInteger 1,SQLText "CA",SQLInteger 0,SQLText "CA",SQLText "WA",SQLInteger 1,SQLText "WA",SQLInteger 0,SQLText "WA"]

Notice that the SQLite query output has provided a dummy LIMIT -1 clause, while the Postgres query has not.

DISTINCT support

SQL can only return unique results from a query through the SELECT DISTINCT statement. Beam supports this using the nub_ command. For example, to get all the unique postal codes where our customers live.

nub_ $ fmap (addressPostalCode . customerAddress) $
  all_ (customer chinookDb)

SELECT DISTINCT "t0"."PostalCode" AS "res0"
FROM "Customer" AS "t0"

SELECT DISTINCT "t0"."PostalCode" AS "res0"
FROM "Customer" AS "t0";

-- With values: []

VALUES support

Sometimes you want to select from an explicit group of values. This is most helpful if you want to join against a set of values that isn't in the database.

For example, to get all customers we know to be in New York, California, and Texas.

do c <- all_ (customer chinookDb)
   st <- values_ [ "NY", "CA", "TX" ]
   guard_' (just_ st ==?. addressState (customerAddress c))
   pure c

SELECT "t0"."CustomerId" AS "res0",
       "t0"."FirstName" AS "res1",
       "t0"."LastName" AS "res2",
       "t0"."Company" AS "res3",
       "t0"."Address" AS "res4",
       "t0"."City" AS "res5",
       "t0"."State" AS "res6",
       "t0"."Country" AS "res7",
       "t0"."PostalCode" AS "res8",
       "t0"."Phone" AS "res9",
       "t0"."Fax" AS "res10",
       "t0"."Email" AS "res11",
       "t0"."SupportRepId" AS "res12"
FROM "Customer" AS "t0"
CROSS JOIN (
            VALUES ('NY'), ('CA'), ('TX')) AS "t1"("res0")
WHERE ("t1"."res0") = ("t0"."State")

Note

beam-sqlite does not support VALUES clauses anywhere within a query, but only within a common table expression.

Ad-hoc queries

Sometimes you want to quickly query a database without having to write out all the boilerplate. Beam supports this with a feature called 'ad-hoc' queries.

For example, to get all the names of customers, without having to use chinookDb at all.

To use this functionality, import Database.Beam.Query.Adhoc. Note that we use TypeApplications to give each field an explicit type. If you don't, GHC will often infer the type, but it's nice to be explicit.

-- import qualified Database.Beam.Query.Adhoc as Adhoc
do ( cId, firstName, lastName )
     <- Adhoc.table_ Nothing {- Schema Name -}
          "Customer"
          ( Adhoc.field_ @Int32 "CustomerId"
          , Adhoc.field_ @Text "FirstName"
          , Adhoc.field_ @Text "LastName" )
   guard_ (firstName `like_` "Jo%")
   return (cId, firstName, lastName)

SELECT "t0"."CustomerId" AS "res0",
       "t0"."FirstName" AS "res1",
       "t0"."LastName" AS "res2"
FROM "Customer" AS "t0"
WHERE ("t0"."FirstName") LIKE ('Jo%')

SELECT "t0"."CustomerId" AS "res0",
       "t0"."FirstName" AS "res1",
       "t0"."LastName" AS "res2"
FROM "Customer" AS "t0"
WHERE ("t0"."FirstName") LIKE (?);

-- With values: [SQLText "Jo%"]