Skip to content

INSERT

SQL INSERT expressions allow you to insert rows in the database.

There is a lot of variety in how you can provide new data, and Beam supports all standard ways.

The insert function from Database.Beam.Query can be used to insert rows into a particular table. insert takes a table and a source of values, represented by SqlInsertValues, and returns a SqlInsert object that can be run in a MonadBeam with runInsert.

The SqlInsertValues type takes two type parameters. The first is the underlying database syntax, and the second is the shape of the data it carries, specified as a beam table type. For example, a source of values in Postgres that can be inserted in the Chinook customers table would have the type SqlInsertValues PgInsertValuesSyntax CustomerT. This abstracts over where those values actually are. The values may be explicit haskell values, expressions returning customers, a query returning customers, or something else. Either way, they can all be used in the same way with the insert function.

Inserting explicit new values

If you have a record of explicit Haskell values, use the insertValues function. For example, to insert a new playlist into our chinook database

runInsert $ insert (playlist chinookDb) $
  insertValues [ Playlist 700 (Just "My New Playlist")
               , Playlist 701 (Just "Another Playlist")
               , Playlist 702 (Just "Look... more playlists") ]

insertedPlaylists <-
  runSelectReturningList $
  select $ filter_ (\p -> playlistId p >=. 700) $
  all_ (playlist chinookDb)

putStrLn "Inserted playlists:"
forM_ insertedPlaylists $ \p ->
  putStrLn (show p)
INSERT INTO "Playlist"("PlaylistId",
                       "Name")
VALUES (?,
        ?), (?,
             ?), (?,
                  ?);

-- With values: [SQLInteger 700,SQLText "My New Playlist",SQLInteger 701,SQLText "Another Playlist",SQLInteger 702,SQLText "Look... more playlists"];

SELECT "t0"."PlaylistId" AS "res0",
       "t0"."Name" AS "res1"
FROM "Playlist" AS "t0"
WHERE ("t0"."PlaylistId")>=(?);

-- With values: [SQLInteger 700];
-- Output: Inserted playlists:
-- Output: Playlist {playlistId = 700, playlistName = Just "My New Playlist"}
-- Output: Playlist {playlistId = 701, playlistName = Just "Another Playlist"}
-- Output: Playlist {playlistId = 702, playlistName = Just "Look... more playlists"}
INSERT INTO "Playlist"("PlaylistId",
                       "Name")
VALUES (700,
        'My New Playlist'), (701,
                             'Another Playlist'), (702,
                                                   'Look... more playlists');


SELECT "t0"."PlaylistId" AS "res0",
       "t0"."Name" AS "res1"
FROM "Playlist" AS "t0"
WHERE ("t0"."PlaylistId") >= (700);

-- Output: Inserted playlists:
-- Output: Playlist {playlistId = 700, playlistName = Just "My New Playlist"}
-- Output: Playlist {playlistId = 701, playlistName = Just "Another Playlist"}
-- Output: Playlist {playlistId = 702, playlistName = Just "Look... more playlists"}
INSERT INTO `Playlist`(`PlaylistId`, `Name`)
VALUES (700,
        'My New Playlist'), (701,
                             'Another Playlist'), (702,
                                                   'Look... more playlists');


SELECT `t0`.`PlaylistId` AS `res0`,
       `t0`.`Name` AS `res1`
FROM `Playlist` AS `t0`
WHERE (`t0`.`PlaylistId`) >= (700);

-- Output: Inserted playlists:
-- Output: Playlist {playlistId = 700, playlistName = Just "My New Playlist"}
-- Output: Playlist {playlistId = 701, playlistName = Just "Another Playlist"}
-- Output: Playlist {playlistId = 702, playlistName = Just "Look... more playlists"}

Inserting calculated values

Inserting explicit values is all well and good, but sometimes we want to defer some processing to the database. For example, perhaps we want to create a new invoice and use the current time as the invoice date. We could grab the current time using getCurrentTime and then use this to construct an explicit Haskell value, but this may cause synchronization issues for our application. To do this, beam allows us to specify arbitrary expressions as a source of values using the insertExpressions function.

