Data migrations in Node applications

Setup db-migrate tool

As your application grow your database model evolve too.  The changes from one version to another is made by migrations. Each migration in the db-migrate tool is in essence  a small javascript program. You can code your migration changes manually in the javascript syntax or generate small javascript program (with –sql-file option) to execute your SQL script files. One for the UP and one for the DOWN function of migration.

up: you upgrade your database schema to the new version
down: you reverse latest changes to the previous version
create: create new migration, with an –sql-file option create a SQL file runner


We install db-migrate module and corresponding database driver with npm.

Depends where you install it the command is available as:

It is possible to add local ./node_module/.bin folder to the local path variable as I described in this article and call it the same way as you would if you install it in to the global space.


Minimal config file (database.json) to work with postgres database in the development environment:

The file should be in the main project folder. More about configuration.

Migrations with SQL files

To create new empty migration use the command:

By default your migration javascript files reside in the “./migrations” folder and sql files in the “./migrations/sqls”  subfolder.

Three new files are created, all files are prefixed with the timestamp which represent order of execution.

Additional two files with the suffix sql are prepared for your upgrade script and downgrade script. If you wish to have ability to downgrade database to the previous level make sure you write down script to.

Example of upgrade script (it is just for the sake of example, you basically write DDL statements with the familiar SQL syntax):

Example of downgrade script:

Run “db-migrate up” command and all your migrations which are not yet executed will run.

The migration log is kept in the database migrations table. The table is automatically created at first run.

Other useful commands

reset: will rewind your database to the state before first migration is called

Using db-migrate with typescript

If you write node programs with the typescript you probably wish to use it with migration to. I didn’t go in this direction simply because I write my scripts in SQL language and runners are just perfect already in javascript. Because the migration are already in the javascript (the runner part), you should exclude migrations folder from the typescript compiler path.

Sample tsconfig.json file with excluded migrations folder :

I didn’t include migration scripts to production deployment code yet, that step would be necessary if you wan’t to upgrade database automatically after deployment.

External links

Database migration tool: Db-migrate.
Gui prototyping and drawing tool: The pencil.

Testing TypeScript Node app with Jasmine

Setup Jasmine testing framework with Node and TypeScript

My typescript project setup is described in this article. To be able to write tests in typescript with Jasmine framework we need to setup project environment with some Jasmine specifics.

This scaffold project is also available on the github.

Initialize typescript compiler and linter :

Compiled javascript files for code and jasmine test specifications will be saved to “dist” folder.

Now we need to install Jasmine and jasmine to typescript reporter:

Jasmine typescript console reporter (jasmine-ts-console-reporter) will report errors based on typescript files instead of javascript files. It will tell you which test and the location in the file failed referring to typescript files instead of javascript files.

Initialize jasmine in your project :

Change generated jasmine.json to match this one:

Create special helper to enable “jasmine to typescript” console reporter. In the spec/helpers folder add new file “ts_console.ts” with the content:

Prepare test code

Write some program logic and tests:

To test this simple api we create “myapi-spec.ts” file in the spec folder:

The full project structure:

To compile all typescript to javascript run “tsc” command in the root of the project.

And to run jasmine tests we run locally installed jasmine with the command:

If everything is correct, jasmine will report result of the run in the console:

Because we add additional “typescript console reporter” to jasmine, the errors reported are referenced to typescript (.ts) files with exact line/column and error message.

Integrate jasmine test results with Code editor

First we need to understand how vs code editor tasks works. As we see in jasmine report, each failure is described in multiple lines. The problem matcher should analyze multiple lines pattern, this is described here.

Problems tab should display errors in tests after test run

Create task for running jasmine and to intercept errors as “problems” in the visual studio code problems tab:

As we see in the screenshot, the exceptions in jasmine test results are intercepted by vs code editor and presented as “problems”.  If you click on the single problem line, the file under the error will be opened and cursor will be moved to the corresponding location.


In the .vscode folder create “tasks.json” file with the jasmine command and additional logic to parse results (problemMatcher) :

How to write regular expressions

If you will study the pattern matching you should remember that matcher works on the console results line by line. All patterns together (all regexp lines patterns) represent one intercepted error.

If you need to change any of patterns in the future, the regex string in the tasks.json file is written in the “escaped” form. To develop and test patterns interactively, use this interactive javascript regex tester.  You can copy specific line from the result (line by line!) into the regex tester and develop a pattern to recognize searched variables.

Some problems still exists:

Because the matching is done line by line, when console window is small, and the lines are wrapped, they are not consistent with the written lines from the jasmine reporter anymore. The reported errors are wrapped at the width of the console and pattern matching doesn’t recognize them as errors any more.

You can somehow mitigate that with larger terminal window or with smaller terminal window font. This way the reported errors will remain in single lines consistent with the jasmine reporter. I wish that this wrapping could be disabled but for now it is not an option.

To change the size of the font in the terminal window add this settings to the settings.json file:

Anyway, this problem is already recognized and I hope it will be resolved in the future.

Create a Node cluster with Koa and TypeScript


Any JavaScript program runs in single OS process but of course is able to process requests asynchronously.  On the Node server we are able to start multiple processes.  To do that we use cluster module, so you are not really limited to one CPU core anymore.

Before start you need to install project prerequisites:

If you need help with basic project setup look at this blog article where I describe basic setup steps.

