zorro-del-caribe / ship-hold Goto Github PK
View Code? Open in Web Editor NEWdata access framework for Postgresql on nodejs
License: MIT License
data access framework for Postgresql on nodejs
License: MIT License
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'));
sh.select().run() // <-- run method on type declaration
sh.select().from("table").run() // <-- run method not on type declaration
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
A declarative, efficient, and flexible JavaScript library for building user interfaces.
๐ Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. ๐๐๐
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google โค๏ธ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.