The MySQL / PostgreSQL Client is responsible for providing an interface for Vert.x applications that need to interact with a MySQL or PostgreSQL database.

It uses Mauricio Linhares open source driver to interact with the MySQL or PostgreSQL databases in a non blocking way

Creating a the client

There are several ways to create a client. Let’s go through them all.

Using default shared pool

In most cases you will want to share a pool between different client instances.

E.g. you scale your application by deploying multiple instances of your verticle and you want each verticle instance to share the same pool so you don’t end up with multiple pools

You do this as follows:

require 'vertx-mysql-postgresql/my_sql_client'
require 'vertx-mysql-postgresql/postgre_sql_client'

# To create a MySQL client:

mySQLClientConfig = {
  'host' => "mymysqldb.mycompany"
}
mySQLClient = VertxMysqlPostgresql::MySQLClient.create_shared(vertx, mySQLClientConfig)

# To create a PostgreSQL client:

postgreSQLClientConfig = {
  'host' => "mypostgresqldb.mycompany"
}
postgreSQLClient = VertxMysqlPostgresql::PostgreSQLClient.create_shared(vertx, postgreSQLClientConfig)

The first call to MySQLClient.createShared or PostgreSQLClient.createShared will actually create the data source, and the specified config will be used.

Subsequent calls will return a new client instance that uses the same data source, so the configuration won’t be used.

Specifying a pool name

You can create a client specifying a pool name as follows

require 'vertx-mysql-postgresql/my_sql_client'
require 'vertx-mysql-postgresql/postgre_sql_client'

# To create a MySQL client:

mySQLClientConfig = {
  'host' => "mymysqldb.mycompany"
}
mySQLClient = VertxMysqlPostgresql::MySQLClient.create_shared(vertx, mySQLClientConfig, "MySQLPool1")

# To create a PostgreSQL client:

postgreSQLClientConfig = {
  'host' => "mypostgresqldb.mycompany"
}
postgreSQLClient = VertxMysqlPostgresql::PostgreSQLClient.create_shared(vertx, postgreSQLClientConfig, "PostgreSQLPool1")

If different clients are created using the same Vert.x instance and specifying the same pool name, they will share the same data source.

The first call to MySQLClient.createShared or PostgreSQLClient.createShared will actually create the data source, and the specified config will be used.

Subsequent calls will return a new client instance that uses the same pool, so the configuration won’t be used.

Use this way of creating if you wish different groups of clients to have different pools, e.g. they’re interacting with different databases.

Creating a client with a non shared data source

In most cases you will want to share a pool between different client instances. However, it’s possible you want to create a client instance that doesn’t share its pool with any other client.

require 'vertx-mysql-postgresql/my_sql_client'
require 'vertx-mysql-postgresql/postgre_sql_client'

# To create a MySQL client:

mySQLClientConfig = {
  'host' => "mymysqldb.mycompany"
}
mySQLClient = VertxMysqlPostgresql::MySQLClient.create_non_shared(vertx, mySQLClientConfig)

# To create a PostgreSQL client:

postgreSQLClientConfig = {
  'host' => "mypostgresqldb.mycompany"
}
postgreSQLClient = VertxMysqlPostgresql::PostgreSQLClient.create_non_shared(vertx, postgreSQLClientConfig)

This is equivalent to calling MySQLClient.createShared or PostgreSQLClient.createShared with a unique pool name each time.

Closing the client

You can hold on to the client for a long time (e.g. the life-time of your verticle), but once you have finished with it, you should close it using close or close

Getting a connection

Use getConnection to get a connection.

This will return the connection in the handler when one is ready from the pool.

# Now do stuff with it:

client.get_connection() { |res,res_err|
  if (res_err == nil)

    connection = res

    # Got a connection

  else
    # Failed to get connection - deal with it
  end
}

Once you’ve finished with the connection make sure you close it afterwards.

The connection is an instance of SQLConnection which is a common interface used by othe SQL clients.

You can learn how to use it in the common sql interface documentation.

Note about date and timestamps

Whenever you get dates back from the database, this service will implicitly convert them into ISO 8601 (yyyy-MM-ddTHH:mm:ss.SSS) formatted strings. MySQL usually discards milliseconds, so you will regularly see .000.

Configuration

Both the PostgreSql and MySql clients take the same configuration:

{
  "host" : <your-host>,
  "port" : <your-port>,
  "maxPoolSize" : <maximum-number-of-open-connections>,
  "username" : <your-username>,
  "password" : <your-password>,
  "database" : <name-of-your-database>
}
host

The host of the database. Defaults to localhost.

port

The port of the database. Defaults to 5432 for PostgreSQL and 3306 for MySQL.

maxPoolSize

The number of connections that may be kept open. Defaults to 10.

username

The username to connect to the database. Defaults to postgres for PostgreSQL and root for MySQL.

password

The password to connect to the database. Default is not set, i.e. it uses no password.

database

The name of the database you want to connect to. Defaults to test.