DOCSEARCHbyAlgolia
Submit Docs

Beam Documentation

repository·master·Indexed 20 days ago

https://github.com/haskell-beam/beam

Beam is a type-safe Haskell library for interacting with relational databases, providing a high-level DSL for writing SQL queries verified at compile-time. It features a modular architecture with pluggable backends including beam-postgres, beam-sqlite, beam-mysql, beam-firebird, and beam-duckdb. The ecosystem includes beam-migrate for type-safe SQL DDL and schema generation, and supports standard SQL numeric and character string type mappings.

Tokens
45.2K
Snippets
146
Records
198
Agent score
67%

What's inside Beam

  1. What is Beam?

    master

    Beam is a highly-general, type-safe Haskell library for accessing relational databases. It acts as a source of truth for your database schema, marshals data between Haskell and the database, and generates SQL from Haskell expressions.

    Key Features

    • No Template Haskell: Beam relies entirely on the GHC type system, making types easier to infer and reason about.
    • Schema Generation: Easy generation of Haskell types from existing databases.
    • Migration Infrastructure: Includes beam-migrate for managing schema versions.
    • SQL Support: Supports most SQL92, SQL99, and SQL2003 features (aggregations, subqueries, window functions).
    • Extensible: Backends can be developed and shipped independently of the core library.
    • Query Syntax: Uses a Q monad that feels similar to the standard list [] monad.

  2. What is Beam?

    master

    Beam is a type-safe Haskell interface to relational databases. It allows you to write queries using a natural monadic syntax that is checked at compile-time.

    Key features include:

    • Type-safe queries: Verified at compile-time using the Haskell type system.
    • Predictable performance: Closely matches SQL semantics.
    • Pluggable backends: Support for various RDBMS (e.g., beam-mysql, beam-firebird, beam-postgres, beam-sqlite) via independent backend packages.
    • Standard Haskell modeling: Database entities like tables are modeled using standard Haskell code without requiring Template Haskell.
    • Human-readable SQL: The generated SQL queries are easy to read.

    Note on Connection Management: Beam does not handle connection or transaction management. Instead, it relies on the appropriate Haskell interface library for your chosen backend (e.g., postgresql-simple for beam-postgres). This allows you to mix Beam queries with direct driver calls seamlessly.

  3. Overview of beam-migrate

    master

    The beam-migrate package provides SQL DDL (Data Definition Language) support for the Beam ecosystem. It allows developers to use Beam's type-safe syntax to write schema generation code.

    Key capabilities include:

    • Type-safe Schema Generation: Using Beam syntax to implement SQL DDL via backend-specific type classes.
    • Schema Introspection: Tools to introspect existing Beam schemas.
    • Automatic Migration Generation: Support for generating migrations in both SQL and Haskell formats.

    Note: This is primarily a low-level support library intended for writing custom tooling for DDL manipulation or for implementing migration support within Beam backends.

  4. Overview of beam-duckdb capabilities

    master

    beam-duckdb is a Beam backend for the DuckDB analytics database. It extends beam-core by allowing you to query data sources that are not standard database tables as if they were regular tables.

    Currently supported data sources include:

    • Parquet files
    • Apache Iceberg tables
    • CSV files

  5. Implement recursive queries with `union_` and `reuse`

    master

    Beam supports recursive queries (often used for iterative logic like Fibonacci sequences) by combining selecting, union_, and reuse.

    A recursive CTE typically consists of:

    1. A base case defined using pure and selecting.
    2. A recursive step that uses reuse to refer to the CTE itself, combined with union_ to append new rows.
    3. A termination condition, often implemented using guard_.

    Warning: Do not attempt to express infinite recursion using pure Haskell laziness; the query generator will loop during serialization because RDBMS engines do not support Haskell-style laziness. Use the database's recursive capabilities instead.

    void $ runSelectReturningList $ selectWith $ do
  rec fib <- selecting (pure (as_ @Int32 0, as_ @Int32 1) `union_`
                        (do (a, b) <- reuse fib
                            guard_ (b <. 1000)
                            pure (b, a + b)))
  pure (reuse fib)

  6. Understand the `Q` data type for building queries

    master

    Beam queries are constructed using the Q monad. It represents a complete query (like a SELECT statement) and is parameterized as follows:

    data Q be db s a

    • be: The Beam backend (e.g., Sqlite from beam-sqlite or Postgres from beam-postgres).
    • db: The database type, ensuring you only query entities within the correct scope.
    • s: The scope parameter, used internally by Beam to ensure field scoping at runtime.
    • a: The type of the query result.

    Because Q is a monad, you can use Functor, Applicative, and Monad operations to build complex queries, such as creating projections or performing JOINs.

    data Q be db s a

  7. Use DataSourceEntity to query external files in DuckDB

    master

    In beam-duckdb, you can treat external files (like Parquet, Iceberg, or CSV) as if they were regular database tables by using the DataSourceEntity type.

    Key characteristics:

    • Read-only: The type system prevents INSERT or UPDATE operations on a DataSourceEntity.
    • Querying: Instead of using all_ (for tables) or allFromView_ (for views), you must use allFromDataSource_ to select data from these entities.
    • Mapping: You can use modifyDataSourceFields to map the file's internal column names to your Beam table schema.

    To declare a data source, wrap the file declaration (e.g., parquet, icebergTable, or csv) in a dataSource call within your DatabaseSettings.

    -- Example of querying a data source
