Functions - Issue using Typescript MSSQL bulk()

Question

I am trying to use the mssql package in my Typescript Azure Function. When I attempt to use Bulk insert, I continue to receive an error no matter what I try.

Here's my SQL DDL:

-- Edited to show that the ID column is defined as an Identity column
CREATE TABLE Teams (
	ID smallint IDENTITY(1,1) NOT NULL,
	Season_ID tinyint NOT NULL,
	Team_Name varchar(50) NOT NULL,
	CONSTRAINT Teams_PK PRIMARY KEY (ID)
);

-- For fun later on - this query should work just fine:
-- INSERT INTO Teams (Season_ID, Team_Name) VALUES (1, 'test');

My function code:

export const CreateTeam = async (
  request: HttpRequest,
  context: InvocationContext
): Promise<HttpResponseInit> => {
  context.log(`Http function processed request for url "${request.url}"`);
  const sqlConfig = {
    user: process.env.SqlConnectionUser,
    password: process.env.SqlConnectionPassword,
    server: process.env.SqlConnectionServer,
    database: process.env.SqlConnectionDatabase,
    options: {
      encrypt: true,
      trustServerCertificate: false,
    },
  };

  let connection;

  try {
    connection = await connect(sqlConfig);
    const request = new Request(connection);

    const table = new Table("Teams");

    // table.columns.add("ID", SmallInt, {
    //   nullable: false,
    //   primary: true,
    //   identity: true,
    // });
    table.columns.add("Season_ID", TinyInt, { nullable: false });
    table.columns.add("Team_Name", VarChar(50), { nullable: false });

    table.rows.add([1, "Team 1"] as any);
    table.rows.add([1, "Team 2"] as any);

    // table.rows.add([null, 1, "Team 1"] as any);
    // table.rows.add([null, 1, "Team 2"] as any);

    const result = await request.bulk(table);
    return {
      status: 200,
      body: `resultCount: ${result.rowsAffected}`,
    };
  } catch (ex) {
    console.error(ex);
    return {
      status: 500,
      body: ex,
    };
  } finally {
    connection.close();
  }
};

(The commented lines are me trying insert including the ID column with a null value).

I always get the error: RequestError: Invalid number. I don't get any more information from the error on which column it is:

• An error on the ID column (either because it's not provided, or when the commented lines are used and it's null)
• An error on the Season_ID column

*To note, typescript also doesn't like tables.rows.add([1, "string"]) , which I believe is a bug, because the [docs] show an example using this exact setup.

I assume this is maybe an issue with using TinyInt for the field type in my DB for Season_ID? It definitely shouldn't be - but if it is, is there a workaround? Or is there a different issue with my code?

Answer 1

Hi @Elliot Stoner,

I used MSSQL bulk() in below code which worked for me:

import { AzureFunction, Context, HttpRequest } from "@azure/functions";

import * as sql from "mssql";

const risqlcnfg = {

    user: "username",

    password: "Rpassword2",

    server: "servername.database.windows.net",

    database: "dbname",

    options: {

        encrypt: true,

        trustServerCertificate: false,

    },

};

const pools = new Map<string, Promise<sql.ConnectionPool>>();

async function getPool(): Promise<sql.ConnectionPool> {

    if (!pools.has("default")) {

        const pool = new sql.ConnectionPool(risqlcnfg);

        pools.set("default", pool.connect());

    }

    return pools.get("default")!;

}

const httpTrigger: AzureFunction = async function (context: Context, req: HttpRequest): Promise<void> {

    context.log("HTTP trigger function started.");

    try {

        if (!req.body || !Array.isArray(req.body.teams) || req.body.teams.length === 0) {

            context.res = {

                status: 400,

                body: "Invalid request. Expected an array of teams with Season_ID and Team_Name",

            };

            return;

        }

        const ripl = await getPool();

        const ri_tb = new sql.Table("Teams");

        ri_tb.create = false;

        ri_tb.columns.add("Season_ID", sql.TinyInt, { nullable: false });

        ri_tb.columns.add("Team_Name", sql.VarChar(50), { nullable: false });

        req.body.teams.forEach((team: { Season_ID: number; Team_Name: string }) => {

            ri_tb.rows.add(team.Season_ID, team.Team_Name);

        });

        const rithsql = ripl.request();

        await rithsql.bulk(ri_tb);

        context.res = {

            status: 200,

            body: `Successfully inserted ${req.body.teams.length} teams.`,

        };

    } catch (ex: any) {

        context.log.error("SQL Error:", ex);

        context.res = {

            status: 500,

            body: `Database error: ${ex.message}`,

        };

    }

};

export default httpTrigger;

Output:

In Post call, the body (array of json) which i sent :

{

  "teams": [

    { "Season_ID": 1, "Team_Name": "Rithwik" },

    { "Season_ID": 1, "Team_Name": "Bojja" },

    { "Season_ID": 2, "Team_Name": "Chotu" }

  ]

}

Hope this helps.

If the answer is helpful, please click Accept Answer and kindly upvote it. If you have any further questions about this answer, please click Comment.