mysql-haskell
pure haskell MySQL driver
https://github.com/winterland1989/mysql-haskell
Version on this page: | 0.8.4.2 |
LTS Haskell 22.37: | 1.1.5 |
Stackage Nightly 2024-10-11: | 1.1.5 |
Latest on Hackage: | 1.1.5 |
mysql-haskell-0.8.4.2@sha256:aa4187f97f7003b001fa790d4a30556361815349bdb4423e8fb3cb2f07a305de,3489
Module documentation for 0.8.4.2
- Database
- Database.MySQL
- Database.MySQL.Base
- Database.MySQL.BinLog
- Database.MySQL.BinLogProtocol
- Database.MySQL.Connection
- Database.MySQL.Protocol
- Database.MySQL.TLS
- Database.MySQL
mysql-haskell
mysql-haskell
is a MySQL driver written entirely in haskell.
Is it fast?
In short, select
(decode) is about 1.5 times slower than pure c/c++ but 5 times faster than mysql-simple
, insert
(encode) is about 1.5 times slower than pure c/c++, there’re many factors involved(tls, prepared statment, batch using multiple statement):
Above figures showed the time to:
- perform a “select * from employees” from a sample table
- insert 1000 rows into a 29-columns table per thread with auto-commit off.
The benchmarks are run by my MacBook Pro 13’ 2015.
Motivation
While MySQL may not be the most advanced sql database, it’s widely used among China companies, including but not limited to Baidu, Alibaba, Tecent etc., but haskell’s MySQL support is not ideal, we only have a very basic MySQL binding written by Bryan O’Sullivan, and some higher level wrapper built on it, which have some problems:
-
lack of prepared statment and binary protocol support.
-
limited concurrency due to FFI.
-
no replication protocol support.
mysql-haskell
is intended to solve these problems, and provide foundation for higher level libraries such as groundhog and persistent, so that accessing MySQL is both fast and easy in haskell.
Guide
The Database.MySQL.Base
module provides everything you need to start making queries:
{-# LANGUAGE OverloadedStrings #-}
module Main where
import Database.MySQL.Base
import qualified System.IO.Streams as Streams
main :: IO ()
main = do
conn <- connect
defaultConnectInfo {ciUser = "username", ciPassword = "password", ciDatabase = "dbname"}
(defs, is) <- query_ conn "SELECT * FROM some_table"
print =<< Streams.toList is
query/query_
will return a column definition list, and an InputStream
of rows, you should consume this stream completely before start new queries.
It’s recommanded to use prepared statement to improve query speed:
...
s <- prepareStmt conn "SELECT * FROM some_table where person_age > ?"
...
(defs, is) <- queryStmt conn s [MySQLInt32U 18]
...
If you want to do batch inserting/deleting/updating, you can use executeMany
to save considerable time.
The Database.MySQL.BinLog
module provides binlog listenning functions and row-based event decoder, following program will automatically get last binlog position, and print every row event it receives:
{-# LANGUAGE LambdaCase #-}
module Main where
import Control.Monad (forever)
import qualified Database.MySQL.BinLog as MySQL
import qualified System.IO.Streams as Streams
main :: IO ()
main = do
conn <- MySQL.connect
MySQL.defaultConnectInfo
{ MySQL.ciUser = "username"
, MySQL.ciPassword = "password"
, MySQL.ciDatabase = "dbname"
}
MySQL.getLastBinLogTracker conn >>= \ case
Just tracker -> do
es <- MySQL.decodeRowBinLogEvent =<< MySQL.dumpBinLog conn 1024 tracker False
forever $ do
Streams.read es >>= \ case
Just v -> print v
Nothing -> return ()
Nothing -> error "can't get latest binlog position"
Build Test Benchmark
Just use the old way:
git clone https://github.com/winterland1989/mysql-haskell.git
cd mysql-haskell
cabal install --enable-tests --only-dependencies
cabal build
Running tests require:
- A local MySQL server, a user
testMySQLHaskell
and a databasetestMySQLHaskell
, you can do it use following script:
mysql -u root -e "CREATE DATABASE IF NOT EXISTS testMySQLHaskell;"
mysql -u root -e "CREATE USER 'testMySQLHaskell'@'localhost' IDENTIFIED BY ''"
mysql -u root -e "GRANT ALL PRIVILEGES ON testMySQLHaskell.* TO 'testMySQLHaskell'@'localhost'"
mysql -u root -e "FLUSH PRIVILEGES"
- Enable binlog by adding
log_bin = filename
tomy.cnf
or add--log-bin=filename
to the server, and grant replication access totestMySQLHaskell
with:
mysql -u root -e "GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'testMySQLHaskell'@'localhost';"
-
Set
binlog_format
toROW
. -
Set
max_allowed_packet
to larger than 256M(for test large packet).
New features will be automatically tested by inspecting MySQL server’s version, travis is keeping an eye on following combinations:
- CABALVER=1.18 GHCVER=7.8.4 MYSQLVER=5.5
- CABALVER=1.22 GHCVER=7.10.2 MYSQLVER=5.5
- CABALVER=1.24 GHCVER=8.0.1 MYSQLVER=5.5
- CABALVER=1.24 GHCVER=8.0.1 MYSQLVER=5.6
- CABALVER=1.24 GHCVER=8.0.1 MYSQLVER=5.7
Please reference .travis.yml
if you have problems with setting up test environment.
Enter benchmark directory and run ./bench.sh
to benchmark 1) c++ version 2) mysql-haskell 3) FFI version mysql, you may need to:
- Modify
bench.sh
(change the include path) to get c++ version compiled. - Modify
mysql-haskell-bench.cabal
(change the openssl’s lib path) to get haskell version compiled. - Setup MySQL’s TLS support, modify
MySQLHaskellOpenSSL.hs/MySQLHaskellTLS.hs
to change the CA file’s path, and certificate’s subject name. - Adjust rts options
-N
to get best results.
With -N10
on my company’s 24-core machine, binary protocol performs almost identical to c version!
Reference
MySQL official site provided intensive document, but without following project, mysql-haskell
may not be written at all:
License
Copyright (c) 2016, winterland1989
All rights reserved.
Redistribution and use in source and binary forms, with or without modification, are permitted provided that the following conditions are met:
* Redistributions of source code must retain the above copyright
notice, this list of conditions and the following disclaimer.
* Redistributions in binary form must reproduce the above
copyright notice, this list of conditions and the following
disclaimer in the documentation and/or other materials provided
with the distribution.
* Neither the name of winterland1989 nor the names of other
contributors may be used to endorse or promote products derived
from this software without specific prior written permission.
THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS “AS IS” AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
Changes
Revision history for mysql-haskell
0.8.4.2 – 2019-01-22
- Fix stackage#4312: Relax
network
bounds.
0.8.4.1 – 2018-10-23
- Relax
tasty
version bound to build with latest stackage. #26
0.8.4.0 – 2018-10-23
0.8.3.0 – 2017-10-09
- Remove unnecessary exports from
Database.MySQL.Base
. - Reuse TCP connection when using TLS.
- Clean up some compiler warnings.
0.8.2.0 – 2017-10-09
Courtesy of naushadh, mysql-haskell
will be on stackage again.
- Update to use
tcp-streams-1.x
. - Fix compatibility with new
tls/memory
version.
0.8.1.0 – 2016-11-09
- Add
Show
instance toConnectInfo
. - Add proper version bound for
binary
.
0.8.0.0 – 2016-11-09
- Add
ciCharset
field to supportutf8mb4
charset. - Add
BitMap
field toCOM_STMT_EXECUTE
, and #8 by alexbiehl.
0.7.1.0 – 2016-11-21
- Add
QueryParam
class andParam
datatype for multi-valued parameter(s) by naushadh.
0.7.0.0 – 2016-11-09
- Split openssl support to mysql-haskell-openssl.
- Expose
Database.MySQL.Connection
module due to this split, it shouldn’t be used by user directly.
0.6.0.0 – 2016-10-25
- Use binary-ieee754 for older binary compatibility.
- Clean up
Database.MySQL.Protocol.MySQLValue
’s export.
0.5.1.0 – 2016-10-20
- Add
queryVector
,queryVector_
andqueryStmtVector
. - Use binary-parsers to speed up binary parsers.
0.5.0.0 – 2016-8-22
- Export exception types.
- Fix a regression cause password authentication failed, add tests.
- Fix a reading order bug cause ‘prepareStmt/prepareStmtDetail’ failed.
0.4.0.0 – 2016-8-22
- Enable TLS support via
tls
package, add benchmarks.
0.3.0.0 – 2016-8-22
- Fix tls connection, change TLS implementation to HsOpenSSL, add benchmarks.
- Fix a bug in ‘putLenEncInt’ which cause sending large field fail.
- Various optimizations.
0.2.0.0 – 2016-8-19
- Fix OK packet decoder.
- Fix sending large packet(>16M).
- Add
executeMany
,withTransaction
to Base module. - Add timestamp field to
RowBinLogEvent
. - Add test, add insert benchmark.
0.1.0.0 – 2016-8-16
- First version. Released on an unsuspecting world.