### Install beam-sqlite

Source: https://github.com/haskell-beam/beam/blob/master/docs/user-guide/backends.md

Install the `beam-sqlite` backend using `cabal` or `stack`.

```bash
$ cabal install beam-sqlite
# or
$ stack install beam-sqlite
```

--------------------------------

### Install Beam Packages with Stack

Source: https://github.com/haskell-beam/beam/blob/master/docs/tutorials/tutorial1.md

Installs the required Beam core, Beam SQLite, SQLite simple, Beam migrate, and text packages using Stack for GHCi.

```console
$ stack repl --package beam-core --package beam-sqlite --package sqlite-simple --package beam-migrate --package text
```

--------------------------------

### Select All Tracks (Example)

Source: https://github.com/haskell-beam/beam/blob/master/docs/user-guide/queries/select.md

Demonstrates selecting all entries from the 'track' table using a simplified syntax.

```haskell
!example chinook
all_ (track chinookDb)
```

--------------------------------

### Full CTE Example with Window Function

Source: https://github.com/haskell-beam/beam/blob/master/docs/user-guide/queries/common-table-expressions.md

A complete example demonstrating the definition and usage of a CTE with a window function to find the representative genre for each album.

```haskell
!example chinookdml cte window
void $ runSelectReturningList $ selectWith $ do
  albumAndRepresentativeGenres <- 
    selecting $ 
    let albumGenreCnts = aggregate_ (	 -> ( group_ (trackAlbumId t) 
                                    , group_ (trackGenreId t) 
                                    , as_ @Int32 countAll_ )) $ 
                         all_ (track chinookDb)

        withMaxCounts = withWindow_ (
                                    (albumId, _, _) -> frame_ (partitionBy_ albumId) noOrder_ noBounds_)
                                    (
                                    (albumId, genreId, trackCnt) albumWindow ->
                                        (albumId, genreId, trackCnt, max_ trackCnt `over_` albumWindow))
                                    $ 
                        albumGenreCnts
    in filter_' (
                                (_, _, trackCnt, maxTrackCntPerAlbum) -> just_ trackCnt ==?. maxTrackCntPerAlbum) withMaxCounts

  pure $ do
    (albumId, genreId, _, _) <- 
       reuse albumAndRepresentativeGenres
    genre_ <- join_' (genre chinookDb) (
                                g -> genreId ==?. just_ (primaryKey g))
    album_ <- join_' (album chinookDb) (
                                a -> albumId ==?. just_ (primaryKey a))
    artist_ <- join_ (artist chinookDb) (
                                a -> albumArtist album_ `references_` a)

    pure ( artistName artist_, albumTitle album_, genreName genre_ )
```

--------------------------------

### Configure Enabled Backends for Documentation

Source: https://github.com/haskell-beam/beam/blob/master/README.md

Modify the mkdocs.yaml file to specify which Beam backends to use when building documentation examples. This example enables only the beam-sqlite backend.

```yaml
  - docs.markdown.beam_query:
      template_dir: 'docs/beam-templates'
      cache_dir: 'docs/.beam-query-cache'
      conf: 'docs/beam.yaml'
      base_dir: '.'
      enabled_backends:
        - beam-sqlite
```

--------------------------------

### Run Beam Query Example

Source: https://github.com/haskell-beam/beam/blob/master/README.md

Use this markdown syntax to include and run Beam query examples within the documentation. Specify the template name (chinook or chinookdml) and any backend requirements.

```markdown
!beam-query
```haskell
!example <template-name> <requirements>
do x <- all_ (customer chinookDb) -- chinookDb available under chinook and chinookdml examples
   pure x
```
```

--------------------------------

### Beam Left Join Query Example

Source: https://github.com/haskell-beam/beam/blob/master/docs/about/faq.md

Demonstrates a left join query in Beam. This example shows how to join artists with their albums, filtering by artist ID.

```haskell
!example chinook
do artist <- all_ (artist chinookDb)
   album  <- leftJoin_ (all_ (album chinookDb)) (\
```

--------------------------------

### Query using Reusable Helper

Source: https://github.com/haskell-beam/beam/blob/master/docs/user-guide/queries/relationships.md

Example of using the `invoiceLines_` helper to fetch invoice lines for all invoices.

```haskell
do i <- all_ (invoice chinookDb)
   ln <- invoiceLines_ i
```

--------------------------------

### List Monad Cartesian Product Example

Source: https://github.com/haskell-beam/beam/blob/master/docs/tutorials/tutorial2.md

Illustrates the behavior of the list monad in generating Cartesian products, similar to SQL JOIN operations.

```console
*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)]
```

```console
*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), ... ]
```

--------------------------------

### Using Window Functions with Partitions, Ordering, and Bounds

Source: https://github.com/haskell-beam/beam/blob/master/docs/user-guide/queries/window-functions.md

This example demonstrates various window function configurations including no partition/ordering/bounds, partitioning by customer, ordering by total, and defining specific row bounds for calculations. It calculates averages and ranks over different window frames.

