OSQL-Based API - Simplify Your API Architecture

A Brief Background

An Application Programming Interface (API) is a set of pre-defined access controls, that permit a software application to access a service that resides on a different domain than the application. A service can perform all sorts of functions from fetching local weather or sending mail, to just simply storing data, and can be on a remote server or even the same device as the application. An API is like a secret code for exchanging messages except that often it's not a secret, thus allowing many companies to build software that can use the service.

APIs are more and more becoming the backbone of the Internet, and there's no indication that that will be changing anytime soon.

How it Works

When a company or individual decides they want to offer a service via an API, they must decide what form the API will take. The form or architecture defines the basic structure of how the messages are exchanged, and can greatly influence how easy it is for the API to be used.

The two leading API architectures/interface standards are SOAP and REST. Each provide an outline for how to make requests to the service, which the API developer then designs their service around. Selection of the API architecture depends on the type of service, the commands and format of the data to be exchanged, and the target market for the API.

The tricky part is that an API architecture is a very high level definition, and each service will have a very unique set of endpoints, authentication requirements, and even supported data formats. Each API intended for public exposure therefore requires detailed documentation to educate developers on its use.

Seeing the Future

It's clear from the trends over the last half-a-dozen years or so that REST is emerging as the API architecture of choice. This is largely in part to its simplicity and flexibility, and that it is simple to deploy in nearly all programming languages and platforms. SOAP by comparison is slower to develop and deploy due to reliance on older technologies, and less popular programming methodologies.

If things stayed on the current path it's clear that in a short few years, REST would continue to dominate; however, it seems the path is due for a detour. As APIs and services evolve and improve, so do their needs from a technology perspective. More data-rich services require more complex requests in order to be sustainable, and the framework established by REST begins to break down.

REST uses a simple request-response system, and therefore requires multiple requests to get more complex information; multiple requests reduce service efficiency, and make the API more difficult to work with for the application developer. With each service having its own unique type of data and commands, it's challenging for a developer to add a new service to their application, without a significant amount of development and testing.

There are a number of new API architectures gaining some publicity; all attempt to solve the shortcomings of REST by adding extra dimensions to the types of requests which can be made, but they all fall short in that they're mostly just variations on a theme. Unless there's a significant benefit across the board to a new API architecture type over the others, APIs will be built with ever more variety of methodology and complexity. This future is bleak if you're an application developer, and you were hoping to spend your time developing that app instead of plugging it in to the variety of services you need.

Heeeeeere's OSQL

Previously I introduced the OSQL project, and how it can be used to give better insight into SQL queries before they're executed; now I'm going to talk about how to use OSQL to not just provide a flexible and feature-rich service via an API, but also how it can greatly simplify the design of the API itself. Not only can OSQL make your API easy to build, flexible, and efficient, but it also makes it considerably easier for a developer to integrate with.

Making life easier for everyone while improving security and performance: too good to be true? Read on and see how OSQL changes the perspective, and opens possibilities.

Let's Simplify

The one commonality between all popular API architectures, is that they require an additional layer on top that defines how to get the data or operation you want. An architecture only defines a framework for exchanging data, and everything else is custom work. OSQL-based APIs combine both into one by giving the developer seemingly direct access to the data in the service. Instead of a whole set of rules for how to Create, Read, Update, Delete (CRUD), just send some OSQL and you're done!

Instead of this HTTP method for this, this URL for that, and multiple requests until you get the whole data puzzle together, you can package all your OSQL queries into a JSON or LOEN array; one request is all a developer needs. For the service provider, all you need to do is publish your data schema, and let the application developers do the rest.

The Details

Alright it's true there's more to it than just a data schema, so let's make sure we're covering the essentials. If you're a service provider considering OSQL, start with this as a checklist.

• Query Limitations and Attack Prevention

From a data security/privacy perspective this one's the most important, so this is where we're going to start. Since unfiltered OSQL could conceivably give someone complete access and control of your service's data, it's critical that certain precautions be taken. It's sounds scary, but OSQL makes things much easier.

Unlike regular SQL for database access, OSQL is object oriented and therefore easy to process in code without a complex SQL parsing engine. Simply decode the queries and scan them for unwanted behaviour. If a query is trying to alter, drop, or create tables, don't allow it. If a query is trying to access another user's data, crush it. If someone is trying to fetch one million rows from a table, limit them to ten. If someone is trying some nasty SQL injection, don't worry as OSQL filters that out automatically.

Still sound too complicated? Just give each of your users their own SQLite database. Connect to the appropriate database based on credentials in the request, and you're done; no additional security required as the OSQL queries will only be run against the user's private database. Obviously this won't work for social media type applications where data is shared between users, but for simpler services this approach is a huge time and stress saver.

OSQL makes security easy since you're always programmatically in control of what your users are allowed to do.

• Access Control

In most cases you probably don't want just anyone access your service. You might have a free tier but you need to make money to pay the bills, so most likely you have parts of your service that require paid permissions. This part doesn't really change with OSQL. You could somehow require it as an OSQL query, but you're better off using whatever method you're most comfortable with. OSQL is a data exchange mechanism that can be passed as an encoded string; whether you're using an authorization header, URL parameter, or POST value, OSQL doesn't mind as long as your service can decode it.

• Non-Data Operations

OSQL is based on SQL, so data access is covered; what about operations that aren't necessarily data related? What if the service is for sending SMS or Email messages? What if the service needs to submit a credit card payment, and wait for a response? What if the service is controlling a physical device, and no data is actually being saved?

Don't just think about OSQL as a wrapper for data access via SQL, and look at it as a whole new way of using the SQL language. OSQL is very easy to process in code, so custom routines can be written to execute under specified conditions. If an email's status is changed/updated from draft to sent, then activate a mail sending routine. If a new payment transaction is saved/inserted, then process the payment to the provided card; you can even remove the credit card number from the OSQL query, to prevent storing sensitive data.

For a physical device where there is no data, things get even more fun. You can publish a data schema that corresponds to actions as opposed to data. There could be a data schema for movement with columns for direction and speed. When a new insert query is received for that table/action, a robotic welder or laser cutter could take that and interpret it accordingly. SQL is a method of transferring information; OSQL makes it easier to work with, and thus more versatile.

Benefit Recap

Phew, that's a lot to take in and consider. Rather than trying to pick out the best parts from what you just read, here's some highlights of what makes an OSQL-based API a great option for your service.

• Simple to create and document: no complex URL structures or request parameters to define and absorb. Just publish a detailed overview/schema of how data is stored and accessed, and let application developers take care of the rest.
• Easier for developers: much more straightforward as it's just SQL-like commands, that nearly every developer learns at some point. The data schema is custom, but the access language is not. Fewer API-specific customizations makes for a straight-forward integration by the developer.
• Greater control over security: all queries can be easily scanned, analyzed, and filtered programmatically in one place. While you can still rely upon the web and database servers to do their work, you can simplify while expanding your capabilities by moving redundancies from one software to another. Instead of complex database permissions and users, have just one database user and let your code analyze as needed (sorry DBAs).
• Efficiency: multiple queries may be sent easily with a single request, greatly reducing overhead for request heavy and/or complex services.
• Flexibility: create APIs that are not just for data but can instead or additionally query other services, and even control physical IOT devices.

Bet you'll never look at SQL the same way.