calvium-node-api-lib

calvium-node-api-lib

This repo contains common code for API servers that we write:

• in node
• using express
• providing JSON APIs
• running on Microsoft Azure

Historically, this code has all come from projects in which we provided the entire vertical - API server, webapp and mobile app.

This is not a framework, it's just a library. Users opt into features individually by calling them.

Tests

This library has two sets of tests:

• Unit tests. You invoke them by just running npm install and npm run test.
• Integration tests. They have some dependencies.
  • You need Docker. This is tested using Docker for Mac.
  • You need to make sure no other programs / other docker containers are listening on TCP port 1433.
  • Once you've sorted that out, invoke them by running npm run test-integration.
  • They're likely to be a bit flaky. Sorry.

Debugging simpleQuery

You can set the following settings to 'y' to get output for debugging:

• SIMPLEQUERY_PRINT_QUERIES to print every SQL query you execute.
• SIMPLEQUERY_PRINT_QUERY_ON_ERROR to print SQL queries which fails.
• SIMPLEQUERY_PRINT_TIMINGS to print how long each query took.
• SIMPLEQUERY_PRINT_TIMINGS_PARAMS to print parameter names with the times (makes it easier to see which timing is for which SQL query).
• SIMPLEQUERY_DEBUG to turn all of the above on at once.

I would recommend setting SIMPLEQUERY_PRINT_QUERY_ON_ERROR, SIMPLEQUERY_PRINT_TIMINGS and SIMPLEQUERY_PRINT_TIMINGS_PARAMS for development, then turn on SIMPLEQUERY_PRINT_QUERIES when you're having specific problems with dynamically generated queries returning wrong results.

See the getDBConfigSync() bit in examples/db.example.js for how you'd could pass these in environment variables.

start-local-db

This library provides a binary called start-local-db. It is for starting up a local instance of SQL Server and then load your SQL schema into it.

Initial setup:

To use it in your project:

• create a directory sql/
• create a shell script sql/startLocalClean.sh with contents like the following:

#!/bin/sh
set -e
cd "$(dirname "$0")"
export CONTAINER_NAME="mssql-FILL_IN_PROJECT_NAME-dev"
export SQL_DATA_VARIANT="${1:-main}"
export AZ_MSSQL_PASSWORD="FILL_IN_DEFAULT_PASSWORD"
export AZ_MSSQL_DATABASE="FILL_IN_DATABASE_NAME"
# uses default 1433 if not defined
export AZ_MSSQL_PORT="FILL_IN_DB_PORT"

../node_modules/.bin/start-local-db "$@"

• Fill in the variables in that script.
• Put the initial schema in sql/original.sql
• For each migration, create a file in sql/migrations with a filename like sql/migrations/0001-describe-change-here.sql
• Put "start-local-db": "sh sql/startLocalDB.sh" in the scripts section of your package.json.

Running it:

• Run npm run start-local-db to start the DB server.
• The script will start a local database, load original.sql, then load each of the migrations in ascending order.
• If you're using multiple variants, run npm run start-local-db -- variant-name instead. (See below.)

Data files:

You will probably want to load test data, separately from the migrations.

The start-local-db script will load data files from several different subdirectories of sql/data.

The data files must be numbered, with numbers that match up with a migration. The data files will be run just after the migration with the same number.

e.g. You might put CREATE TABLE unicorn (id bigint IDENTITY PRIMARY KEY, name nvarchar(64) NOT NULL); in sql/migrations/0005-add-unicorns.sql, then INSERT INTO unicorn (name) VALUES (N'Sammy the Iridescent Unicorn King'); in sql/data/test/common/0005-add-unicorn-sammy.sql. The data file is run just after the migration, so that the table will be there for it to insert rows into

Files with the following patterns will be run:

• sql/data/real/common/*.sql
• sql/data/test/common/*.sql
• sql/data/real/${variant-name}/*.sql
• sql/data/test/${variant-name}/*.sql

Variants:

The variant-name is the first argument to start-local-db. If you don't supply one, it defaults to the value you set in SQL_DATA_VARIANT above. The idea of this is that you might have multiple variations of your test data.

For example, I might have two different test data files (say sql/data/test/unicorns/0005-add-unicorn-sammy.sql and sql/data/test/goblins/0005-add-goblin-king-greg.sql). When I run npm run start-local-db -- unicorns, the files in sql/data/test/unicorns/*.sql will be run and ones in sql/data/test/goblins/*.sql will be ignored. The files in sql/data/test/common/*.sql will always be run, regardless of which variant is selected.

Cached snapshots:

start-local-db may take snapshots of the SQL server instance partway through in order to save time next time you run it. The snapshots will show up in the output of docker images. It's harmless to delete them. The snapshots are named based on the hashes of all the contents that went into them (a bit like how git SHAs depend on the contents of the files in each commit), so the script won't attempt to reuse a snapshot if it becomes inapplicable due to the source sql files having been changed.

You can disable this caching by running start-local-db like env START_LOCAL_DB_IGNORE_CACHE=y npm run start-local-db - this will prevent existing snapshots being used and prevent new snapshots from being taken. You hopefully shouldn't ever need to do this, though. Existing snapshots won't be reused if the SQL code that was used to create them is changed.

Post-setup SQL code:

After running all other SQL, migration and data files, start-local-db will run one last file, if it exists:

• sql/postStartLocalDB.sql

If your example data files create entities in the database that expire over time, use sql/postStartLocalDB.sql to reset all the timers on them.