```haskell
withWindow_ (\i -> ( frame_ noPartition_ noOrder_ noBounds_
                   , frame_ (partitionBy_ (invoiceCustomer i)) noOrder_ noBounds_
                   , frame_ noPartition_ (orderPartitionBy_ (asc_ (invoiceTotal i))) noBounds_
                   , frame_ noPartition_ (orderPartitionBy_ (asc_ (invoiceDate i))) (bounds_ (nrows_ 2) (Just (nrows_ 2))))) 
            (\i (allRows_, sameCustomer_, totals_, fourInvoicesAround_) ->
                 ( i
                 , avg_ (invoiceTotal i) `over_` allRows_
                 , avg_ (invoiceTotal i) `over_` sameCustomer_
                 , as_ @Int32 rank_ `over_` totals_
                 , avg_ (invoiceTotal i) `over_` fourInvoicesAround_ ))
            (all_ (invoice chinookDb))
```

--------------------------------

### Get Primary Key of User

Source: https://github.com/haskell-beam/beam/blob/master/docs/tutorials/tutorial2.md

Demonstrates how to retrieve the primary key of a user object using the `pk` function in GHCi.

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

--------------------------------

### Create a Primary Key Instance for UserT

Source: https://github.com/haskell-beam/beam/blob/master/docs/tutorials/tutorial1.md

Example of constructing a primary key for a UserT object using its defined UserId constructor.

```haskell
userKey = UserId "john@doe.org"
```

--------------------------------

### Query using Reusable Helper with Specific Invoice

Source: https://github.com/haskell-beam/beam/blob/master/docs/user-guide/queries/relationships.md

Example of using the `invoiceLines_` helper with a specific invoice value.

```haskell
invoiceLines (val_ i)
```

--------------------------------

### Filter Customers by First Name (UTF-8)

Source: https://github.com/haskell-beam/beam/blob/master/docs/user-guide/expressions.md

Example demonstrating UTF-8 support in Beam queries, filtering customers by a non-ASCII first name.

```haskell
filter_ (\s -> customerFirstName s ==. "あきら") $ all_ (customer chinookDb)
```

--------------------------------

### CTE with Window Function and Album Genre Filtering

Source: https://github.com/haskell-beam/beam/blob/master/docs/user-guide/queries/common-table-expressions.md

This example extends the previous CTE by adding a filter to exclude albums that only have tracks of a single genre. It uses a subquery and quantified comparison to achieve this.

```haskell
void $ runSelectReturningList $ selectWith $ do
  albumAndRepresentativeGenres <- 
    selecting $ 
    let albumGenreCnts = aggregate_ (	 -> ( group_ (trackAlbumId t) 
                                    , group_ (trackGenreId t) 
                                    , as_ @Int32 countAll_ )) $ 
                         all_ (track chinookDb)

    in withWindow_ (
      (albumId, _, _) -> frame_ (partitionBy_ albumId) noOrder_ noBounds_)
                   (
      (albumId, genreId, trackCnt) albumWindow ->
                       (albumId, genreId, trackCnt, max_ trackCnt `over_` albumWindow)) $ 
       albumGenreCnts

  pure $ do
    (albumId@(AlbumId albumIdColumn), genreId, _, _) <- 
       filter_' (
        (_, _, trackCnt, maxTrackCntInAlbum) -> 
                     just_ trackCnt ==?. maxTrackCntInAlbum) $ 
       reuse albumAndRepresentativeGenres
    genre_ <- join_' (genre chinookDb) (
      g -> genreId ==?. just_ (primaryKey g))
    album_ <- join_' (album chinookDb) (
      a -> albumId ==?. just_ (primaryKey a))
    artist_ <- join_ (artist chinookDb) (
      a -> albumArtist album_ `references_` a)

    -- Filter out all albums with tracks of only one genre
    guard_' (albumIdColumn ==*.
             anyOf_ (orderBy_ asc_ $ fmap (
              (AlbumId albumIdRaw, _) -> albumIdRaw) $ 
                     filter_ (
                      (_, genreCntByAlbum) -> genreCntByAlbum >. 1) $ 
                     aggregate_ (	 -> let GenreId genreId = trackGenreId t
                                       in ( group_ (trackAlbumId t) 
                                          , as_ @Int32 (countOver_ distinctInGroup_ genreId))) $ 
                     all_ (track chinookDb))) 

    pure ( artistName artist_, albumTitle album_, genreName genre_ )
```

--------------------------------

### Arbitrary Join Example

Source: https://github.com/haskell-beam/beam/blob/master/docs/user-guide/queries/relationships.md

Demonstrates rewriting a query that uses `oneToMany_` to use `join_` directly with an explicit join condition.

```haskell
!example chinook
do i <- all_ (invoice chinookDb)
   ln <- join_ (invoiceLine chinookDb) (\line -> invoiceLineInvoice line ==. primaryKey i)
   pure (i, ln)
```

--------------------------------

### Compose Filter, Limit, and Join Queries in Haskell

Source: https://github.com/haskell-beam/beam/blob/master/docs/user-guide/queries/basic.md

Demonstrates composing filter, limit, and join operations in Haskell using Beam. This example shows how Beam handles the generation of subselects and projections automatically.

```haskell
!example chinook
do tbl1 <-
     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)
tbl2 <- all_ (track chinookDb)
     pure (tbl1, tbl2)
```

--------------------------------

### Initialize SELECT with WITH Clause

Source: https://github.com/haskell-beam/beam/blob/master/docs/user-guide/queries/common-table-expressions.md

