= Vert.x MySQL / PostgreSQL client

The Async 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 https://github.com/mauricio/postgresql-async[async driver] to interact with the MySQL
or PostgreSQL databases in a non blocking way

== Using the MySQL and PostgreSQL client

This section describes how to configure your project to be able to use the MySQL / PostgreSQL client in your
application.

=== In a regular application

To use this client, you need to add the following jar to your `CLASSPATH`:

* vertx-mysql-postgresql-client 3.3.2 (the client)
* scala-library 2.11.4
* the postgress-async-2.11 and mysdql-async-2.11 from https://github.com/mauricio/postgresql-async
* joda time

All these jars are downloadable from Maven Central.

=== In an application packaged in a fat jar

If you are building a _Fat-jar_ using Maven or Gradle, just add the following dependencies:

* Maven (in your `pom.xml`):

[source,xml,subs="+attributes"]
----
<dependency>
  <groupId>io.vertx</groupId>
  <artifactId>vertx-mysql-postgresql-client</artifactId>
  <version>3.3.2</version>
</dependency>
----

* Gradle (in your `build.gradle` file):

[source,groovy,subs="+attributes"]
----
compile 'io.vertx:vertx-mysql-postgresql-client:3.3.2'
----

=== In an application using a vert.x distributions

If you are using a vert.x distribution, add the jar files listed above to the `$VERTX_HOME/lib` directory.

Alternatively, you can edit the `vertx-stack.json` file located in `$VERTX_HOME`, and set `"included": true`
for the `vertx-mysql-postgresql-client` dependency. Once done, launch: `vertx resolve --dir=lib --stack=
./vertx-stack.json`. It downloads the client and its dependencies.

== 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:

[source,java]
----
import io.vertx.groovy.ext.asyncsql.MySQLClient
import io.vertx.groovy.ext.asyncsql.PostgreSQLClient

// To create a MySQL client:

def mySQLClientConfig = [
  host:"mymysqldb.mycompany"
]
def mySQLClient = MySQLClient.createShared(vertx, mySQLClientConfig)

// To create a PostgreSQL client:

def postgreSQLClientConfig = [
  host:"mypostgresqldb.mycompany"
]
def postgreSQLClient = PostgreSQLClient.createShared(vertx, postgreSQLClientConfig)


----

The first call to `link:../../groovydoc/io/vertx/groovy/ext/asyncsql/MySQLClient.html#createShared(io.vertx.core.Vertx,%20io.vertx.core.json.JsonObject)[MySQLClient.createShared]`
or `link:../../groovydoc/io/vertx/groovy/ext/asyncsql/PostgreSQLClient.html#createShared(io.vertx.core.Vertx,%20io.vertx.core.json.JsonObject)[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

[source,java]
----
import io.vertx.groovy.ext.asyncsql.MySQLClient
import io.vertx.groovy.ext.asyncsql.PostgreSQLClient

// To create a MySQL client:

def mySQLClientConfig = [
  host:"mymysqldb.mycompany"
]
def mySQLClient = MySQLClient.createShared(vertx, mySQLClientConfig, "MySQLPool1")

// To create a PostgreSQL client:

def postgreSQLClientConfig = [
  host:"mypostgresqldb.mycompany"
]
def postgreSQLClient = PostgreSQLClient.createShared(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 `link:../../groovydoc/io/vertx/groovy/ext/asyncsql/MySQLClient.html#createShared(io.vertx.core.Vertx,%20io.vertx.core.json.JsonObject,%20java.lang.String)[MySQLClient.createShared]`
or `link:../../groovydoc/io/vertx/groovy/ext/asyncsql/PostgreSQLClient.html#createShared(io.vertx.core.Vertx,%20io.vertx.core.json.JsonObject,%20java.lang.String)[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.

In that case you can use `link:../../groovydoc/io/vertx/groovy/ext/asyncsql/MySQLClient.html#createNonShared(io.vertx.core.Vertx,%20io.vertx.core.json.JsonObject)[MySQLClient.createNonShared]`
or `link:../../groovydoc/io/vertx/groovy/ext/asyncsql/PostgreSQLClient.html#createNonShared(io.vertx.core.Vertx,%20io.vertx.core.json.JsonObject)[PostgreSQLClient.createNonShared]`

[source,java]
----
import io.vertx.groovy.ext.asyncsql.MySQLClient
import io.vertx.groovy.ext.asyncsql.PostgreSQLClient

// To create a MySQL client:

def mySQLClientConfig = [
  host:"mymysqldb.mycompany"
]
def mySQLClient = MySQLClient.createNonShared(vertx, mySQLClientConfig)

// To create a PostgreSQL client:

def postgreSQLClientConfig = [
  host:"mypostgresqldb.mycompany"
]
def postgreSQLClient = PostgreSQLClient.createNonShared(vertx, postgreSQLClientConfig)


----

This is equivalent to calling `link:../../groovydoc/io/vertx/groovy/ext/asyncsql/MySQLClient.html#createShared(io.vertx.core.Vertx,%20io.vertx.core.json.JsonObject,%20java.lang.String)[MySQLClient.createShared]`
or `link:../../groovydoc/io/vertx/groovy/ext/asyncsql/PostgreSQLClient.html#createShared(io.vertx.core.Vertx,%20io.vertx.core.json.JsonObject,%20java.lang.String)[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 `link:../../groovydoc/io/vertx/groovy/ext/asyncsql/AsyncSQLClient.html#close(io.vertx.core.Handler)[close]` or
`link:../../groovydoc/io/vertx/groovy/ext/asyncsql/AsyncSQLClient.html#close()[close]`

== Getting a connection

Use `link:../../groovydoc/io/vertx/groovy/ext/asyncsql/AsyncSQLClient.html#getConnection(io.vertx.core.Handler)[getConnection]` to get a connection.

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

[source,java]
----

// Now do stuff with it:

client.getConnection({ res ->
  if (res.succeeded()) {

    def connection = res.result()

    // Got a connection

  } else {
    // Failed to get connection - deal with it
  }
})


----

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

The connection is an instance of `link:../../groovydoc/io/vertx/groovy/ext/sql/SQLConnection.html[SQLConnection]` which is a common interface used by
othe SQL clients.

You can learn how to use it in the http://foobar[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`.

=== Note about last inserted ids

When inserting new rows into a table, you might want to retrieve auto-incremented ids from the database. The JDBC API
usually lets you retrieve the last inserted id from a connection. If you use MySQL, it will work the way it does like
the JDBC API. In PostgreSQL you can add the
http://www.postgresql.org/docs/current/static/sql-insert.html["RETURNING" clause] to get the latest inserted ids. Use
one of the `query` methods to get access to the returned columns.

=== Note about stored procedures

The `call` and `callWithParams` methods are not implemented currently.

== 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>,
  "charset" : <name-of-the-character-set>,
  "queryTimeout" : <timeout-in-milliseconds>
}
----

`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`.
`charset`:: The name of the character set you want to use for the connection. Defaults to `UTF-8`.
`queryTimeout`:: The timeout to wait for a query in milliseconds. Defaults to `10000` (= 10 seconds).