Skip to content

Introduction

In the last part, we created a simple database with one table. We then used the beam interface to add entities into that table and query them. In this tutorial, we'll see how to update and delete rows and how to establish and query relations between tables.

We'll then delve deeper into queries to see how to create queries that return multiple tables.

Adding a related table

The users in our simple e-commerce application would like to ship orders to their homes. Let's build an addresses model to allow users to add home addresses to their profile. Our table will store United States addresses for now. An address in the United States consists of

  • an auto-incrementing primary key
  • one required house number and street line
  • an optional apartment/suite number line
  • a required city
  • a required 2-letter state/territory code
  • one 5-digit ZIP code

Let's build the AddressT table. AddressT will follow a similar formula to UserT, but it will contain a reference to a UserT table. ]

data AddressT f = Address
                { _addressId    :: C f Int
                , _addressLine1 :: C f Text
                , _addressLine2 :: C f (Maybe Text)
                , _addressCity  :: C f Text
                , _addressState :: C f Text
                , _addressZip   :: C f Text

                , _addressForUser :: PrimaryKey UserT f }
                  deriving Generic
type Address = AddressT Identity
deriving instance Show (PrimaryKey UserT Identity)
deriving instance Show Address

instance Table AddressT where
    data PrimaryKey AddressT f = AddressId (Columnar f Int) deriving Generic
    primaryKey = AddressId . _addressId
type AddressId = PrimaryKey AddressT Identity -- For convenience

instance Beamable AddressT
instance Beamable (PrimaryKey AddressT)

Tip

Above, we used the C constructor instead of Columnar for each column. C is a type synonym for Columnar, and some find it reduces the syntactic overhead of model declaration.

Notice that _addressForUser is declared as a PrimaryKey UserT f. This pulls in all the columns necessary for referencing a UserT 1. Later, we'll also see how beam can use the field to automatically create JOINs.

Notice also that _addressId corresponds to our auto-increminting primary key field. In general, beam doesn't care if the underlying field is assigned automatically, only about the type of final values of that field.

We have all the tables we need now, so let's go ahead and redefine our newest database type.

data ShoppingCartDb f = ShoppingCartDb
                      { _shoppingCartUsers         :: f (TableEntity UserT)
                      , _shoppingCartUserAddresses :: f (TableEntity AddressT) }
                        deriving Generic

instance Database be ShoppingCartDb

Modifying the default naming choices

In the last part of the tutorial, we let beam decide our field names for us. This is great for simple cases. However, sometimes you want more control over the naming options.

Note

Previous versions of this tutorial had instructions on changing the schema type of particular tables. This functionality has been moved from beam-core into the beam-migrate package. See the migrations guide for more information.

The defaultDbSettings function generates names using the Haskell record selector names 2. This function returns the DatabaseType parameterized over DatabaseEntity, which is a type that contains metadata about entity names. We can modify this description after it is created by using the withDbModification function. You can think of withDbModification as applying a transformation function to each name in our database.

Most of the time withDbModification needs a full description of the database names. However, most of the time we only want to rename certain columns or tables. We can use the dbModification value to construct a modification that doesn't change any names. We can then use the Haskell record update syntax to update field and column names. This is best illustrated by an example.

Recall our Haskell data types above.

data UserT f
    = User
    { _userEmail     :: Columnar f Text
    , _userFirstName :: Columnar f Text
    , _userLastName  :: Columnar f Text
    , _userPassword  :: Columnar f Text }
    deriving Generic

data AddressT f = Address
                { _addressId    :: C f Int
                , _addressLine1 :: C f Text
                , _addressLine2 :: C f (Maybe Text)
                , _addressCity  :: C f Text
                , _addressState :: C f Text
                , _addressZip   :: C f Text

                , _addressForUser :: PrimaryKey UserT f }
                  deriving Generic

data ShoppingCartDb f = ShoppingCartDb
                      { _shoppingCartUsers         :: f (TableEntity UserT)
                      , _shoppingCartUserAddresses :: f (TableEntity AddressT) }
                        deriving Generic

Now, let's say we want beam to use the name addresses to access the _shoppingCartUserAddresses table, and the names address1 and address2 to access _addressLine1 and _addressLine2 respectively.

