sql-template-js

A flexible SQL template utility for Node.js that works with any database library. Transform SQL templates into type-safe JavaScript functions with parameter validation.

Features

• 🚀 Convert SQL templates to JavaScript functions using tagged templates
• ✅ Runtime type validation for SQL parameters
• 📂 Support for loading SQL from files
• 🏗️ Create model classes from SQL schemas
• 🔄 Support for JavaScript expressions in SQL templates
• 🔒 SQL injection prevention with parameterized queries
• 🔌 Works with any database library that has a query method

Installation

npm install sql-template-js

Usage

Basic Example

importcreateSqlifyfrom'sql-template-js';import{Pool}from'pg';// or any other DB library// Initialize with your database connectionconstpool=newPool();constsql=createSqlify({query: (text,params)=>pool.query(text,params)});// Create SQL template functionsconstfindUser=sql`SELECT * FROM users WHERE id = $(id):integer`;// Execute the queryconstresult=awaitfindUser({id: 1});console.log(result.rows);

Parameter Types

You can specify parameter types for validation:

constfindUser=sql` SELECT * FROM users  WHERE id = $(id):integer AND created_at > $(since):date AND active = $(active):boolean`;// This will be validated at runtimeconstresult=awaitfindUser({id: 123,since: newDate('2023-01-01'),active: true});

Supported types:

• string (default if not specified)
• integer
• float
• boolean
• date
• json

JavaScript Expressions in Templates

You can use JavaScript expressions in your SQL templates for dynamic SQL generation:

constfindByProperty=sql` SELECT * FROM users  WHERE ${prop=>prop.field} = $(value):string`;constusers=awaitfindByProperty({field: 'email',value: 'user@example.com'});

Loading from SQL Files

You can load SQL from external files:

// Load a regular queryconstgetUserQuery=sql.fromFile('./queries/users.sql');constuser=awaitgetUserQuery({id: 1});// Load a schema and create a modelconstUser=sql.fromFile('./schemas/user.sql');constusers=awaitUser.find();constuser=awaitUser.findOne({id: 1});

The content of users.sql might look like:

-- User queriesSELECT*FROM users WHERE id = $(id):integer

Model Classes from Schemas

When loading a file containing a SQL schema definition (using CREATE TABLE or CREATE VIEW), a model class is automatically created:

// Schema file: user.sql/*CREATE TABLE users ( id INTEGER PRIMARY KEY, name VARCHAR(255), email VARCHAR(255), created_at TIMESTAMP)*/constUser=sql.fromFile('./schemas/user.sql');// Use the modelconstusers=awaitUser.find();constuser=awaitUser.findOne({id: 1});constnewUser=awaitUser.create({name: 'John',email: 'john@example.com'});awaitUser.update(1,{name: 'Jane'});awaitUser.delete(1);

Working with Views

You can also create models from SQL VIEW definitions:

// View file: user_info.sql/*CREATE VIEW "UserInfo" AS SELECT u.id, email, name, bio FROM "User" u LEFT JOIN "Profile" p ON u.id = p."userId";*/constUserInfo=sql.fromFile('./views/user_info.sql');// Query the viewconstusers=awaitUserInfo.find();constuser=awaitUserInfo.findOne({id: 1});

The library automatically extracts field names from the SELECT statement for views, making them accessible in your code.

How It Works

The library works by:

1. Parsing SQL templates using tagged template literals
2. Extracting parameters and their types
3. Converting templates to parameterized SQL with proper placeholder numbering
4. Validating parameters at runtime
5. Executing the SQL with a database connection

Examples

See the examples directory for more usage examples.

Advanced Example

Check out our advanced example that demonstrates the full power of sql-template-js by combining:

• SQL Views for complex data aggregation
• JavaScript expressions for dynamic query parts
• Conditional filters that can be toggled at runtime
• Type-safe parameters for data validation

// Dynamic query with conditional filters and custom sortingconstgetOrderAnalytics=sql` SELECT * FROM "ORDER_TOTAL" WHERE ${props=>props.minTotal ? createFilter('total_amount','>=')(props) : '1=1'} AND ${props=>props.afterDate ? createFilter('last_order_date','>=')(props) : '1=1'} ORDER BY ${props=>props.orderBy||'total_amount'}${props=>props.orderDirection||'DESC'} LIMIT $(limit):integer`;// Use with different parameters each timeconstbigSpenders=awaitgetOrderAnalytics({minTotal: true,paramName: 'minTotalAmount',minTotalAmount: 500,orderBy: 'username',orderDirection: 'ASC',limit: 10});

Contributing

Contributions are welcome! Please feel free to submit a Pull Request.

License

MIT