tsrecord

tsrecord is an active record ORM implementation for typescript and javascript es6 and above. it essentially is a secure way for communicating with your database. it abstracts SQL queries under using powerful object oriented system. communicating with database is super simple with this tool. you can communicate with database using javasccript. Yes, without using SQL. this tutorial uses typescript which helps to achieve some type safety.

Installation and Configuration

installation and configuration is simple

npm install tsrecord

this will install it. currently only mysql is supported. it is built in such a way that very easy to add new database using files called drivers.

Now you need to add connection info with database.

import {Config} from "tsrecord"
Config["MYSQL"] = {
        host     : 'localhost',
        user     : 'username',
        database : 'database',
        password : 'password'
}

that's it. it will register our connection details for mysql database. it is not connecting to database, it is just registering this details inside tsrecord config. so, you will not get any error messages for now.

well Installation and Configuaration is done :)

Basic CRUD operations.

CRUD operations means CREATE, READ, UPDATE and DELETE. Yes, they are same as INSERT, SELECT, UPDATE and DELETE in SQL. tsrecord follow active record pattern. it is similar to rails activerecord. here a table is mapped to a class.

consider you have a table with the name flights. you have to create a class with the name Flights. you may wounder why this naming is important. because there are some naming convenssions here.(ofcource you can override conventions if you want)

conventions

these conventions are for helping you to develop applications really faster. if you don't like it you can override

table name

1. table name is plural it's corresponding class name should be its singular. for example the class Flight is equivalent to the table flights
2. table name should be in lower case snake_case and its corresponding class name should be in CamelCase. example: person_names table will have equivalent class PersonName

table structure

1. it assumes each table has a primary key with the name id

the model

a model is a class mapping to a table in a database. consider you have a table flights with the following structure.

###  table structure  ###
id int auto increment
name varchar(50)
route_from varchar(100)
route_to varchar(100)

you can create class Flight like this.

class Flight extends Model{
    public id :number;
    public name :string;
    public route_from :string;
    public route_to :string;
}

guys or girls with rails experience may think why is defining columns as variables needed at all. but for typescript it has its own advantages. if you dont like you can also define like,

class Flight extends Model{
    //yes,its blank compared to previous. but it will work.
}

for typescript i always recommend using the first form with all columns in database declared inside the class.

CREATE (insert)

for inserting data into flights table we can use save() function from our model. this will convert into equivalent INSERT INTO flights... by the model

var a = new Flight();
a.name = "Air india"
a.route_from = "india";
a.route_to = "Singapore";
a.save();

READ (select)

for getting data we can use methods like all() or first(). to do something with this data you have to pass a callback function to these. and for filtering you have where() also.

getting a single row of data

to read a single record you have to use first(). we pass a callback function with an object of Flight as parameter. this will have the required data.

var a = new Flight();
a.first(
    (flight :Flight)=>{
        console.log(flight.name); //Air india
        console.log(flight.route_from) //india
        console.log(flight.route_to) //Singapore
    }
);

getting multiple rows of data

to get multiple rows you have to use all(). we pass a callback function as argument to this method. we get an array of Flight objects as result.

var a = new Flight();
a.all(
    (flights :Flight[])=>{
        console.log(flights);
    }
);

adding conditions

you also have where part of your query using where() function.

the basic form of where clause is

    var flights = new Flight();
    flights.where('name','Air india').all(
    (flights :Flight[])=>{
        console.log(flights);
    }
    //this translates to "...WHERE name = 'Air india'"
);

if you want to use some other conditions other than = then you can use that as second parameter like

flights.where('name','!=','Air india').all(
    (flights :Flight[])=>{
        console.log(flights);
    }
);
// means ".. WHERE name !=  'Air india'"

you can also chain multiple where conditions,

flights.where('name','Air india').where('route_from','india')all(
    (flights :Flight[])=>{
        console.log(flights);
    }
);

// translates to ".. WHERE name =  'Air india' AND route_from = 'india'"

if you want OR conditions, you have orWhere(),

flights.where('name','Air india').orWhere('route_from','india')all(
    (flights :Flight[])=>{
        console.log(flights);
    }
);

// translates to ".. WHERE name =  'Air india' OR route_from = 'india'"

finally there is an advanced form of where clause when you want to nest where clause with callback function

 where((b)=>{b.where('name','Air india').orWhere('name','Jet Airways')}).orWhere('route_from','india')
// translates to ".. WHERE (name = 'Air india' or name = 'Jet Airways') OR route_from = 'india'

DELETE

you can perform an sql DELETE operation easily

var flight = new Flight();
flight.where("id","2").delete();

UPDATE

UPDATE operations is just saving some updated data using save() after getting it using a read query.

var a = new Flight();
a.where("id","1").first(
    (flight :Flight)=>{
        flight.name = "Air Asia"
        flight.route_from = "Shanghai" 
        flight.save();
        //content updated
    }
);