This snippet demonstrates the initial setup for using Common Table Expressions (CTEs) with Beam's `selectWith` function. It prepares to run a list of results from a query that will include a WITH clause.

```haskell
runSelectReturningList $ selectWith $ do
```

--------------------------------

### Modify Database Naming Choices

Source: https://github.com/haskell-beam/beam/blob/master/docs/tutorials/tutorial2.md

Illustrates how to use withDbModification and dbModification to customize database and table naming conventions, starting from default settings.

```haskell
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 Int32
                , _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)

```

--------------------------------

### Query Data from Parquet Source

Source: https://github.com/haskell-beam/beam/blob/master/docs/user-guide/backends/beam-duckdb.md

Example of querying data from a Parquet data source using `runBeamDuckDBDebug` and `allFromDataSource_`. This demonstrates fetching the maximum exam score.

```haskell
Just bestScore <-
  runBeamDuckDBDebug putStrLn conn
    $ runSelectReturningOne
      $
        select
          $
            aggregate_ (max_ . _examScore) (allFromDataSource_ (_exams schoolDB))

print bestScore
```

--------------------------------

### Access Table Fields with Concrete Values

Source: https://github.com/haskell-beam/beam/blob/master/docs/user-guide/queries/basic.md

This example shows how to access a field from a concrete table value, contrasting with accessing fields within a query.

```haskell
personFirstName (Person "John" "Smith" 23 "john.smith@example.com" "8888888888" :: Person) :: Text
```

--------------------------------

### Left Join Example

Source: https://github.com/haskell-beam/beam/blob/master/docs/user-guide/queries/relationships.md

Illustrates how to use `leftJoin_` to retrieve all artists and their associated albums, ensuring all artists are included even if they have no albums. The join condition should be written as if a concrete row from the joined table exists.

```haskell
!example chinook
do artist <- all_ (artist chinookDb)
   album  <- leftJoin_ (all_ (album chinookDb)) (\album -> albumArtist album ==. primaryKey artist)
   pure (artist, album)
```

--------------------------------

### SQL2003 T611: Window functions with FILTER

Source: https://github.com/haskell-beam/beam/blob/master/docs/user-guide/queries/advanced-features.md

Combines window functions with FILTER clauses. This example calculates the average total of invoices by the same customer, filtered by invoices billed to a Los Angeles address, over a specified window frame.

```haskell
!example chinook t611
withWindow_ (\i -> frame_ (partitionBy_ (invoiceCustomer i)) noOrder_ noBounds_)
            (\i w -> (i, avg_ (invoiceTotal i) `filterWhere_` (addressCity (invoiceBillingAddress i) ==. just_ "Los Angeles") `over_` w))
            (all_ (invoice chinookDb))
```

--------------------------------

### Cast Data Type with `cast_`

Source: https://github.com/haskell-beam/beam/blob/master/docs/user-guide/expressions.md

Use `cast_` to convert an expression to a specified datatype. This example filters invoice lines where the quantity starts with '2'.

```haskell
!example chinook
filter_ (\ln -> cast_ (invoiceLineQuantity ln) (varchar Nothing) `like_` "2%") $
  all_ (invoiceLine chinookDb)
```

--------------------------------

### Build Documentation with Nix

Source: https://github.com/haskell-beam/beam/blob/master/README.md

Use Nix to build the project's documentation. The `-L` flag provides detailed output.

```console
nix build .#docs
```

```console
nix build .#docs -L
```

--------------------------------

### Implement Window Functions with `withWindow_`

Source: https://context7.com/haskell-beam/beam/llms.txt

Introduce SQL window (analytic) functions using `withWindow_`. Define window frames with `frame_`, `partitionBy_`, `orderPartitionBy_`, and apply aggregate functions using `over_`.

```haskell
import Database.Beam

-- Average invoice total per customer alongside each invoice row
invoicesWithCustomerAvg :: Q Sqlite ChinookDb s
  (InvoiceT (QExpr Sqlite s), QExpr Sqlite s (Maybe Scientific))
invoicesWithCustomerAvg =
  withWindow_
    (\i -> frame_ (partitionBy_ (invoiceCustomer i)) noOrder_ noBounds_)
    (\i w -> (i, avg_ (invoiceTotal i) `over_` w))
    (all_ (invoice chinookDb))
```

```haskell
-- Rank invoices globally and within each customer, by total
invoicesRanked :: Q Sqlite ChinookDb s
  (InvoiceT (QExpr Sqlite s), QExpr Sqlite s Int32, QExpr Sqlite s Int32)
invoicesRanked =
  withWindow_
    (\i -> ( frame_ noPartition_ (orderPartitionBy_ (asc_ (invoiceTotal i))) noBounds_ 
           , frame_ (partitionBy_ (invoiceCustomer i)) (orderPartitionBy_ (asc_ (invoiceTotal i))) noBounds_ ))
    (\i (allInvoices, customerInvoices) ->
       (i, as_ @Int32 rank_ `over_` allInvoices, as_ @Int32 rank_ `over_` customerInvoices))
    (all_ (invoice chinookDb))
```

--------------------------------

### Delete Invoices with More Than Five Lines (Postgres)

Source: https://github.com/haskell-beam/beam/blob/master/docs/user-guide/manipulation/delete.md

This example demonstrates deleting invoices that have more than five associated invoice lines. It uses a subquery with `aggregate_` and `countAll_` to count the invoice lines. Note: This specific example was originally provided for SQLite and may violate foreign key constraints on other backends.