runInsert $ insert (invoice chinookDb) $
  insertExpressions [ Invoice (val_ 800) (CustomerId (val_ 1)) currentTimestamp_
                              (val_ (Address (Just "123 My Street") (Just "Buenos Noches") (Just "Rio") (Just "Mozambique") (Just "ABCDEF")))
                              (val_ 1000) ]

Just newInvoice <-
  runSelectReturningOne $
  lookup_ (invoice chinookDb) (InvoiceId 800)

putStrLn ("Inserted invoice: " ++ show newInvoice)
INSERT INTO "Invoice"("InvoiceId",
                      "CustomerId",
                      "InvoiceDate",
                      "BillingAddress",
                      "BillingCity",
                      "BillingState",
                      "BillingCountry",
                      "BillingPostalCode",
                      "Total")
VALUES (?,
        ?,
        CURRENT_TIMESTAMP,
        ?,
        ?,
        ?,
        ?,
        ?,
        ?);

-- With values: [SQLInteger 800,SQLInteger 1,SQLText "123 My Street",SQLText "Buenos Noches",SQLText "Rio",SQLText "Mozambique",SQLText "ABCDEF",SQLText "1000.0"];

SELECT "t0"."InvoiceId" AS "res0",
       "t0"."CustomerId" AS "res1",
       "t0"."InvoiceDate" AS "res2",
       "t0"."BillingAddress" AS "res3",
       "t0"."BillingCity" AS "res4",
       "t0"."BillingState" AS "res5",
       "t0"."BillingCountry" AS "res6",
       "t0"."BillingPostalCode" AS "res7",
       "t0"."Total" AS "res8"
FROM "Invoice" AS "t0"
WHERE ("t0"."InvoiceId")=(?);

-- With values: [SQLInteger 800];
-- Output: Inserted invoice: Invoice {invoiceId = SqlSerial {unSerial = 800}, invoiceCustomer = CustomerId 1, invoiceDate = 2018-04-09 22:44:24, invoiceBillingAddress = Address {address = Just "123 My Street", addressCity = Just "Buenos Noches", addressState = Just "Rio", addressCountry = Just "Mozambique", addressPostalCode = Just "ABCDEF"}, invoiceTotal = 1000.0}
INSERT INTO "Invoice"("InvoiceId",
                      "CustomerId",
                      "InvoiceDate",
                      "BillingAddress",
                      "BillingCity",
                      "BillingState",
                      "BillingCountry",
                      "BillingPostalCode",
                      "Total")
VALUES (800,
        1,
        CURRENT_TIMESTAMP,
        '123 My Street',
        'Buenos Noches',
        'Rio',
        'Mozambique',
        'ABCDEF',
        '1000.0');


SELECT "t0"."InvoiceId" AS "res0",
       "t0"."CustomerId" AS "res1",
       "t0"."InvoiceDate" AS "res2",
       "t0"."BillingAddress" AS "res3",
       "t0"."BillingCity" AS "res4",
       "t0"."BillingState" AS "res5",
       "t0"."BillingCountry" AS "res6",
       "t0"."BillingPostalCode" AS "res7",
       "t0"."Total" AS "res8"
FROM "Invoice" AS "t0"
WHERE ("t0"."InvoiceId") = (800);

-- Output: Inserted invoice: Invoice {invoiceId = SqlSerial {unSerial = 800}, invoiceCustomer = CustomerId 1, invoiceDate = 2018-05-02 18:17:27.060309, invoiceBillingAddress = Address {address = Just "123 My Street", addressCity = Just "Buenos Noches", addressState = Just "Rio", addressCountry = Just "Mozambique", addressPostalCode = Just "ABCDEF"}, invoiceTotal = 1000.0}
INSERT INTO `Invoice`(`InvoiceId`, `CustomerId`, `InvoiceDate`, `BillingAddress`, `BillingCity`, `BillingState`, `BillingCountry`, `BillingPostalCode`, `Total`)
VALUES (800,
        1,
        CURRENT_TIMESTAMP,
        '123 My Street',
        'Buenos Noches',
        'Rio',
        'Mozambique',
        'ABCDEF',
        1000.0);


