node-ejsql
v1.0.2TypeScript
EJSQL is a light weight cross-database interface that creates templatized SQL across MySQL, SQLite, MSSQL and Postgres.
0/weekUpdated 3 years agoMITUnpacked: 79.0 KB
Published by Roger Fedor
EJSQL
     
EJSQL is a light weight cross-database interface that creates templatized SQL across MySQL, SQLite, MSSQL and Postgres. API documentation is available here.
Background
> Over time as an engineer, we like to write good old SQL queries without the overhead or restrictions of a complex ORM. ORMs can introduce complexities where simple aggregation becomes troublesome to implement because it has to conform to a way the ORM can understand.
>
> Over time though, as features are implemented and queries become more spread out over multiple functions, it can become difficult to see how changes affect the big picture of what the query is supposed to look like.
>
> This project aims to simplify the process of creating complex queries without sacrificing code readability. Better readability in how SQL is generated means fewer errors would be missed during code review.
Installation
Open your terminal and type in
via npm,
``sh`
$ npm install --save node-ejsql
or via Yarn,
`sh`
$ yarn add node-ejsql
Usage
EJSQL leverages Embedded JavaScript to handle templating of the SQL with SqlString providing escaping and formatting for JavaScript objects.
$3
* uses { and } instead of < and > for command delemiters{%- 'test' %}
* escape template values using
* creates format objects syntax using
$3
EJSQL comes with a command line interface that allows for an sql.ejs file to be ran from command line. If the package is installed globally, ejsql should be available via command line and if installed locally, it can be accessed through npx ejsql.
`
Usage: ejsql [options]
Runs a templatized sql.ejs file against a database. When using an adapter, make sure the adapter's companion module is installed (mssql, mysql, sqlite3, pg). See https://rdfedor.gitlab.io/node-ejsql/ for additional details.
Options:
-V, --version output the version number
-a, --adapter
-h, --host
-n, --dbname
-u, --user
-p, --pass
-d, --data [var1=val1,var2=val2,...] Pass data to the template as a name value pair (default: "")
-c, --connector [var1=val1,var2=val2,...] Add additional options for the database connection (default: "")
-s, --skipHeaders Disables the output of the headers (default: false)
--help display help for command
`
Quickstart
This quickstart assumes that there's an existing database and table called users with data.
* Create an ejsql directory in your source directory.list-users.sql.ejs
* Create in the ejsql directory with the following contents,`
SELECT
{% if (typeof count !== 'undefined') { %}
count(*) as countUsers
{% } else { %}
*
{% } %}
FROM users
WHERE 1=1
{% if (typeof email !== 'undefined') { %}
AND email LIKE {%: %}`
{% } %}
* Create index.js in the root of the source directory with the following contents,
`
const mysql = require("mysql")
const { join } = require('path')
const { Adapters, Loader } = require("node-ejsql")
// Create a connection the adapter will use
const connection = mysql.createConnection({
host : '127.0.0.1',
port : 3307,
database : 'test-database'
})
// Create the adapter
const adapter = new Adapters.MySQLConnection(connection)
// Load our queries
const queries = Loader(join(__dirname, './ejsql'), adapter)
// Do something
async function foo() {
const rows = await queries.listUsers({
count: true,
email: '@gmail.com'
})
rows.map(row => {
console.log('Total Users: ' + row.countUsers)
})
}
foo()
``
Additional examples can be found here and API documentation can be found here.
Supported Adapters
- mssql
- mysql
- pg
- sqlite3
Bugs
If you have questions, feature requests or a bug you want to report, please click here to file an issue.
Author
* Roger Fedor  
Support
Like what you see? Keep me awake at night by buying me a coffee or two.
License
Copyright (c) 2020 Roger Fedor.
Usage is provided under the MIT License. See LICENSE for the full details.