Stl - Sql function tagged template literal library

Stl is a JS library that simplifies the creation of parameterized SQL statements. It shares the interface and a significant portion of logic from porsager's postgres.js for constructing both safe and unsafe SQL. The key distinction with Stl is that it doesn't handle database connectivity. This gives developers the freedom to combine it with their preferred database library.

This library exists so that we can build MVP and prototypes with little to no friction when working with databases. It is biased towards sqlite being the storage layer of choice.

Quick start

Install stl

$ npm install @m5nv/stl

Use it

For stl to work effectively, it requires integration with a database library of your choice. Essentially, stl is compatible with any database library that supports parameterized SQL statements. In the code snippet below, simply swap out <your-favorite-database-library> with the actual database library you're using.

import stl from '@m5nv/stl'
import db from '<your-favorite-database-library>'

const sql = stl({debug: false});
const name = "Mur", age = 60;
const query = sql`
    select
      name,
      age
    from users
    where
      name like ${name + "%"}
      and age > ${age}
  `;

const result = await db.all(query.string, query.parameters);

Development and testing

• run npm i to install dev dependencies
• cd test && npm link @m5nv/stl to link to src in development
• npm run test || npm run test:coverage to test

Notes

identifiers and keywords in SQL:

• 'keyword' A keyword in single quotes is a string literal.
• "keyword" A keyword in double-quotes is an identifier.
• identifier is a string that names an object/entity.

Overview of interpolation syntax

stl can interpolate the following use cases:

| Interpolation syntax | Usage | Example | | ------------- | ------------- | ------------- | | ${ sql`` } | for keywords or sql fragments | sql`SELECT * FROM users ${sql`order by age desc` }` | | ${ sql(string) } | for identifiers | sql`SELECT * FROM ${sql('table_name')` | | ${ sql([] or {}, ...) } | for helpers | sql`INSERT INTO users ${sql({ name: 'Peter'})}` | | ${ 'somevalue' } | for literal values | sql`SELECT * FROM users WHERE age = ${42}` |

Queries

Consult porsager's queries section as a reference. Please file a bug report if you find any discrepancy with the interpolation.

Deviation from porsager's postgres library

Remove origin of Error reporting from stl layer

Rationale:

• the original implementation's optimization leads to faulty origin
• probably best implemented at the application layer
• Read more here

Error codes are specfied in cause of Error instead of in code

Rationale:

• reuse platform provided ways and means...

Remove code for transforming data

Rationale:

• new code is unlikely to use this feature (?)
• better handled at the application layer

References

Database security and SQL Injection

• SQL Injection Attack: What it is and how to prevent it
• Wikipedia: SQL Injection
• OWASP: SQL Injection Prevention
• Database Security: User Management

The no ORM camp

• Just write SQL
• ORM vs Query Builders

SQLite

• JSON improvements
• Virtual columns to speed up json data query
• Retrieving related rows in a single query
• Passing arrays as parameters using json trick
• SQLite CLI

SQL standard and differences

• SQL that works on PostgreSQL, MySQL, and SQLite
• Differences between engines