SELECT `t0`.`InvoiceId` AS `res0`,
       `t0`.`CustomerId` AS `res1`,
       `t0`.`InvoiceDate` AS `res2`,
       `t0`.`BillingAddress` AS `res3`,
       `t0`.`BillingCity` AS `res4`,
       `t0`.`BillingState` AS `res5`,
       `t0`.`BillingCountry` AS `res6`,
       `t0`.`BillingPostalCode` AS `res7`,
       `t0`.`Total` AS `res8`
FROM `Invoice` AS `t0`
WHERE (`t0`.`InvoiceId`) = (800);

-- Output: Inserted invoice: Invoice {invoiceId = SqlSerial {unSerial = 800}, invoiceCustomer = CustomerId 1, invoiceDate = 2018-04-10 00:32:22, invoiceBillingAddress = Address {address = Just "123 My Street", addressCity = Just "Buenos Noches", addressState = Just "Rio", addressCountry = Just "Mozambique", addressPostalCode = Just "ABCDEF"}, invoiceTotal = 1000.0}

insertExpressions is strictly more general than insertValues. We can turn any insertValues to an insertExpressions by running every table value through the val_ function to convert a Haskell literal to an expression.

For example, we can write the playlist example above as

runInsert $ insert (playlist chinookDb) $
  insertExpressions [ val_ $ Playlist 700 (Just "My New Playlist")
                    , val_ $ Playlist 701 (Just "Another Playlist")
                    , val_ $ Playlist 702 (Just "Look... more playlists") ]

insertedPlaylists <-
  runSelectReturningList $
  select $ filter_ (\p -> playlistId p >=. 700) $
  all_ (playlist chinookDb)

putStrLn "Inserted playlists:"
forM_ insertedPlaylists $ \p ->
  putStrLn (show p)
INSERT INTO "Playlist"("PlaylistId",
                       "Name")
VALUES (?,
        ?), (?,
             ?), (?,
                  ?);

-- With values: [SQLInteger 700,SQLText "My New Playlist",SQLInteger 701,SQLText "Another Playlist",SQLInteger 702,SQLText "Look... more playlists"];

SELECT "t0"."PlaylistId" AS "res0",
       "t0"."Name" AS "res1"
FROM "Playlist" AS "t0"
WHERE ("t0"."PlaylistId")>=(?);

-- With values: [SQLInteger 700];
-- Output: Inserted playlists:
-- Output: Playlist {playlistId = 700, playlistName = Just "My New Playlist"}
-- Output: Playlist {playlistId = 701, playlistName = Just "Another Playlist"}
-- Output: Playlist {playlistId = 702, playlistName = Just "Look... more playlists"}
INSERT INTO "Playlist"("PlaylistId",
                       "Name")
VALUES (700,
        'My New Playlist'), (701,
                             'Another Playlist'), (702,
                                                   'Look... more playlists');


SELECT "t0"."PlaylistId" AS "res0",
       "t0"."Name" AS "res1"
FROM "Playlist" AS "t0"
WHERE ("t0"."PlaylistId") >= (700);

-- Output: Inserted playlists:
-- Output: Playlist {playlistId = 700, playlistName = Just "My New Playlist"}
-- Output: Playlist {playlistId = 701, playlistName = Just "Another Playlist"}
-- Output: Playlist {playlistId = 702, playlistName = Just "Look... more playlists"}
INSERT INTO `Playlist`(`PlaylistId`, `Name`)
VALUES (700,
        'My New Playlist'), (701,
                             'Another Playlist'), (702,
                                                   'Look... more playlists');


SELECT `t0`.`PlaylistId` AS `res0`,
       `t0`.`Name` AS `res1`
FROM `Playlist` AS `t0`
WHERE (`t0`.`PlaylistId`) >= (700);

