SQiggL

What and Why?!?!?!

SQiggL is in active development to become the best SQL templating tool for DBAs and developers. While modern ORMs are very feature rich sometimes you just need raw SQL to run, but would like a tool that can help make those SQL files reusable and add some configuration options to them. SQiggL would like to become your preferred SQL templating tool to accomplish this goal. Some example use cases could be customizable SQL seed files for testing and DB setup, or dynamic query generation based on any rules in your application. In reality SQiggL could be used to template any type of text file, but uses SQL as it's main target audience.

SQiggL aims to provide a fully functional CLI with multiple file parsing abilities and a feature rich templating language that is both extensible and powerful. It will have a plugin API that can be used to add any features to the language and will allow for many supporting libraries to support it's core.

Many of these features are not yet available, but are all in active development. Contributions are more than welcome and encouraged. The source code is heavily documented and would be the best place to get started.

Getting Started:

First install SQiggL with

npm install sqiggl

Next require it in your node application using

var SQiggL = require('sqiggl');

Supply a query to be parsed and any variables

var result = SQiggL.parse("UPDATE Names {% if example is not null } SET Name = '{example}' {% else } SET Name = 'Cow' {% endif } WHERE Name = 'Awesome'", {example: 'Dragon'});

Finally log your result, or use it in a database request

console.log(result);

SQiggL does NOT protect against SQL injection. Passing a dangerous string into a SQiggL variable will result in an unsafe query. NEVER trust user input, use SQiggL with caution!

Error Support

SQiggL is still young and new so error support is a little rough at the moment. Please create an issue if you receive an error that you feel is an issue with library or encountered something that SQiggL should have thrown an error for. Help SQiggL grow to have fantastic error support with error messages that actually help you instead of lead you down the wrong path.

For the actual SQiggL error that you do receive at the moment, an Error Guide has been created with all of the errors listed by code and full explanations of what the errors mean as well as common mistakes that will cause them.

Error messages are very limited at the moment, but will become a top priority once the API stabilizes at 1.0.0.

Current Features

Commands

A Command is a SQiggL statement that has an Action and an Expression. Commands are used to perform manipulations to the query. For example a command with an if Action will conditionally include text in your query.

Commands are surrounded by {% } in SQiggL queries.

SQiggL currently supports the following actions: with more coming soon

action	example
if	{% if <boolean expression> } output this text {% endif }
unless	{% unless <boolean expression> } output this text {% endunless }
else	{% if <boolean expression> } output this {% else } output that {% endif }
for	{% for <iterable expression> } output this {% endfor }
endif	see if above
endunless	see unless above
endfor	see for above
end	A generic end that works with all actions above.

else works with both if and unless.

Notes:

• An Action must be the first word in a command
• Only 1 action/expression pair may be defined in a single {% }. Support for and, or, and complex expressions coming soon.
• The % character is the default but is customizable. See Configuration for more information.
• Custom actions may be added to provide more features. See Extensible for more information.

Replacements

A Replacement is an Expression or a Variable that should be evaluated and then injected into the final output at the location where it was defined.

Replacements are surrounded by { }

examples	value of var	output
Hello {var}	World	Hello World
Hello {'Cat'}		Hello Cat
Number {12}		Number 12
{var > 13}	15	1 1 is true in SQL

Notes:

• Only 1 replacement is allowed per { }. Support for and, or, and complex expressions coming soon.

Expressions

Expressions are the main logic of SQiggL and can be used in Commands or Replacements and can also have Modifiers to extend their functionality.

There are 3 types of expressions:

• Boolean expressions return either true or false. (Which resolve to 1 or 0 in the outputted SQL)
• Value expressions return a value. (a string or a number)
• Iterable expressions return a collection of values that can be iterated over by a Replacement or a Command.

The following boolean expressions are supported:

expression	rule	example
is null	is null	example is null
is NaN	is not a number	example is NaN
>	greater than	13 > 12
<	less than	12 < 13
=	equal to	example = something
len>	length greater than	'SQiggL' len> 3
len<	length less than	'SQiggL' len< 10
abc>	lexical greater than	'Dragon' abc> 'Cat'
abc<	lexical less than	'Cat' abc< 'Dragon'
><	between	10 5 >< 15

