How to get a connection to SQLServer from a connection string

[ModernRonin]

Hi,

this is probably a pretty newb question, but I haven't been using TypeScript for database stuff before, so sorry.

I've looked at your examples and cannot seem to find one showing how to connect to a (MS) SqlServer via a connection string. The only thing I found are .prisma files, but I'm not clear what I need them for.

Maybe a bit of background:
I need to write a migration tool that migrates data currently stored in an Azure SqlServer database to a document database. This tool must run as part of a deployment pipeline and as I'm using Pulumi with Typescript for the whole deployment infrastructure, so I must implement said tool in TypeScript, too.

So, this tool is going to run exactly three times (once for each deployment environment), then it's going to be thrown away. Ergo, I'd like to make do with as few infrastructural complications as possible, and those .prisma files look like they are for more advanced use-cases.

So, how can I get a connection usable with ts-sql-query to an SqlServer from a connection string?

Many thanks,
MR

[juanluispaz]

Hi,

Prisma is not your path, use prisma query runner only if you are in a prisma project; if not, use Mssql query runner instead.

Mssql's ConnectionPool object have two constructors: (Notice: Mssql's ConnectionPool is not a ts-sql-query object)

export declare class ConnectionPool extends events.EventEmitter {
    public constructor(config: config, callback?: (err?: any) => void);
    public constructor(connectionString: string, callback?: (err?: any) => void);
    // ...
}

The first one is the one used in the documentation, with a config object:

import { ConnectionPool } from 'mssql'
import { MssqlPoolPromiseQueryRunner } from "./queryRunners/MssqlPoolPromiseQueryRunner";

const poolPromise = new ConnectionPool({
    user: '...',
    password: '...',
    server: 'localhost',
    database: '...'
}).connect();

async function main() {
    const connection = new DBConection(new MssqlPoolPromiseQueryRunner(poolPromise));
    // Do your queries here
}

And the second one with the connection string, will looks like this:

import { ConnectionPool } from 'mssql'
import { MssqlPoolPromiseQueryRunner } from "./queryRunners/MssqlPoolPromiseQueryRunner";

const connectionString = 'Server=localhost,1433;Database=database;User Id=username;Password=password;Encrypt=true';
const poolPromise = new ConnectionPool(connectionString).connect();

async function main() {
    const connection = new DBConection(new MssqlPoolPromiseQueryRunner(poolPromise));
    // Do your queries here
}

Let me know if that worked for you.

[ModernRonin]

thanks a lot, Juan, this is exactly what I need!