Create Koa web server application

The purpose of this server application is for testing cluster technology only.  The application support two routes , first is default route (/) for checking on which process runs and another (/stop) for stopping active process. The console log show workers statuses from starting , stopping and restarting.

If you want to debug this server application just create simple index.ts and start the ServerRun class.

Create cluster server start

To start this server in the cluster we need to fork process workers and start koa application.

To start this server we need to compile everything to TypeScript first and then start node with “run_cluster” procedure.

As we see on the console the server start multiple processes and listen on the same port. Well, that’s whole point, to be able to send requests to the same address and server will divide load to multiple processes.

Test the server

Now if you go to browser and navigate to “localhost:3002/”, the server will respond with value of the current process id and count variable. Try refreshing multiple times, the counter will always increase by 1. If you then kill the process and navigate again, you will get another process id and the counter will start again with one. Each process has his own Koa application instance.

Kill the process and test server robustness

If you navigate to “localhost:3002/stop”, you will essentially kill current process. After irrecoverable failure on the server process,  cluster “exit” event is triggered and we simply start new process again.



Setting up the Environment for Node.js and TypeScript

To be productive as a programmer the write/run/debug cycle should be as fast as possible.

Because I am a little spoiled by angular-cli setup for client side web development I search for similar experience in the server side. It means that transpiling the typescript to javascript and node server restarts should be automatic and with hassle free debugging.

Install prerequisites

To create basic node application in the current folder use this command :

Install additional features needed for the application:

If you wish to write with express framework you will need additional libs:


The structure of the project folder

Server application is running in completely different project (folder) as angular client application. To develop client and server I will run two instances of vscode editors, each open corresponding folder.

The server folder contains this base structure:

I created src sub-folder where all source files will reside. For now I put index.ts file in it.

Setup typescript compiler

Create tsconfig.json file in the root folder of the project :

Compile and run the node application

Transpile (compile) typescript into javascript and watch any changes (use local tsconfig.json configuration):

Start node with debug (inspect) and in auto restart mode (nodemon) :

Create combined npm command in package.json file

To specify multiple commands in the npm  under one script command, divide them with && character. Let’s create a “server” command:

  •  “tsc“: start typescript compiler in watch mode,
  •  “|” : pipe character allow to add aditional command in same script
  • nodemon“:  this command will start and restart “node” server after each detected change made by tsc compiler. Nodemon watch destination folder because it need to restart only after javascript files are changed (after transpile).

Before running nodemon for the first time in the project, you need to have compiled index.js at the required place (“dist folder”) so you need to compile at least first time the whole application manually (run tsc from the command line). After that initialization the watchers will watch for changes in source file automatically.

Run combined command from the terminal:

Be aware of the small problem, if you ran the server inside vscode terminal window and then close the editor after that, without canceling the running process (with Ctrl+C), the node server will not automatically stop. You will need to find it between the running processes and stop it manually. To eliminate this obstacle create task and run combined command as task.


To ease start and stop of “npm run server” command and to have better control over all started processes, create a task and a task runner for the “server” command.

if you do not have “.vscode” folder  and tasks.json file yet you create it with /Tasks/Configure tasks menu option.

Inside tasks.json file (in .vscode folder) define the command in that form (use version 0.1.0, the version 2.0.0 has problem with task termination on exiting editor):

To run the task in vscode editor, open Tasks menu, open Run task and select run server task.

When you want to terminate the task, it means that stop will terminate the tsc instance and all node instances started by the “server” command.  From the Tasks menu select  Terminate task… .

If you close the editor with File/Exit , the program should also ask to terminate still running task.

If you omit “promptOnClose” setting you will not be interrupted at the exit from the editor and the processes will close as expected.


Keyboard shortcuts

Find command (F1)  “Open keyboard shortcuts file” and add definition to it.

Press F1 and search for keyboard … The file is located in the Roaming folder in usual windows users location.


Create attach debuger command

To intercept debug breakpoints in code you need to attach to running node process with a debugger session in the vscode editor. Create new “attach” launch configuration in .vscode/launch.json file:

Start debugging with “Attach by Process ID” configuration, then select the running node server instance . You can use F5 key when staying in the source file.

Now execution should stop at defined breakpoints as expected.

Debugging doesn’t stop the tsc & nodemon processes ! In fact you are only attached to your running server process. If you detach from it, you will be able to work forward as you never dive into debugging.  If you change any typescript file and save the file, watchers will detect change and restart all processes. Though you will be automatically detached from the debugging session.

Sample index.ts file

The file is a simplest REST service example in express/node program written in typescript, needed to test compiling, debugging environment.

Final version of package.json file:

Not sure if is important in this setup, but I use next workspace vscode settings:

And the user settings for vscode :

Installed visual studio code extensions

Setup GIT BASH as internal terminal inside code

Download and install portable GIT Bash .

Create .bash_profile in /Users/_user_name_/ folder.


Add current project node modules binary folder to the path

Add at least “./node_modules/.bin/” folder to bash profile, to be able to call locally installed modules directly inside internal terminal window. The internal terminal window will open in the project root folder.

To be able to use git bash terminal inside visual studio code we add the terminal setup command in user settings:

Don’t forget to define .bash_profile file in the shell arguments.

After everything is properly set up you will be able to use internal projects node programs directly from the command line:

How to setup git to run with ssh I described here.