File size: 2,806 Bytes
f8b5d42 |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 |
const mssql = require("mssql");
const { ConnectionStringParser } = require("./utils");
class MSSQLConnector {
#connected = false;
database_id = "";
connectionConfig = {
user: null,
password: null,
database: null,
server: null,
port: null,
pool: {
max: 10,
min: 0,
idleTimeoutMillis: 30000,
},
options: {
encrypt: false,
trustServerCertificate: true,
},
};
constructor(
config = {
// we will force into RFC-3986 from DB
// eg: mssql://user:password@server:port/database?{...opts}
connectionString: null, // we will force into RFC-3986
}
) {
this.connectionString = config.connectionString;
this._client = null;
this.#parseDatabase();
}
#parseDatabase() {
const connectionParser = new ConnectionStringParser({ scheme: "mssql" });
const parsed = connectionParser.parse(this.connectionString);
this.database_id = parsed?.endpoint;
this.connectionConfig = {
...this.connectionConfig,
user: parsed?.username,
password: parsed?.password,
database: parsed?.endpoint,
server: parsed?.hosts?.[0]?.host,
port: parsed?.hosts?.[0]?.port,
options: {
...this.connectionConfig.options,
encrypt: parsed?.options?.encrypt === "true",
},
};
}
async connect() {
this._client = await mssql.connect(this.connectionConfig);
this.#connected = true;
return this._client;
}
/**
*
* @param {string} queryString the SQL query to be run
* @returns {Promise<import(".").QueryResult>}
*/
async runQuery(queryString = "") {
const result = { rows: [], count: 0, error: null };
try {
if (!this.#connected) await this.connect();
const query = await this._client.query(queryString);
result.rows = query.recordset;
result.count = query.rowsAffected.reduce((sum, a) => sum + a, 0);
} catch (err) {
console.log(this.constructor.name, err);
result.error = err.message;
} finally {
// Check client is connected before closing since we use this for validation
if (this._client) {
await this._client.close();
this.#connected = false;
}
}
return result;
}
async validateConnection() {
try {
const result = await this.runQuery("SELECT 1");
return { success: !result.error, error: result.error };
} catch (error) {
return { success: false, error: error.message };
}
}
getTablesSql() {
return `SELECT name FROM sysobjects WHERE xtype='U';`;
}
getTableSchemaSql(table_name) {
return `SELECT COLUMN_NAME,COLUMN_DEFAULT,IS_NULLABLE,DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='${table_name}'`;
}
}
module.exports.MSSQLConnector = MSSQLConnector;
|