15 minutes Guide to sqlREST

SourceForge.net Logo

This guide shows, how a relational database can be enabled as REST style Web Service within minutes. You can read the guide and try the rest examples online or you can deploy sqlREST and do it on your computer.


To run sqlREST you need a Java runtime version 1.4 or higher and a Servlet 1.2 conformant Web Container like Tomcat 4.X or newer.


The Installation is quite simple:


A glimpse to a Web Service

After the installation you can start exploring the Web Service with your browser. A good starting point for querying the web service is the list of toplevel resources. The toplevel list can be accessed using a URL like the following:


Perhaps at your installtion the right URL is:


You should get a result like the one in picture 1.

Toplevel list of resources
Picture 1.) Toplevel list of resources

From here you can dive deeper into the service. This Web Service offers four kinds of resources. There are customers, products, invoices and items. Every kind of resource have a XLink attribute href with an URL. A list of resources is a resource too.

You can copy and paste the URL in Addressbar of your browser.

Attention: Cause version 0.3.1 doesn't support composed primary keys, the items resources can't be accessed.

As you might have guessed the resources shown in the response are generated on-the-fly from database metadata. Metadata provides a description of data. For example information about the tables, columns and relations in a database. Every kind of resource correspondends to a table in the database. sqlREST can be used with almost all database on condition there is a suitable JDBC driver. The meta data provided by the driver about the database is used by sqlREST to compute the response and to execute actions on server side.

List of invoices

Next we want to see a list of invoices. Enter the following URL in your addressbar:


The result is a list pointing to resources representing invoices. Every row in the invoice table is referenced by a XLink href attribute as you can see in picture 2.

Picture 2.) A list pointing to invoice resources

Choose an invoice an follow the link. For e.g.:


The representation of an invoice entity is shown in picture 3. The details of the invoice contains an id, a total amount and an id of the customer the invoice belongs to. The element CUSTOMERID has an XLink reference to the refered customer. sqlREST uses the database meta data to create links to referenced resources.

Representation of a customer
Picture 3.) The representation of a customer

As you can imagine, if you follow the link:


you will get the representation of a customer like the one in the screenshot in picture 4.

Representation of a customer
Picture 4.) Representation of a customer

We have followed the links in the browser, like we follow links in every web application. Despite the fact, that we can't follow the links by a klick. We have to copy the location into the addressbar. A Web Service is for engines not for humans. A client requests the resources, interprets the result and dives deeper into the system following the links.

The diverence to RPC style Web Services

In an RPC style Web Service like SOAP or XML-RPC you may get back a result of a certain type. For example you get back a string type with the value BA4C. The clients needs to know the meaning of the result in order to make further processing. Maybe BA4C is a title of something or BA4C is some code or it is a primary key refering to a different resource. Every method with a correspondending parameter type can be invoked with the value BA4C. But not every call makes any sense. The knowledge about the methods and parameters that can be called with a previously returned value must be available to the client.

getCustomer("BA4C");        // OK - A Customer will be returned
getProduct("BA4C");         // Nonsense - No product will be found
setTitle("BA4C");           // Will work, but not a nice title for a document
setDescription("BA4C");     // Will work also, but it is no description

In a RESTian Web Service you get the value BA4C and a link to follow. The client gets all the information with the response it needs, for following the link.

REST Browser

GET requests for querying can be send to RESTian Web Service using an ordinary Web Browser like Mozilla, Opera or MS Internet Explorer. Unfortunatly POST, PUT and DELETE can't be send that easy. The reminder of this guide uses a REST Gateway called RESTGate to illustrate the examples with screenshots. You can use also small programs in Java, Perl, Ruby or your prefered language, if you want to try the examples. With the screenshots provided in this guide it should be possible to follow without trying the examples yourself.

The screenshots in this guide are form an early version of RESTGate. Formerly known as webrest.

Lets have a look at the REST Gateway. A request with URL, Method and Content can be specified in a form. RESTGate takes the information from the form and sends a HTTP request to a REST Web Service.

Picture 5.) RESTGate - A Gateway tool for REST Web Services

The Web Services processes the request and answers with a response Message. The response is read by the gateway and transformed to a HTML Page as you can see in Picture 6.

A response in RESTGate
Picture 6.) A response in RESTGate

Next we will see how to work with DELETE, POST and PUT methods.

Removing resources

HTTP provides the DELETE Method to remove resources from the server. This method is scarcely used in normal Web applications. In REST the DELETE Method is the choice to remove resources. A DELETE request send to resource will cause its deletion in a RESTian Web Service. Picture 7 shows RESTGate with a DELETE request for an invoice.

Removing an invoice using HTTP DELETE
Picture 7.) Removing a resource with HTTP Delete

At server side sqlREST receives the delete request and executes the deletion of the addressed resource. In Picture 8 you can see the server is responding with a HTTP Status Code of 200. A Status Code of 200 means, everything is Ok.

Response for HTTP DELETE
Picture 8.) Repsonse after removing a resource

Altering resources

The representation of a product resource is shown in figure 9. We want to update the price to a new value. To modify an existing resource we use a POST request.

Picture 9.) The product with the old price

With the request we send content to the server containing new values for the resource. Figure 10 shows the POST request method and the new value for the price expressed in XML.

Altering a resource with POST
Picture 10.) Altering a resource with POST

At server side the product resource is altered to the new value 8.5. sqlREST creates a SQL-Update statement using the XML content and executes the statement. Depending on success the Status Code is either 200 OK or 403 Forbidden.

Response after modifying a product
Picture 11.) Response after modifying a product

The next picture shows a representation of the modified resource.

The product with the new price
Picture 12.) The product with the new price

Creating resources

New resources can be created by POST and PUT requests.

Creating with POST

A new resource can be created by a POST request. The request have to be send to a higher level resource. To create a new product we send a POST request containing all the data to a URL representing the product table. In figure 13 you can see a request creating a new product with the ID 60.

Create a new product using POST
Picture 13.) Create a new product using POST

Creating with PUT

To create a resource with PUT a request is send to a URL, which should be the address for the new resource. The URL in figure 14 contains the new ID with a value of 61. In the content there are only elements with values for none-primarykey field.

Create a new product using PUT
Picture 14.) Create a new product using PUT

What to do next?

You can connect sqlREST with your own database and build sample Web Services using the REST Style. All you have to do, is to configure a JDBC connection as described in the user guide.

Enjoy sqlREST!

Thomas Bayer