The only iterable expression supported at the moment is

<var> of <collection> using <joiner>

which iterates a collection of values.

In a For command the <var> will be set as a local variable on the current scope and will contain the current value of <collection>. The output of each iteration of the command will be separated by the <joiner>. See the Examples below for more information.

In a For replacement the <var> is ignored and will instead output each value in the <collection> separated by the <joiner>

Notes:

• Arithmetic is not currently supported.
• Expressions cannot currently be grouped or nested at this time

Conjunctions

A Conjunction is a connector that combines multiple Expressions into one outcome. Example: {% if myVar > 12 || myVar < 3 %}

conjunction	rule
and	Only true if all conditions pass
&&	Same as and
or	True if any of the conditions pass
`

Variables

Variables be defined in the SQiggL query, or can be provided as the second argument to SQiggL.parse. Variables also honor their scope within a query. For example a variable defined in an if action will be undefined outside of that action.

Variables are defined in this syntax: {+ key : 'value' } Variables can also be aliased by other variables

Modifiers

Modifiers add additional features to Expressions. Each expression defines what modifiers it can use and where they can be located in the expression.

modifier	general rule (Each implementation may be slightly different)	examples
=	Returns true if the expression is equal	13 >= 13 10 5 >=< 10
! or not	Negates the result of the expression	var is not null 'it' !len> 3

Examples

{+ myVar : 'Bob' }
UPDATE Something 
SET
{% if myVar is not null }
    FirstName = '{ myVar }'
{% else }
    FirstName = 'Default' 
{% end }
WHERE ID = 1

{+ isAdmin: 1 }
{+ fields:['FirstName', 'LastName', 'Birthdate'] }
SELECT {var of fields using ','} FROM TableA;
SELECT {var of fields using ','} FROM TableB WHERE Admin = {isAdmin};

This is just a taste of what SQiggL can do and it is already capable of much more but many many more more features are coming soon.

Configuration

SQiggL can be configured and includes the following options that can be passed in as an object in the third argument to parse().

option	type	default	description
leftWrapperChar	string	{	Sets the left wrapper character to use for SQiggL statements.
rightWrapperChar	string	}	Sets the right wrapper character to use for SQiggL statements.
commandChar	string	%	Sets the character to denote a command statement
variableChar	string	+	Sets the character to denote a variable statement
commentChar	string	#	Sets the character to denote a comment statement
variableAssignmentChar	string	:	Sets the character that goes between a variable key and value
stringEscapeChar	string	\	Sets the character to use as the string escape character
customActions	Action[]	null	Sets any custom actions to use
customExpressions	Expression[]	null	Sets any custom expressions to use
customModifiers	Modifier[]	null	Sets any custom modifiers to use
customConjunctions	Conjunction[]	null	Sets any custom conjunctions to use
includeCoreLibrary	boolean	true	Set to false to only use custom actions, expression, modifiers, and conjunctions.
exportComments	boolean	false	Sets whether SQiggL comments should be outputted as SQL comments
commentBeginning	string	/*	Sets the SQL comment beginning string
commentEnding	string	*/	Sets the SQL comment end string
trueString	string	1	Sets the string to use for true
falseString	string	0	Sets the string to use for false

Extensible

SQiggL is an extensible language, in the future you will be able to add in new actions, expressions, modifiers, and other core features. Once the official 1.0 release drops there will be plugin instructions here explaining how. Technically it's possible to extend SQiggL now, the hooks are in the options, but actual support for this will come when the API stabilizes

Milestone 0.5 features:

This update will add ternary statements to the language if myVar > 12 then myVar else otherVar. The other common syntax will also be supported myVar > 12 ? myVar : otherVar.

Milestone 0.6 features:

This update will add basic arithmetic to expressions and hopefully the ability to nest expressions within other expressions.

Milestone 0.7 features:

This update will add a CLI interface.