connecting to h2 from non-java languages

[Adam McMahon]

Hi Group,

i am considering a project that may require me to connect to an H2 database froma non-JVM language (in this case php). I know the postgres driver might work and querus is an option, but I would like to consider a more general option: an http connector.

It should be pretty easy to create a HTTP-JDBC bridge (perhaps an afternoon of work) that accepts a a post request (with a json payload describing the query) and return a json array of the results.  A simple servlet would do the trick that acts as a type of proxy to an underlying JDBC connection.

My question is:

[1] Perhaps this is already done in the web-console?  Is there documentation for how the server of the web-console could be used as a more general API over HTTP for H2?

[2] Do you think anyone else would have use for this outside of my private projects?  I could fairly easily create a standalone sever (perhaps using embedded jetty) that would allow someone to  extend an H2 database over http. A few security things would need to be worked out, but it seems straight-forward.  This may be nice as it would open H2 to a variety of non JDBC languages (node.js, perl, php), using simple REST-like http requests...thoughts?

-Adam

[Thomas Mueller Graf]

Hi,

[1] I'm afraid the web-console doesn't use JSON or any other "standard" format (it would be nice thought).

[2] This I can't say.

Regards,

Thomas

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscribe@googlegroups.com.
To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

[Christian MICHON]

on point 2: it can be easily done with spark (sparkjava.com not apache spark) + jooq

You'll need to ensure security, using json web tokens for example.

[Adam McMahon]

>>You'll need to ensure security, using json web tokens for example

I have never used spark, looks fun (bad name for a project considering apache spark dominates the "spark" name). I was thinking Jetty would be pretty ideal, and/or restlet.  I also like Grizzly.  Vertx. is great, but maybe overkill and would require too many dependencies.

I was thinking that the http server could optionally be run using an SSL connector (JSSE SSL).  Most micro-server frameworks make pretty easy to use ssl.  This should sufficiently  handle security over the wire.

The format for querying the http server could be something like this in JSON:

{

    user: 'dbUserName',

    pass: 'password',

    sql: "slect * from users where name=? AND isIdiot=?",

    params: ['Adam', true]

}

the response would just be an json array of rows.

The server could be run standalone something like this:

java -jar httpH2.jar  -options <options.json>

The options.json would contain information like follwoing:

- path to DB:port

 -user name, password of h2 database

- embedded or TCP  (if embedded, the http server would run H2, else it will simply connect to an existing H2 TCP server over JDBC

- ip whitelist - list of ips that can access the http server

 -http port 

 - etc

[Christian MICHON]

Spark contains an embedded jetty out of the box, and it's compatible with SSL and keystores.

I might contribute code if you open a github project. I usually handle this "proxy" access to H2 using jruby, but it's straight forward to make the concept you described in java.

Christian

[Adam McMahon]

Hi,

How does jRuby help in this situation?  Is there a particually nice jRuby http server that you use to wrap h2?

-Adam

[Christian MICHON]

Jruby was used to write an proxy data API server using Sequel and Roda. Since it's ruby inside java, connections to H2 are fully supported via Sequel.

For the container, I used Puma.

[Steve McLeod]

+1 for Spark. It is one of the few tech products that is truthful when it says, "you'll have your first example running in five minutes".

The solution you propose would involve would establish a connection for each query? I'd imagine that would not perform too well. 

I suggest establishing a connection once with one API call, which returns a token. Then use that token with future queries instead of supplying the credentials with each API call.

[Manfred Rosenboom]

Maybe this project is what you are looking for

https://github.com/bjornharrtell/jdbc-http-server

Best,
Manfred

[Adam McMahon]

>> The solution you propose would involve would establish a connection for each query? I'd imagine that would not perform too well.

I agree that it would not perform too well to establish a new connection at each query.  Instead, I plan on using "long-lived" jdbc connections.  At a client's first http query, it creates a JDBC connection, if successful, it adds that connection to a map and keeps it "alive".  Every so often the connection is refreshed, and unused connections are reaped by a background thread.

>>I suggest establishing a connection once with one API call, which returns a token. Then use that token with future queries instead of supplying the credentials with each API call.

That is a good idea bout the token, but I am thinking about doing the following:  I suggest requiring the user/pass at each request (which makes each request the same format), and I grab the long-lived connection from the map keyed to String(userName+pass+ipadress).  This way a partciular client will aways get his connection back (assuming it has not been refreshed or reaped), and even if a different client uses the same username and password, they will still have their own connections (because the map of connection is keyed also to the client's ip address).  In short: the http server maintains a set of connection pools (with default size of 1) for each client that connects, and it reaps the connections periodically.

>> Maybe this project is what you are looking for
>>https://github.com/bjornharrtell/jdbc-http-server

This is interesting, thanks for sharing it.  But, there are a few limitations to that project (if I understand it correctly)

1.  It does not use standard "raw" sql for select/updates/insert/etc.  Instead, it uses a custom url (parameter based) format that, while being very REST-like, is non standard.

2.  According to the doc, the non-standard format can't handle more complex queries

3.  Does not take advantage of prepared statements.

Instead, I suggest a format that uses normal sql and allows prepared statements, which is delivered via a json string in the HTTP post, example:

{
    user: 'dbUserName',
    pass: 'password',

    sql: "select * from users where name=? AND isIdiot=?",
    params: ['Adam', true]
}

returns the result, something like:

[
{
    name: "Adam",
    isIdiot: true,
    country: "USA",
    language: "English"
}
]