Thursday, 4 May 2017

Basic model with 1:n association with Sequelize and SQL Server

I'm looking into Sequelize right now and I have a problem with setting up a simple model which is based on two tables in my SQL Server DB.

What I have:

In my DB there are two tables, Stations and Datapoints. They are independent of one another and there are no constrains etc. (I can't change the DB structure, btw):

Table Stations:

[Id] [int] IDENTITY(1,1) NOT NULL
[Text] [nvarchar](255) NULL

Table Datapoints:

[Id] [int] IDENTITY(1,1) NOT NULL
[StationId] [int] NULL
[DataPointId] [int] NULL

Table Stations contains multiple stations. Each Station can have multiple datapoints.

What I want to achieve:

I want to find a station by its Id and include the associated datapoints in the model. Here's what my model should look like:

var exampleStation = {
  Id: 1,
  Text: 'My first Station',
  Datapoints: [
    {Id: 12, DataPointId: 117},
    {Id: 13, DataPointId: 122},
    {Id: 14, DataPointId: 123},
    {Id: 15, DataPointId: 258}
  ]
}

Do I need to define a station model and a datapoint model independently and then put the datapoints into the station object "by hand" when querying the DB? Or do I have to have to incorporate the datapoints array into the station object already in the Sequelize model definition?

Based on my description, can someone give me an explanatory model(s) definition and maybe a simple query that would produce an object as given in the example above?



via Robert

No comments:

Post a Comment