Overview

Setting up an ORM in Node JS using the following:

Pre-prerequisites

You will need the following things properly installed on your computer.

  • node >= 5
  • npm >= 3

To install the above, it is highly recommended to use a node version manager - https://github.com/creationix/nvm
  • MySQL and/or Postgresql

Pre-requisites

  • typescript >= 1.8 npm install -g typescript
  • typings >= 1.x npm install -g typings
  • grunt-cli npm install -g grunt-cli

(05/20/2016) - check the version of typings you are using. if it’s < 1.x, there are breaking changes that 1.x has introduced and you will need to install the latest version.

Database Setup

In this post, we’ll use a sample “Employee” database. Instructions for how to install into MySQL and Postgres are below.

Feel free to install one or both dialects. We’ll need at least one though.

Setting up a Sample Employees MySql Database

  • Goto to the following url: https://launchpad.net/test-db/+download
  • Click the link to download the file: employees_db-full-1.0.6.tar.bz2
  • Once downloaded, decompress the file.
  • If you are running MySql >= 5.7.5, you will need to edit the file, “employees.sql” so that it will work with your version of MySQL, otherwise continue to next step.
    • For versions >= 5.7.5, change all instances of the variable storage_engine to default_storage_engine, found in the .sql files. You can probably get away with just doing a global replace, if you’re editor supports it.
  • From the command line, make sure you’re at the root of the decompressed directory. Inside of that directory, you will see all the necessary scripts for creating the sample “employee” database.
  • Execute the following command at the commandline prompt:

    • Create the “employees” database and execute the SQL script to create tables and load data:

      mysql -u [database-username] -p -t < employees.sql

Setting up a Sample Employees Postgres Database

  • Clone the following repository: https://github.com/keunlee/pgsql-sample-data.git
  • Once cloned, from the command line, change directories to the “employees” directory. Inside of that directory, you will see all the necessary scripts for creating the sample “employee” database.
  • Execute the following commands at the commandline prompt:

    • Create the “employees” database:

      createdb employees -U [database-username]

    • Execute the SQL script to create tables and load data:

      psql -f employees.sql -d employees -U [database-username]

Generating our Entity Model and Repository Layer

(1) Clone and build “Sequelize-Auto-TS”

Now that we’ve got a database to work with, we’re going to take a database to code approach to generate our entity models and persistence layer. At this point, our database schema is defined as well it’s relational constraints.

We’ll be utilizing an open source tool, which will generate our models and persistence layer.

Clone the following repository: https://github.com/keunlee/sequelize-auto-ts.git

Run the following at the root of the source:

1
2
3
npm install
typings install
npm run build

All commands should run successfully without error at this point.

(2) Run “Sequelize-Auto-TS” against existing database


A few notes about the tool, ‘Sequelize-Auto-TS’:



  • When running the tool, best to specify a ‘relative’ output directory. There are a few bugs that need to get addressed to allow for ‘absolute’ and nested ‘relative’ paths.

  • Composite Primary and Foreign Keys are currently NOT supported in Sequelize. Hence as a result, are NOT supported by this library.


Run the following from the root of ‘Sequelize-Auto-TS’:

create a temp directory to output generated files to:

1
mkdir temp

generate our models and repository layer:

1
node lib/cli.js [database-name] [username] [password] [output-path] [database-dialect]

for example:

1
node lib/cli.js employees dbuser password temp postgres

at this point you should now end up with three files in the “temp” folder:

1
2
3
sequelize-models.ts
sequelize-names.ts
sequelize-types.ts

Hold on to these generated files for now. We’ll come back to them. For more info about these generated files, take a look at the repository’s README from which they came from - https://github.com/keunlee/sequelize-auto-ts

Join next time in part II of this series:

Setting up an ORM in Node JS - Part II - Integrating our Entity Model and Repository Layer