4 ms·
You're being downvoted, but I completely agree. If you are on Node and Postgres I highly recommend using slonik - it makes it easy to just write SQL but at the
by hn_throwaway_99 5y ago
You're being downvoted, but I completely agree. If you are on Node and Postgres I highly recommend using slonik - it makes it easy to just write SQL but at the same time makes it almost impossible to have mistakes like SQL injections:
https://gajus.medium.com/stop-using-knex-js-and-earn-30-bf410349856c https://gajus.medium.com/stop-using-knex-js-and-earn-30-bf41...
https://github.com/gajus/slonik https://github.com/gajus/slonik
- bhj 5y agoAlong similar lines but more simple/agnostic, check out node-sql-template-strings[0] or sqlate[1]. [0]: https://github.com/felixfbecker/node-sql-template-strings https://github.com/felixfbecker/node-sql-template-strings [1]: https://github.com/moll/js-sqlate https://github.com/moll/js-sqlate
- mirekrusin 5y agofor mssql https://github.com/appliedblockchain/tsql/ https://github.com/appliedblockchain/tsql/
- girvo 5y ago> it makes it easy to just write SQL but at the same time makes it almost impossible to have mistakes like SQL injections While I think this is totally the way to go, it is kind of amusing that PHP's PDO system was the "right" way to go all along in some ways
- hn_throwaway_99 5y agoMeh, not really. Some of the biggest benefits from slonik are only possible because of Javascript's support for tagged template literals [1], e.g. you write a statement like sql`SELECT id FROM foo WHERE bar = ${barValue}` Tagged template literals make it feel like you're just doing string concatenation, but it's done in such a way where under the covers it's actually creating prepared statements, and it is literally impossible to have SQL injection bugs unless you deliberately go out of your way to make them. 1. https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Template_literals#tagged_templates https://developer.mozilla.org/en-US/docs/Web/JavaScript/Refe...
- girvo 5y agoThat is exactly what PDO’s prepared statements do. It’s not string concatenation, and it’s explicitly to prevent SQL injection? I must be misunderstanding I guess.
- golergka 5y agoThanks for the link to the slonik, never heard about it before. But if I'm not mistaken, if doesn't generate types for result days from the queries, unlike pgtyped?
- hn_throwaway_99 5y ago> But if I'm not mistaken, if doesn't generate types for result days from the queries, unlike pgtyped? You are correct. But note that pgtyped is really only able to generate types because it actually needs to run your queries against a running instance of your DB. Some contributors have commented they are working on something similar for Slonik, https://github.com/gajus/slonik/pull/267#issuecomment-840559530 https://github.com/gajus/slonik/pull/267#issuecomment-840559...
- zomglings 5y agoThe "Stop using knex.js" blog post misses a very important point in favor of using knex.js: database migrations. Migrations allow a development team to synchronize database schema changes across multiple branches of development. It is the main reason I end up using tools like knex.js (node) and sqlalchemy (python). As far as knex is concerned, you can use it to manage migrations without using it in your application code.
- hn_throwaway_99 5y agoI don't really think that blog post misses that, I just think that DB migrations functionality is very different from SQL builder functionality, and that blog post is referring to the SQL builder part of this. FWIW, I do use knex for migrations, but I hate the SQL builder part of it, so most of my migrations are littered with knex.raw statements. This was discussed at length in this slonik issue, https://github.com/gajus/slonik/issues/42 https://github.com/gajus/slonik/issues/42 , with some recommendations for migrations functionality.
- ttfkam 5y agoIdempotent SQL updates are my preference. ``` ALTER TABLE IF EXISTS ADD COLUMN IF NOT EXISTS foo varchar; CREATE TABLE IF NOT EXISTS sample ( -- all the fields including foo ); ``` Run it on a clean DB, you get the table. Run it on an existing DB, it adds the column. Run it on a current DB, it does nothing. Run it once or a hundred times, the DB ends up in the same target state. Can only be used on some databases though. The DBs without transactional DDL are a risk. Then again, they were a risk no matter what method you use. (I'm looking at you, MySQL.)
- ttfkam 5y agoI prefer the postgres module. 0 dependencies. Easy to use API. Wicked fast. Also really hard to fall victim to SQL injection attacks unless you actually put some extra effort into doing something foolish. https://github.com/porsager/postgres#readme https://github.com/porsager/postgres#readme