npm package discovery and stats viewer.

Discover Tips

  • General search

    [free text search, go nuts!]

  • Package details

    pkg:[package-name]

  • User packages

    @[username]

Sponsor

Optimize Toolset

I’ve always been into building performant and accessible sites, but lately I’ve been taking it extremely seriously. So much so that I’ve been building a tool to help me optimize and monitor the sites that I build to make sure that I’m making an attempt to offer the best experience to those who visit them. If you’re into performant, accessible and SEO friendly sites, you might like it too! You can check it out at Optimize Toolset.

About

Hi, 👋, I’m Ryan Hefner  and I built this site for me, and you! The goal of this site was to provide an easy way for me to check the stats on my npm packages, both for prioritizing issues and updates, and to give me a little kick in the pants to keep up on stuff.

As I was building it, I realized that I was actually using the tool to build the tool, and figured I might as well put this out there and hopefully others will find it to be a fast and useful way to search and browse npm packages as I have.

If you’re interested in other things I’m working on, follow me on Twitter or check out the open source projects I’ve been publishing on GitHub.

I am also working on a Twitter bot for this site to tweet the most popular, newest, random packages from npm. Please follow that account now and it will start sending out packages soon–ish.

Open Software & Tools

This site wouldn’t be possible without the immense generosity and tireless efforts from the people who make contributions to the world and share their work via open source initiatives. Thank you 🙏

© 2024 – Pkg Stats / Ryan Hefner

yesql

v7.0.0

Published

Read named SQL statements from .sql files. Also named parameters for prepared statements.

Downloads

105,610

Readme

Greenkeeper badge Known Vulnerabilities

Read named SQL statements from .sql files and/or use named parameters in prepared statements.

Read named SQL statements from .sql files

Put your statements in a .sql file and name them with a comment above. e.g. /myproject/sql/pokemon.sql

-- getPokemon
SELECT * from pokemon
  WHERE id = ?; -- raw style

-- addPokemon
INSERT INTO pokemon(name, price)
  VALUES ($name, $price); -- SQLite named parameter style

-- updatePokemon
UPDATE pokemon
  SET price = :price; -- PostgreSQL / MySQL named parameter style

Raw / SQLite

Use them in code by giving the directory where .sql files(s) are

const sql = require('yesql')('/myproject/sql/')
const db = new sqlite3.Database('/myproject/sql/db.sqlite3')

db.all(sql.getPokemon, 1337, (err, rows) => {...})

db
  .prepare(sql.addPokemon)
  .run({name: 'pikachu', price: 99}, err => {...}

MySQL / MariaDB

Prepared statements for MySQL / MariaDB are supported

const sql = require('yesql')('/myproject/sql/', {type: 'mysql'})
const named = require('yesql').mysql
const mysql = require('mysql').createConnection...

// read from file
mysql.query(sql.updatePokemon({price: 5}), (err, result) => {...})

// use only named parameters
mysql.query(named('UPDATE ::ptable SET price = :price;')({price: 5, ptable: 'pokemon'}), (err, result) => {...})

PostgreSQL

Prepared statements for node-postgres (pg) are supported

const sql = require('yesql')('/myproject/sql/',  {type: 'pg'})
const named = require('yesql').pg
const pg = require('pg').connect...

// read from file
pg.query(sql.updatePokemon({price: 5}), (err, result) => {...})

// use only named parameters
pg.query(named('UPDATE pokemon SET price = :price;')({price: 5}), (err, result) => {...})

Handling missing parameters

By default MySQL and PG versions throw an error if a parameter is not given. Passing a flag "useNullForMissing" a null value is used instead. Example only for PG, but works for MySQL also.

const sql = require('yesql')('/myproject/sql/',  {type: 'pg', useNullForMissing: true})
const named = require('yesql').pg
const pg = require('pg').connect...

// read from file and insert null values for missing parameters (price)
pg.query(sql.updatePokemon(), (err, result) => {...})

// use only named parameters with nulls for missing values
pg.query(named('UPDATE pokemon SET price = :price;', {useNullForMissing: true})({}), (err, result) => {...})

Changelog

7.0.0
  • MySQL supports quoted strings
    • Thanks @tbantle22 https://github.com/pihvi/yesql/issues/31
6.1.0
  • PG statement returning string with escaped backslash
    • Now also working with double quoted strings
    • Thanks @t-sky https://github.com/pihvi/yesql/issues/30
6.0.0
  • PG statement returning string with escaped backslash
    • Thanks @t-sky https://github.com/pihvi/yesql/issues/30
5.0.0
  • Fail fast on duplicate SQL query name
  • Thanks @asafyish https://github.com/pihvi/yesql/pull/26
  • Support comments in PG
  • Thanks @dwelch2344 https://github.com/pihvi/yesql/pull/23
4.1.3
  • Support fully double quoted strings
  • thanks @AcerLaurinum https://github.com/pihvi/yesql/issues/22
4.1.2
  • Support fully quoted strings
  • Fix regression, thanks @besk-cerity https://github.com/pihvi/yesql/issues/18
4.1.1
  • Fix close by parameter names in PG
  • Thanks @rockdriven https://github.com/pihvi/yesql/issues/14
4.1.0
  • With "useNullForMissing" flag enabled, use null for missing parameter
  • Thanks @dwelch2344 https://github.com/pihvi/yesql/pull/10
4.0.0
  • Moderner JS with arrow functions and consts
  • Support PG date format function
  • Thanks @ericxinzhang https://github.com/pihvi/yesql/issues/13
3.2.2
  • Allow missing slash on directory path
  • Thanks @critocrito https://github.com/pihvi/yesql/pull/9
3.2.1
  • Add security build and badge
  • Update deps
3.2.0
  • Support Windows new lines
3.1.6
  • Add CI build and Greenkeeper check
  • Update dev dependencies
3.1.5
  • Add MySQL table name as parameter to example
3.1.4
  • Fix pg type cast and docs
3.1.1
  • Support mysql prepared statements
2.6.0
  • Support pg prepared statements