@appku/stashku-sql

v0.8.4

Published

2 years ago

A SQL storage engine for StashKu. Allows RESTful queries to popular SQL databases.

Downloads

0High
0Medium
0Low

chriseaton

stashku sql mssql database

StashKu SQL Engine

Configuration

This engine utilizes environmental variables and configuration objects that are passed from StashKu. StashKu will pass any configuration object with the same engine name to the loaded engine itself (as shown in the example below).

let sku = new StashKu({
    engine: '@append/stashku-sql'
    //below, we put all engine-specific configuration in an object with the same name
    '@append/stashku-sql': {
        host: 'localhost',
        post: 1433,
        auth: {
            user: 'first.last',
            password: 'supersecret'
        },
        options: {
            appName: 'my cool app'
        }
    }
});
...

Available Properties

You can utilize environmental variables or define the values through a configuration object passed to StashKu. This project also loads .env files in it's package directory.

Permissions

Note that the OPTIONS requests may require additional permissions for the user. In Microsoft SQL Server, the user will need the "VIEW DEFINITIONS" permission in order for the engine to detect a database column default value.

GRANT VIEW DEFINITION ON <sometable> TO [someuser]
--or
GRANT VIEW DEFINITION ON SCHEMA::<someschema> TO [someuser]

StashKu Request Customization

This engine supports additional StashKu request metadata, specifically:

For example, the following posts objects (...) to the database server in "batch" mode, without returning the created records.

let res = await stash.post(r => r
    .to('dbo.Locations')
    .objects(...}
    .headers({
        batch: true,
        output: false
    });

Driver-Specific Configuration Properties

If the database driver you are utilizing supports additional configuration properties, you can place these in an .options object (like the appName property shown in the example above).

`sql-server` Driver

This driver utilizes the tedious package under the rhino wrapper, which supports all tedious options. You can find a listing of the tedious options here.

REST

The StashKu SQL storage engine supports all RESTful StashKu methods, GET, POST, PUT, PATCH, and DELETE.

Example GET Operation

To retrieve information from the database...

let stash = new StashKu({
    engine: '@append/stashku-sql'
});
...
let res = await stash.get(r => r
    .from('dbo.Persons')
    .properties('FirstName', 'LastName', 'Title')
    .where(f => f.and('FirstName', Filter.OP.STARTSWITH, 'K'))
    .skip(0)
    .take(15)
    .sort('LastName', Sort.desc('FirstName'))
);
console.log(res);

Example POST Operation

To add information to the database...

let stash = new StashKu({
    engine: '@append/stashku-sql'
});
...
let res = await stash.post(r => r
    .to('dbo.Locations')
    .objects(
        {
            Name: 'America',
            CostRate: 1,
            Availability: 123
        },
        {
            Name: 'Canada',
            CostRate: 2,
            Availability: 234
        },
        {
            Name: 'Brazil',
            CostRate: 3,
            Availability: 345
        }
    )
);
console.log(res);

Running

Run npm start

Building

This project uses node.js to run and does not have an explicit build process.

Code Documentation

You can generate a static JSDoc site under the docs/ path using the command npm run docs.

Test

Run npm test to run jest unit tests.

Run npm run lint to run ESLint, optionally install the Visual Studio Code ESLint extension to have linting issues show in your "Problems" tab and be highlighted.

If you are writing unit tests, you may need to npm install @types/jest to get intellisense in Visual Studio Code if for some reason it did not get installed.

Setup

This project leverages the docker image chriseaton/adventureworks:light using the AdventureWorks sample database (created by Microsoft) to perform database tests.

You can grab and start the AdventureWorks sample database on your system in the background by running:

docker run -p 1433:1433 -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=mySup3r_p4ssw0rd' -d chriseaton/adventureworks:light

The password can be set to whatever you wish. The docker container may take a minute to get initialized once started.

You will need to set the environmental variables to match the docker container's settings, for example:

| ENV | Example Value | |-----|---------------| | STASHKU_SQL_HOST | "localhost" | | STASHKU_SQL_DATABASE | "AdventureWorks" | | STASHKU_SQL_AUTH_USER | "sa" | | STASHKU_SQL_AUTH_PASSWORD | "mySup3r_p4ssw0rd" |

These can also be created using .env files, with the name of the driver being tested, specifically: driver-sql-server.env for Microsoft SQL Server tests, and driver-postgres.env for Postgres tests.