table-builder.js

// const connectionString = 'Driver={ODBC Driver 17 for SQL Server}; Server=DESKTOP-VIUCH90;UID=linux; PWD=linux; Database=node'

const { TestEnv } = require('../../test/env/test-env')
const env = new TestEnv()
const connectionString = env.connectionString

builder().then(() => {
  console.log('done')
})

async function builder () {
  function makeOne (i) {
    return {
      id: i,
      MatterColumn: `MatterColumn_${i}`,
      SearchTerm: `SearchTerm_${i}`,
      Comparator: `Comparator_${i}`
    }
  }

  try {
    const rows = 5
    const connection = await env.sql.promises.open(connectionString)
    const tableName = 'tmpTableBuilder'
    const mgr = connection.tableMgr()
    const builder = mgr.makeBuilder(tableName)

    builder.addColumn('id').asInt().isPrimaryKey(1)
    builder.addColumn('MatterColumn').asVarChar(100).notNull()
    builder.addColumn('SearchTerm').asNVarCharMax().notNull()
    builder.addColumn('Comparator').asNVarChar(20).notNull()

    const vec = Array(rows).fill(0).map((_, i) => makeOne(i))
    const t = builder.toTable()
    const dropTypeSql = builder.dropTypeSql
    const userTypeSql = builder.userTypeTableSql
    const dropTvpInsert = builder.dropInsertTvpProcedure
    const tvpInserterSql = builder.insertProcedureTvpSql
    const typeName = `${tableName}Type`
    const selectSql = `DECLARE @test AS ${typeName};
      INSERT INTO @test SELECT * FROM ?;
      SELECT * FROM @test`

    console.log(dropTvpInsert)
    await connection.promises.query(dropTvpInsert)
    const create = builder.createTableSql
    const dropTable = builder.dropTableSql
    console.log(dropTable)
    await builder.drop()
    console.log(create)
    await builder.create()
    console.log(dropTypeSql)
    await connection.promises.query(dropTypeSql)
    console.log(userTypeSql)
    await connection.promises.query(userTypeSql)
    console.log(tvpInserterSql)
    await connection.promises.query(tvpInserterSql)
    const table = t.asTableType()

    await connection.promises.getUserTypeTable('dbo.' + typeName)
    // Works (only changing that line)
    await connection.promises.getUserTypeTable('[dbo].[' + typeName + ']')
    await connection.promises.getUserTypeTable('[' + typeName + ']')

    console.log(JSON.stringify(table, null, 4))
    // convert a set of objects to rows
    table.addRowsFromObjects(vec)
    // use a type the native driver can understand, using column based bulk binding.
    const tp = env.sql.TvpFromTable(table)
    // can now clear rows
    table.rows = []
    const res = await connection.promises.query(selectSql, [tp])
    console.log(JSON.stringify(res.meta[0], null, 4))
    console.log(JSON.stringify(res.first, null, 4))
    await builder.drop()
    await connection.promises.close()
  } catch (e) {
    console.log(e)
  }
}