ORM

ORM is an acronym for object-relational mapping which is a way in which data types in one system can be represented in another using object oriented programming. Common ORM solutions in modern server-side programming include NHibernate and Entity Framework.

WireBootstrap has an ORM solution built on top of it's base query features that creates Typescript classes, interfaces, and enums from tables, views, and stored procedures inside a database, data service, or other data connector end-point. The resulting TypeScript classes allow CRUD operations such as reading, writing, deleting, and updating these objects directly from JavaScript.

The WireBootstrap Query Service uses the WireBootstrap ORM solution for Microsoft SQL Server. It contains a TSQL script that can be run against a database that creates an ORM file. The ORM file can then be added to TypeScript projects in order to program against the SQL objects directly from code.

The files needed for this solution are installed in the \orm directly off of the WireBootstrap Query Service installation root.

This pages uses the Northwind sample database for illustration purposes.

DataSource

Before creating an ORM file, be sure a WireBootstrap DataSource object is available to be used by the objects in the ORM to make calls to the query service. See Service Provider for setting the data source configuration Provider attributes when working with the query service.

export const Northwind = new wire.data.DataSource("sql", {
    ServiceRoot: "http://localhost:1895",
    Provider: {
        ServiceId: "qs_ebi-appdev2",
        SecretKey: "WireBootstrap$",
        ConnectionId: "northwind_ebi-appdev2"
    }
});

SQL Script

In order to generate the TypeScript objects needed for a database ORM, a SQL script needs to be run from SQL Server Management Studio (SSMS) or other SQL Server IDE. The script is located at \orm\wire-mssql-orm.sql inside the installation root.

Script Variables

Once the script is opened in SSMS, three inputs are needed at the top of the SQL Script. These are described below.

Input	Description
Database Name	Replace %DatabaseName% with the name of the database in SQL Server. This is used once in the use statement to set the database current for the execution of the script.
DataSource Name	Replace %DataSourceName% with the name of the WireBootstrap wire.data.DataSource object that will be used to connect to the database in the target project.
DataSource Full Path	Replace %DataSourceFullPath% with the full path to the module file containing the object in %DataSourceName%.

/****************************************************************
	Variables and Replacements
*****************************************************************/

/*
	The database name in SQL Server
*/
use [%DatabaseName%]

/*
	The name of the WireBootstrap wire.data.DataSource object	
	Example: Northwind
*/
declare @datasourceName varchar(max) = '%DataSourceName%'

/*
	The full path to the module file containing %DataSourceName%
	Example: ./datasources/northwind.source.js
*/
declare @datasourceFullPath varchar(max) = '%DataSourceFullPath%'

Running the Script

Once the replacements are complete, the script can be executed. The script will create the contents of the ORM file by printing the output to the Messages tab as seen below.

ORM File

Once the script has finished running, the contents of the Message tab can be copy and pasted into an empty TypeScript file.

/* ./northwind.orm.ts */

import { Northwind } from "./northwind.source.js";
import { ProcedureBase } from "./_procedure-base.js";
import { EntityBase } from "./_entity-base.js";
 
enum Orders_Fields {
  CustomerID = "CustomerID",
  EmployeeID = "EmployeeID",
  Freight = "Freight",
  OrderDate = "OrderDate",
  OrderID = "OrderID",
  RequiredDate = "RequiredDate",
  ShipAddress = "ShipAddress",
  ShipCity = "ShipCity",
  ShipCountry = "ShipCountry",
  ShipName = "ShipName",
  ShippedDate = "ShippedDate",
  ShipPostalCode = "ShipPostalCode",
  ShipRegion = "ShipRegion",
  ShipVia = "ShipVia"
}
 
...

For details on how to use the ORM classes in projects, visit WireBootstrap ORM.