shoppingCartDb :: DatabaseSettings be ShoppingCartDb
shoppingCartDb = defaultDbSettings `withDbModification`
                 dbModification {
                   _shoppingCartUserAddresses =
                     modifyTable (\_ -> "addresses") $
                     tableModification {
                       _addressLine1 = fieldNamed "address1",
                       _addressLine2 = fieldNamed "address2"
                     }
                 }

Above, we use dbModification to produce a default modification, then we override the _shoppingCartUserAddresses modification to change the addresses table. The modifyTable function takes a function to transform the default table name and a table modification. Like the dbModification value, tableModification produces a modification for each table field that does nothing.

We only override the _addressLine1 and _addressLine2 modifications with fieldNamed "address1" and fieldNamed "address2". Because tableModification produces a default modification, the other columns are kept at their default value.

If you didn't need to modify any of the table names, simply do not update any of the table fields. For example, to simply produce a database with the first table named users and the second named user_addresses, you can do

shoppingCartDb1 :: DatabaseSettings be ShoppingCartDb
shoppingCartDb1 = defaultDbSettings `withDbModification`
                  dbModification {
                    _shoppingCartUsers = modifyTable (\_ -> "users") tableModification,
                    _shoppingCartUserAddresses = modifyTable (\_ -> "user_addresses") tableModification
                  }

For the purposes of this tutorial, we'll stick with shoppingCartDb.

Easier queries with lenses

In the previous part, we accessed table columns by using regular Haskell record syntax. Sometimes, we would like to use the more convenient lens syntax to access columns. Of course, all of beam's definitions are compatible with the lens library -- that is to say, makeLenses will work just fine. However, beam's motivation is, in part, the avoidance of Template Haskell, and it would hardly be worth it if you had to include a Template Haskell splice just to have lenses for the models you declared TH free.

In reality, the lens library isn't required to construct valid lenses. Lenses are a plain old Haskell type.

We can use beam's Columnar mechanism to automatically derive lenses. The tableLenses function produces a table value where each column is given a type LensFor, which is a newtype wrapper over a correctly constructed, polymorphic Van Laarhoven lens.

We can bring these lenses into scope globally via a global pattern match against tableLenses. For example, to get lenses for each column of the AddressT and UserT table.

