Setting Up a Node & TypeScript Project - Setup DB & ORM
Published on
We have our base project setup with Express, but what about connecting to a databse? We could just use raw SQL queries and that might be a good option for some project. Or we could use an ORM. An ORM, or Object Relational Mapper, is a library/framework that allows for the bi-directional mapping of your OOP model classes to relational database tables. It basically abstracts away SQL querying and result set mapping to our TypeScript models into a library to simplify our data access layer.
The two big ORM’s in Node are Sequelize and TypeORM with the later being the newest of the two. We’re going to use TypeORM. TypeORM supports both DataMapper and ActiveRecord patterns. Since I come from a Hibernate/Entity Framework background, we’re going to setup our project with the DataMapper pattern but if you prefer the ActiveRecord approach like Eloquent/Larvel, feel free make changes where necessary, TypeORM has excellent documentation on how to setup both.
We are also going to be using Sqlite for our project simply because it’s quick and easy to setup.
Prerequisites
- Setting Up a Node & TypeScript Project - Initial Setup
- Setting Up a Node & TypeScript Project - Configure Jest
- Setting Up a Node & TypeScript Project - Configure Tooling
- Setting Up a Node & TypeScript Project - Setup Express
- OR Clone this github repository
Installing Our Dependencies
First we need to install our dependencies as usual. There are only a few we are going to need: typeorm, reflect-metadata, sqlite3.
npm install --save typeorm reflect-metadata sqlite3
The typeorm package is the core TypeORM library. We’ll also need reflect-metatdata for our TypeORM decorators. And of course sqlite3 for our Sqlite database.
Configuring TypeORM
TypeORM supports many configuration file formats include .env files so if you wish to do so, you can configure TypeORM in your already existing .env file. For our project we’re going to create a ormconfig.json file for our TypeORM configuration. Create your ormconfig.json file at the root of the project touch ormconfig.json with the following json data…
{
"type": "sqlite",
"database": "database.sqlite",
"synchronize": true,
"logging": false,
"entities": [
"src/app/data/entities/**/*.entity.ts"
],
"migrations": [
"src/app/data/migrations/**/*.ts"
],
"subscribers": [
"src/app/data/subscribers/**/*.subscriber.ts"
],
"cli": {
"entitiesDir": "src/app/data/entities",
"migrationsDir": "src/app/data/migrations",
"subscribersDir": "src/app/data/subscribers"
}
}
Your ormconfig.json contains your connection details as well has where your entities, migrations, and subscribers are located in your project. Now we can create our data directories and barrel file…
# Create directories
mkdir src/app/data \
src/app/data/migrations \
src/app/data/entities \
src/app/data/subscribers
# Create our data directory barrel file
touch src/app/data/index.ts
TypeORM also requires us to add three additional properties to our tsconfig.json file. Open your tsconfig and add strictPropertyInitialization, experimentalDecorators, and emitDecoratorMetadata. These properties are needed for our entity classes in order to use the TypeORM decorators.
{
"compilerOptions": {
"target": "es6",
"module": "commonjs",
"strict": true,
"esModuleInterop": true,
"noImplicitAny": true,
"moduleResolution": "node",
"sourceMap": true,
"outDir": "dist",
"baseUrl": ".",
"forceConsistentCasingInFileNames": true,
"strictPropertyInitialization": false,
"experimentalDecorators": true,
"emitDecoratorMetadata": true,
"paths": {
"*": [
"node_modules/*"
]
}
},
"include": ["src/**/*"],
"exclude": ["node_modules"]
}
We also can add a couple scripts to our package.json file to help us create and run our migrations. Create two scripts, migrations:gen and migrations:run…
{
...
"scripts": {
"test": "jest",
"coverage": "jest --coverage",
"lint": "eslint \"{src,test}/**/*.{js,ts}\"",
"lint-fix": "npm run lint -- --fix",
"migrations:gen": "ts-node ./node_modules/.bin/typeorm migration:generate",
"migrations:run": "ts-node ./node_modules/.bin/typeorm migration:run",
"start": "ts-node src/index.ts",
"build": "tsc"
},
...
}
And finally we will need to initialize our database connection in our entry point file, src/index.ts, by creating our database connection pool with TypeORMS’s createConnection function…
import 'reflect-metadata';
import { createConnection } from 'typeorm';
import { Application } from './app';
import * as dotenv from 'dotenv';
dotenv.config();
createConnection().then(async () => {
const application: Application = new Application();
application.startServer();
});
Creating An Entity Model
There is serveral approaches for database to entity mapping. One way is creating and mapping your entities based on an already existing database also known as a database first approach. The other approach is called code first where you create your entities first and a database schema is generated based on those entity classes (this is where migrations will come in to play). Since we don’t have an existing database, we are going to go with the code first approach and let TypeORM create our databse based on our entities.
Expanding on our previous post, we’re going to create a User entity. We will use inheritance by creating an abstract base class which all of our entities will extend. The abstract base class will hold all common attribute/columns shared by all of our entities to keep our entities DRY. Since all tables in a relational database required a primary key, our abstract base class will hold a unique id (primary key) that all entities will require. You could also include things like creation timestamps in this base class as well.
Create our abstract base class with `touch src/app/data/entities/base.entity.ts
import { PrimaryGeneratedColumn } from 'typeorm';
export abstract class BaseEntity {
@PrimaryGeneratedColumn()
public id: number;
}
Now create our User entity, touch src/app/data/entities/user.entity.ts…
import { Column, Entity, JoinTable, ManyToMany } from 'typeorm';
import { BaseEntity } from './base.entity';
@Entity()
export class User extends BaseEntity {
@Column({ unique: true })
public username: string;
@Column()
public fistname: string;
@Column()
public lastname: string;
}
Export our entity in our data/index.ts barrel file…
export * from './entities/user.entity.ts';
Creating a Migration
If you aren’t familiar with migrations, you can think of a migration as a way to keep track of changes to a database schema. A migration is created based on our entity classes. If we make a change to an entity class, we can create a migration which will contain all the SQL statements that need to run in order to keep our entity models and database schema in sync. You can kinda of think of a migration as a version control for a database schema in a way. Lets create our first migration.
We can use the script we added to our package.json file to create our first migration. The -n in the command will allow us to name our migration.
npm run migration:gen -- -n "generate-and-seed-users-table"
After generating the migration, you will see that a database.sqlite file was create at the root of our project. We will need to add this file to our `.gitignore since we do not want to commit our database to our repository.
dist/
node_modules/
coverage/
*.sqlite
Also if you check your migrations directory, you should see our newly generate migration file. We’re going to make a quick edit to this file. Since I haven’t been able to find a simple way to create seeder files with TypeORM, I generally modifiy the migrations file to add temporary seed data when running the migration. If there is a better way to do this, please reach out and let me know, I’d love to learn a better way. We will add a private method called seed() to our migration and call it after our table is created in the up(...) method of the migration class.
import { MigrationInterface, QueryRunner, getRepository } from 'typeorm';
import { User } from '../entities/user.entity';
export class GenerateAndSeedUserTable1585862017523 implements MigrationInterface {
name = 'generateAndSeedUserTable1585862017523';
public async up(queryRunner: QueryRunner): Promise<void> {
await queryRunner.query(
`CREATE TABLE "user" ("id" integer PRIMARY KEY AUTOINCREMENT NOT NULL, "username" varchar NOT NULL, "firstname" varchar NOT NULL, "lastname" varchar NOT NULL, CONSTRAINT "UQ_78a916df40e02a9deb1c4b75edb" UNIQUE ("username"))`,
undefined,
);
await this.seed();
}
public async down(queryRunner: QueryRunner): Promise<void> {
await queryRunner.query(`DROP TABLE "user"`, undefined);
}
private async seed(): Promise<void> {
await getRepository(User).save([
{
id: 1,
username: 'jmw5598',
firstname: 'Jason',
lastname: 'White',
},
{
id: 2,
username: 'djt2020',
firstname: 'Daniel',
lastname: 'Townswell',
},
{
id: 3,
username: 'dlw3512',
firstname: 'Danielle',
lastname: 'Whitemore',
},
] as User[]);
}
}
Now we can run our migrations with npm run migrations:run. Your output should look similar to this…
Connecting & Getting Our Users From the Database
Next we are going to refactor our UsersController by abstracting out our data access logic to our repository layer. We are going to follow some of SOLID priciples by hiding our repository implementation behind an interface. We will then use dependency inversion to provide a repository to our UsersController through the constructor.
Lets start by creating files for our repository interface and our users repository implementation.
touch src/app/repositories/repository.interface.ts src/app/repositories/users.repository.ts
Our simple repository interface…
export interface IRepository<T> {
findAll(): Promise<T[]>;
}
Our users repository implementation which will implement our IRepository…
import { getRepository, Repository } from 'typeorm';
import { IRepository } from './repository.interface';
import { User } from '../data';
export class UsersRepository implements IRepository<User> {
public async findAll(): Promise<User[]> {
const repository: Repository<User> = getRepository(User);
return repository.find();
}
}
Update our repositories/ directory’s barrel file (src/app/repositories/index.ts)…
export * from './repository.interface';
export * from './users.repository';
Now we can update our controller to use dependency inversion by accepting an IRepository as a constructor argument. We will also update our getAllUsers(...) method to use that repository.
import { Request, Response, NextFunction } from 'express';
import { IRepository } from '../repositories';
import { User } from '../data';
export class UsersController {
private readonly _repository: IRepository<User>;
constructor(repository: IRepository<User>) {
this._repository = repository;
}
public async getAllUsers(request: Request, response: Response, next: NextFunction): Promise<any> {
return this._repository
.findAll()
.then((users) => response.status(200).send(users))
.catch((error) => response.status(500).send({ error: error }));
}
}
Update our users.router.ts to create and provide an IRepository to our UsersController…
import express, { Request, Response, Router, NextFunction } from 'express';
import { UsersController } from '../controllers';
import { IRepository, UsersRepository } from '../repositories';
import { User } from '../data';
const router: Router = express.Router();
const usersRepository: IRepository<User> = new UsersRepository();
const controller: UsersController = new UsersController(usersRepository);
router.get('/', async (request: Request, response: Response, next: NextFunction) => {
await controller.getAllUsers(request, response, next);
});
export const usersRouter: Router = router;
By using depenency inversion, our controller is loosely coupled with our data access layer and not tighly tied to TypeORM, so down the road if we wanted to switch to sequelize or maybe just raw SQL queries and ditch the ORM completely, we won’t have to change any logic in our Controller (or anywhere that uses an IRepository interface). We would simply create new implentation of our IRepository and provide the new implementation to the constructor of our controller. Our code becomes more play-doh like and less like trying to bend glass.
Using dependency inversion also makes testing easier for us as well. Since we’re able to control the dependencies of our UsersController, we’re able to create a mock repository when isolating and testing our UsersControllers methods. You can learn a bit more about SOLID priciples here
The completed github repository can be found here