-- Output: Inserted playlists:
-- Output: Playlist {playlistId = 700, playlistName = Just "My New Playlist"}
-- Output: Playlist {playlistId = 701, playlistName = Just "Another Playlist"}
-- Output: Playlist {playlistId = 702, playlistName = Just "Look... more playlists"}

One common use of insertExpressions_ is when adding new rows to tables where one field needs to be set to the default value. For example, auto-incrementing keys or random UUIDs are a common way to assign primary keys to rows. You can use insertExpressions_ using the default_ expression for each column that you want to use the default value for.

For example, the query below adds a new invoice asking the database to assign a new id.

runInsert $ insert (invoice chinookDb) $
  insertExpressions [ Invoice default_ -- Ask the database to give us a default id
                              (val_ (CustomerId 1)) currentTimestamp_
                              (val_ (Address (Just "123 My Street") (Just "Buenos Noches") (Just "Rio") (Just "Mozambique") (Just "ABCDEF")))
                              (val_ 1000) ]
INSERT INTO "Invoice"("InvoiceId",
                      "CustomerId",
                      "InvoiceDate",
                      "BillingAddress",
                      "BillingCity",
                      "BillingState",
                      "BillingCountry",
                      "BillingPostalCode",
                      "Total")
VALUES (DEFAULT,
        1,
        CURRENT_TIMESTAMP,
        '123 My Street',
        'Buenos Noches',
        'Rio',
        'Mozambique',
        'ABCDEF',
        '1000.0');
INSERT INTO `Invoice`(`InvoiceId`, `CustomerId`, `InvoiceDate`, `BillingAddress`, `BillingCity`, `BillingState`, `BillingCountry`, `BillingPostalCode`, `Total`)
VALUES (DEFAULT,
        1,
        CURRENT_TIMESTAMP,
        '123 My Street',
        'Buenos Noches',
        'Rio',
        'Mozambique',
        'ABCDEF',
        1000.0);

Warning

SQLite is a great little backend, but it doesn't support some standard SQL features, like the DEFAULT keyword in inserts. You can retrieve the same functionality by only inserting into a subset of columns. See the section on that below.

Retrieving the rows inserted

However, now we have no way of knowing what value the database assigned. Unfortunately, there is no database-agnostic solution to this problem. However, it's a common enough use case that beam provides a backend-agnostic way for some backends. Backends that provide this functionality provide an instance of MonadBeamInsertReturning. In order to use this class, you'll need to explicitly import Database.Beam.Backend.SQL.BeamExtensions. Below, we've imported this module qualified.

[newInvoice] <-
  BeamExtensions.runInsertReturningList (invoice chinookDb) $
  insertExpressions [ Invoice default_ -- Ask the database to give us a default id
                              (val_ (CustomerId 1)) currentTimestamp_
                              (val_ (Address (Just "123 My Street") (Just "Buenos Noches") (Just "Rio") (Just "Mozambique") (Just "ABCDEF")))
                              (val_ 1000) ]

putStrLn ("We inserted a new invoice, and the result was " ++ show newInvoice)
INSERT INTO "Invoice"("CustomerId",
                      "InvoiceDate",
                      "BillingAddress",
                      "BillingCity",
                      "BillingState",
                      "BillingCountry",
                      "BillingPostalCode",
                      "Total")
VALUES (?,
        CURRENT_TIMESTAMP,
        ?,
        ?,
        ?,
        ?,
        ?,
        ?);

-- With values: [SQLInteger 1,SQLText "123 My Street",SQLText "Buenos Noches",SQLText "Rio",SQLText "Mozambique",SQLText "ABCDEF",SQLText "1000.0"];
-- Output: We inserted a new invoice, and the result was Invoice {invoiceId = SqlSerial {unSerial = 413}, invoiceCustomer = CustomerId 1, invoiceDate = 2018-04-09 22:44:31, invoiceBillingAddress = Address {address = Just "123 My Street", addressCity = Just "Buenos Noches", addressState = Just "Rio", addressCountry = Just "Mozambique", addressPostalCode = Just "ABCDEF"}, invoiceTotal = 1000.0}
INSERT INTO "Invoice"("InvoiceId",
                      "CustomerId",
                      "InvoiceDate",
                      "BillingAddress",
                      "BillingCity",
                      "BillingState",
                      "BillingCountry",
                      "BillingPostalCode",
                      "Total")
