June 14, 2020

Why Are We Still Using SQL? Try OSQL

Meet OSQL, an object-oriented approach to SQL. SQL is middle-aged; it's time for its mid-life crisis.

Why Are We Still Using SQL? Try OSQL

What is SQL? - A Quick Recap

Structured Query Language (SQL) is a specialized programming language used pretty much exclusively for the reading and writing of data to and from databases. Unlike more modern programming languages, the syntax is much closer to spoken language. Scripts of SQL tell a story to the database about what to do with some data, or how to fetch some. This story-like syntax makes SQL extremely versatile, and simple to learn the basics. At time of writing, SQL is over 40 years old with no sign of slowing down.

Despite its wide-spread use however, I wouldn't venture to say SQL is popular. Developers learn SQL because they have to, not because so many of them love it and enjoy working with it. The age of SQL shows through in many ways in the language: it is difficult to skim read large amounts of SQL code and gain any sense of what it's doing, it has numerous easily exploited security issues unless the developer takes great care, data is passed in more human-readable formats making it more verbose and slower for computers to interpret, and its lack of modern object oriented design makes it difficult to maintain, to name a few things.

So Why is SQL Still a Thing?

Despite its many shortcomings, SQL is still one of the best tools for the job. Data can take many forms and be organized in all sorts of ways; SQL is versatile enough to handle a large variety of scenarios, and with its age also comes software and tool maturity. A great deal of work has been invested in optimizing storage solutions around SQL, and the tools to go with them. SQL processing/parsing is fast and effective at scale, and is in many ways compatible between vendors. Data storage is an essential part of modern computing, and is only becoming more and more important as cloud services and big data services come into their own.

Data is complex, and a rival to SQL has not yet risen to the status of true challenger. At this point as well, due to market penetration and huge numbers of developers trained in SQL, it seems hard to conceive of any solution challenging SQL no matter how good it may be.

End of the Article Then...

Woah not so fast!

SQL may be in a dominant position and potentially impossible to fully replace, but that doesn't mean we can't give it a helping hand. There are a variety of 'wrapping' solutions that make SQL more compatible with modern technologies. These kinds of solutions allow SQL to run as it always has, while in some cases allowing the developer to avoid touching SQL altogether. These kinds of solutions take advantage of the maturity and performance of SQL, while presenting the developer with a more modern SQL interface that is much more easily maintained.

There is of course a but... The afore mentioned 'wrapping' solutions are not without drawbacks. There is no universal solution as each language must implement its own wrapper, and these wrappers need to handle the nuances between different database engines and the flavours of SQL that they use. Many of these solutions are also difficult to setup and thus hard to maintain, while also introducing inefficiencies into the data flow, limiting the scalability of the application, or increasing hardware requirements.

A Different Approach

Introducing Object SQL (OSQL), an object-oriented approach to SQL. OSQL is a set of classes and types which are combined to form a code object representing an equivalent SQL query. Joins, fields, values, clauses, etc, are all objects which are loaded into the parent query object. The query object may then be converted to a real SQL string, or may be encoded for transport over a socket or other communication channel; one can even build the query in a web browser using Javascript, and send the query via an HTTP connection.

At cursory glance this may seem like a pointless tool as you still have to understand the SQL language to write it, but it enables a whole set of new abilities.

  • Security by default - No longer do you need to concern yourself with SQL injection as all input is parameterized and sanitized by default.
  • Much simpler APIs - No longer do you need a complex set of commands for an integration; now you just need to tell the client how to authenticate, and what the storage schema is.
  • More sophisticated user management - User permissions may now be dynamic based on any number of custom factors. Reads and writes can be restricted to specific tables, rows, and columns, based on criteria in code, not database management.
  • Better performance - Database engines can now absorb the query in an object-oriented fashion, rather than having to interpret the SQL, and build the objects themselves.
  • Automated maintenance - Data queries may be logged more easily and specifically, triggering automated actions like creating and deleting indexes to improve performance.
  • SQL Compiler - Code compilers can scan the OSQL object for syntax errors, or other issues.
  • IDE Auto-complete - Code editors can take advantage of the object-oriented structure, and suggest auto-complete options to the developer.

I believe this is just the tip of the iceberg in terms of potential; admittedly I'm biased, but time will tell. It doesn't take long to come up with web results that complain about SQL, but then it doesn't take long to find proponents either. OSQL bridges the gap between the camps, and brings cake that anyone can eat if they want to.

The Roadmap

The goal of OSQL is to become a standard; one that does not differ between platforms or languages. One of the most persistent annoyances of SQL is that each database vendor has a slightly different take on it. OSQL parsing engines can be made specific to their respective database engine; thus removing this burden from the developer, and enabling application compatibility across all OSQL compatible database engines. To best ensure this idea, OSQL is and always will be an open-source initiative.

To ease introduction and collaboration, a number of development libraries are being developed and maintained. The primary goal of OSQL is to become a standard, and so the project is focused on creating documentation to support that end. These libraries are intended to encourage experimentation, and potentially to act as a jump-off point for the creation of more optimized libraries by other developers.

Check it all out on the OSQL project's Github page.