Have ideas to improve npm?Join in the discussion! »

    sql-pg

    10.0.0 • Public • Published

    Build Status Coverage Status Greenkeeper badge

    Latest Release: v10.0.0, Latest Major Release: v10.0.0, All Releases

    SQL-PG

    Complex queries can be written with normal SQL, including the values needs to be bound and prefixed with the sql tag.

    SQL Make the name variable? SQL
    Or even more simple? SQL

    Features

    • Built on top of pg as database driver
    • Simple data manipulation and selection methods without the need to write SQL
    • SQL Tag and Tag Helpers to write queries looks like native SQL and be secure by design
    • Write easy unit testable queries
    • Possibility to add own Tag Helpers to extend the functionality

    Installation

    npm install --save pg sql-pg

    Initialisation

    Create a sql.js file in your project directory:

    module.exports = async () => {
      const { Client } = require('pg')
     
      const client = new Client({ connectionString: process.env.DATABASE_URL })
      await client.connect()
     
      const sql = require('sql-pg')({ client })
      
      return { client, sql }
    }

    Use it in your project:

    ;(async () => {
      const path = require('path')
     
      const { sql } = await require(path.join(process.cwd(), 'sql.js'))()
    })()

    Usage

    Manipulation Methods

    Simple data manipulation can be done without writing any SQL Statements.

    E.g. some user data manipulation:

    const id = await sql.insert(
      'users',
      { name: 'Sharaal', email: 'sql-pg@sharaal.de', passwordhash: '...' }
    )
     
    await sql.update('users', { validated: 1 }, { id })
     
    await sql.delete('users', { id })

    More complex data manipulation can be done with the SQL Tag.

    Selection Methods

    Often needed convenient methods to check and extract query results are available with the Selection Methods.

    E.g. select all not validated users:

    const users = await sql.any('users', ['name', 'email'], { validated: 0 })

    Also the Selection Methods supports SQL Tag as parameter for more complex selections. Because they are highly inspired by pg-promise, there are the Selection Methods any/manyOrNone, many, oneOrNone and one available.

    SQL Tag and Tag Helpers

    If it becomes more complex the SQL Tag and Tag Helpers are the way to go.

    They are as near as possible to native SQL queries to be readable and easy to write. All variables can be directly used and will be exchanged via placeholders and given to the database separately as values. For non native values like lists, for table/column names and conditions there are Tag Helpers.

    E.g. list of not activated users filtered by name:

    const name = 'raa'
     
    const users = await sql.any(
      sql`
        SELECT "name""email" FROM "users"
          WHERE
            "validated" = 0
            AND
            "name" LIKE ${`%${name}%`}
      `
    )

    There are a lot more Tag Helpers available like .identifier, .table, .column(s), .value(s), .valuesList, .assignments, .conditions, .limit, .offset, .pagination, .if, .jsonColumnObject and .jsonColumnText.

    More

    Available Tag Helpers, Nested Queries, Transaction, Writing Tag Helpers, Migrations, Syntax Highlighting in Atom... All additional documentation can be found in the Wiki.

    Contact

    Found a bug or missing a feature? -> Create a new Issue

    Found a security issue? -> Look at the Security Policy

    Having questions, want to give feedback or talk to me? -> E-Mail me sql-pg@sharaal.de

    Install

    npm i sql-pg

    DownloadsWeekly Downloads

    0

    Version

    10.0.0

    License

    MIT

    Unpacked Size

    22.2 kB

    Total Files

    37

    Last publish

    Collaborators

    • avatar