VALUES (DEFAULT,
        1,
        CURRENT_TIMESTAMP,
        '123 My Street',
        'Buenos Noches',
        'Rio',
        'Mozambique',
        'ABCDEF',
        '1000.0') RETURNING "InvoiceId",
                            "CustomerId",
                            "InvoiceDate",
                            "BillingAddress",
                            "BillingCity",
                            "BillingState",
                            "BillingCountry",
                            "BillingPostalCode",
                            "Total";

-- Output: We inserted a new invoice, and the result was Invoice {invoiceId = SqlSerial {unSerial = 501}, invoiceCustomer = CustomerId 1, invoiceDate = 2018-05-02 18:17:37.475852, invoiceBillingAddress = Address {address = Just "123 My Street", addressCity = Just "Buenos Noches", addressState = Just "Rio", addressCountry = Just "Mozambique", addressPostalCode = Just "ABCDEF"}, invoiceTotal = 1000.0}

The pattern match on the single newInvoice is safe, even though its partial. In general, you can expect the same amount of rows returned as specified in your SqlInsertValues. If you know what this is statically, then you can feel free to pattern match directly. Otherwise (if you used insertFrom, for example), you'll need to handle the possibility that nothing was inserted.

Tip

Note that unlike the standard beam INSERT functionality, which can run any SqlInsert, runInsertReturningList requires that we supply a table and a SqlInsertValues.

This is because this functionality is emulated on some backends. Some backends (such as postgres) provide a more advanced INSERT .. RETURNING statement that can be used more like SqlInsert. See the backend documentation for more details.

Note

Although SQLite has no support for the DEFAULT clause, MonadBeamInsertReturning in beam-sqlite inserts rows one at a time and will detect usage of the DEFAULT keyword. The beam authors consider this okay. While most beam statements are guaranteed to translate directly to the underlying DBMS system, runInsertReturningList is explicitly marked as emulated functionality.

Inserting from the result of a SELECT statement

Sometimes you want to use existing data to insert values. For example, perhaps we want to give every customer their own playlist, titled "'s playlist".

We can use the insertFrom function to make a SqlInsertValues corresponding to the result of a query. Make sure to return a projection with the same 'shape' as your data. If not, you'll get a compile time error.

For example, to create the playlists as above

runInsert $ insert (playlist chinookDb) $
  insertFrom $ do
    c <- all_ (customer chinookDb)
    pure (Playlist (customerId c + 1000) (just_ (concat_ [ customerFirstName c, "'s Playlist" ])))

playlists <- runSelectReturningList $ select $ limit_ 10 $
             orderBy_ (\p -> asc_ (playlistId p)) $
             filter_ (\p -> playlistId p >=. 1000) $
             all_ (playlist chinookDb)

putStrLn "Inserted playlists"
forM_ playlists $ \playlist ->
  putStrLn ("  - " ++ show playlist)
INSERT INTO "Playlist"("PlaylistId",
                       "Name")
SELECT ("t0"."CustomerId") + (?) AS "res0",
       ("t0"."FirstName" || (?)) AS "res1"
FROM "Customer" AS "t0";

-- With values: [SQLInteger 1000,SQLText "'s Playlist"];

SELECT "t0"."PlaylistId" AS "res0",
       "t0"."Name" AS "res1"
FROM "Playlist" AS "t0"
WHERE ("t0"."PlaylistId")>=(?)
ORDER BY "t0"."PlaylistId" ASC
LIMIT 10;