-- Add the following to the top of the file, for GHC >8.2
{-#  LANGUAGE ImpredicativeTypes #-}

Address (LensFor addressId)    (LensFor addressLine1)
        (LensFor addressLine2) (LensFor addressCity)
        (LensFor addressState) (LensFor addressZip)
        (UserId (LensFor addressForUserId)) =
        tableLenses

User (LensFor userEmail)    (LensFor userFirstName)
     (LensFor userLastName) (LensFor userPassword) =
     tableLenses

Note

The ImpredicativeTypes language extension is necessary for newer GHC to allow the polymorphically typed lenses to be introduced at the top-level. Older GHCs were more lenient.

As in tables, we can generate lenses for databases via the dbLenses function.

ShoppingCartDb (TableLens shoppingCartUsers)
               (TableLens shoppingCartUserAddresses) =
               dbLenses

We can ask GHCi for the type of a column lens.

And a table lens.

Warning

These lens generating functions are awesome but if you use them in a compiled Haskell module (rather than GHC), GHC may give you odd compile errors about ambiguous types. These occur due to what's known as the monomorphism restriction. You can turn it off using the NoMonomorphismRestriction extension.

The monomorphism restriction is part of the Haskell standard, but there has been talk about removing it in future language versions. Basically, it requires GHC to not automatically infer polymorphic types for global definitions. In this case though, polymorphic global definitions is exactly what we want.

Working with relations

Now, let's see how we can add related addresses to our database. We begin by opening up a connection for us to use in the rest of the tutorial.

First, let's open a new database and create the schema.

$ sqlite3 shoppingcart2.db
SQLite version 3.14.0 2016-07-26 15:17:14
Enter ".help" for usage hints.
sqlite> CREATE TABLE cart_users (email VARCHAR NOT NULL, first_name VARCHAR NOT NULL, last_name VARCHAR NOT NULL, password VARCHAR NOT NULL, PRIMARY KEY( email ));
sqlite> CREATE TABLE addresses ( id INT AUTO_INCREMENT PRIMARY KEY, address1 VARCHAR NOT NULL, address2 VARCHAR, city VARCHAR NOT NULL, state VARCHAR NOT NULL, zip VARCHAR NOT NULL, for_user__email VARCHAR NOT NULL );

Now, in GHCi, we can use sqlite-simple to get a handle to this database.

conn <- open "shoppingcart2.db"

Before we add addresses, we need to add some users that we can reference.

let james = User "james@example.com" "James" "Smith" "b4cc344d25a2efe540adbf2678e2304c"
    betty = User "betty@example.com" "Betty" "Jones" "82b054bd83ffad9b6cf8bdb98ce3cc2f"
    sam = User "sam@example.com" "Sam" "Taylor" "332532dcfaa1cbf61e2a266bd723612c"
runBeamSqliteDebug putStrLn conn $ runInsert $
  insert (_shoppingCartUsers shoppingCartDb) $
  insertValues [ james, betty, sam ]

Now that we have some User objects, we can create associated addresses. Notice that above, we used insertValues to insert concrete User rows. This worked because we could determine every field of User before insertion. Addresses however have a pesky auto-incrementing primary key field. We can get around this by inserting expressions instead of values. We can use default_ to stand for a value that the database needs to fill in. We can use val_ to lift a literal value into an expression.

With that in mind, let's give James one address, Betty two addresses, and Sam none.

let addresses = [ Address default_ (val_ "123 Little Street") (val_ Nothing) (val_ "Boston") (val_ "MA") (val_ "12345") (pk james)
                , Address default_ (val_ "222 Main Street") (val_ (Just "Ste 1")) (val_ "Houston") (val_ "TX") (val_ "8888") (pk betty)
                , Address default_ (val_ "9999 Residence Ave") (val_ Nothing) (val_ "Sugarland") (val_ "TX") (val_ "8989") (pk betty) ]

runBeamSqliteDebug putStrLn conn $ runInsert $
  insert (_shoppingCartUserAddresses shoppingCartDb) $
  insertExpressions addresses

Warning

Previous versions of beam used a type called Auto to insert values into default fields. It was found to be a leaky abstraction and removed in version 0.7.0.0. Current best practices are to use default_ instead.

Notice that we used the pk function to assign the reference to the UserT table. pk is a synonym of the primaryKey function from the Table type class. It should be clear what's going on, but if it's not, let's ask GHCi.

*NextSteps> pk (james :: User)
UserId "james@example.com"

If we query for all the addresses, we'll see that SQLite has assigned them an appropriate id.

First, let's use the new lenses we made. Make sure to import Lens.Micro or Control.Lens or whichever (van Laarhoven) lens module you prefer.

-- import Lens.Micro
-- import Control.Lens
addresses <- runBeamSqliteDebug putStrLn conn $
             runSelectReturningList $
             select (all_ (shoppingCartDb ^. shoppingCartUserAddresses))
mapM_ print addresses
SELECT "t0"."id" AS "res0",
       "t0"."address1" AS "res1",
       "t0"."address2" AS "res2",
       "t0"."city" AS "res3",
       "t0"."state" AS "res4",
       "t0"."zip" AS "res5",
       "t0"."for_user__email" AS "res6"
FROM "addresses" AS "t0";

-- With values: []
Address {_addressId = 1, _addressLine1 = "123 Little Street", _addressLine2 = Nothing, _addressCity = "Boston", _addressState = "MA", _addressZip = "12345", _addressForUser = UserId "james@example.com"}
Address {_addressId = 2, _addressLine1 = "222 Main Street", _addressLine2 = Just "Ste 1", _addressCity = "Houston", _addressState = "TX", _addressZip = "8888", _addressForUser = UserId "betty@example.com"}
Address {_addressId = 3, _addressLine1 = "9999 Residence Ave", _addressLine2 = Nothing, _addressCity = "Sugarland", _addressState = "TX", _addressZip = "8989", _addressForUser = UserId "betty@example.com"}

A note about queries

In the last tutorial, we saw how queries and list supported similar interfaces. Namely we saw how limit_ is like take, offset_ like drop, orderBy like an enhanced sortBy, and aggregate like an enhanced groupBy. These corresponded to the LIMIT, OFFSET, ORDER BY, and GROUP BY SQL constructs. The missing SQL operation in this list is the JOIN, which computes the cartesian product of two tables. In other words, a join between table A and table B results in a query of pairs (x, y) for every x in A and every y in B. SQL joins can result in two-way, three-way, four-way, etc. cartesian products.

Those familiar with lists in Haskell will note that there is an easy abstraction for taking n-ary cartesian products over lists: monads.

The list monad

We can use GHCi to see what we mean.

*NextSteps> do { x <- [1,2,3]; y <- [4,5,6]; return (x, y); }
[(1,4),(1,5),(1,6),(2,4),(2,5),(2,6),(3,4),(3,5),(3,6)]

We get the two-way cartesian product of [1,2,3] and [4,5,6]. We can make the product arbitrarily long.

*NextSteps> do { w <- [10, 20, 30]; x <- [1,2,3]; y <- [4,5,6]; z <- [100, 200, 1]; return (x, y, z, w); }
[(1,4,100,10),(1,4,200,10),(1,4,1,10),(1,5,100,10),(1,5,200,10),(1,5,1,10), ... ]

We can also use guard from Control.Monad to limit the combinations that the list monad puts together. For example, if we had the lists

let usersList = [(1, "james"), (2, "betty"), (3, "tom")]
    addressesList = [(1, "address1"), (1, "address2"), (3, "address3")]

We can use guard to return all pairs of elements from usersList and addressesList that matched on their first element. For example,

*NextSteps> do { user <- usersList; address <- addressesList; guard (fst user == fst address); return (user, address) }
[((1,"james"),(1,"address1")),((1,"james"),(1,"address2")),((3,"tom"),(3,"address3"))]

The query monad

As I claimed in the first tutorial, queries support many of the same interfaces and operations lists do. It follows that queries also expose a monadic interface.

For example, to retrieve every pair of user and address, we can write the following query:

allPairs <- runBeamSqliteDebug putStrLn conn $
            runSelectReturningList $ select $ do
              user <- all_ (shoppingCartDb ^. shoppingCartUsers)
              address <- all_ (shoppingCartDb ^. shoppingCartUserAddresses)
              return (user, address)

mapM_ print allPairs
SELECT "t0"."email" AS "res0",
       "t0"."first_name" AS "res1",
       "t0"."last_name" AS "res2",
       "t0"."password" AS "res3",
       "t1"."id" AS "res4",
       "t1"."address1" AS "res5",
       "t1"."address2" AS "res6",
       "t1"."city" AS "res7",
       "t1"."state" AS "res8",
       "t1"."zip" AS "res9",
       "t1"."for_user__email" AS "res10"
FROM "cart_users" AS "t0"
INNER JOIN "addresses" AS "t1";

-- With values: []
(User {_userEmail = "james@example.com", _userFirstName = "James", _userLastName = "Smith", _userPassword = "b4cc344d25a2efe540adbf2678e2304c"},Address {_addressId = 1, _addressLine1 = "123 Little Street", _addressLine2 = Nothing, _addressCity = "Boston", _addressState = "MA", _addressZip = "12345", _addressForUser = UserId "james@example.com"})
(User {_userEmail = "james@example.com", _userFirstName = "James", _userLastName = "Smith", _userPassword = "b4cc344d25a2efe540adbf2678e2304c"},Address {_addressId = 2, _addressLine1 = "222 Main Street", _addressLine2 = Just "Ste 1", _addressCity = "Houston", _addressState = "TX", _addressZip = "8888", _addressForUser = UserId "betty@example.com"})
(User {_userEmail = "james@example.com", _userFirstName = "James", _userLastName = "Smith", _userPassword = "b4cc344d25a2efe540adbf2678e2304c"},Address {_addressId = 3, _addressLine1 = "9999 Residence Ave", _addressLine2 = Nothing, _addressCity = "Sugarland", _addressState = "TX", _addressZip = "8989", _addressForUser = UserId "betty@example.com"})
(User {_userEmail = "betty@example.com", _userFirstName = "Betty", _userLastName = "Jones", _userPassword = "82b054bd83ffad9b6cf8bdb98ce3cc2f"},Address {_addressId = 1, _addressLine1 = "123 Little Street", _addressLine2 = Nothing, _addressCity = "Boston", _addressState = "MA", _addressZip = "12345", _addressForUser = UserId "james@example.com"})
(User {_userEmail = "betty@example.com", _userFirstName = "Betty", _userLastName = "Jones", _userPassword = "82b054bd83ffad9b6cf8bdb98ce3cc2f"},Address {_addressId = 2, _addressLine1 = "222 Main Street", _addressLine2 = Just "Ste 1", _addressCity = "Houston", _addressState = "TX", _addressZip = "8888", _addressForUser = UserId "betty@example.com"})
(User {_userEmail = "betty@example.com", _userFirstName = "Betty", _userLastName = "Jones", _userPassword = "82b054bd83ffad9b6cf8bdb98ce3cc2f"},Address {_addressId = 3, _addressLine1 = "9999 Residence Ave", _addressLine2 = Nothing, _addressCity = "Sugarland", _addressState = "TX", _addressZip = "8989", _addressForUser = UserId "betty@example.com"})
(User {_userEmail = "sam@example.com", _userFirstName = "Sam", _userLastName = "Taylor", _userPassword = "332532dcfaa1cbf61e2a266bd723612c"},Address {_addressId = 1, _addressLine1 = "123 Little Street", _addressLine2 = Nothing, _addressCity = "Boston", _addressState = "MA", _addressZip = "12345", _addressForUser = UserId "james@example.com"})
(User {_userEmail = "sam@example.com", _userFirstName = "Sam", _userLastName = "Taylor", _userPassword = "332532dcfaa1cbf61e2a266bd723612c"},Address {_addressId = 2, _addressLine1 = "222 Main Street", _addressLine2 = Just "Ste 1", _addressCity = "Houston", _addressState = "TX", _addressZip = "8888", _addressForUser = UserId "betty@example.com"})
(User {_userEmail = "sam@example.com", _userFirstName = "Sam", _userLastName = "Taylor", _userPassword = "332532dcfaa1cbf61e2a266bd723612c"},Address {_addressId = 3, _addressLine1 = "9999 Residence Ave", _addressLine2 = Nothing, _addressCity = "Sugarland", _addressState = "TX", _addressZip = "8989", _addressForUser = UserId "betty@example.com"})

Just like with lists we can also use a construct similar to guard to ensure that we only retrieve users and addresses that are related. The guard_ function takes in expression of type QExpr s Bool which represents a SQL expression that returns a boolean. QExpr s Bools support all the common operators we have on regular Bool, except they're suffixed with a .. For example, where you'd use (&&) on two Haskell-level Bools, we'd use (&&.) on QExpr-level bools.

usersAndRelatedAddresses <-
  runBeamSqliteDebug putStrLn conn $
    runSelectReturningList $ select $
    do user <- all_ (shoppingCartDb ^. shoppingCartUsers)
       address <- all_ (shoppingCartDb ^. shoppingCartUserAddresses)
       guard_ (address ^. addressForUserId ==. user ^. userEmail)
       pure (user, address)

mapM_ print usersAndRelatedAddresses
SELECT "t0"."email" AS "res0",
       "t0"."first_name" AS "res1",
       "t0"."last_name" AS "res2",
       "t0"."password" AS "res3",
       "t1"."id" AS "res4",
       "t1"."address1" AS "res5",
       "t1"."address2" AS "res6",
       "t1"."city" AS "res7",
       "t1"."state" AS "res8",
       "t1"."zip" AS "res9",
       "t1"."for_user__email" AS "res10"
FROM "cart_users" AS "t0"
INNER JOIN "addresses" AS "t1"
WHERE ("t1"."for_user__email")=("t0"."email");

-- With values: []
(User {_userEmail = "james@example.com", _userFirstName = "James", _userLastName = "Smith", _userPassword = "b4cc344d25a2efe540adbf2678e2304c"},Address {_addressId = 1, _addressLine1 = "123 Little Street", _addressLine2 = Nothing, _addressCity = "Boston", _addressState = "MA", _addressZip = "12345", _addressForUser = UserId "james@example.com"})
(User {_userEmail = "betty@example.com", _userFirstName = "Betty", _userLastName = "Jones", _userPassword = "82b054bd83ffad9b6cf8bdb98ce3cc2f"},Address {_addressId = 2, _addressLine1 = "222 Main Street", _addressLine2 = Just "Ste 1", _addressCity = "Houston", _addressState = "TX", _addressZip = "8888", _addressForUser = UserId "betty@example.com"})
(User {_userEmail = "betty@example.com", _userFirstName = "Betty", _userLastName = "Jones", _userPassword = "82b054bd83ffad9b6cf8bdb98ce3cc2f"},Address {_addressId = 3, _addressLine1 = "9999 Residence Ave", _addressLine2 = Nothing, _addressCity = "Sugarland", _addressState = "TX", _addressZip = "8989", _addressForUser = UserId "betty@example.com"})

Of course this is kind of messy because it involves manually matching the primary key of User with the reference in Address. Alternatively, we can use the references_ predicate to have beam automatically generate a QExpr expression that can match primary keys together.

usersAndRelatedAddressesUsingReferences <-
  runBeamSqliteDebug putStrLn conn $
    runSelectReturningList $ select $
    do user <- all_ (shoppingCartDb ^. shoppingCartUsers)
       address <- all_ (shoppingCartDb ^. shoppingCartUserAddresses)

       guard_ (_addressForUser address `references_` user)
       pure (user, address)

mapM_ print usersAndRelatedAddressesUsingReferences
SELECT "t0"."email" AS "res0",
       "t0"."first_name" AS "res1",
       "t0"."last_name" AS "res2",
       "t0"."password" AS "res3",
       "t1"."id" AS "res4",
       "t1"."address1" AS "res5",
       "t1"."address2" AS "res6",
       "t1"."city" AS "res7",
       "t1"."state" AS "res8",
       "t1"."zip" AS "res9",
       "t1"."for_user__email" AS "res10"
FROM "cart_users" AS "t0"
INNER JOIN "addresses" AS "t1"
WHERE ("t1"."for_user__email")=("t0"."email");

-- With values: []
(User {_userEmail = "james@example.com", _userFirstName = "James", _userLastName = "Smith", _userPassword = "b4cc344d25a2efe540adbf2678e2304c"},Address {_addressId = 1, _addressLine1 = "123 Little Street", _addressLine2 = Nothing, _addressCity = "Boston", _addressState = "MA", _addressZip = "12345", _addressForUser = UserId "james@example.com"})
(User {_userEmail = "betty@example.com", _userFirstName = "Betty", _userLastName = "Jones", _userPassword = "82b054bd83ffad9b6cf8bdb98ce3cc2f"},Address {_addressId = 2, _addressLine1 = "222 Main Street", _addressLine2 = Just "Ste 1", _addressCity = "Houston", _addressState = "TX", _addressZip = "8888", _addressForUser = UserId "betty@example.com"})
(User {_userEmail = "betty@example.com", _userFirstName = "Betty", _userLastName = "Jones", _userPassword = "82b054bd83ffad9b6cf8bdb98ce3cc2f"},Address {_addressId = 3, _addressLine1 = "9999 Residence Ave", _addressLine2 = Nothing, _addressCity = "Sugarland", _addressState = "TX", _addressZip = "8989", _addressForUser = UserId "betty@example.com"})

You may have noticed that the joins up until now did not include a SQL ON clause. Instead we joined the tables together, and then used the WHERE clause to filter out results we don't want. If you'd like to use the ON clause to make the SQL clearer or save a line in your code, beam offers the related_ combinator to pull related tables directly into the query monad.

usersAndRelatedAddressesUsingRelated <-
  runBeamSqliteDebug putStrLn conn $
    runSelectReturningList $ select $
    do address <- all_ (shoppingCartDb ^. shoppingCartUserAddresses)
       user <- related_ (shoppingCartDb ^. shoppingCartUsers) (_addressForUser address)
       pure (user, address)

mapM_ print usersAndRelatedAddressesUsingRelated
SELECT "t1"."email" AS "res0",
       "t1"."first_name" AS "res1",
       "t1"."last_name" AS "res2",
       "t1"."password" AS "res3",
       "t0"."id" AS "res4",
       "t0"."address1" AS "res5",
       "t0"."address2" AS "res6",
       "t0"."city" AS "res7",
       "t0"."state" AS "res8",
       "t0"."zip" AS "res9",
       "t0"."for_user__email" AS "res10"
FROM "addresses" AS "t0"
INNER JOIN "cart_users" AS "t1" ON ("t0"."for_user__email")=("t1"."email");

-- With values: []
(User {_userEmail = "james@example.com", _userFirstName = "James", _userLastName = "Smith", _userPassword = "b4cc344d25a2efe540adbf2678e2304c"},Address {_addressId = 1, _addressLine1 = "123 Little Street", _addressLine2 = Nothing, _addressCity = "Boston", _addressState = "MA", _addressZip = "12345", _addressForUser = UserId "james@example.com"})
(User {_userEmail = "betty@example.com", _userFirstName = "Betty", _userLastName = "Jones", _userPassword = "82b054bd83ffad9b6cf8bdb98ce3cc2f"},Address {_addressId = 2, _addressLine1 = "222 Main Street", _addressLine2 = Just "Ste 1", _addressCity = "Houston", _addressState = "TX", _addressZip = "8888", _addressForUser = UserId "betty@example.com"})
(User {_userEmail = "betty@example.com", _userFirstName = "Betty", _userLastName = "Jones", _userPassword = "82b054bd83ffad9b6cf8bdb98ce3cc2f"},Address {_addressId = 3, _addressLine1 = "9999 Residence Ave", _addressLine2 = Nothing, _addressCity = "Sugarland", _addressState = "TX", _addressZip = "8989", _addressForUser = UserId "betty@example.com"})

We can also query the addresses for a particular user given a UserId.

-- This is a contrived example to show how we can use an arbitrary UserId to fetch a particular user.
-- We don't always have access to the full 'User' lying around. For example we may be in a function that
-- only accepts 'UserId's.

let bettyId = UserId "betty@example.com" :: UserId

bettysAddresses <-
  runBeamSqliteDebug putStrLn conn $
    runSelectReturningList $ select $
    do address <- all_ (shoppingCartDb ^. shoppingCartUserAddresses)
       guard_ (_addressForUser address ==. val_ bettyId)
       pure address

mapM_ print bettysAddresses
SELECT "t0"."id" AS "res0",
       "t0"."address1" AS "res1",
       "t0"."address2" AS "res2",
       "t0"."city" AS "res3",
       "t0"."state" AS "res4",
       "t0"."zip" AS "res5",
       "t0"."for_user__email" AS "res6"
FROM "addresses" AS "t0"
WHERE ("t0"."for_user__email")=(?);

-- With values: [SQLText "betty@example.com"]
Address {_addressId = 2, _addressLine1 = "222 Main Street", _addressLine2 = Just "Ste 1", _addressCity = "Houston", _addressState = "TX", _addressZip = "8888", _addressForUser = UserId "betty@example.com"}
Address {_addressId = 3, _addressLine1 = "9999 Residence Ave", _addressLine2 = Nothing, _addressCity = "Sugarland", _addressState = "TX", _addressZip = "8989", _addressForUser = UserId "betty@example.com"}

Tip

More complicated joins are also supported. See the section on relationships

Updates and deletions

So far we've only seen how to insert data and query it. There are two other SQL operations that we have not covered: updates and deletions. Beam has full support for these manipulations as well.

Updates

Like INSERT and SELECT, to run an UPDATE command, we use the runUpdate function, with a value of SqlUpdate.

The save function constructs a value of SqlUpdate given a full record. It will generate an UPDATE that will set every field (except for the primary key fields) for the row that completely matches the primary key.

Let's first look at updating passwords given a User. For this we can use the saveTo function. Suppose James wants to change his password to the md5 hash of "supersecure", which is 52a516ca6df436828d9c0d26e31ef704. We have a User object representing James so we can simply call saveTo on the update value to update the corresponding record in the database.

[james] <-
  runBeamSqliteDebug putStrLn conn $
    do runUpdate $
         save (shoppingCartDb ^. shoppingCartUsers) (james { _userPassword = "52a516ca6df436828d9c0d26e31ef704" })

       runSelectReturningList $
         lookup_ (shoppingCartDb ^. shoppingCartUsers) (UserId "james@example.com")

putStrLn ("James's new password is " ++ show (james ^. userPassword))
UPDATE "cart_users"
SET "first_name"=?,
    "last_name"=?,
    "password"=?
WHERE ("email")=(?);

-- With values: [SQLText "James",SQLText "Smith",SQLText "52a516ca6df436828d9c0d26e31ef704",SQLText "james@example.com"]

SELECT "t0"."email" AS "res0",
       "t0"."first_name" AS "res1",
       "t0"."last_name" AS "res2",
       "t0"."password" AS "res3"
FROM "cart_users" AS "t0"
WHERE ("t0"."email")=(?);

-- With values: [SQLText "james@example.com"]
James's new password is "52a516ca6df436828d9c0d26e31ef704"

Tip

lookup_ (defined in Database.Beam.Query) can be used to easily lookup a single entity given a table entity in a database and a primary key.

This works great, but save requires that we have the whole User object at our disposal. Additionally, you'll notice that it causes every field to be set in the UPDATE query. Typically, this doesn't matter, but sometimes we'd like to update fewer fields, multiple rows, or use criteria other than a primary key match. The update function offers finer-grained control over the command submitted to the database.

To illustrate use of this function, let's suppose the city of "Sugarland, TX" was renamed "Sugarville, TX" and had its ZIP code changed to be "12345" citywide. The following beam command will update all addresses in the old city to use the new name and ZIP code.

addresses <-
  runBeamSqliteDebug putStrLn conn $
    do runUpdate $
         update (shoppingCartDb ^. shoppingCartUserAddresses)
                (\address -> [ address ^. addressCity <-. val_ "Sugarville"
                             , address ^. addressZip <-. val_ "12345" ])
                (\address -> address ^. addressCity ==. val_ "Sugarland" &&.
                             address ^. addressState ==. val_ "TX")

       runSelectReturningList $ select $ all_ (shoppingCartDb ^. shoppingCartUserAddresses)

mapM_ print addresses
UPDATE "addresses"
SET "city"=?,
    "zip"=?
WHERE (("city")=(?))
  AND (("state")=(?));

-- With values: [SQLText "Sugarville",SQLText "12345",SQLText "Sugarland",SQLText "TX"]

SELECT "t0"."id" AS "res0",
       "t0"."address1" AS "res1",
       "t0"."address2" AS "res2",
       "t0"."city" AS "res3",
       "t0"."state" AS "res4",
       "t0"."zip" AS "res5",
       "t0"."for_user__email" AS "res6"
FROM "addresses" AS "t0";

-- With values: []
Address {_addressId = 1, _addressLine1 = "123 Little Street", _addressLine2 = Nothing, _addressCity = "Boston", _addressState = "MA", _addressZip = "12345", _addressForUser = UserId "james@example.com"}
Address {_addressId = 2, _addressLine1 = "222 Main Street", _addressLine2 = Just "Ste 1", _addressCity = "Houston", _addressState = "TX", _addressZip = "8888", _addressForUser = UserId "betty@example.com"}
Address {_addressId = 3, _addressLine1 = "9999 Residence Ave", _addressLine2 = Nothing, _addressCity = "Sugarville", _addressState = "TX", _addressZip = "12345", _addressForUser = UserId "betty@example.com"}

Deletions

Now suppose that Betty has decided to give up her place in Houston. We can use runDelete to run a DELETE command.

runBeamSqliteDebug putStrLn conn $
  runDelete $
  delete (shoppingCartDb ^. shoppingCartUserAddresses)
         (\address -> address ^. addressCity ==. "Houston" &&.
                      _addressForUser address `references_` betty)
DELETE
FROM "addresses"
WHERE (("city")=(?))
  AND (("for_user__email")=(?));

-- With values: [SQLText "Houston",SQLText "betty@example.com"]

Conclusion

In this tutorial we created our first beam relationship. We saw how to use the modifications system to override the default names given to database entities. We saw how to use tableLenses to generate lenses that can be used with any lens library. We used the monadic query interface to write queries that used SQL joins, and we saw how beam makes it easy to automatically pull related tables into our queries. Finally we introduced the runUpdate and runDelete functions and demonstrated several ways to construct UPDATEs and DELETEs.

At this point, we've covered enough of the beam interface to start writing interesting programs. Take some time to explore beam and create your own databases. Afterwards, read on for the last part of the tutorial.


  1. Actually, any Beamable type can be wholly embedded in another. See the section on models in the user guide for more information. 

  2. The models guide explains the exact mechanisms used