```haskell
runDelete $ delete (invoice chinookDb) \
  (\i -> 5 <. subquery_ (aggregate_ (\_ -> as_ @Int32 countAll_) $
                         invoiceLines i))
```

--------------------------------

### BeamSqlBackend Info

Source: https://github.com/haskell-beam/beam/blob/master/docs/user-guide/queries/basic.md

Demonstrates how to inspect the BeamSqlBackend typeclass in GHCi to understand available SQL backends and their syntax.

```haskell
Prelude Database.Beam Database.Beam.Sqlite Data.Text Database.SQLite.Simple Lens.Micro Data.Time Database.Beam.Backend.SQL T> :info BeamSqlBackend
ass (Database.Beam.Backend.Types.BeamBackend be,
     IsSql92Syntax (BeamSqlBackendSyntax be),
     Sql92SanityCheck (BeamSqlBackendSyntax be),
     HasSqlValueSyntax (BeamSqlBackendValueSyntax be) Bool,
     HasSqlValueSyntax (BeamSqlBackendValueSyntax be) SqlNull,
     Eq (BeamSqlBackendExpressionSyntax be)) =>
    BeamSqlBackend be
	-- Defined at /Users/travis/Projects/beam/beam-core/Database/Beam/Backend/SQL.hs:212:1
stance BeamSqlBackend Sqlite
-- Defined at /Users/travis/Projects/beam/beam-sqlite/Database/Beam/Sqlite/Connection.hs:157:10
stance (IsSql92Syntax syntax, Sql92SanityCheck syntax,
        HasSqlValueSyntax (Sql92ValueSyntax syntax) Bool,
        HasSqlValueSyntax (Sql92ValueSyntax syntax) SqlNull,
        Eq (Sql92ExpressionSyntax syntax)) =>
       BeamSqlBackend (MockSqlBackend syntax)
-- Defined at /Users/travis/Projects/beam/beam-core/Database/Beam/Backend/SQL.hs:238:10
```

--------------------------------

### Insert Sample Data and Retrieve IDs

Source: https://github.com/haskell-beam/beam/blob/master/docs/tutorials/tutorial3.md

Inserts sample user, address, and product data into the database using `runInsertReturningList`. This function allows retrieval of inserted rows, including auto-incremented IDs, which are crucial for establishing relationships between entities.

```haskell
let users@[james, betty, sam] =
          [ User "james@example.com" "James" "Smith"  "b4cc344d25a2efe540adbf2678e2304c" {- james -}
          , User "betty@example.com" "Betty" "Jones"  "82b054bd83ffad9b6cf8bdb98ce3cc2f" {- betty -}
          , User "sam@example.com"   "Sam"   "Taylor" "332532dcfaa1cbf61e2a266bd723612c" {- sam -} ]
    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) ]

    products = [ Product default_ (val_ "Red Ball") (val_ "A bright red, very spherical ball") (val_ 1000)
               , Product default_ (val_ "Math Textbook") (val_ "Contains a lot of important math theorems and formulae") (val_ 2500)
               , Product default_ (val_ "Intro to Haskell") (val_ "Learn the best programming language in the world") (val_ 3000)
               , Product default_ (val_ "Suitcase") "A hard durable suitcase" 15000 ]

(jamesAddress1, bettyAddress1, bettyAddress2, redBall, mathTextbook, introToHaskell, suitcase) <- 
  runBeamSqliteDebug putStrLn conn $ do
    runInsert $ insert (shoppingCartDb ^. shoppingCartUsers) $ 
```

--------------------------------

### Create SQLite Table

Source: https://github.com/haskell-beam/beam/blob/master/docs/user-guide/backends.md

Use `sqlite-simple` to execute a raw SQL CREATE TABLE command. Beam itself does not provide DDL support; use `beam-migrate` for schema management.

```text
Prelude Schema> execute_ conn "CREATE TABLE persons ( first_name TEXT NOT NULL, last_name TEXT NOT NULL, age INT NOT NULL, PRIMARY KEY(first_name, last_name) )"
```

--------------------------------

### Implement oneToMany_ using join_

Source: https://github.com/haskell-beam/beam/blob/master/docs/user-guide/queries/relationships.md

Shows how the `oneToMany_` relationship can be implemented using the more general `join_` construct with a specific join condition.

```haskell
oneToMany_ rel getKey tbl =
  join_ rel (\rel -> getKey rel ==. pk tbl)
```

--------------------------------

### Filter Customers by Name Prefix

Source: https://github.com/haskell-beam/beam/blob/master/docs/user-guide/queries/select.md

Selects customers whose first name starts with 'Jo' using the `filter_` and `like_` functions.

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

--------------------------------

### Create and Query SQLite Database

Source: https://github.com/haskell-beam/beam/blob/master/docs/user-guide/backends.md

Open a connection to a SQLite database and run a select query using `runBeamSqlite` and `runSelectReturningList`.

```haskell
Prelude Database.Beam.Sqlite> basics <- open "basics.db"
Prelude Database.Beam.Sqlite> runBeamSqlite basics $ runSelectReturningList (select (all_ (persons exampleDb)))
```

--------------------------------

### Run Backend Benchmark

Source: https://github.com/haskell-beam/beam/blob/master/README.md

