Skwell
A promised based SQL Server client with connection pooling.
Getting Started
First we need to create a client.
const skwell = require("skwell");
const sql = skwell.connect( {
username: "ima_user",
password: "sekret",
server: "localhost",
database: "test",
domain: "lol.com",
port: 1433,
pool: {
min: 1,
max: 10
},
connectTimeout: 15000,
requestTimeout: 15000,
encrypt: false,
onBeginTransaction( tx ){
},
onEndTransaction( tx ){
},
} );
NOTE: Setting pool:false
will disable pooling entirely.
At this point, you have a client (sql
) that is ready to be used non-transactionally. A pool of connections is maintained in the background and one will be chosen for you to execute your queries. Queries will resolve with the values or be rejected with an error.
Server errors(like when the server goes away) will be emitted on this client and can be handled accordingly.
sql.on( "error", err => {
} )
The signature of everything except bulkLoad
takes a query as the first argument. This query can be a string or a promise that resolves to a string. Skwell provides a sql.file( "./relative.file.sql" )
method to load a file and cache the resulting text. If you are calling a stored procedure, use sql.sproc( "name of stored procedure" )
.
Now, let's make some noise.
const insertedCount = await sql.execute(
"INSERT INTO SuperCoolPeople (name) values(@name)",
{
name: { val: "josh", type: sql.nvarchar( 20 ) }
} );
await sql.executeBatch(
"CREATE TABLE lol (id int);"
)
const usersWithPageInfo = await sql.querySets( query, params );
const users = await sql.query( query, params );
const user = await sql.queryFirst( query, params );
const userId = await sql.queryValue( query, params );
const userStream = await sql.queryStream( query, params );
const insertedCount = await sql.bulkLoad( "SomeTable", {
schema: {
id: sql.int.nullable()
},
rows: [ { id: 1 }, { id: 2 }, { id: 3 } ]
} );
If you need to pass an array of parameters into your query, there are two ways to do so.
Simple Values
Assign val
to an array of simple values(strings, numbers, etc.) and type
to be the sql type of each item. Skwell will expand the parameter list and create one parameter per value.
await sql.query( "select * from my_table where id in @ids", {
ids: {
val: [ 1, 2, 3 ],
type: sql.int
} );
If you pass in an empty array, skwell will instead provide SQL that generates an empty set.
await sql.query( "select * from my_table where id in @ids", {
ids: {
val: [],
type: sql.int
} );
Complex Values
Assign val
to an array of objects and type
to an object mapping properties to a sql type. Skwell will create a table paramater with multiple columns named after the object keys defined in type
.
await sql.query( "select name from @people", {
people: {
val: [
{ id: 1, name: "Josh" },
{ id: 2, name: "Calvin" },
{ id: 3, name: "Jim"}
],
type: {
id: sql.int,
name: sql.nvarchar(100)
}
} );
Sometimes you need to execute multiple queries in a transaction. Don't worry, we've got you covered!
const opts = {
isolationLevel: sql.read_uncommitted,
context: { userId: 123 }
};
const result = await sql.transaction( async tx => {
const { userId } = tx.context;
const groupId = 89;
await tx.execute(
"INSERT INTO Users(id, name) values(@id, @name)",
{
id: { val: userId, type: sql.int },
name: { val: "josh", type: sql.nvarchar( 20 ) }
} );
await tx.execute(
"INSERT INTO Groups(id, userId) values(@id, @userId)",
{
id: { val: groupId, type: sql.int },
userId: { val: userId, type: sql.int }
} );
}, opts );
That's about it. 👍
Running the tests (Dockerized SQL Server)
HEADS UP: sql server needs 3.5gb of ram. You'll want to allocate more resources in docker.
npm install
npm run sql:start
to get a local instance of sql servernpm test