**Course Title:** Functional Programming with Haskell: From Fundamentals to Advanced Concepts **Section Title:** Working with Databases and Web Services in Haskell **Topic:** Connecting to and querying relational databases (PostgreSQL, SQLite) **Objective:** By the end of this topic, you will be able to connect to and query relational databases using Haskell, including PostgreSQL and SQLite. You will understand how to use Haskell database libraries, execute queries, and retrieve data from databases. **Connecting to Relational Databases with Haskell** To connect to relational databases with Haskell, we will use the HDBC (Haskell Database Connectivity) library, which provides a generic interface for HDBC drivers, allowing you to query various databases. We will focus on using HDBC with PostgreSQL and SQLite databases. **Prerequisites:** * Install the HDBC package using Cabal: `cabal install HDBC` * Install the HDBC-PostgreSQL or HDBC-SQLite driver, depending on the database you want to connect to: `cabal install HDBC-postgresql` or `cabal install HDBC-sqlite3` **Database Connection** Before querying the database, we need to establish a connection to it. The `connect` function from the HDBC library creates a database connection. ```haskell import Database.HDBC -- Create a database connection connectDB :: IO Connection connectDB = connectPostgreSQL "host=localhost dbname=mydb user=myuser password=mypassword" -- or connectSQLite3 "mydb.db" ``` **Executing Queries** Once the connection is established, you can execute queries using the `run` function from the HDBC library. This function takes the query string and a parameter list as arguments. ```haskell -- Execute a query queryDB :: Connection -> String -> IO [[SqlValue]] queryDB conn query = do stmt <- prepare conn query _ <- executeRaw conn [stmt] fetchAllRows stmt ``` **Querying with PostgreSQL** Let's create a PostgreSQL database and execute some queries. ```haskell -- CREATE TABLE users ( -- id SERIAL PRIMARY KEY, -- name VARCHAR(50) NOT NULL, -- email VARCHAR(100) UNIQUE NOT NULL -- ); main :: IO () main = do conn <- connectDB stmt <- prepare conn "INSERT INTO users (name, email) VALUES (?, ?)" execute conn stmt [toSql "John Doe", toSql "johndoe@example.com"] rows <- queryDB conn "SELECT * FROM users WHERE email = ?" mapM_ (putStrLn . show) rows disconnect conn ``` **Querying with SQLite** For SQLite, create a similar database and execute some queries. ```haskell -- CREATE TABLE users ( -- id INTEGER PRIMARY KEY, -- name TEXT NOT NULL, -- email TEXT UNIQUE NOT NULL -- ); main :: IO () main = do conn <- connectDB stmt <- prepare conn "INSERT INTO users (name, email) VALUES (?, ?)" execute conn stmt [toSql "John Doe", toSql "johndoe@example.com"] rows <- queryDB conn "SELECT * FROM users WHERE email = ?" mapM_ (putStrLn . show) rows disconnect conn ``` **Advice and Next Steps:** * Remember to close the database connection when you're done with it using `disconnect`. * Use prepared statements to avoid SQL injection attacks. * Investigate other Haskell database libraries, such as Persistent, which provides a high-level interface for database access. * In the next topic, we will explore consuming and serving RESTful APIs using Servant or Yesod. If you have any questions or need help with the material, please leave a comment below. **External Resources:** * [HDBC documentation](https://hackage.haskell.org/package/HDBC-2.4.0.3/docs/Database-HDBC.html) * [HDBC on GitHub](https://github.com/hdbc/hdbc) * [PostgreSQL Haskell driver documentation](https://hackage.haskell.org/package/HDBC-postgresql-2.4.0.4/docs/Database-HDBC-PostgreSQL.html) * [SQLite Haskell driver documentation](https://hackage.haskell.org/package/HDBC-sqlite3-2.4.0.4/docs/Database-HDBC-SQLite3.html)