title: Using mongoose style schemas MSSQL in Node.js
body: <p>I am currently writing an API with Express.js and have seen many examples about how to create schemas/models for mongodb, but was wondering if there is any similar solution for MSSQL. For example, I have the following method in a C# controller:</p>
  
  <pre><code>public void SubmitExpenses(List&lt;Expense&gt; expenses)
          {
              using (cnxn)
              {
                  cnxn.Open();
                  for (int i = 0; i &lt; expenses.Count; i++)
                  {
                      int employeeId = expenses.ElementAt(i).employeeId;
                      string expenseDate = expenses.ElementAt(i).expenseDate;
                      int taskId = expenses.ElementAt(i).taskId;
                      int expenseTypeId = expenses.ElementAt(i).expenseTypeId;
                      int billingCategory = expenses.ElementAt(i).billingCategory;
                      string notes = expenses.ElementAt(i).notes;
                      float amount = expenses.ElementAt(i).amount;
                      string lastUpdatedDate = expenses.ElementAt(i).LastUpdatedDate;
                      int lastUpdatedBy = expenses.ElementAt(i).LastUpdatedBy;
                      string dateSubmitted = expenses.ElementAt(i).dateSubmitted;
  
  
                      //public void SubmitExpenses(int employeeId, string expenseDate, int taskId, int expenseTypeId, int billingCategory,
                      //    string notes, float amount, string lastUpdatedDate, int lastUpdatedBy, string dateSubmitted)
                      //{
  
                      using (SqlCommand sqlQuery = new SqlCommand("INSERT INTO Expenses " +
                          "(Employee_ID, Task_ID, Expense_Date, Expense_Type_ID, Billing_Category_ID, " +
                          "Amount, Notes, Last_Updated_By, Last_Update_Datetime, Date_Submitted, Location) " +
                          "Values (@employeeId, @taskId, @expenseDate, @expenseTypeId, @billingCategory, @amount, @notes, " +
                          "@lastUpdatedBy, @lastUpdatedDate, @dateSubmitted, @locationId)", cnxn))
                      {
                          sqlQuery.Parameters.Add(new SqlParameter("@employeeId", SqlDbType.Int) { Value = employeeId });
                          sqlQuery.Parameters.Add(new SqlParameter("@expenseDate", SqlDbType.DateTime) { Value = expenseDate });
                          sqlQuery.Parameters.Add(new SqlParameter("@taskId", SqlDbType.Int) { Value = taskId });
                          sqlQuery.Parameters.Add(new SqlParameter("@expenseTypeId", SqlDbType.Int) { Value = expenseTypeId });
                          sqlQuery.Parameters.Add(new SqlParameter("@billingCategory", SqlDbType.Int) { Value = billingCategory });
                          sqlQuery.Parameters.Add(new SqlParameter("@notes", SqlDbType.Text) { Value = notes });
                          sqlQuery.Parameters.Add(new SqlParameter("@amount", SqlDbType.Money) { Value = amount });
                          sqlQuery.Parameters.Add(new SqlParameter("@lastUpdatedDate", SqlDbType.DateTime) { Value = lastUpdatedDate });
                          sqlQuery.Parameters.Add(new SqlParameter("@lastUpdatedBy", SqlDbType.Int) { Value = lastUpdatedBy });
                          sqlQuery.Parameters.Add(new SqlParameter("@dateSubmitted", SqlDbType.DateTime) { Value = dateSubmitted });
                          sqlQuery.Parameters.Add(new SqlParameter("@locationId", SqlDbType.VarChar) { Value = "" });
  
                          sqlQuery.ExecuteNonQuery();
                      }
                  }
              }
          }
  </code></pre>
  
  <p>And the <code>Expense.cs</code> model:</p>
  
  <pre><code>public class Expense
      {
          public int employeeId { get; set; }
          public string expenseDate { get; set; }
          public int taskId { get; set; }
          public int expenseTypeId { get; set; }
          public int billingCategory { get; set; }
          public string notes { get; set; }
          public float amount { get; set; }
          public string LastUpdatedDate { get; set; }
          public int LastUpdatedBy { get; set; }
          public string dateSubmitted { get; set; }
          public string location { get; set; }
      }
  </code></pre>
  
  <p>How would I go about in similar fashion in Express.js so I could take a list of JavaScript objects, break them into elements which could then be parameterized and submitted?</p>
  
  <p>Would it be something like...</p>
  
  <pre><code>app.post('/route/to/api', function (req, res) {
      var sql = require('mssql');
  
      sql.connect("mssql://user:password@localhost/Northwind").then(function () {
          console.log('connected');
  
  
  
         for (var i = 0; i &lt; req.length; i++) {
              new sql.Request()
                  .input('input_param', sql.VARCHAR, req[i]['PropertyName'])
                  .query('INSERT INTO Table VALUES (@input_param)')
                  .then(function (recordset) {
                      console.log(recordset);
                  })
                  .catch(function (err) {
                      console.log(err);
                  });
          }
      }).catch(function (err) {
          console.log(err);
      });
  });
  </code></pre>
  
  <p>Any advice would be much appreciated!</p>