Just bestScore <-
  runBeamDuckDBDebug putStrLn conn
    $ runSelectReturningOne
      $ select
        $ aggregate_
            (max_ . _examScore)
            (allFromDataSource_ (_exams schoolDB))

  8. Understand Beam's SQL standard compatibility

    master

    Beam aims to cover the breadth of relevant SQL standards, specifically SQL-92, SQL:1999, SQL:2003, SQL:2008, and SQL:2011.

    Key principles of Beam's compatibility:

    • Generic Implementation: beam-core implements SQL features in a generic manner. If a standard feature is missing from beam-core, it is considered unsupported.
    • Purposeful Omissions: Some features are omitted if no major RDBMS implements them (e.g., database-level assertions).
    • Extensibility: If you require a feature that is not currently supported, you can file an issue to request support. When doing so, provide specific use cases, examples, and a testing strategy.
    • Contribution Opportunities: Features marked as TODO in compatibility documentation are active areas for potential contribution.

  9. Perform aggregations and groupings

    master

    Aggregations are performed using the aggregate_ function.

    • Counting: Use countAll_ to count rows. Since countAll_ can unmarshal into any Integral type, use as_ @Type (e.g., as_ @Int32) to specify the desired return type.
    • Grouping: To group data, include a group_ expression within the lambda passed to aggregate_.
    • Execution: For aggregations that return a single value, use runSelectReturningOne.

    Example of counting all users:

    let userCount = aggregate_ (\u -> as_ @Int32 countAll_) (all_ (_shoppingCartUsers shoppingCartDb))

runBeamSqliteDebug putStrLn conn $ do
  Just c <- runSelectReturningOne $ select userCount
  liftIO $ putStrLn ("We have " ++ show c ++ " users in the database")

    Example of grouping by a field and counting:

    let numberOfUsersByName = aggregate_ (\u -> (group_ (_userFirstName u), as_ @Int32 countAll_)) $
                          all_ (_shoppingCartUsers shoppingCartDb)

runBeamSqliteDebug putStrLn conn $ do
  countedByName <- runSelectReturningList $ select numberOfUsersByName
  mapM_ (liftIO . putStrLn . show) countedByName

  10. Handle Null Values in Beam

    master

    Beam supports SQL NULL values through Haskell's Maybe type. To work with nullable columns, use Maybe column types and the Nullable wrapper. You can manipulate these values in queries using just_, nothing_, and maybe_ functions.

    -- Use Maybe for nullable columns
-- Use just_, nothing_, and maybe_ for query expressions

  11. How to use aggregate_ for grouping and computing aggregates

    master

    The aggregate_ function allows you to group a result set and compute aggregates within those groups. It functions similarly to Haskell's groupBy but is designed for SQL generation.

    To use aggregate_, you provide an underlying query and an aggregation projection. The projection can be:

    • A value of type QAgg syntax s a (an aggregate expression).
    • A value of type QGroupExpr syntax s a (a grouping expression).
    • A tuple containing these types.

    During query generation:

    • Expressions of type QGroupExpr are added to the GROUP BY clause.
    • Expressions of type QAgg are treated as aggregation computations.
    • The result of aggregate_ lifts these specialized types into regular QExpr values, allowing them to be used in subsequent expressions.
    aggregate_ (\_ -> as_ @Int32 countAll_) (all_ (genre chinookDb))

  12. Perform left joins in Beam queries

    master

    Use leftJoin_ to include all rows from the primary table, even if there is no matching row in the joined table. When a match is not found, the joined table's fields will be returned as Nothing (wrapped in a Maybe).

    To filter results based on the presence or absence of a joined row, use isNothing_ or isJust_ within a guard_ statement.

    usersAndOrders <-
  runBeamSqliteDebug putStrLn conn $
    runSelectReturningList $
    select $ do
      user  <- all_ (shoppingCartDb ^. shoppingCartUsers)
      order <- leftJoin_ (all_ (shoppingCartDb ^. shoppingCartOrders)) (\order -> _orderForUser order `references_` user)
      pure (user, order)