A RESTful JDBC HTTP Server built on top of jOOQ

The jOOQ ecosystem and community is continually growing. We’re personally always thrilled to see other Open Source projects built on top of jOOQ. Today, we’re very happy to introduce you to a very interesting approach at combining REST and RDBMS by Björn Harrtell.

bjorn-harrtellBjörn Harrtell is a swedish programmer since childhood. He is usually busy writing GIS systems and integrations at Sweco Position AB but sometimes he spends time getting involved in Open Source projects and contributing to a few pieces of work related to Open Source projects like GeoTools and OpenLayers. Björn has also initiated a few minor Open Source projects himself and one of the latest projects he’s been working on is jdbc-http-server.

We’re excited to publish Björn’s guest post introducing his interesting work:

JDBC HTTP Server

Ever found yourself writing a lot of REST resources that do simple CRUD against a relational database and felt the code was repeating itself? In that case, jdbc-http-server might be a project worth checking out.

jdbc-http-server exposes a relational database instance as a discoverable REST API making it possible to perform simple CRUD from a browser application without requiring any backend code to be written.

A discoverable REST API means you can access the root resource at / and follow links to subresources from there. For example, let’s say you have a database named testdb with a table named testtable in the public schema you can then do the following operations:

Retrieve (GET), update (PUT) or delete (DELETE) a single row at:

/db/testdb/schemas/public/tables/testtable/rows/1

Retrieve (GET), update (PUT) rows or create a new row (POST) at:

/db/testdb/schemas/public/tables/testtable/rows

The above resources accepts parameters select, where, limit, offset
and orderby where applicable. Examples:

GET a maximum of 10 rows where cost>100 at:

/db/testdb/schemas/public/tables/testtable/rows?where=cost>100&limit=10

jdbc-http-server is database engine agnostic since it utilizes jOOQ to generate SQL in a dialect suited to the target database engine. At the moment H2, PostgreSQL and HSQLDB are covered by automated tests. Currently the only available representation data format is JSON but adding more is an interesting possibility.

Feedback and, of course, contributions are welcome :)

6 thoughts on “A RESTful JDBC HTTP Server built on top of jOOQ

    1. As a responsible engineer that has been observing the market for more than the last 2-3 years, you certainly do realise that we have been constantly oscillating between mainframe, desktop, J2EE, Applet, Java EE, Mobile/JavaScript. In fact, if you really want to be ahead of your time, bet on moving things back towards the server again. As RAM has reached unprecedented levels of ubiquity and cheapness, you simply can’t beat scaling up in the next 5 years.

      Still, yes, it’s an interesting library :)

  1. How do i run the http server with maven ?

    maven is installed
    sudo apt-get install maven

    also the test are run

    mvn test -B

    INFO: Started listener bound to [localhost:9998]
    Jan 15, 2015 5:50:52 PM org.glassfish.grizzly.http.server.HttpServer start
    INFO: [HttpServer] Started.

    1. Hmm, not sure. Can it be done with the dependencies that are in place? You could certainly deploy the war, but it would be nice to be able to do something like mvn jetty:run!

    1. Hey there! :)

      I’m not aware of any authentication package, although there probably is one out there… Seems like a reasonable question for Stack Overflow…

Leave a Reply