Execute individual backend benchmarks using the cabal command. Replace 'beam-duckdb', 'beam-postgres', or 'beam-sqlite' with the desired backend.

```bash
cabal -v0 bench beam-backend-benck:beam-duckdb
```

```bash
cabal -v0 bench beam-backend-benck:beam-postgres
```

```bash
cabal -v0 bench beam-backend-benck:beam-sqlite
```

--------------------------------

### Use Subqueries with `subquery_`

Source: https://github.com/haskell-beam/beam/blob/master/docs/user-guide/expressions.md

Convert a query into an expression using `subquery_`. This example updates track prices based on the average track duration.

```haskell
!example chinookdml
runUpdate $ update (track chinookDb)
  (\track' -> trackUnitPrice track' <-. current_ (trackUnitPrice track') / 2)
  (\track' ->
    let avgTrackDuration = aggregate_ (avg_ . trackMilliseconds) (all_ $ track chinookDb)
    in just_ (trackMilliseconds track') <. subquery_ avgTrackDuration
  )
```

--------------------------------

### Create SQLite Database Schema

Source: https://github.com/haskell-beam/beam/blob/master/docs/tutorials/tutorial1.md

Use the sqlite3 command-line tool to create a new database file and define the schema for the 'cart_users' table.

```console
sqlite3 shoppingcart1.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> 

```

--------------------------------

### Get Unique Results with DISTINCT

Source: https://github.com/haskell-beam/beam/blob/master/docs/user-guide/queries/select.md

Employ the `nub_` command to retrieve unique results from a query, analogous to SQL's `SELECT DISTINCT`.

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

--------------------------------

### Pagination with limit_ and offset_

Source: https://context7.com/haskell-beam/beam/llms.txt

Implement SQL `LIMIT` and `OFFSET` using `limit_` and `offset_`. For `Maybe`-valued bounds, use `limitMaybe_` and `offsetMaybe_`.

```haskell
import Database.Beam

pagedUsers :: Q Sqlite ShoppingCartDb s (UserT (QExpr Sqlite s))
pagedUsers =
  limit_ 10 $ offset_ 20 $
  orderBy_ (asc_ . _userFirstName) $
  all_ (_shoppingCartUsers shoppingCartDb)

-- Generated SQL:
-- SELECT ... FROM "shopping_cart_users" AS "t0"
-- ORDER BY "t0"."first_name" ASC LIMIT 10 OFFSET 20
```

--------------------------------

### Select Users and Their Orders with Left Join

Source: https://github.com/haskell-beam/beam/blob/master/docs/tutorials/tutorial3.md

Demonstrates how to query users and their associated orders using a left join. This ensures all users are included, even those without orders.

```haskell
usersAndOrders <-
  runBeamSqliteDebug putStrLn conn $ 
    runSelectReturningList $ 
    select $ do

```

--------------------------------

### Inspect Inserted Data

Source: https://github.com/haskell-beam/beam/blob/master/docs/tutorials/tutorial3.md

Examine a returned record to verify that default values have been correctly assigned. This example shows the structure of an `Address` record after insertion.

```haskell
Prelude Database.Beam Database.Beam.Sqlite Data.Time Database.SQLite.Simple Data.Text Lens.Micro> jamesAddress1
Address {_addressId = 1, _addressLine1 = "123 Little Street", _addressLine2 = Nothing, _addressCity = "Boston", _addressState = "MA", _addressZip = "12345", _addressForUser = UserId "james@example.com"}
```

--------------------------------

### Delete Records in Beam

Source: https://github.com/haskell-beam/beam/blob/master/docs/tutorials/tutorial2.md

Use `runDelete` to remove records from a table based on specified criteria. This example deletes Betty's place in Houston.

```haskell
!employee2sql sql

runBeamSqliteDebug putStrLn conn $ 
  runDelete $ 
  delete (shoppingCartDb ^. shoppingCartUserAddresses)
         (\(address) -> address ^. addressCity ==. "Houston" &&.
                      _addressForUser address `references_` betty)

```

--------------------------------

### Calculate Average Invoice Total per Customer

Source: https://github.com/haskell-beam/beam/blob/master/docs/user-guide/queries/window-functions.md

Use `withWindow_` to define a window partitioned by customer and calculate the average invoice total for each customer.

```haskell
withWindow_ (\i -> frame_ (partitionBy_ (invoiceCustomer i)) noOrder_ noBounds_)
            (\i w -> (i, avg_ (invoiceTotal i) `over_` w))
            (all_ (invoice chinookDb))
```

--------------------------------

### Implement SqlValueSyntax for Custom Type

Source: https://github.com/haskell-beam/beam/blob/master/docs/user-guide/custom-type-migration.md

Provide an instance for HasSqlValueSyntax to allow your custom type to be used in SQL queries. This example uses autoSqlValueSyntax for String-based types.

```haskell
import qualified Data.Text as T

instance HasSqlValueSyntax be String => HasSqlValueSyntax be ShippingCarrier where
  sqlValueSyntax = autoSqlValueSyntax
```

--------------------------------

### Select Using Functor Instance

Source: https://github.com/haskell-beam/beam/blob/master/docs/user-guide/queries/select.md

Shows an alternative way to project columns using the Functor instance of the Q monad.

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

--------------------------------

