dql

Contents
--------

- Introduction
- What is DatQL?
- Installing
- Motivation
- Legitimacy
- The Markup
- Queries & Mutations
- Selecting
- Joining
- Inserting & Updating
- Deleting
- API
- Caveats
- Support Table

Introduction
------------

$3

DatQL (a working title short for Data Query Language and pronounced dat-quill) is a
GraphQL-inspired markup language that compiles to vanilla SQL. While this library is still highly
experimental, DatQL supports basic SQL operations such as querying, inserting, updating, and even nested join statements.
So instead of writing this:

``sql
SELECT
user_id
FROM text_messages
INNER JOIN (SELECT
users.id AS user_id
FROM users) ON (users.id = text_messages.user_id)) AS users
WHERE (conversation_id = 5)
`

You can write this:

`
query getUserTextMessages($conversation_id) {
text_messages(conversation_id = $conversation_id) {
...on users(id = text_messages.user_id) {
id[user_id]
}
}
}
`

$3

It is strongly recommended that you also use the Dracula package for running DQL queries on your database directly

DatQL has the super-slick dql package name thanks to @maxogden and can be installed via
NPM or Yarn:

`bash
$ npm install dql --save-dev
$ yarn add dql@ --dev
`

Currently, DatQL is only accessible as a CommonJS module that can be used in Node 8+ (maybe earlier, but that's the Babel
preset it uses, sooo..). You must also install v0.2.0 and up, otherwise you'll get the old library, which can now be found here.

It is also important to note that @maxogden happens to (coincidentally) be the a prominent
contributor of the Dat Project, a "distributed data community" that is in no way
related to this project other than just have "dat" in the title.

$3

Right now you're probably thinking “Big whoop man. Who the hell cares? SQL ain't that difficult.” Well, let's talk
database abstraction for a minute. Say you don't want to get bogged down in the specifics of a database language, seeing
you might switch over to a different database in the future. After all, all query languages are slightly different.
What are your current options?

1. Use object-relational mapping (ORM), such as Laravel or Ruby on Rails,
or even something like SQLAlchemy or Sequelize.
2. Use a query-building library such as Squel, which this library actually uses.

The choices are slim. The biggest issue with ORMs is the fact they usually work best on empty databases,
where all data is inserted and managed by a locally-defined schema consisting of models and controllers. Query builders
are great, but can hard to swap out later on down the line, and the available API is usually attached to a particular
language. For example, I used Squel for all my Node.js database communication, but used
Quill for all my Scala database communication. And in case you're wondering, no, the libraries
are nothing like each other, despite doing virtually the same thing.

So that's where DatQL comes in. DatQL is an abstraction over the abstractions, so to speak. The DatQL markup is parsed
using a publicly-available context-free grammar and can be adapted to virtually any language. While query builders can
be replaced depending on which language the DatQL library supports, the markup remains unchanged, creating an extensible,
open way to write SQL. Plus, DatQL at a glance is much easier to understand than SQL, especially for those who are not SQL
experts.

$3

Yes, as a matter of fact, DQL is slowly replacing all query building operations in the
@GoLinguistic codebase (closed source, sorry). DQL was developed out of Linguistic do
to the need for an abstract, unified way for communicating with the platform's Postgres database. As a result, DQL will
continue to improve and grow to meet the needs of the Linguistic platform.

The Markup
----------

$3

DatQL is fairly straightforward to understand. Like GraphQL, .dql files contain a collection of documents, which can
be one of two types: a mutation (insertion/update) or a query. Each document definition contains a name, as well as any
declared variable parameters it uses. For example:

`
query getBookmarksForUser($user_id)
`

You can also call neighboring mutations or queries as well (where appropriate), such as:

`
users(id = getUserIDFromPage(102)) {
id
}
`

$3


Inside the query block, all top-level blocks are tables from which to SELECT from. Currently, only one table block
per query is supported; however, in the future multiple may be supported. Tables also accept a list of WHICH statements
that filter the results returned. So let's start out by selecting from our users table, filtering by entries whose ID
matches the user ID provided to the query:

`
query getBookmarksForUser($user_id) {
users(id = $user_id) {
id
}
}
`

Like GraphQL, you can specify which fields to return inside each table block. You can alias these fields by specifying
an alias in square brackets ([]) next to the field name.

$3


JOINs take on a form similar to those of fragments in GraphQL. While their fundamental philosophies differ, the syntax
is the same. JOIN blocks begin with ...on and must specify a table name and ON clause in parentheticals. Like
tables, JOIN blocks accept field names, and it is strongly advised that you alias all JOIN fields to avoid conflicts.
So, let's wrap up our statement by joining the users table with the bookmarks table:

`
query getBookmarksForUser($user_id) {
users(id = $user_id) {
id

...on bookmarks(user_id = users.id, name = "MyBookmark") {
name[bookmark_name]
}
}
}
`

It is important to note tht only the first item in the on parenthetical is considered to be an "ON" statement. Every
subsequent item is considered to belong to the "WHERE" statement. Therefore the above output would look something like
this:

`sql
SELECT users.id
FROM users
INNER JOIN (SELECT * name AS bookmark_name
FROM bookmarks WHERE (name = 'MyBookmark'))
AS bookmarks ON (bookmarks.user_id = users.id)
`

$3

INSERT and UPDATE statements are both grouped under mutation documents. Whether the resultant query uses INSERT or UPDATE
is dependent on whether a selector is specified for the table. If one is present, the existing row is updated. If one is
not provided, a new row is inserted. For example, to update a user's name:

`
mutation getBookmarksForUser($user_id, $user_name) {
users(id = $user_id) {
name: $user_name
}
}
`

$3


DELETE statements can only be run on mutations and consist of a single table entry, prefixed with a minus sign (-).
Deletes must contain a selector clause and can only contain join statements (but don't necessarily have to):
`
mutation deleteUser($name) {
- users(name = $name)
}
`

API
---
Similar to Apollo's graphql-tag, DatQL uses an ES2015 template literal
tag which is supported by most recent versions of Node. Currently, DatQL supports three SQL flavors: MySQL (mysql), PostgresQL (postgres),
and Microsoft SQL (mssql). The dql tag processes documents into a tree, returning a function that accepts variables, as well
as the name of the query or mutation to execute. By default, DatQL will always execute the last defined document in a
file. So for our above query:

`javascript
const dql = require('dql').postgres;

