The SQL Developer’s Guide to REST Services

This is a practical guide (with lots of examples) to help SQL developers quickly learn the basics of RESTful Web Services.

Data Storage: Tables versus Resources

Both SQL and RESTful Web Services are centered around data.

In SQL, data is normally stored in tables, but in REST Services it is stored in resources.

For example, in a database you could have a customer table:

SQL> SELECT * FROM customer;

ID FIRST_NAME LAST_NAME OCCUPATION
-- ---------- --------- -----------
1  Luke       Skywalker Jedi Master
2  Leia       Organa    Princess

In REST Services, you would have a /customers resource instead of a customer table.

For example, if you want to get all customers (similar to the SQL statement above), you do it like this:

GET /customers

The response to this request would be a JSON array with an object for each customer:

[
  {
    "id": 1,
    "firstName": "Luke",
    "lastName": "Skywalker",
    "occupation": "Jedi Master"
  },
  {
    "id": 2,
    "firstName": "Leia",
    "lastName": "Organa",
    "occupation": "Princess"
  }
]

CRUD Operations

The HTTP methods, which are used for RESTful Web Services, map neatly to the common SQL statements:

The following sections will explain each of them in more details.

Create

To create a new customer, you use the INSERT statement in SQL. For example:

INSERT INTO customer (first_name, last_name, occupation) 
     VALUES ("Han", "Solo", "Smuggler");

In REST, you create a new customer by sending a POST request with the new customer as a JSON object:

POST /customers

{
  "firstName": "Han",
  "lastName": "Solo",
  "occupation": "Smuggler"
}

Read

To read data in SQL, you use the SELECT statement.

For example, to get a complete list of all customers, you simply call:

SELECT * FROM customer;

The corresponding HTTP command is GET, which you can call like this to the same result:

GET /customers

If you want to lookup a specific customer using the primary key, you would do it like this in SQL:

SELECT * FROM customer

WHERE id = 2;

In REST you would append the id to the REST resource:

GET /customers

/2

But what if you want to lookup something using a non-primary key?

In SQL you would just add a WHERE clause to your SELECT statement:

SELECT * FROM customer

WHERE first_name = “Luke”;

In REST, you append a query parameter to the GET statement:

GET /customers

?firstName=Luke

Note: The specific query parameters available depend on the REST service you are using.

You may want to limit the number of fields returned by a query, because you don’t need to display all the fields, or because you want to improve performance.

In SQL, you just specify what columns should be returned:

SELECT

first_name, last_name

 FROM customer;

In REST, you request a partial response:

GET /customers

?fields=firstName,lastName

Note: Partial responses are not available in all RESTful Web Services, but usually in those where performance is key. For example, mobile apps that may need to operate in an environment with limited bandwidth.

Update

If you want to update all columns on a customer via SQL, you use the UPDATE statement:

UPDATE customer
   SET id = 2, 
       first_name = "Leia", 
       last_name = "Organa", 
       occupation = "General"
 WHERE id = 2;

In REST, you do the same by using the PUT method:

PUT /customers/2

{
  "id": 2, 
  "firstName": "Leia", 
  "lastName": "Organa", 
  "occupation": "General"
}

But what if you only want to update some of the fields?

In SQL you simply limit the fields to those you want to update:

UPDATE customer
   SET occupation = "General"
 WHERE id = 2;

In REST, you use the PATCH method:

PATCH /customers/2

{
  "occupation": "General"
}

Note: The difference between PUT and PATCH is that PUT must update all fields to make it idempotent. This fancy word basically means that you must always get the same result no matter how many times it is executed. This is important in network traffic, because if you’re in doubt whether your request has been lost during transmission, you can just send it again without worrying about messing up the resource’s data.

Delete

If you need to delete a customer, you use the DELETE statement in SQL:

DELETE FROM customer WHERE id = 2;

Similar, in REST you use the DELETE method:

DELETE /customers/2

That’s it! This is my attempt to map the key concepts in RESTful Web Services to the corresponding key concepts in SQL. If you understand these, you already got a pretty good headstart towards learning REST Services.