Database.Persist.MySQL

A MySQL backend for persistent.

Documentation

withMySQLPool

Arguments

:: (MonadIO m, MonadLogger m, MonadBaseControl IO m, IsSqlBackend backend)

=> ConnectInfo

Connection information.

-> Int

Number of connections to be kept open in the pool.

-> (Pool backend -> m a)

Action to be executed that uses the connection pool.

-> m a

Create a MySQL connection pool and run the given action. The pool is properly released after the action finishes using it. Note that you should not use the given ConnectionPool outside the action since it may be already been released.

withMySQLConn

Arguments

:: (MonadBaseControl IO m, MonadIO m, MonadLogger m, IsSqlBackend backend)

=> ConnectInfo

Connection information.

-> (backend -> m a)

Action to be executed that uses the connection.

-> m a

Same as withMySQLPool, but instead of opening a pool of connections, only one connection is opened.

createMySQLPool

Arguments

:: (MonadBaseControl IO m, MonadIO m, MonadLogger m, IsSqlBackend backend)

=> ConnectInfo

Connection information.

-> Int

Number of connections to be kept open in the pool.

-> m (Pool backend)

Create a MySQL connection pool. Note that it's your responsibility to properly close the connection pool when unneeded. Use withMySQLPool for automatic resource control.

module Database.Persist.Sql

data ConnectInfo :: *

Constructors

ConnectInfo

Fields

• connectHost :: String
• connectPort :: Word16
• connectUser :: String
• connectPassword :: String
• connectDatabase :: String
• connectOptions :: [Option]
• connectPath :: FilePath
• connectSSL :: Maybe SSLInfo

Instances

Eq ConnectInfo

Methods

(==) :: ConnectInfo -> ConnectInfo -> Bool

(/=) :: ConnectInfo -> ConnectInfo -> Bool

Read ConnectInfo

Methods

readsPrec :: Int -> ReadS ConnectInfo

readList :: ReadS [ConnectInfo]

readPrec :: ReadPrec ConnectInfo

readListPrec :: ReadPrec [ConnectInfo]

Show ConnectInfo

Methods

showsPrec :: Int -> ConnectInfo -> ShowS

show :: ConnectInfo -> String

showList :: [ConnectInfo] -> ShowS

data SSLInfo :: *

Constructors

SSLInfo

Fields

• sslKey :: FilePath
• sslCert :: FilePath
• sslCA :: FilePath
• sslCAPath :: FilePath
• sslCiphers :: String

  Comma-separated list of cipher names.

Instances

Eq SSLInfo

Methods

(==) :: SSLInfo -> SSLInfo -> Bool

(/=) :: SSLInfo -> SSLInfo -> Bool

Read SSLInfo

Methods

readsPrec :: Int -> ReadS SSLInfo

readList :: ReadS [SSLInfo]

readPrec :: ReadPrec SSLInfo

readListPrec :: ReadPrec [SSLInfo]

Show SSLInfo

Methods

showsPrec :: Int -> SSLInfo -> ShowS

show :: SSLInfo -> String

showList :: [SSLInfo] -> ShowS

defaultConnectInfo :: ConnectInfo

Default information for setting up a connection.

Defaults are as follows:

• Server on localhost
• User root
• No password
• Database test
• Character set utf8

Use as in the following example:

connect defaultConnectInfo { connectHost = "db.example.com" }

defaultSSLInfo :: SSLInfo

Default (empty) information for setting up an SSL connection.

data MySQLConf

Information required to connect to a MySQL database using persistent's generic facilities. These values are the same that are given to withMySQLPool.

Constructors

MySQLConf

Fields

• myConnInfo :: ConnectInfo

  The connection information.

• myPoolSize :: Int

  How many connections should be held on the connection pool.

Instances

Show MySQLConf

Methods

showsPrec :: Int -> MySQLConf -> ShowS

show :: MySQLConf -> String

showList :: [MySQLConf] -> ShowS

FromJSON MySQLConf

Methods

parseJSON :: Value -> Parser MySQLConf

parseJSONList :: Value -> Parser [MySQLConf]

PersistConfig MySQLConf

Associated Types

type PersistConfigBackend MySQLConf :: (* -> *) -> * -> *

type PersistConfigPool MySQLConf :: *

Methods

loadConfig :: Value -> Parser MySQLConf

applyEnv :: MySQLConf -> IO MySQLConf

createPoolConfig :: MySQLConf -> IO (PersistConfigPool MySQLConf)

runPool :: (MonadBaseControl IO m, MonadIO m) => MySQLConf -> PersistConfigBackend MySQLConf m a -> PersistConfigPool MySQLConf -> m a