const getBookmarksForUser = dql
query getBookmarksForUser($user_id) {
users(id = $user_id) {
id

...on bookmarks(user_id = users.id) {
name[bookmark_name]
}
}
}
;

/**
* Outputs {
* text: 'SELECT id, bookmarks.name AS bookmark_name FROM users INNER JOIN (SELECT bookmarks.name, bookmarks.user_id FROM bookmarks) AS bookmarks ON (bookmarks.user_id = users.id) WHERE (id = $1)',
* values: [ 1002 ]
* }
*/
const sql = getBookmarksForUser({
variables: {
user_id: 1002
}
});

// Outputs SELECT id, bookmarks.name AS bookmark_name FROM users INNER JOIN (SELECT bookmarks.name, bookmarks.user_id FROM bookmarks) AS bookmarks ON (bookmarks.user_id = users.id) WHERE (id = 1002)
const sql_str = getBookmarksForUser({
variables: {
user_id: 1002
}
});
`

By default, DatQL outputs an object containing both the text of the query and any variables associated with it. This
allows your database engine to sanitize any variables to prevent SQL-injection attacks. To override this behavior,
simply pass true as the last parameter of the function. If your string contains multiple documents, you can
pass in the name of the entry-point document as the first argument of the function like so:

`javascript
getBookmarksForUser('getBookmarksForUser', {
variables: {
user_id: 1002
}
}, true);
`

To order your query by a specific field, simply pass in the orderBy configuration option. By default, all fields are
in ascending order. To switch to descending, set the descending property to true:


`javascript
getBookmarksForUser('getBookmarksForUser', {
variables: {
user_id: 1002
},
orderBy: 'id',
descending: true
}, true);
`

Lastly, you can also pass in a groupBy option to group aggregated results based on certain fields.

Caveats
-------
As stated, this library is highly experimental. A couple things to note:

1. The library assumes any fields on the left-side of an operator in any WHICH/ON statement is a field belonging to
the table in question. As a result, they should not be prefixed by the table name. So for example, the following is correct,
assuming the users table has name field:

`
...on users(name = 'Tyler')
`

The following will not work:

`
...on users('Tyler' = users.name)
`

2. DatQL is capable of detecting built-in functions, method calls, and variables in WHICH/ON statements.
Any text that does not match one of these is susceptible to being recognized as a field name. As a result, try to keep
your selectors simple and keep fields to the left of any operator.

3. While this library is designed to be an abstraction over SQL, certain database-specific functions such as Now()` have
not yet been abstracted and will need to be changed manually if you switch databases. Ideally, in the future DatQL will
include its own built-in functions which will automatically be converted between databases.

Support Table
-------------

Selecting (63% completed)	Single Table	✔
	Multiple tables	✘
	Sub-queries as tables	✘
	Fields & Aliases	✔
	Joins	✔
	Filtering	✔
	Sorting	✔
	Grouping	✔
	Having	✘
	Limits & Offsets	✔
	Unions	✘
Inserting & Updating (66% completed)	Fields	✔
	Batch operations	✘
	Filtering	✔
	Sorting	✔
	Limits	✔
	Functions as values	✘
Deleting (100% completed)	Joins	✔
	Filtering	✔
	Sorting	✔
	Limits	✔