SQL turns 50 and pql makes it easier to use: here’s how

Acronym for Structured Query Language, SQL is a widely used language for managing and manipulating data within relational databases. It was developed to allow users to query, update and manage databases in an effective and structured way.

The first versions of SQL were born in laboratories IBM in 1974, 50 years ago, under the leadership of Donald Chamberlin and Raymond Boyce. After the “promotion” to standard in 1986 by ANSI (American National Standards Institute), a whole series of improved versions of SQL followed in the following years. After so much time, even today SQL remains fundamental for the management of relational databases and is widely used in the software industry and enterprise applications. However, there is still ample room for development: for example, Vanna allows you to chat with SQL databases using AI.

pql, SQL queries become simpler and more easily editable

In the panorama of software developmentstill largely characterized by the use of SQL, RunReveal decided to present a concrete solution that helps programmers overcome some of the most important limitations of SQL. The proposal of RunReveal is called pqlacronym for Pipelined Query Language.

SQL, as we know, is a consolidated language but its structure often imposes substantial changes for adding operations like GROUP, AGGREGATE, JOIN or subquery. The idea behind pql is to extend to SQL queries systems philosophy Unix-like which are based on the structure “a pipeline“.

The term pipeline refers to a flow of data or processes organized in a sequential manner, in which the output of one stage becomes the input for the next stage. It is a common concept in data processing and transformation operations. In pql, the pipeline represents a sequence of operations of chained queries. Each step of the pipeline receives the data processed by the previous step and produces a new version of it which will be the input for the next step. This approach allows you to build complex queries in a modular way, progressively extending the sequence of desired operations. In short, with pql you are dealing with a complete, flexible, versatile and powerful “reinterpretation” of SQL.

A tool like pql is designed to be SQL-agnostic: means that it allows access to the functions of the underlying database, facilitating theintegration with existing products that support SQL.

Examples of using pql

Developed in language Go and distributed under license Apache 2.0pql makes even the most complex SQL queries simple and more manageable.

Take a look at the example site on pql: RunReveal provides some practical examples and it is possible to intervene directly on them pql syntax to get an immediate conversion to SQL.

Take for example the following pql query that extracts from the table users, contained in a certain database of users, who use a Gmail account. The goal is to count (count) in those who use a Gmail account:

users
| where like(email, 'gmail')
| count

The query automatically becomes the following SQL query:

WITH
"__subquery0" AS (
SELECT
*
FROM
"users"
WHERE
like ("email", 'gmail')
)
SELECT
COUNT(*) AS "count()"
FROM
"__subquery0";

On the same page (scroll to the bottom) references to the operators and functions currently supported by pql are also indicated.

How to import pql library into Go and get working SQL queries

The pql language operates through a “compilation” process that translate the query pql in SQL statements. The pql library in Go provides a function called Compile which takes as input a pql query in the form of a string and returns the corresponding SQL statement, along with any errors.

Using Go code, a language that is impossible not to know, you can import the pql library and pass the syntax to be translated into SQL:

package main

import (
"github.com/runreveal/pql"
)

func main() {
sql, err := pql.Compile("users | project id, email | limit 5")
if err != nil {
panic(err)
}
println(sql)
}

By running this Go code, you will get the following SQL query:

WITH "__subquery0" AS (SELECT "id" AS "id", "email" AS "email" FROM "users")
SELECT * FROM "__subquery0" LIMIT 5;

The goal of RunReveal is to make pql the default language, first and foremost, for security professionals. By sharing code under the Apache 2.0 license, it is possible to adopt PQL without being tied to any vendor.

Opening image credit: iStock.com – islander11