type PersistConfigPool MySQLConf

type PersistConfigPool MySQLConf = ConnectionPool

type PersistConfigBackend MySQLConf

type PersistConfigBackend MySQLConf = SqlPersistT

mockMigration :: Migration -> IO ()

Mock a migration even when the database is not present. This function will mock the migration for a database even when the actual database isn't already present in the system.

insertOnDuplicateKeyUpdate :: (backend ~ PersistEntityBackend record, PersistEntity record, MonadIO m, PersistStore backend, BackendCompatible SqlBackend backend) => record -> [Update record] -> ReaderT backend m ()

MySQL specific upsert. This will prevent multiple queries, when one will do.

insertManyOnDuplicateKeyUpdate

Arguments

:: (backend ~ PersistEntityBackend record, BackendCompatible SqlBackend backend, PersistEntity record, MonadIO m)

=> [record]

A list of the records you want to insert, or update

-> [SomeField record]

A list of the fields you want to copy over.

-> [Update record]

A list of the updates to apply that aren't dependent on the record being inserted.

-> ReaderT backend m ()

Do a bulk insert on the given records in the first parameter. In the event that a key conflicts with a record currently in the database, the second and third parameters determine what will happen.

The second parameter is a list of fields to copy from the original value. This allows you to specify which fields to copy from the record you're trying to insert into the database to the preexisting row.

The third parameter is a list of updates to perform that are independent of the value that is provided. You can use this to increment a counter value. These updates only occur if the original record is present in the database.

More details on SomeField usage

The [SomeField] parameter allows you to specify which fields (and under which conditions) will be copied from the inserted rows. For a brief example, consider the following data model and existing data set:

Item
  name        Text
  description Text
  price       Double Maybe
  quantity    Int Maybe

  Primary name

items:
+------+-------------+-------+----------+
| name | description | price | quantity |
+------+-------------+-------+----------+
| foo  | very good   |       |    3     |
| bar  |             |  3.99 |          |
+------+-------------+-------+----------+

This record type has a single natural key on itemName. Let's suppose that we download a CSV of new items to store into the database. Here's our CSV:

name,description,price,quantity
foo,,2.50,6
bar,even better,,5
yes,wow,,

We parse that into a list of Haskell records:

records = 
  [ Item { itemName = "foo", itemDescription = ""
         , itemPrice = Just 2.50, itemQuantity = Just 6
         }
  , Item "bar" "even better" Nothing (Just 5)
  , Item "yes" "wow" Nothing Nothing
  ]

The new CSV data is partial. It only includes updates from the upstream vendor. Our CSV library parses the missing description field as an empty string. We don't want to override the existing description. So we can use the copyUnlessEmpty function to say: "Don't update when the value is empty."

Likewise, the new row for bar includes a quantity, but no price. We do not want to overwrite the existing price in the database with a NULL value. So we can use copyUnlessNull to only copy the existing values in.

The final code looks like this: insertManyOnDuplicateKeyUpdate records [ copyUnlessEmpty ItemDescription , copyUnlessNull ItemPrice , copyUnlessNull ItemQuantity ] []

Once we run that code on the datahase, the new data set looks like this:

items:
+------+-------------+-------+----------+
| name | description | price | quantity |
+------+-------------+-------+----------+
| foo  | very good   |  2.50 |    6     |
| bar  | even better |  3.99 |    5     |
| yes  | wow         |       |          |
+------+-------------+-------+----------+

data SomeField record where

This type is used to determine how to update rows using MySQL's INSERT ON DUPLICATE KEY UPDATE functionality, exposed via insertManyOnDuplicateKeyUpdate in the library.

Since: 2.6.2

Constructors

SomeField :: EntityField record typ -> SomeField record

Copy the field directly from the record.

copyUnlessNull :: PersistField typ => EntityField record (Maybe typ) -> SomeField record

Copy the field into the database only if the value in the corresponding record is non-NULL.

@since 2.6.2

copyUnlessEmpty :: (Monoid typ, PersistField typ) => EntityField record typ -> SomeField record

Copy the field into the database only if the value in the corresponding record is non-empty, where "empty" means the Monoid definition for mempty. Useful for Text, String, ByteString, etc.

The resulting SomeField type is useful for the insertManyOnDuplicateKeyUpdate function.

@since 2.6.2

copyUnlessEq :: PersistField typ => EntityField record typ -> typ -> SomeField record

Copy the field into the database only if the field is not equal to the provided value. This is useful to avoid copying weird nullary data into the database.

The resulting SomeField type is useful for the insertManyOnDuplicateKeyUpdate function.

@since 2.6.2