Vert.x 2.x is deprecated - use instead http://vertx.io/docs/vertx-mysql-postgresql-client/java/
mod-mysql-postgresql
This Vert.x module uses the https://github.com/mauricio/postgresql-async drivers to support a fully async module for MySQL and PostgreSQL.
Requirements
- Vert.x 2.1+ (with Scala language module v1.1.0+)
- A working PostgreSQL or MySQL server
- For testing PostgreSQL: A
testdb
database on a local PostgreSQL install and a user calledvertx
- For testing MySQL: A
testdb
database on a local MySQL install and a user calledroot
Installation
Depending on your Scala version, you should download the specific version. If you're using Scala 2.10.x:
vertx install io.vertx~mod-mysql-postgresql_2.10~0.3.1
If you're using Scala 2.11.x:
vertx install io.vertx~mod-mysql-postgresql_2.11~0.3.1
If you get a "not found" exception, you might need to edit the repos.txt of your Vert.x installation to use https. See issue 35 (thanks, @dparshin!).
If you get java.lang.ClassNotFoundException: org.vertx.scala.core.VertxAccess$class
please update your langs.properties
scala entry to:
scala=io.vertx~lang-scala_2.10~1.1.0-M1:org.vertx.scala.platform.impl.ScalaVerticleFactory
If you're using Scala in your own project and want to use Scala 2.11, you can change lang-scala_2.10
to lang-scala_2.11
.
Configuration
{
"address" : <event-bus-addres-to-listen-on>,
"connection" : <MySQL|PostgreSQL>,
"host" : <your-host>,
"port" : <your-port>,
"maxPoolSize" : <maximum-number-of-open-connections>,
"username" : <your-username>,
"password" : <your-password>,
"database" : <name-of-your-database>
}
address
- The address this module should register on the event bus. Defaults tocampudus.asyncdb
connection
- The database you want to use. Defaults toPostgreSQL
.host
- The host of the database. Defaults tolocalhost
.port
- The port of the database. Defaults to5432
for PostgreSQL and3306
for MySQL.maxPoolSize
- The number of connections that may be kept open. Defaults to10
.username
- The username to connect to the database. Defaults topostgres
for PostgreSQL androot
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 totestdb
.
Usage
All commands are relatively similar. Use JSON with the action
field and add the needed parameters for each command.
There are only a few commands available currently, but in theory you should be able to invoke any command on the database with the raw
action.
Reply messages
The module will reply to all requests. In the message, there will be either a "status" : "ok"
or a "status" : "error"
. If the request could be processed without problems, it will result in an "ok" status. See an example here:
{
"status" : "ok",
"rows" : 2,
"message" : "SELECT 2",
"fields" : [ "name", "email", "is_male", "age", "money", "wedding_date" ],
"results" : [
["Mr. Test", "[email protected]", true, 32, 123.45, "2014-04-04"],
["Mrs. Test", "[email protected]", false, 16, 543.21, "2022-02-22"]
]
}
rows
gives you the number of rows affected by the statement sent to the server. Bear in mind that PostgreSQL 8.4 only shows a row count on changed rows (DELETE, UPDATE, INSERT statements) whereas PostgreSQL 9.x and MySQL also show the number of SELECTed rows here.message
is a status message from the server.fields
contains the list of fields of the selected table - Only present if the request resulted in a result set.results
contains a list of rows - Only present if the request resulted in a result set.
If the request resulted in an error, a possible reply message looks like this:
{
"status" : "error",
"message" : "column \"ager\" does not exist"
}
insert
Use this action to insert new rows into a table. You need to specify a table, the fields to insert and an array of rows to insert. The rows itself are an array of values.
{
"action" : "insert",
"table" : "some_test",
"fields" : ["name", "email", "is_male", "age", "money", "wedding_date"],
"values" : [
["Mr. Test", "[email protected]", true, 32, 123.45, "2014-04-04"],
["Mrs. Test", "[email protected]", false, 16, 543.21, "2022-02-22"]
]
}
select
The select
action creates a SELECT
statement to get a projection from a table. You can filter the columns by providing a fields
array. If you omit the fields
array, it selects every column available in the table.
{
"action" : "select",
"table" : "some_test",
"fields" : ["name", "email", "is_male", "age", "money", "wedding_date"], // Optional
}
prepared
Creates a prepared statement and lets you fill the ?
with values.
{
"action" : "prepared",
"statement" : "SELECT * FROM some_test WHERE name=? AND money > ?",
"values" : ["Mr. Test", 15]
}
raw - Raw commands
Use this action to send arbitrary commands to the database. You should be able to submit any query or insertion with this command.
Here is an example for creating a table in PostgreSQL:
{
"action" : "raw",
"command" : "CREATE TABLE some_test (
id SERIAL,
name VARCHAR(255),
email VARCHAR(255),
is_male BOOLEAN,
age INT,
money FLOAT,
wedding_date DATE
);"
}
And if you want to drop it again, you can send the following:
{
"action" : "raw",
"command" : "DROP TABLE some_test;"
}
Transactions
These commands let you begin a transaction and send an arbitrary number of statements within the started transaction. You can then commit or rollback the transaction. Nested transactions are not possible until now!
Remember to reply to the messages after you send the begin
command. Look in the docs how this works (e.g. for Java: http://vertx.io/core_manual_java.html#replying-to-messages). With replying to the messages, the module is able to send all statements within the same transaction. If you don't reply within the timeoutTransaction
interval, the transaction will automatically fail and rollback.
transaction begin
This command starts a transaction. You get an Ok message back to which you can then reply with more statements.
{
"action" : "begin"
}
transaction commit
To commit a transaction you have to send the commit
command.
{
"action" : "commit"
}
transaction rollback
To rollback a transaction you have to send the rollback
command.
{
"action" : "rollback"
}
Example for a transaction
Here is a small example on how a transaction works.
{
"action" : "begin"
}
This will start the transaction. You get this response:
{
"status" : "ok"
}
You can then reply to this message with the commands select
, prepared
, insert
and raw
. A possible reply could be this:
{
"action" : "raw",
"command" : "UPDATE some_test SET email = '[email protected]' WHERE id = 1"
}
You get a reply back depending on the statement you sent. In this case the answer would be:
{
"status" : "ok",
"rows" : 1,
"message" : "UPDATE 1"
}
If you want to make more statements you just have to reply to this message again with the next statement. When you have done all statements you can commit
or rollback
the transaction.
{
"action" : "commit"
}
If everything worked, the last answer will be:
{
"status" : "ok"
}
old transaction command (deprecated, use the new transaction mechanism with begin and commit)
Takes several statements and wraps them into a single transaction for the server to process. Use statement : [...actions...]
to create such a transaction. Only select
, insert
and raw
commands are allowed right now.
{
"action" : "transaction",
"statements" : [
{
"action" : "insert",
"table" : "account",
"fields" : ["name", "balance"],
"values" : ["Mr. Test", "0"]
},
{
"action" : "raw",
"command" : "UPDATE account SET balance=balance+1 WHERE name='Mr. Test'",
},
{
"action" : "prepared",
"statement" : "UPDATE account SET balance=balance+? WHERE name=?",
"values" : [25, 'Mr. Test']
}
]
}
Planned actions
You can always use raw
to do anything on the database. If the statement is a query, it will return its results just like a select
.
The select
and insert
commands are just for you to be able to have a cross-database application in the end. If you do not use raw
, these commands should create the needed statements for you.
update
- Updates rows of a tabledelete
- Deletes rows from a tablecreate
- Creates a tabledrop
- Drops a table
These actions are currently not available, but they should be implemented in the future. Please see the following examples and send feedback:
{ // UPDATE some_test SET age=age+1 WHERE id=1
"action" : "update",
"table" : "some_test",
"set" : {
"age" : {$add : 1}
},
"conditions" : {
"$eq" {
"id" : 1
}
}
}
{ // DELETE FROM some_test WHERE id = 5
"action" : "delete",
"table" : "some_test",
"conditions" : {
"$eq" {
"id" : 5
}
}
}
// SELECT name, email FROM some_test WHERE is_male=? AND money >= ?
{
"action" : "prepared",
"statement" : "SELECT name, email FROM some_test WHERE is_male=? AND money >= ?",
"values" : [true,100]
}
// CREATE TABLE some_test (
// id SERIAL,
// name VARCHAR(255),
// email VARCHAR(255),
// age INTEGER
// );
{
"action" : "create",
"table" : "some_test",
"fields" : ["id PRIMARY KEY", "name VARCHAR(255)", "email VARCHAR(255)", "age INTEGER"]
}
{ // DROP TABLE some_test
"action" : "drop",
"table" : "some_test",
}