Add helpers functions to sql(db) (#4)

lucleray · web-flow · commit 72aaeb4f4804 · 2018-09-15T18:58:11.000+02:00
* separate pg shorthand

* add pg.js to package.json's files

* update readme

* update types

* Add helpers functions to `sql(db)`

* replace sql(db).one by sql.one(db)

* update tests

* cleaner tests

* add types

* add sql(db).query

* update test

* update type definitions

* update readme
diff --git a/pg.js b/pg.js
@@ -1,8 +1,22 @@
 const sql = require('./sql')
 
-const sqlPG = db => async (...args) => {
+module.exports = sql
+
+module.exports.query = db => (...args) => db.query(sql(...args))
+
+module.exports.one = db => async (...args) => {
+  const {
+    rows: [row]
+  } = await db.query(sql(...args))
+  return row
+}
+
+module.exports.many = db => async (...args) => {
   const { rows } = await db.query(sql(...args))
   return rows
 }
 
-module.exports = sqlPG
+module.exports.count = db => async (...args) => {
+  const { rowCount } = await db.query(sql(...args))
+  return rowCount
+}
diff --git a/readme.md b/readme.md
@@ -165,25 +165,31 @@ const main = async () => {
 
 #### Shorthand for postgres
 
-Since we &#10084;&#65039; [node-postgres](https://github.com/brianc/node-postgres) so much, we created a shorthand for it :
+Since we &#10084;&#65039; [node-postgres](https://github.com/brianc/node-postgres) so much, we created shorthands and helpers for it :
 
 ```js
-// long-version
-const sql = require('@sequencework/sql')
-const { rows: movies } = await db.query(sql`select * from movies`)
-
-// equivalent, short-version
 const sql = require('@sequencework/sql/pg') // &#9888;&#65039; we import @sequencework/sql/pg
-const movies = await sql(db)`select * from movies`
-// sql(db) just calls db.query so db can be a client or a pool :)
+
+// main export stays the same
+const query = sql`select * from movies where id = ${id}`
+
+// default pg result object : https://node-postgres.com/api/result
+const { rows, rowCount } = await sql.query(db)`select * from movies`
+
+// helpers
+const movies = await sql.many(db)`select * from movies`
+const movie = await sql.one(db)`select * from movies where id = ${id}`
+const nbMovie = await sql.count(
+  db
+)`update from movies set name = ${name}&nbsp;where id = ${id}`
 ```
 
 You can then rewrite the previous `listMoviesByYear` function in a much more concise way &#128526;
 
 ```js
 const sql = require('@sequencework/sql/pg') // &#9888;&#65039; we import @sequencework/sql/pg
 
-const listMoviesByYear = async (db, yearRange) => sql(db)`
+const listMoviesByYear = async (db, yearRange) => sql.many(db)`
   select * from movies
   where 
     year >= ${yearRange[0]} 
diff --git a/test/pg.test.js b/test/pg.test.js
@@ -1,16 +1,32 @@
-const sqlPG = require('../pg')
+const sql = require('../pg')
 
-test('shorthand for node-postgres', async () => {
-  const sampleBooks = ['book1', 'book2']
-  const db = {
-    query: async ({ text, values }) => {
-      if (text === 'select * from books') {
-        return { rows: sampleBooks }
-      }
-      return { rows: [] }
+const sampleBooks = ['book1', 'book2']
+const db = {
+  query: async ({ text, values }) => {
+    if (text === 'select * from books') {
+      return { rows: sampleBooks, rowCount: sampleBooks.length }
     }
+    return { rows: [], rowCount: 0 }
   }
+}
 
-  const books = await sqlPG(db)`select * from books`
+test("sql.query should return pg's query result", async () => {
+  const { rows, rowCount } = await sql.query(db)`select * from books`
+  expect(rows).toBe(sampleBooks)
+  expect(rowCount).toBe(sampleBooks.length)
+})
+
+test('sql.one should return the first row', async () => {
+  const book = await sql.one(db)`select * from books`
+  expect(book).toBe(sampleBooks[0])
+})
+
+test('sql.many should return rows', async () => {
+  const books = await sql.many(db)`select * from books`
   expect(books).toBe(sampleBooks)
 })
+
+test('sql.count should return rowCount', async () => {
+  const nbBooks = await sql.count(db)`select * from books`
+  expect(nbBooks).toBe(sampleBooks.length)
+})
diff --git a/types/index.d.ts b/types/index.d.ts
@@ -1,36 +1,64 @@
 // TypeScript Version: 2.9
 
-declare namespace sqlElements {
-  interface QueryConfig {
-    _sql?: SqlContainer
-    text: string
-    values: any[]
-  }
-
-  class SqlContainer {
-    constructor(chains: ReadonlyArray<string>, expressions: any[])
-    readonly chains: ReadonlyArray<string>
-    readonly expressions: any[]
-  }
-}
-
 declare module '@sequencework/sql' {
   function sql(
     chains: ReadonlyArray<string>,
     ...expressions: any[]
-  ): sqlElements.QueryConfig
+  ): sql.QueryConfig
+
+  namespace sql {
+    interface QueryConfig {
+      _sql?: SqlContainer
+      text: string
+      values: any[]
+    }
+
+    class SqlContainer {
+      constructor(chains: ReadonlyArray<string>, expressions: any[])
+      readonly chains: ReadonlyArray<string>
+      readonly expressions: any[]
+    }
+  }
 
   export = sql
 }
 
 declare module '@sequencework/sql/pg' {
-  function sqlPG(chains: {
-    readonly query: (
-      queryExpression: sqlElements.QueryConfig
-    ) => Promise<{
+  import _sql = require('@sequencework/sql')
+
+  function sql(
+    chains: ReadonlyArray<string>,
+    ...expressions: any[]
+  ): sql.QueryConfig
+
+  namespace sql {
+    type QueryConfig = _sql.QueryConfig
+
+    interface PGQueryResult {
+      rowCount: number
       rows: any[]
-    }>
-  }): (chains: ReadonlyArray<string>, ...expressions: any[]) => Promise<any[]>
+    }
+
+    interface queryable<T extends PGQueryResult = PGQueryResult> {
+      readonly query: (queryExpression: QueryConfig) => Promise<T>
+    }
+
+    function query<T extends PGQueryResult>(
+      db: queryable<T>
+    ): (chains: ReadonlyArray<string>, ...expressions: any[]) => Promise<T>
 
-  export = sqlPG
+    function one(
+      chains: queryable
+    ): (chains: ReadonlyArray<string>, ...expressions: any[]) => Promise<any>
+
+    function many(
+      chains: queryable
+    ): (chains: ReadonlyArray<string>, ...expressions: any[]) => Promise<any[]>
+
+    function count(
+      chains: queryable
+    ): (chains: ReadonlyArray<string>, ...expressions: any[]) => Promise<number>
+  }
+
+  export = sql
 }
diff --git a/types/test.ts b/types/test.ts
@@ -53,17 +53,39 @@ sql`
 
 // shorthand for node-postgres
 const sampleBooks = ['book1', 'book2']
-const db = {
-  query: async ({ text, values }: { text: string; values: any[] }) => {
+interface CustomQueryResult {
+  rows: any[]
+  rowCount: number
+  oid: number
+}
+const db: sqlPG.queryable<CustomQueryResult> = {
+  query: async ({ text, values }) => {
     if (text === 'select * from books') {
-      return { rows: sampleBooks }
+      return {
+        rows: sampleBooks,
+        rowCount: sampleBooks.length,
+        oid: Math.random()
+      }
     }
-    return { rows: [] }
+    return { rows: [], rowCount: 0, oid: Math.random() }
   }
 }
-const getBooks = async (): Promise<string[]> => {
-  const rows = await sqlPG(db)`select * from books`
-  return rows as string[]
-}
-// $ExpectType Promise<string[]>
-getBooks()
+// sqlPG.query should return pg's query result
+// $ExpectType Promise<CustomQueryResult>
+sqlPG.query(db)`select * from books`
+
+// sqlPG.one should return the first row
+// $ExpectType Promise<any>
+sqlPG.one(db)`select * from books`
+
+// sqlPG.many should return rows
+// $ExpectType Promise<any[]>
+sqlPG.many(db)`select * from books`
+
+// sqlPG.count should return rowCount
+// $ExpectType Promise<number>
+sqlPG.count(db)`select * from books`
+
+// sqlPG should return PGQueryConfig
+// $ExpectType QueryConfig
+sqlPG`select * from books`
