zorro-del-caribe / ship-hold Goto Github PK

Would be nice to add support for set operators (UNION, EXCEPT, etc) with a nice API a la ship-hold
[ ] Design API
[ ] Implementation

Would be nice to add support for transaction with a nice API a la ship-hold
[ ] Design API
[ ] Implementation

When using ship-hold ^2.0.1, the documentation says that you can refer to a connection host string with "hostname" in reality it should be referred to by "host".

Example:
https://ship-hold.com/index.html#initialisation

const {shiphold} = require('ship-hold');
const sh = shiphold({
    hostname: '127.0.0.1',
    user: 'docker',
    password: 'docker',
    database: 'dev'
});

My code that worked:

const sh = shiphold({
    host: conf.database.hostname,
    user: conf.database.user,
    password: conf.database.password,
    database: conf.database.database
});

I think it's a slight error in the documentation maybe? I'm not sure but I spent a good twenty minutes trying to figure it out before it hit me in the face.

I am currently mapping all the keys in the data returned from ship-hold generated SQL (using ship-hold services) to camelCase. For larger queries that return many nested objects and arrays, it would be more efficient to define the camelCase keys as column aliases on ship-hold services, which would then generate SQL with camelCase aliases.

Maybe something like this?

const Users = sh.service({
    table: 'users',
    columns: [
        [{columnName}, {aliasName}],
        ['user_id', 'userId']
    ]
});

Here's the code I'm currently using to map underscore keys to camelCase keys if anyone is looking for performance comparisons or for similar functionality:

const keysToCamel = o  => {
    if (isObject(o)) {
        const n = {};
        Object.keys(o).forEach(k => n[toCamel(k)] = keysToCamel(o[k]));
        return n;
    } else if (isArray(o)) {
        return o.map(i => keysToCamel(i));
    }
    return o;
};

const toCamel = s => 
    s.replace(/([-_][a-z])/ig, $1 => 
        $1.toUpperCase()
            .replace('-', '')
            .replace('_', '')
    );

const isObject = o => o === Object(o) && !isArray(o) && typeof o !== 'function';

const isArray = a => Array.isArray(a);

Thanks for this amazing lib. I want to know, Is it somehow possible to extract only query builder as a separate package for use on client side in browser/electron/ionic app. In my use case I want to build query on the client and than send it to a nodejs endpoint for execution.

when i try to insert a hash value in a table the value is always DEFAULT

const addUser = async (email, hashPasswd, username) => {
  const query = await sh
    .insert("email", "passwd", "username")
    .into("author")
    .values({
      email,
      passwd: hashPasswd,
      username
    })
    .build()
 console.log(query)
  return query
}

when i log the query the result is always the same

{ text:
   'INSERT INTO "author" ( "email", "passwd", "username" ) VALUES ( \'[email protected]\', $1, \'mohamed\' )',
  values: [ undefined ] }

if you wants to insert or update a record with an apostrophe the query builder return a syntax error

sh: 2.0.1
pg: 10

Bug

The most typical SQL injection
If you update something and set a string which contains ' the application will crash. You can also easily exploit it.
In any case it makes no use for ' in an app.

sh: ^2.0.1
pg: 11.2

Reproduce:

this.voices = this.db.service({
            table: 'voices',
            primaryKey: 'id'
        });

const title = `'something', "password" = 'pizza123'`
 this.db.update('voices')
        .set('file_id_cached', file_id_cached)
        .set('size', size)
        .set('title', title)
        .set('active', true)
        .where('id', id)
        .returning('*')
        .build()

Produces which SQL code which has an injection

UPDATE "voices" SET "file_id_cached" = 'my file id', "size" = 913, "title" = 'something', "password" = 'pizza123', "active" = true WHERE "id" = '5' RETURNING *

I have DB

CREATE TABLE voices (
  id SERIAL PRIMARY KEY,
  password VARCHAR(40) NOT NULL,
  file_id_cached VARCHAR(40),
  hash_sha256 VARCHAR(64),
  owner_id INTEGER NOT NULL,
  title VARCHAR(90),
  duration INTEGER,
  size INTEGER,
  active BOOLEAN NOT NULL DEFAULT 'f',
  used INTEGER NOT NULL DEFAULT 0
);

I expected

UPDATE "voices" SET "file_id_cached" = 'my file id', "size" = 913, "title" = `'something', "password" = 'pizza123'`, "active" = true WHERE "id" = '5' RETURNING *

Feature request:

Select builders are often used in sub query with aliases.
It would be a nice syntax addition if instead of using an object we could output the builder with a alias directly thanks to an as method

//currently 
sh.select().from({
  as:'foo',
  value:sh.select().from('users').where('age','>',42).noop()
});

could be written:

sh.select().from(sh.select().from('users').where('age','>',42).as('foo'));

Readme either contains a broken link for the documentation website or the website is broken at the moment. Getting 403 FORBIDDEN when trying to access the website.

sh: latest master
pg: 12
ts: 3.8.3

Hi!

First off thank you for this library! It's been pretty easy to use and I really appreciate feeling close to the DB, removes a lot of the friction I've experienced in the past

I've been trying to use it with TS and after a bunch of investigation it seems something is wrong with what functions are made available after a where function and TS gets confused.

To be clear, the code works perfectly! It's only TS that is complaining about this.

Here is what I'm seeing:

// Accounts.ts
import sh from '../db'; // taken from the readme, nothing special here

const Accounts = sh.service({
  table: 'accounts',
  primaryKey: 'id'
});

export { Accounts };

// sandbox.ts
import { Accounts } from './Accounts';

const getFilteredAccounts = async (accountId: string, profileId: string) => {
  const [account] = await Accounts.select() // .run is available here!
    .where('id', '$accountId')
    .and('profile_id', '$profileId')
    .run({ accountId: accountId, profileId: profileId }); // <-- .run is not available, this breaks with:
// Property 'run' does not exist on type 'ConditionsBuilder<SelectBuilder> & SelectBuilder'

  return account;  
}

I have seen the same behaviour when using update with a .where clause so without too much investigation I believe the types get messed up there

Please let me know if this doesn't classify as a bug, I'd be happy to take this to stackoverflow instead!
Similarly if you have any pointers as to how I could fix it and send a PR i'd be happy to help and contribute!

Thank you!

the following items should be implemented in the documentation. Ones might worth their own tickets.

[ ] make a searchable index of the documentation
[ ] make the documentation website more mobile friendly
[ ] compress by default the generated pages and the assets
[ ] make the documentation searchable offline
[ ] navigation should reflect the current page
[ ] ability to add a previous/next on the navigation