Strongly-typed database access with Node.JS and TypeScript

Question

When we use C#, we can access our database in a strongly-typed manner using Code-First approach:

public class Blog 
{ 
    public int BlogId { get; set; } 
    public string Name { get; set; } 
    public virtual List<Post> Posts { get; set; } 
}
...
public class Database : DbContext 
{ 
    public DbSet<Blog> Blogs { get; set; } 
    public DbSet<Post> Posts { get; set; } 
}
var db = new Database()
var blog = new Blog { Name = "My new blog", BlogId = 1 }; 
db.Blogs.Add(blog); 
db.SaveChanges(); // save object to database

The compiler will encure that we only access existing properties/methods and also that we use correct types everywhere in our code.

How can I do the same with TypeScript and Node.JS?

I found Knex.JS and bookshelf libraries for database access, but I cannot find any samples on how to uses them with strongly-typed TypeScript objects and classes.

Answer 1

I searched the web for examples on how to use Bookshelfjs with Typescript and there were no articles or blog posts that could help. I did find tests distributed as part of the DefinatelyTyped test file on github, which were a good starting point. Further, most likely you would want to store each model in its own file, which would require the Bookshelfjs registry plugin. This article explains why but in the context of regular javascript.

Putting it all together, assuming you've installed typings for knexjs and bookshelfjs properly. Using your code as inspiration, read further:

You might have a file called "Config.ts" that has all your database details in it:

import * as Knex from 'knex';
import * as Bookshelf from 'bookshelf';

export class Config {

    private static _knex:Knex = Knex({
        client: 'mysql',
        connection: {
            host     : '127.0.0.1',
            user     : 'your_database_user',
            password : 'your_database_password',
            database : 'myapp_test',
            charset  : 'utf8'
        }
    });

    private static bookshelf:Bookshelf = Bookshelf(Config.knex);

    public static bookshelf(): Bookshelf {
        Config.bookshelf.plugin('registry');
        return Config._bookshelf;
    }
}

You might have a filed called "Blog.ts" to hold the Blog model (and another called "Post.ts" to hold the Post model):

import {Config} from './Config';
import {Post} from './Post';

export class Blog extends Config.bookshelf.Model<Blog> 
{ 
    get tableName() { return 'books'; }

    // strongly typed model properties linked to columns in table
    public get BlogId(): number {return this.get('id');}
    public set BlogId(value: number) {this.set({id: value})}
    public get Name(): string {return this.get('name');}
    public set Name(value: string) {this.set({name: value});}

    posts(): Bookshelf.Collection<Post> {
        return this.hasMany(Post);
    } 
}

module.exports = Server.bookshelf.model('Blog', Blog);

And in your "App.ts" file you would run your code like so:

import {Config} from './Config';
import {Blog} from './Blog';

var blog = new Blog();
blog.set({ Name : "My new blog", BlogId : 1 }); 
    .save(); 

I haven't tested the code here so I may have some small typos but you get the idea. Note that I've used title case for class properties, but I've used snake case for the database fields. For Bookshelf to work out of the box certain naming conventions must be adhered to like the Id field for every table being called 'id' and foreign keys for relationships have singular version of table name (e.g. for users table, the Id in the table would be 'id' but the foreign key in the login table would be 'user_id').

Anyway, whe best way to figure out how to use Bookshelfjs with TypeScript thought (in light of the lack of documentation on the subject) would be to take a look at the Bookshelfjs documentation in combination with the DefinatelyTyped typedef bookshelf.d.ts file.

Answer 2

You can find type definitions for a lot of js libraries in the DefinitelyTyped repository on github.

You can also add those definition files to your project using the tsd tool found here.

in short you need to run these 4 commands in a terminal.

$ npm install tsd -g
$ tsd init
$ tsd install knex
$ tsd install bookshelf