rawsqljs — Generate Javascript code snippet from SQL file

rawsqljs

How do we go about maintaining our code? Same as maintaining a house. Maintain it just a little at a time. You should also start on the first day. Maintaining a code base is about finding better ways at doing what you used to do. Software Business is about writing code to solve client’s problem, but it is not always about solving the problem. How you solve the problem also matters. So you would need to evaluate that piece of code often and often. More maintainable code means more productivity.

Productivity traditionally refers to the ratio between the quantity of software produced and the cost spent for it.

While it may all sound fancy and cool, the development of these softwares can prove to be complex. Modern day systems handling such kind of data can have millions of database transactions per day — the database queries can run into multiple pages can go up to a hundred pages. These queries are responsible for fetching or updating data. Hence it is of utmost importance that these queries are correct and error proof for smooth functioning of businesses.

Photo by Mika Baumeister on Unsplash

One such tool that we have developed is rawsqljs which has the ability to codify your SQL scripts into Javascript functions which can connect to the database and execute the transactions. Please refer to the SQL file below:

Below is the rawsqljs generated Javascript snippet for the above SQL:

work.js

rawsqljs eliminates the SQL errors which can occur when coded manually in Javascript or any other language. Want to make any changes to the SQL? Simply do it in the .sql file and let our tool take care of the rest.

Installation

1. We haven’t published it to npm yet so, install from github

yarn add --dev git+https://github.com/debjyoti-in/rawsqljs.git

or

npm install --save-dev git+https://github.com/debjyoti-in/rawsqljs.git

2. Copy the database connection template file in <project_root>/database

$(npm bin)/rawsql --copy
#or if you have a rawsql target in npm script as described below run
npm run rawsql -- --copy
# Args after -- are passed to the underlying npm script cmd

3. In your package.json ‘s scripts section add a <script_name> and run using npm run <script_name>

package.json

...otheroptions
"scripts": {
  "rawsql": "rawsql"
}
...otheroptions

4. npm run rawsql

It will expect your .sql files in src/database folder and will write the generated files in the same folder.

Usage

Now you can execute each sql statement and get a promise by requiring(require('./database/<sqlfilename>.js)) in the corrosponding generated .js file. The generated file will expect a database/connection.js file exporting a getConnection function which returns a disposable Bluebird promise. In the default setup we get db connection from connection pool and release automatically using bluebird's Promise.using.

See example/ folder for examples

To override the default behavior add a rawsql.conf.js file in your project directory (the same directory where package.json resides). e.g.

//rawsql.conf.js
module.exports = {
  src_glob: 'src/database/sql/*.sql',
  dest_folder: 'src/database/js'
}

Notes

Before every sql statement in a sql file write a name of the of the query followed by its parameter names like. Note: Every sql statement must ends with semicolon(;).

-- @@@ update_works_stage_status_assigned # current_stage # status # assigned_to # id # work_details_table_id
  UPDATE med_works
  SET current_stage=?,
      status=?,
      assigned_to=?
  WHERE id=?
    AND work_details_table_id=?;

The generated js files will export each sql statement by its name. See example/src/database/js/works.js

Photo by Jan Antonin Kolar on Unsplash

By definition, the very idea of software development appears to be at odds with the idea of user-centered design — where we start problem-solving with the people we’re designing for. In my experience, this conflict has been illustrated by software solutions that are cluttered and hard to use in their efforts to display large amounts of complex data. Hence to get rid of much of this complexity; it is important to use tools like this to eliminate any kind of error in SQL queries; the component responsible for handling data.