-- With values: [SQLInteger 1000];
-- Output: Inserted playlists
-- Output:   - Playlist {playlistId = 1001, playlistName = Just "Lu\237s's Playlist"}
-- Output:   - Playlist {playlistId = 1002, playlistName = Just "Leonie's Playlist"}
-- Output:   - Playlist {playlistId = 1003, playlistName = Just "Fran\231ois's Playlist"}
-- Output:   - Playlist {playlistId = 1004, playlistName = Just "Bj\248rn's Playlist"}
-- Output:   - Playlist {playlistId = 1005, playlistName = Just "Franti\353ek's Playlist"}
-- Output:   - Playlist {playlistId = 1006, playlistName = Just "Helena's Playlist"}
-- Output:   - Playlist {playlistId = 1007, playlistName = Just "Astrid's Playlist"}
-- Output:   - Playlist {playlistId = 1008, playlistName = Just "Daan's Playlist"}
-- Output:   - Playlist {playlistId = 1009, playlistName = Just "Kara's Playlist"}
-- Output:   - Playlist {playlistId = 1010, playlistName = Just "Eduardo's Playlist"}
INSERT INTO "Playlist"("PlaylistId",
                       "Name")
SELECT ("t0"."CustomerId") + (1000) AS "res0",
       CONCAT("t0"."FirstName", '''s Playlist') AS "res1"
FROM "Customer" AS "t0";


SELECT "t0"."PlaylistId" AS "res0",
       "t0"."Name" AS "res1"
FROM "Playlist" AS "t0"
WHERE ("t0"."PlaylistId") >= (1000)
ORDER BY "t0"."PlaylistId" ASC
LIMIT 10;

-- Output: Inserted playlists
-- Output:   - Playlist {playlistId = 1001, playlistName = Just "Lu\237s's Playlist"}
-- Output:   - Playlist {playlistId = 1002, playlistName = Just "Leonie's Playlist"}
-- Output:   - Playlist {playlistId = 1003, playlistName = Just "Fran\231ois's Playlist"}
-- Output:   - Playlist {playlistId = 1004, playlistName = Just "Bj\345rn's Playlist"}
-- Output:   - Playlist {playlistId = 1005, playlistName = Just "Franti\154ek's Playlist"}
-- Output:   - Playlist {playlistId = 1006, playlistName = Just "Helena's Playlist"}
-- Output:   - Playlist {playlistId = 1007, playlistName = Just "Astrid's Playlist"}
-- Output:   - Playlist {playlistId = 1008, playlistName = Just "Daan's Playlist"}
-- Output:   - Playlist {playlistId = 1009, playlistName = Just "Kara's Playlist"}
-- Output:   - Playlist {playlistId = 1010, playlistName = Just "Eduardo's Playlist"}
INSERT INTO `Playlist`(`PlaylistId`, `Name`)
SELECT (`t0`.`CustomerId`) + (1000) AS `res0`,
       CONCAT(`t0`.`FirstName`, '\'s Playlist') AS `res1`
FROM `Customer` AS `t0`;


SELECT `t0`.`PlaylistId` AS `res0`,
       `t0`.`Name` AS `res1`
FROM `Playlist` AS `t0`
WHERE (`t0`.`PlaylistId`) >= (1000)
ORDER BY `t0`.`PlaylistId` ASC
LIMIT 10;

-- Output: Inserted playlists
-- Output:   - Playlist {playlistId = 1001, playlistName = Just "Lu\237s's Playlist"}
-- Output:   - Playlist {playlistId = 1002, playlistName = Just "Leonie's Playlist"}
-- Output:   - Playlist {playlistId = 1003, playlistName = Just "Fran\231ois's Playlist"}
-- Output:   - Playlist {playlistId = 1004, playlistName = Just "Bj\248rn's Playlist"}
-- Output:   - Playlist {playlistId = 1005, playlistName = Just "Franti\353ek's Playlist"}
-- Output:   - Playlist {playlistId = 1006, playlistName = Just "Helena's Playlist"}
-- Output:   - Playlist {playlistId = 1007, playlistName = Just "Astrid's Playlist"}
-- Output:   - Playlist {playlistId = 1008, playlistName = Just "Daan's Playlist"}
-- Output:   - Playlist {playlistId = 1009, playlistName = Just "Kara's Playlist"}
-- Output:   - Playlist {playlistId = 1010, playlistName = Just "Eduardo's Playlist"}

Choosing a subset of columns

Above, we used the default_ clause to set a column to a default value. Unfortunately, not all backends support default_ (SQLite being a notable exception). Moreover, some INSERT forms simply can't use default_, such as insertFrom_ (you can't return default_ from a query). The standard SQL tool used in these cases is limiting the inserted data to specific columns. For example, suppose we want to insert new invoices for every customer with today's date. We can use the insertOnly function to project which field's are being inserted.

runInsert $
  insertOnly (invoice chinookDb)
             (\i -> ( invoiceCustomer i, invoiceDate i, invoiceBillingAddress i, invoiceTotal i ) ) $
  insertFrom $ do
    c <- all_ (customer chinookDb)

    -- We'll just charge each customer $10 to be mean!
    pure (primaryKey c, currentTimestamp_, customerAddress c, as_ @Scientific $ val_ 10)
INSERT INTO "Invoice"("CustomerId",
                      "InvoiceDate",
                      "BillingAddress",
                      "BillingCity",
                      "BillingState",
                      "BillingCountry",
                      "BillingPostalCode",
                      "Total")
SELECT "t0"."CustomerId" AS "res0",
       CURRENT_TIMESTAMP AS "res1",
                            "t0"."Address" AS "res2",
                            "t0"."City" AS "res3",
                            "t0"."State" AS "res4",
                            "t0"."Country" AS "res5",
                            "t0"."PostalCode" AS "res6",
                            ? AS "res7"
FROM "Customer" AS "t0";

-- With values: [SQLText "10.0"];
INSERT INTO "Invoice"("CustomerId",
                      "InvoiceDate",
                      "BillingAddress",
                      "BillingCity",
                      "BillingState",
                      "BillingCountry",
                      "BillingPostalCode",
                      "Total")
SELECT "t0"."CustomerId" AS "res0",
       CURRENT_TIMESTAMP AS "res1",
                            "t0"."Address" AS "res2",
                            "t0"."City" AS "res3",
                            "t0"."State" AS "res4",
                            "t0"."Country" AS "res5",
                            "t0"."PostalCode" AS "res6",
                            '10.0' AS "res7"
FROM "Customer" AS "t0";
INSERT INTO `Invoice`(`CustomerId`, `InvoiceDate`, `BillingAddress`, `BillingCity`, `BillingState`, `BillingCountry`, `BillingPostalCode`, `Total`)
SELECT `t0`.`CustomerId` AS `res0`,
       CURRENT_TIMESTAMP AS `res1`,
                            `t0`.`Address` AS `res2`,
                            `t0`.`City` AS `res3`,
                            `t0`.`State` AS `res4`,
                            `t0`.`Country` AS `res5`,
                            `t0`.`PostalCode` AS `res6`,
                            10.0 AS `res7`
FROM `Customer` AS `t0`;

Inserting nothing

Oftentimes, the values to be inserted are generated automatically by some Haskell function, and you just insert the resulting list. Sometimes, these lists may be empty. If you blindly translated this into SQL, you'd end up with INSERTs with empty VALUE clauses, which are illegal. Beam actually handles this gracefully. If a SqlInsertValues has no rows to insert, the SqlInsert returned by insert will know that it is empty. Running this SqlInsert results in nothing being sent to the database, which you can verify below.

let superComplicatedAction = pure [] -- Hopefully, you're more creative!

valuesToInsert <- superComplicatedAction

putStrLn "The following runInsert will send no commands to the database"
runInsert $ insert (playlist chinookDb) $
  insertValues valuesToInsert
putStrLn "See! I told you!"
-- Output: The following runInsert will send no commands to the database
-- Output: See! I told you!
-- Output: The following runInsert will send no commands to the database
-- Output: See! I told you!
-- Output: The following runInsert will send no commands to the database
-- Output: See! I told you!