We can PostgreSQL on Arch Linux for our Haskell projects with packages like persistent and persistent-postgresql setting up involves several steps: installing and setting up PostgreSQL, configuring your Haskell environment, and modifying your Haskell code to connect to PostgreSQL instead of SQLite.
Additionally look into the esqueleto package for advanced SQL-specific support.
First, ensure that PostgreSQL is installed on your Arch Linux system.
sudo pacman -Syu postgresqlAfter installation, you need to initialize the PostgreSQL database cluster.
sudo -iu postgres initdb --locale $LANG -E UTF8 -D /var/lib/postgres/dataStart the PostgreSQL service and enable it to start automatically on boot.
sudo systemctl enable postgresql --nowSwitch to the postgres user to create a new PostgreSQL role and database.
sudo -iu postgresCreate a new PostgreSQL user (replace yourusername and yourpassword with your desired credentials):
createuser yourusername --interactiveYou’ll be prompted to answer some questions. For most Haskell applications, you might want to grant the user superuser privileges or specific privileges as needed.
Set a password for the new user:
psql
ALTER USER yourusername WITH PASSWORD 'yourpassword';
\qCreate a new database (typically the same name as your project or user):
createdb yourdatabaseExit the postgres user session:
exitBy default, PostgreSQL may be configured to allow only local connections. You might need to modify the pg_hba.conf file to allow your application to connect.
Edit the pg_hba.conf file:
sudo nano /var/lib/postgres/data/pg_hba.confEnsure you have a line like the following to allow local connections using MD5 (password):
# TYPE DATABASE USER ADDRESS METHOD
local all all md5Similarly, if you need to allow TCP/IP connections, ensure listen_addresses is set appropriately in postgresql.conf:
sudo nano /var/lib/postgres/data/postgresql.confSet listen_addresses to '*' to allow connections from any IP (use with caution):
listen_addresses = '*'After making changes, restart PostgreSQL:
sudo systemctl restart postgresqlEnsure that you have the necessary Haskell libraries installed for interacting with PostgreSQL via persistent.
Add the following dependencies to your .cabal file or package.yaml:
dependencies:
- persistent
- persistent-postgresql
- postgresql-simpleAlternatively, if you’re using stack, add them to your package.yaml under dependencies.
Then, update your package list:
cabal updateor for stack:
stack updateModify your Haskell code to use PostgreSQL instead of SQLite. Here’s a basic example of how to set up persistent with PostgreSQL.
a. Define Your Models
In your models file (commonly named models.hs or similar), ensure you have your entities defined. For example:
{-# LANGUAGE GADTs #-}
{-# LANGUAGE GeneralizedNewtypeDeriving #-}
{-# LANGUAGE TypeFamilies #-}
{-# LANGUAGE TemplateHaskell #-}
{-# LANGUAGE QuasiQuotes #-}
{-# LANGUAGE MultiParamTypeClasses #-}
{-# LANGUAGE OverloadedStrings #-}
import Database.Persist.TH
share [mkPersist sqlSettings, mkMigrate "migrateAll"] [persistLowerCase|
User
name String
email String
deriving Show
|]b. Set Up the Connection
In your main application file, set up the PostgreSQL connection. Here’s a simple example using persistent:
{-# LANGUAGE OverloadedStrings #-}
import Control.Monad.IO.Class (liftIO)
import Database.Persist
import Database.Persist.Postgresql
import Database.Persist.TH
import Control.Monad.Logger (runStdoutLoggingT)
import qualified Data.Text as T
-- Import your models
import Models -- Replace with your actual models module
main :: IO ()
main = runStdoutLoggingT $ withPostgresqlPool connStr 10 $ \pool -> liftIO $ do
flip runSqlPersistMPool pool $ do
runMigration migrateAll
-- Example: Insert a user
userId <- insert $ User "John Doe" "john@example.com"
-- Example: Retrieve users
users <- selectList [] []
liftIO $ print (users :: [Entity User])
where
connStr = "host=localhost dbname=yourdatabase user=yourusername password=yourpassword port=5432"c. Environment Variables (Optional but Recommended)
For security reasons, it’s better to manage your database credentials using environment variables instead of hard-coding them.
Modify the connection string to read from environment variables:
import System.Environment (getEnv)
main :: IO ()
main = do
host <- getEnv "DB_HOST"
dbname <- getEnv "DB_NAME"
user <- getEnv "DB_USER"
password <- getEnv "DB_PASSWORD"
let connStr = T.pack $ "host=" ++ host ++
" dbname=" ++ dbname ++
" user=" ++ user ++
" password=" ++ password ++
" port=5432"
runStdoutLoggingT $ withPostgresqlPool connStr 10 $ \pool -> liftIO $ do
flip runSqlPersistMPool pool $ do
runMigration migrateAll
-- Your database operationsThen, set the environment variables in your shell or using a .env file with a tool like direnv.
Before integrating deeply into your application, it’s a good idea to test the connection separately to ensure everything is set up correctly.
You can use psql to test:
psql -h localhost -U yourusername -d yourdatabaseIf you can connect and perform basic operations, your PostgreSQL setup is working correctly.
If you’re transitioning an existing project from SQLite to PostgreSQL:
Backup Your SQLite Data: Export your existing SQLite data, possibly to a format like CSV or SQL.
Import into PostgreSQL: Use psql or tools like pgloader to import the data into your new PostgreSQL database.
Example with pgloader:
pgloader sqlite:///path/to/sqlite.db postgresql://yourusername:yourpassword@localhost/yourdatabaseUpdate Your Haskell Code: Modify the connection settings as shown above to point to PostgreSQL instead of SQLite.
Security: Always ensure that your database credentials are secured and not exposed in your codebase. Use environment variables or secure configuration management.
Performance Tuning: PostgreSQL offers many configuration options for performance tuning. Depending on your application’s needs, you might want to adjust settings in postgresql.conf.
Backup Strategy: Implement regular backups of your PostgreSQL database to prevent data loss.
Monitoring: Use tools like pgAdmin, PostgreSQL Exporter for Prometheus, or other monitoring tools to keep an eye on your database’s health and performance.