### Query All Users from SQLite Database

Source: https://github.com/haskell-beam/beam/blob/master/docs/tutorials/tutorial1.md

Retrieve all user records from the 'cart_users' table using a Beam select statement and print them to the console. This example uses `runBeamSqliteDebug` for visibility.

```haskell
let allUsers = all_ (_shoppingCartUsers shoppingCartDb)

runBeamSqliteDebug putStrLn conn $ do
  users <- runSelectReturningList $ select allUsers
  mapM_ (liftIO . putStrLn . show) users

```

--------------------------------

### Haskell Beam: Insert or Do Nothing on Conflict (SQLite/Postgres)

Source: https://github.com/haskell-beam/beam/blob/master/docs/user-guide/manipulation/insert.md

Example of using `insertOnConflict` with `anyConflict` and `onConflictDoNothing` to insert a customer if they don't already exist. This is useful for ensuring data uniqueness.

```haskell
let
  newCustomer = Customer 42 "John" "Doe" Nothing (Address (Just "Street") (Just "City") (Just "State") Nothing Nothing) Nothing Nothing "john.doe@johndoe.com" nothing_

runInsert $ 
  insertOnConflict (customer chinookDb) (insertValues [newCustomer])
     anyConflict
     onConflictDoNothing
```

--------------------------------

### Open SQLite Database Connection in Haskell

Source: https://github.com/haskell-beam/beam/blob/master/docs/tutorials/tutorial1.md

Establish a connection to the SQLite database file using the 'Database.SQLite.Simple' library in Haskell.

```haskell
import Database.SQLite.Simple

conn <- open "shoppingcart1.db"

```

--------------------------------

### Using guard_ with SqlBool

Source: https://github.com/haskell-beam/beam/blob/master/beam-core/ChangeLog.md

Demonstrates how to use the `guard_'` function with `SqlBool` expressions, corresponding to the change where functions accepting `Bool` now have `SqlBool` variants.

```haskell
Correspondingly, many functions that took `Bool` expressions now have
corresponding versions that take `SqlBool`. For example, to use `guard_` with a
`SqlBool` expression use `guard_'` (note the prime).
```

--------------------------------

### Force Sub-SELECT Generation with subselect_

Source: https://github.com/haskell-beam/beam/blob/master/docs/tutorials/tutorial3.md

This example explicitly forces the generation of sub-SELECT statements using the subselect_ combinator. It achieves the same result as the previous snippet but with explicit control over subquery creation.

```haskell
shippingInformationByUser <- 
  runBeamSqliteDebug putStrLn conn $ 
    runSelectReturningList $ 
    select $ 
    do user <- all_ (shoppingCartDb ^. shoppingCartUsers)

       (userEmail, unshippedCount) <- 
         subselect_ $ 
         aggregate_ (
           (userEmail, order) -> (group_ userEmail, as_ @Int32 (count_ (_orderId order)))
         ) $ 
         do user  <- all_ (shoppingCartDb ^. shoppingCartUsers)
            order <- leftJoin_ (all_ (shoppingCartDb ^. shoppingCartOrders))
                               (
                                 order -> _orderForUser order `references_` user &&. isNothing_ (_orderShippingInfo order))
            pure (pk user, order)

       guard_ (userEmail `references_` user)

       (userEmail, shippedCount) <- 
         subselect_ $ 
         aggregate_ (
           (userEmail, order) -> (group_ userEmail, as_ @Int32 (count_ (_orderId order)))
         ) $ 
         do user  <- all_ (shoppingCartDb ^. shoppingCartUsers)
            order <- leftJoin_ (all_ (shoppingCartDb ^. shoppingCartOrders))
                               (
                                 order -> _orderForUser order `references_` user &&. isJust_ (_orderShippingInfo order))
            pure (pk user, order)
       guard_ (userEmail `references_` user)

       pure (user, unshippedCount, shippedCount)

mapM_ print shippingInformationByUser
```

--------------------------------

### Update Multiple Addresses in Beam

Source: https://github.com/haskell-beam/beam/blob/master/docs/tutorials/tutorial2.md

Use `runUpdate` to modify multiple rows in a table. This example updates the city and ZIP code for all addresses in 'Sugarland, TX' and then selects the updated addresses.

```haskell
!employee2sql sql
!employee2out output

