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
andnpm 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.
If you are using a Mac with an "Apple Silicon" CPU, see the section "start-local-db on MacOS with Apple Silicon" below.
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 likesql/migrations/0001-describe-change-here.sql
- Put
"start-local-db": "sh sql/startLocalDB.sh"
in thescripts
section of yourpackage.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.
start-local-db on MacOS with Apple Silicon
-
If your computer has an Intel or AMD CPU, you can just install Docker normally. Skip this section.
-
If your computer has an "Apple Silicon" CPU and is running on macOS Ventura (macOS 13) or later, you can just install Docker normally, then
- Enable in Settings > General > Choose file sharing implementation > VirtioFS
- Enable in Settings > Features in Development > Beta Features > Use Rosetta for x86/amd64 emulation on Apple Silicon
-
If you have an Apple Silicon Mac with an older macOS version you need an amd64 virtual machine to run Docker, follow the steps below.
There's an incompatiblity between current versions of MS SQL Server and Docker on "Apple Silicon" CPUs. This leads to SQL Server crashing with an error like:
Invalid mapping of address 0x40080f9000 in reserved address space below 0x400000000000
To fix this, use an amd64 virtual machine.
- Install UTM:
- You should be able to install it with brew, by running:
brew install utm
- (If the brew version doesn't work for you, instead install it by downloading the dmg from https://mac.getutm.app/ and dragging the UTM app to the Applications folder.)
- You should be able to install it with brew, by running:
- Create an amd64 virtual machine with docker in it, listening on port 12376
- Port forward ports 1433 and 12376 to the virtual machine
- See instructions below for creating the VM image
- (Start the VM by double clicking it in UTM. When it reaches the login prompt, Docker should be running.)
- Tell docker to use the virtual machine to create containers:
docker context create slowvm --docker 'host=tcp://127.0.0.1:12376'
docker context use slowvm
- now if you run
docker images
you should see output like the following:
$ docker images
REPOSITORY TAG IMAGE ID CREATED SIZE
hello-world latest feb5d9fea6a5 9 months ago 13.3kB
- Now you should be able to use start-local-db.
- Run
npm run test-integration
to verify that everything is working.
Creating the amd64 virtual machine
The following instructions were based on https://kitloong.medium.com/how-to-run-sql-server-2019-on-macbook-pro-m1-d1448525f805
- install UTM
- create a new "slow" emulated amd64 virtual machine
- set the VM to have 2GB of RAM, 2 CPUs and 60GB of disk space
- use debian-11.3.0-amd64-netinst.iso to install Debian 11
- (say no to most stuff in the installer, use guided partitioning for the entire disk)
- inside the Debian VM, run:
apt-get install ca-certificates curl gnupg lsb-release
curl -fsSL https://download.docker.com/linux/debian/gpg | gpg --dearmor -o /usr/share/keyrings/docker-archive-keyring.gpg
echo "deb [arch=$(dpkg --print-architecture) signed-by=/usr/share/keyrings/docker-archive-keyring.gpg] https://download.docker.com/linux/debian \
$(lsb_release -cs) stable" | tee /etc/apt/sources.list.d/docker.list > /dev/null
apt-get update
apt-get install docker-ce docker-ce-cli containerd.io
docker run -t -i --rm hello-world
- Edit /etc/systemd/system/sockets.target.wants/docker.socket
- Comment out the lines under the
[Socket]
section by putting '#'s in front - Add a line
ListenSocket=2376
- Run
/usr/sbin/shutdown -h now
to switch the VM off - Add port forwards:
- 12322 -> 22, for ssh
- 12376 -> 2376, for the docker daemon
- 1433 -> 1433, for the MS SQL server
- Export the VM at this point
- Now it works as a docker context from outside