A collection of useful tools to help you work with SQL.
IMPORTANT: This is an ESM only package.
npm install @coderundebug/sql-tools
Each database library has its own method of taking SQL commands and adding input values.
Some use ?
characters and an array of values.
These tools will allow you to do the same sort of thing but for any database, using $
markers to place values, and handle preprocessor sections.
In the below example we are loading a template SQL file, formatting it with some values, which in the end will give us the final SQL command we can use with a database.
// Load in SQL template file const sqlTemplate = await SqlTemplateFile.getTemplate('insert.sql'); // Format template with values const sql = sqlTemplate.format({ name: 'Stephen', age: 25 }); // Use SQL command to insert the record
The insert.sql file would be formatted as follows.
-- Insert record into user table INSERT INTO user (name, age) VALUES ($name, $age);
The $name
and $age
parts will be replaced by the values with the same name.
The name of the value has the dollar $
character placed in front of it when used inside the SQL template.
After formatting the end result would look like this.
-- Insert record into user table INSERT INTO user (name, age) VALUES ('Stephen', 25);
You will notice that the name was a string and is therefore formatted using single quotation marks.
The values are converted into text and then their $
markers are replaced with it.
This type of formatting depends on the values type and the database you want the SQL to be formatted for.
Preprocessing is a method of including and excluding blocks of SQL depending on the values you are using. The example below lists the users, searching either by the user's name or age.
-- List users SELECT name, age FROM user WHERE --if $searchByAge age=$searchByAge; --else name=$searchByName; --endif
This uses the preprocessor statement --if $searchByAge
which checks to see if the value is used.
If it is then the search by age part is used, but if not then the --else
part is used instead.
// Load in SQL template file const sqlTemplate = await SqlTemplateFile.getTemplate('list.sql'); // Format template with values const sql = sqlTemplate.format({ searchByAge: 25 }); // Use SQL command to list records with the same age
The final SQL command that can be used, will look like this below.
-- List users SELECT name, age FROM user WHERE age=25;
The preprocess comments have been processed, with the ones required being kept, and all the others being removed. The preprocess conditions are looked at in more detail later on below.
We have taken a quick look at formatting a SQL template.
Here we will look at it in more detail.
There are two parts, converting $values
into SQL text, and preprocessor conditions.
The SqlTemplate format
function takes an object that contains a number of different values.
These are converted into SQL text and inserted into the SQL template.
// Create values object and add data const values = {}; values.fBoolean = true; values.fText = 'Hello World'; values.fInteger = 1234; values.fDecimal = 3.142; values.fDateTime = new Date(Date.UTC(2024, 3, 27, 11, 15, 45)); // Format template with values const sql = sqlTemplate.format(values);
SELECT $fBoolean, $fText, $fInteger, $fDecimal, $fDateTime;
The output of the formatting process will depend on the database type you have configured. It would look something like this.
-- MySQL SELECT TRUE, 'Hello World', 1234, 3.142, '2024-04-27 11:15:45';
-- PostgreSQL SELECT TRUE, E'Hello World', 1234, 3.142, '2024-04-27 11:15:45';
-- Microsoft SQL Server SELECT 1, N'Hello World', 1234, 3.142, '2024-04-27 11:15:45';
Different database types output the values in slightly different ways.
It can handle different value data types. Below is a list.
Data Type | Information |
---|---|
undefined | Will always output the text "NULL". |
null | Will always output the text "NULL". |
Number | Converts the number into a string without any extra formatting. |
Boolean | Converts the boolean value as either TRUE or FALSE, or where needed 1 or 0. |
String | Checks and replaces any escape characters. Will stop any SQL injection strings. |
Date | Will convert the date into YYYY-MM-DD HH-MM-SS format and if there are any millisecond parts this will also be added to the end. |
Buffer | Converts into a hexadecimal string. |
Array | Writes the array data into a list of parts. |
toSql | If the function toSql is available then it is called and its return string will be used. |
SqlIdentifier | Class that can be used to output identifiers (table and field names). |
SqlTrusted | Class that is used to output plain text without it being checked first. |
SqlJson | Class that is used to output JSON data. |
SqlNonUnicode | Class used to output non-unicode text data. This is only needed in Microsoft SQL server when dealing with character fields that are non-unicode. |
SqlTimestamp | Class used for Oracle timestamps. |
// Create values object and add data const values = {}; values.fBuffer = Buffer.from([0x00, 0x11, 0x22, 0x33]); values.fArray = [123, 456, 789]; values.tableIdentifier = new SqlIdentifier('test'); values.fJson = new SqlJson(someObject); values.trusted = new SqlTrusted('@test_id'); // Format template with values const sql = sqlTemplate.format(values);
INSERT INTO $tableIdentifier (fields...) VALUES ( $fBuffer, $fArray, $fJson ); SET $thrusted=LAST_INSERT_ID();
The outputted SQL text would end up looking something like this.
INSERT INTO `test` (fields...) VALUES ( X'00112233', '{123, 456, 789}', '{ "property1": "hello", "property2": "world" }' ); SET @test_id=LAST_INSERT_ID();
Another useful feature is to include and exclude different parts of the SQL template depending on the values. This uses preprocessor markers with conditions.
--if <condition> SELECT * FROM table1; --elif <condition> SELECT * FROM table2; --else SELECT * FROM table3; --endif
The preprocessor markers are --if
, --elif
(else if), --else
and --endif
.
You can also use #if
, #elif
, #else
and #endif
.
The conditions are used to control which parts of the preprocessor sections end up in the final SQL.
Here are some examples of the type of conditions you can have.
$fNumber = 10 | If the fNumber value is 10. |
$fBoolean = TRUE | If the fBoolean value is true. |
$fText != "Hello World" | If the fText value is not equal to “Hello World”. |
$fDateTime >= "2024-01-01" | If the fDateTime is greater or equal to the given date. |
$first = true AND $second = false | If the first is true and the second is false. |
$a = 1 && ($b = 2 || $c = 3) | If a value is 1 and either b is 2 or c is 3. |
The list of possible conditions are as follows.
= , == , === |
Equal. You can use either a single = character value or 2, or even 3. They all do the same thing. |
!= , !== ,<> |
Not equal to. Both do the same thing. |
> |
Greater than. |
< |
Less than. |
>= |
Greater than or equal to. |
<= |
Less than or equal to. |
You can combine conditions together using the AND
, &&
operations or the OR
, ||
ones.
It is done to look either like an SQL condition or a JavaScript condition, but either way they do the same thing.
You can use some calculations too but there are some limitations here.
$first * 2 > $second + 10 |
$first / 4 <= $second - 42 |
$first >= $second * $third |
You need to be careful when comparing dates. The date value will contain the date and time parts, but you can compare only the date section if you want.
$fDateTime = "2024-04-27 11:23:45" | Both the date and the time need to match. |
$fDateTime = "2024-04-27" | Only the date needs to match. The time part can be anything. |
$fDateTime <= "2024-04-27" | If the date is the same or less then it passes the condition. Because only the date part is looked at, this will pass, even though the actual date and time is greater than the date being compared. |
$fDateTime <= "2024-04-27 00:00:00" | This time the time is also given. This means it compares not only the date but the time too. As a result, this time, it will not pass the condition. |
You can also check which database type is being used.
CREATE TABLE USER ( --if MYSQL OR POSTGRESQL id SERIAL PRIMARY KEY, --elif MS_SQL_SERVER id BIGINT IDENTITY(1, 1) NOT NULL PRIMARY KEY, --else ID INTEGER GENERATED ALWAYS AS IDENTITY, --endif );
This is used to load SQL template files and create a new SqlTemplate
object.
These are *.sql
files with template formatting within.
This static function is used to load in a SQL template file, convert it into a SqlTemplate
object and return a promise that resolves to it.
SqlTemplate
object is cached in memory.
If it is and the same file is requested again, then instead of loading the SQL file and creating the SqlTemplate
object,
it just reuses the same one it created before.
The default is true and caching is always used if not set otherwise.
// Load in SQL template file that is in a "sql" subfolder const sqlTemplate1 = await SqlTemplateFile.getTemplate('./sub/insert.sql', import.meta.url); // Create PostgreSQL config const postgresqlConfig = new SqlConfig(); postgresqlConfig.databaseType = DatabaseType.POSTGRESQL; // Load in SQL template file for PostgreSQL database const sqlTemplate2 = await SqlTemplateFile.getTemplate('/pg/select.sql', null, postgresqlConfig);
This static function is just like getTemplate
but it looks inside the SQL file for the template section.
Instead of the whole file being a single template, you can put multiple SQL templates inside a single file.
This is done by using --template [name]
and --endtemplate
markers.
SqlTemplate
object is cached in memory.
If it is and the same file and name is requested again, then instead of loading the SQL file and creating the SqlTemplate
object,
it just reuses the same one it created before.
The default is true and caching is always used if not set otherwise.
-- Collection of templates --template list SELECT * FROM table; --endtemplate --template insert INSERT INTO table (name, age) VALUES ($name, $age); --endtemplate
// Load in SQL template from within file const sqlTemplate = await SqlTemplateFile.getTemplateByName( 'insert', './sub/collection.sql', import.meta.url);
The SQL template class contains the template information and is used to format it, with a given set of values, to create a final SQL which you would use with your database.
Normally you would get one of these objects by calling one of the SqlTemplateFile
static functions, but you can create one supplying your own SQL template text.
The constructor of the SqlTemplate
class object.
When creating your own instance of the class you pass the template text and the configuration object (if required).
// Set template text const template = 'INSERT INTO table (name, age) VALUES ($name, &age);'; // Create SQL template using the template const sqlTemplate = new SqlTemplate(template); // Create SQL command const sql = sqlTemplate.format({ name: 'Stephen', age: 42 });
Uses the SQL template information the object was created with, along with the values given to it, and creates the final SQL text.
This performs all the preprocessor steps, and inserts all the $values
.
You can use the same SQL template with different values to get different final SQL text.
// Set template text const template = 'INSERT INTO table (name, age) VALUES ($name, &age);'; // Create SQL template using the template const sqlTemplate = new SqlTemplate(template); // Create SQL commands const sql1 = sqlTemplate.format({ name: 'Stephen', age: 42 }); const sql2 = sqlTemplate.format({ name: 'Paul', age: 31 }); const sql3 = sqlTemplate.format({ name: 'Sue', age: 58 });
There are a number of configuration settings that can be used to change the SQL text created.
There are default values, which can be changed, making all templates output SQL to that configuration, but you can create SqlTemplate
objects
with their own configuration.
This would allow you to have one group of templates for MySql and another group for PostgreSQL.
Global default SqlConfig object. If no sqlConfig object is passed to any of the template or format functions, then this default will be used. You can change these default settings at the start of your application and all SqlTool functions will use them by default.
// Start of application SqlConfig.default.databaseType = DatabaseType.POSTGRESQL; SqlConfig.default.utc = true; SqlConfig.removeComments = false; SqlConfig.singleLine = false; // Load in SQL template file (will default to PostgreSQL) const sqlTemplate1 = await SqlTemplateFile.getTemplate('./sql/select.sql');
Gets and sets the database type.
This can be either MYSQL
, POSTGRESQL
, MS_SQL_SERVER
(Microsoft SQL Server) or ORACLE
.
Gets and sets whether to use UTC when outputting date and time text. If set to false, then it will use the local date and time values of the date object. The results of this will depend on the time zone the application is running on. By default this is set to true, so that it will always use the UTC part of the date when converting it into text.
Gets and sets if the comments are to be removed when formatting the SQL template.
Comments are the parts that do nothing but can help to give extra information about when is happening.
These are the parts the start with /*
and end with */
.
Other comment types are the ones that start with the --
characters (or the #
character) and stop at the end of the line.
By default the comments are not removed.
Gets and sets if all the SQL text will be put onto a single line of text. All the new line characters will be removed and the final SQL text will be trimmed. This will also remove any comments too. By default this is not set.
// Create PostgreSQL and MySQL configs const postgreSqlConfig = new SqlConfig(); postgreSqlConfig.databaseType = DatabaseType.POSTGRESQL; const mySqlConfig = new SqlConfig(); mySqlConfig.databaseType = DatabaseType.MYSQL; // Load in SQL template file (will defaults) const sqlTemplate = await SqlTemplateFile.getTemplate('./sql/select.sql'); // Format SQL for postgreSQL and MySQL const sqlPostgreSql = sqlTemplate.format(values, postgreSqlConfig); const sqlMySql = sqlTemplate.format(values, mySqlConfig);
If you want to manually convert values into SQL safe text, without using a template, then you can use this class and its many static functions. Normally you would not need to use this class but it may be useful for you to convert values into SQL yourself.
Converts the value, which can be of any known or unknown type, into safe SQL text.
// Convert the values into safe SQL const sqlInteger = SqlConvert.valueToSql(123); const sqlDate = SqlConvert.valueToSql(new Date(Date.UTC(2024, 4, 1, 11, 43, 32))); const sqlText = SqlConvert.valueToSql('Hello world'); // sqlInteger = 123 // sqlDate = '2024-05-01 11:43:32' // sqlText = 'Hello world'
Converts the boolean value into safe SQL text. Some databases will output TRUE or FALSE, but some will use 1 and 0 instead.
// Convert boolean value in safe SQL const sqlBoolean = SqlConvert.valueToSql(true); // sqlBoolean = TRUE
Converts the date value into safe SQL text. This should be a Date
object.
// Convert date value in safe SQL const sqlDate = SqlConvert.dateToSql(new Date(Date.UTC(2024, 4, 1, 11, 43, 32))); // sqlDate = '2024-05-01 11:43:32'
Converts the string value into safe SQL text.
// Convert string value in safe SQL const sqlString = SqlConvert.stringToSql("Hello 'small' world"); // sqlString = 'Hello \'small\' world'
Converts an identifier string value into safe SQL text. An identifier is the name of a database, table, column, etc.
// Convert identifier value in safe SQL const sqlIdentifier = SqlConvert.identifierToSql('testTable'); // sqlIdentifier = [testTable]
Converts a buffer into safe SQL text. This will be binary data normally in some type of hexadecimal format.
// Convert buffer in safe SQL const sqlBuffer = SqlConvert.bufferToSql(Buffer.from([0x1F, 0x2E])); // sqlBuffer = X'1F2E'
Converts an array into safe SQL text. This is only used with PostgreSQL databases. However, you can convert arrays of values into strings. You should only use arrays of the same type. It can only use numbers, string, dates and booleans. Any other data type will create an error.
// Convert array in safe SQL const sqlArray = SqlConvert.arrayToSql([123, 456, 789]); // sqlArray = '{123, 456, 789}'
Converts any object into JSON safe SQL text.
// Convert object in JSON safe SQL const sqlJson = SqlConvert.jsonToSql({ name: "Stephen", age: 34 }); // sqlJson = '{"name": "Stephen", "age": 34}'
There are some limitations that you may need to know about.
If you load a SqlTemplate
from a file and set the SqlConfig
at the same time, then load the
same SqlTemplate
file but with a different SqlConfig
, then
because it is being obtained from the cache, the new SqlConfig
is not used, instead the first one will still be used.
Loading the same SqlTemplate
from a file with different SqlConfigs
will not work.
The only way around this is to load the SqlTemplate
from a file and set the cache parameter to false.
By default the Date
object is always converted into date only SQL, with no time parts used.
If you want to output time then you will need to use a SqlTimestamp
class object.
When using Oracle you need to remove all the comments and put everything on to a single line.
This can be done by setting SqlConfig
.
Take a look at the Oracle testing section for ideas on how to interact with the database.