addresses <-
  runBeamSqliteDebug putStrLn conn $ 
    do runUpdate $ 
         update (shoppingCartDb ^. shoppingCartUserAddresses)
                (\(address) -> mconcat
                             [ 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

```

--------------------------------

### SQL2003 Null Ordering

Source: https://github.com/haskell-beam/beam/blob/master/docs/user-guide/queries/advanced-features.md

Enables nulls to appear before or after non-null values in sort ordering. This example sorts employees by state ascending, and then by city descending, with null cities appearing last.

```haskell
!example chinook t611
limit_ 10 $
orderBy_ (\e -> (asc_ (addressState (employeeAddress e)), nullsLast_ (desc_ (addressCity (employeeAddress e))))) $
all_ (employee chinookDb))
```

--------------------------------

### Run SELECT Query

Source: https://github.com/haskell-beam/beam/blob/master/docs/user-guide/queries/select.md

A general template for running a SELECT query and returning a list of results.

```haskell
withConnectionTutorial $ runSelectReturningList $ select $ <query>
```

--------------------------------

### Custom Type Marshalling with `HasSqlValueSyntax` and `FromBackendRow`

Source: https://context7.com/haskell-beam/beam/llms.txt

Implement `HasSqlValueSyntax` for serialization and `FromBackendRow` for deserialization to use custom Haskell types as database columns. This example uses `Show` and `Read` instances for marshalling.

```haskell
import Database.Beam.Backend.SQL
import Database.Beam.Sqlite

data ShippingCarrier = USPS | FedEx | UPS | DHL
  deriving (Show, Read, Eq, Ord, Enum)

-- Serialize using Show instance (stores as string)
instance HasSqlValueSyntax be String => HasSqlValueSyntax be ShippingCarrier where
  sqlValueSyntax = autoSqlValueSyntax

-- Deserialize using Read instance
instance FromBackendRow Sqlite ShippingCarrier where
  fromBackendRow = read . T.unpack <$> fromBackendRow

-- Now ShippingCarrier can be used as a Columnar field:
data ShippingInfoT f = ShippingInfo
  { _shippingInfoId             :: C f Int32
  , _shippingInfoCarrier        :: C f ShippingCarrier  -- custom type
  , _shippingInfoTrackingNumber :: C f Text
  }
  deriving (Generic, Beamable)

-- Insert:
-- runInsert $ insert (shoppingCartDb ^. shoppingCartShippingInfos) $
--   insertExpressions [ShippingInfo default_ (val_ USPS) (val_ "TRACK123")]
```

--------------------------------

### HAVING Clause Floated Out Due to Monadic Structure

Source: https://github.com/haskell-beam/beam/blob/master/docs/user-guide/queries/aggregates.md

When a filtered aggregate is part of a monadic join, the filter might be floated out to a WHERE clause if the compiler cannot guarantee it only depends on aggregate results. This example shows such a scenario.

```haskell
do track_ <- all_ (track chinookDb)
   (genre, priceCnt, trackLength) <-
            filter_ (\(genre, distinctPriceCount, totalTrackLength) -> totalTrackLength >=. 300000) $ \
            aggregate_ (\(genre, track) ->
                          ( group_ genre
                          , as_ @Int32 $ countOver_ distinctInGroup_ (trackUnitPrice track)
                          , fromMaybe_ 0 (sumOver_ allInGroupExplicitly_ (trackMilliseconds track)) `div_` 1000 )) $ \
            ((,) <$> all_ (genre chinookDb) <*> all_ (track chinookDb))
   guard_ (trackGenreId track_ ==. just_ (pk genre))
   pure (genre, track_, priceCnt, trackLength)
```

--------------------------------

### Enable GHC Extensions for Deriving Instances

Source: https://github.com/haskell-beam/beam/blob/master/docs/tutorials/tutorial1.md

Enable necessary GHC extensions to derive `Show` and `Eq` instances for the `User` type synonym.

```haskell
> :set -XStandaloneDeriving -XTypeSynonymInstances -XMultiParamTypeClasses
```

--------------------------------

### Insert with ON CONFLICT updating only specific fields

Source: https://github.com/haskell-beam/beam/blob/master/docs/user-guide/backends/beam-postgres.md

Use `Pg.onConflictUpdateInstead` to specify that only certain fields should be updated upon a conflict. This example updates only the first and last names when a primary key conflict occurs.

```haskell
-- import qualified Database.Beam.Postgres as Pg
let
  newCustomer = Customer 42 "John" "Doe" Nothing (Address (Just "Street") (Just "City") (Just "State") Nothing Nothing) Nothing Nothing "john.doe@johndoe.com" nothing_

runInsert $ Pg.insert (customer chinookDb) (insertValues [newCustomer]) $ Pg.onConflict
  (Pg.conflictingFields primaryKey)
  (Pg.onConflictUpdateInstead
     (\c -> ( customerFirstName c
            , customerLastName c )))

```

--------------------------------

### Default Database Settings for ShoppingCartDb

Source: https://github.com/haskell-beam/beam/blob/master/docs/tutorials/tutorial1.md

Generate default database settings for the ShoppingCartDb. Beam can automatically infer table names if selectors are camelCased, otherwise manual naming might be required.

```haskell
shoppingCartDb :: DatabaseSettings be ShoppingCartDb
shoppingCartDb = defaultDbSettings
```

--------------------------------

### Define Connection Utility

Source: https://github.com/haskell-beam/beam/blob/master/docs/user-guide/queries/select.md

Defines a utility function to run Beam SQL queries with debugging output enabled.

```haskell
Prelude Chinook.Schema> let withConnectionTutorial = runBeamSqliteDebug putStrLn chinook
```

--------------------------------

### Create Migration with Custom Type Column

Source: https://github.com/haskell-beam/beam/blob/master/docs/user-guide/custom-type-migration.md

Write a Beam migration function that includes the custom type column. This example demonstrates creating an 'address' table with a 'shipper' column of type ShippingCarrier.

```haskell
-- | Pagila db
data PagilaDb f
  = PagilaDb
  {
    address    :: f (TableEntity AddressT)
  }
  deriving Generic
instance Database PagilaDb

lastUpdateField :: TableFieldSchema PgColumnSchemaSyntax LocalTime
lastUpdateField = field "last_update" timestamp (defaultTo_ now_) notNull

migration :: () -> Migration PgCommandSyntax (CheckedDatabaseSettings Postgres PagilaDb)
migration () = do
--  year_ <- createDomain "year" integer (check (\yr -> yr >=. 1901 &&. yr <=. 2155))
  PagilaDb <$> createTable "address"
                 (AddressT
                    (field "address_id" smallserial)
                    (field "address" (varchar (Just 50)) notNull)
                    (field "address2" (maybeType $ varchar (Just 50)))
                    (field "district" (varchar (Just 20)) notNull)
                    (field "shipper" shippingCarrierType)
                    (field "postal_code" (varchar (Just 10)))
                    (field "phone" (varchar (Just 20)) notNull) lastUpdateField)
```

--------------------------------

### Create Shopping Cart Database Tables

Source: https://github.com/haskell-beam/beam/blob/master/docs/tutorials/tutorial3.md

Executes SQL statements to create the necessary tables for the shopping cart database. This includes tables for users, addresses, products, orders, shipping information, and line items.

```haskell
conn <- open "shoppingcart3.db"

execute_ conn "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 ));"
execute_ conn "CREATE TABLE addresses ( id INTEGER PRIMARY KEY AUTOINCREMENT, address1 VARCHAR NOT NULL, address2 VARCHAR, city VARCHAR NOT NULL, state VARCHAR NOT NULL, zip VARCHAR NOT NULL, for_user__email VARCHAR NOT NULL );"
execute_ conn "CREATE TABLE products ( id INTEGER PRIMARY KEY AUTOINCREMENT, title VARCHAR NOT NULL, description VARCHAR NOT NULL, price INT NOT NULL );"
execute_ conn "CREATE TABLE orders ( id INTEGER PRIMARY KEY AUTOINCREMENT, date TIMESTAMP NOT NULL, for_user__email VARCHAR NOT NULL, ship_to_address__id INT NOT NULL, shipping_info__id INT);"
execute_ conn "CREATE TABLE shipping_info ( id INTEGER PRIMARY KEY AUTOINCREMENT, carrier VARCHAR NOT NULL, tracking_number VARCHAR NOT NULL);"
execute_ conn "CREATE TABLE line_items (item_in_order__id INTEGER NOT NULL, item_for_product__id INTEGER NOT NULL, item_quantity INTEGER NOT NULL)"
```

--------------------------------

### Haskell Beam: Update on Primary Key Conflict (SQLite/Postgres)

Source: https://github.com/haskell-beam/beam/blob/master/docs/user-guide/manipulation/insert.md

Example of using `insertOnConflict` with `conflictingFields` targeting the primary key and `onConflictUpdateSet` to update an existing customer's details. This demonstrates an upsert operation.

```haskell
--! import Database.Beam.Backend.SQL.BeamExtensions (BeamHasInsertOnConflict(..))
let
  newCustomer = Customer 42 "John" "Doe" Nothing (Address (Just "Street") (Just "City") (Just "State") Nothing Nothing) Nothing Nothing "john.doe@johndoe.com" nothing_

runInsert $ 
  insertOnConflict (customer chinookDb) (insertValues [newCustomer])
    (conflictingFields (\tbl -> primaryKey tbl))
    (onConflictUpdateSet (\fields oldValues -> fields <-. val_ newCustomer))
```

--------------------------------

### Import Beam and SQLite Modules

Source: https://github.com/haskell-beam/beam/blob/master/docs/tutorials/tutorial1.md

Imports the core Beam module and the Beam SQLite backend module, along with the Text type for string handling.

```haskell
import Database.Beam
import Database.Beam.Sqlite

import Data.Text (Text)
```

--------------------------------

### Create Default Database Descriptor

Source: https://github.com/haskell-beam/beam/blob/master/docs/user-guide/databases.md

Use `defaultDbSettings` to create a backend-agnostic database descriptor. This assigns default names to all entities based on your data model.

```haskell
exampleDb :: DatabaseSettings be ExampleDb
exampleDb = defaultDbSettings
```

--------------------------------

### Insert with ON CONFLICT on a Specific Constraint

Source: https://github.com/haskell-beam/beam/blob/master/docs/user-guide/backends/beam-postgres.md

Perform an insert operation that handles conflicts on a specific named constraint using `Pg.conflictingConstraint`. This example updates the row when a conflict occurs on the `"PK_CUSTOMER"` constraint.

```haskell
--! import Database.Beam.Backend.SQL.BeamExtensions (BeamHasInsertOnConflict(..))
--! import qualified Database.Beam.Postgres as Pg
let
  newCustomer = Customer 42 "John" "Doe" Nothing (Address (Just "Street") (Just "City") (Just "State") Nothing Nothing) Nothing Nothing "john.doe@johndoe.com" nothing_

runInsert $ insertOnConflict (customer chinookDb) (insertValues [newCustomer])
  (Pg.conflictingConstraint "PK_Customer")
  (onConflictUpdateSet (\fields _ -> fields <-. val_ newCustomer))
```

--------------------------------

### Simplified MonadBeam Type for Postgres

Source: https://github.com/haskell-beam/beam/blob/master/beam-core/ChangeLog.md

Illustrates the simplified type for `MonadBeam` when targeting a Postgres database, allowing for easier integration with monad transformers.

```haskell
dbComputation :: MonadBeam Postgres m => m result
```