Gathering detailed insights and metrics for sql
Gathering detailed insights and metrics for sql
Gathering detailed insights and metrics for sql
Gathering detailed insights and metrics for sql
npm install sql
Typescript
Module System
Min. Node Version
Node Version
NPM Version
88.1
Supply Chain
99.3
Quality
75.7
Maintenance
25
Vulnerability
100
License
Updated on 26 Nov 2024
Minified
Minified + Gzipped
JavaScript (99.95%)
Makefile (0.05%)
Cumulative downloads
Total Downloads
Last day
11.1%
Compared to previous day
Last week
-11.9%
Compared to previous week
Last month
34.6%
Compared to previous month
Last year
16.2%
Compared to previous year
sql string builder for node - supports PostgreSQL, mysql, Microsoft SQL Server, Oracle and sqlite dialects.
Building SQL statements by hand is no fun, especially in a language which has clumsy support for multi-line strings.
So let's build it with JavaScript.
Maybe it's still not fun, but at least it's less not fun.
1$ npm install sql
1//require the module 2var sql = require('sql'); 3 4//(optionally) set the SQL dialect 5sql.setDialect('postgres'); 6//possible dialects: mssql, mysql, postgres (default), sqlite 7 8//first we define our tables 9var user = sql.define({ 10 name: 'user', 11 columns: ['id', 'name', 'email', 'lastLogin'] 12}); 13 14var post = sql.define({ 15 name: 'post', 16 columns: ['id', 'userId', 'date', 'title', 'body'] 17}); 18 19//now let's make a simple query 20var query = user.select(user.star()).from(user).toQuery(); 21console.log(query.text); //SELECT "user".* FROM "user" 22 23//something more interesting 24var query = user 25 .select(user.id) 26 .from(user) 27 .where( 28 user.name.equals('boom').and(user.id.equals(1)) 29 ).or( 30 user.name.equals('bang').and(user.id.equals(2)) 31 ).toQuery(); 32 33//query is parameterized by default 34console.log(query.text); //SELECT "user"."id" FROM "user" WHERE ((("user"."name" = $1) AND ("user"."id" = $2)) OR (("user"."name" = $3) AND ("user"."id" = $4))) 35 36console.log(query.values); //['boom', 1, 'bang', 2] 37 38//queries can be named 39var query = user.select(user.star()).from(user).toNamedQuery('user.all'); 40console.log(query.name); //'user.all' 41 42//how about a join? 43var query = user.select(user.name, post.body) 44 .from(user.join(post).on(user.id.equals(post.userId))).toQuery(); 45 46console.log(query.text); //'SELECT "user"."name", "post"."body" FROM "user" INNER JOIN "post" ON ("user"."id" = "post"."userId")' 47 48//this also makes parts of your queries composable, which is handy 49 50var friendship = sql.define({ 51 name: 'friendship', 52 columns: ['userId', 'friendId'] 53}); 54 55var friends = user.as('friends'); 56var userToFriends = user 57 .leftJoin(friendship).on(user.id.equals(friendship.userId)) 58 .leftJoin(friends).on(friendship.friendId.equals(friends.id)); 59 60//and now...compose... 61var friendsWhoHaveLoggedInQuery = user.from(userToFriends).where(friends.lastLogin.isNotNull()); 62//SELECT * FROM "user" 63//LEFT JOIN "friendship" ON ("user"."id" = "friendship"."userId") 64//LEFT JOIN "user" AS "friends" ON ("friendship"."friendId" = "friends"."id") 65//WHERE "friends"."lastLogin" IS NOT NULL 66 67var friendsWhoUseGmailQuery = user.from(userToFriends).where(friends.email.like('%@gmail.com')); 68//SELECT * FROM "user" 69//LEFT JOIN "friendship" ON ("user"."id" = "friendship"."userId") 70//LEFT JOIN "user" AS "friends" ON ("friendship"."friendId" = "friends"."id") 71//WHERE "friends"."email" LIKE %1 72 73//Using different property names for columns 74//helpful if your column name is long or not camelCase 75var user = sql.define({ 76 name: 'user', 77 columns: [{ 78 name: 'id' 79 }, { 80 name: 'state_or_province', 81 property: 'state' 82 } 83 ] 84}); 85 86//now, instead of user.state_or_province, you can just use user.state 87console.log(user.select().where(user.state.equals('WA')).toQuery().text); 88// "SELECT "user".* FROM "user" WHERE ("user"."state_or_province" = $1)"
There are a lot more examples included in the test/dialects folder. We encourage you to read through them if you have any questions on usage!
You can use the sql-generate module
to automatically generate definition files from a database instance. For example,
running node-sql-generate --dsn "mysql://user:password@host/database"
will generate
something similar to:
1// autogenerated by node-sql-generate v0.0.1 on Tue May 21 2013 01:04:12 GMT-0700 (PDT) 2var sql = require('sql'); 3 4/** 5 * SQL definition for database.bar 6 */ 7exports.bar = sql.define({ 8 name: 'bar', 9 columns: [ 10 'id', 11 'foo_id' 12 ] 13}); 14 15/** 16 * SQL definition for database.foo 17 */ 18exports.foo = sql.define({ 19 name: 'foo', 20 columns: [ 21 'id', 22 'field_1', 23 'foo_bar_baz' 24 ] 25}); 26 27/** 28 * Adding a column to an existing table: 29 */ 30var model = sql.define({ name: 'foo', columns: [] }); 31model.addColumn('id'); 32 33// If you try to add another column "id", node-sql will throw an error. 34// You can suppress that error via: 35model.addColumn('id', { noisy: false });
Read the module's documentation for more details.
We love contributions.
node-sql wouldn't be anything without all the contributors and collaborators who've worked on it. If you'd like to become a collaborator here's how it's done:
git pull https://github.com/(your_username)/node-sql
cd node-sql
npm install
npm test
At this point the tests should pass for you. If they don't pass please open an issue with the output or you can even send me an email directly. My email address is on my github profile and also on every commit I contributed in the repo.
Once the tests are passing, modify as you see fit. Please make sure you write tests to cover your modifications. Once you're ready, commit your changes and submit a pull request.
As long as your pull request doesn't have completely off-the-wall changes and it does have tests we will almost always merge it and push it to npm
If you think your changes are too off-the-wall, open an issue or a pull-request without code so we can discuss them before you begin.
Usually after a few high-quality pull requests and friendly interactions we will gladly share collaboration rights with you.
After all, open source belongs to everyone.
MIT
Stable Version
1
0/10
Summary
SQL Injection in sql
Affected Versions
<= 0.78.0
Patched Versions
Reason
no binaries found in the repo
Reason
0 existing vulnerabilities detected
Reason
license file detected
Details
Reason
Found 10/20 approved changesets -- score normalized to 5
Reason
0 commit(s) and 0 issue activity found in the last 90 days -- score normalized to 0
Reason
no effort to earn an OpenSSF best practices badge detected
Reason
security policy file not detected
Details
Reason
project is not fuzzed
Details
Reason
branch protection not enabled on development/release branches
Details
Reason
SAST tool is not run on all commits -- score normalized to 0
Details
Score
Last Scanned on 2024-12-02
The Open Source Security Foundation is a cross-industry collaboration to improve the security of open source software (OSS). The Scorecard provides security health metrics for open source projects.
Learn More