Get Started with Waddler and MsSQL

This guide assumes familiarity with:

dotenv - package for managing environment variables - read here
tsx - package for running TypeScript files - read here
mssql - package for querying your MsSQL database - read here

To use Waddler with a MsSQL database, you should use the mssql driver.

Waddler natively supports mssql with waddler/node-mssql package.

Basic file structure

This is the basic file structure of the project.

📦 <project root>
 ├ 📂 src
 │   └ 📜 index.ts
 ├ 📜 .env
 ├ 📜 package.json
 └ 📜 tsconfig.json

Step 1 - Install mssql package

npm

yarn

pnpm

bun

npm i waddler mssql dotenv
npm i -D tsx

yarn add waddler mssql dotenv
yarn add -D tsx

pnpm add waddler mssql dotenv
pnpm add -D tsx

bun add waddler mssql dotenv
bun add -D tsx

Step 2 - Setup connection variables

Create a .env file in the root of your project and add your database connection variable:

DATABASE_URL=

Step 3 - Connect Waddler to the database

node-mssql

node-mssql with config

your mssql driver

AutoPool

import 'dotenv/config';
import { waddler } from "waddler/node-mssql";

const sql = waddler(process.env.DATABASE_URL);

// process.env.DATABASE_URL should be provided in the following format:
`Server=<server>,<port>;Database=<database>;User Id=<user>;Password=<password>;Encrypt=<encrypt>;TrustServerCertificate=<trustServerCertificate>;`

import 'dotenv/config';
import { waddler } from "waddler/node-mssql";

// connection can accept either a database url or an mssql config
const sql = waddler({ connection: process.env.DATABASE_URL });

// database url should be in the following format:
`Server=<server>,<port>;Database=<database>;User Id=<user>;Password=<password>;Encrypt=<encrypt>;TrustServerCertificate=<trustServerCertificate>;`

// mssql config example:
{
	server: 'localhost',
	port: 1433,
	database: 'master',
	user: 'SA',
	password: '...',
	pool: {
		max: 1,
	},
	options: {
		requestTimeout: 100_000,
		encrypt: true, // for azure
		trustServerCertificate: true,
	},
}

import 'dotenv/config';
import { waddler } from "waddler/node-mssql";
import mssql from 'mssql';

async function main() {
  // mssql.connect can accept either a database url or an mssql config
  const client = await mssql.connect(process.env.DATABASE_URL);
	await client.connect();

	const sql = waddler({ client });
}

main();

// database url should be in the following format:
`Server=<server>,<port>;Database=<database>;User Id=<user>;Password=<password>;Encrypt=<encrypt>;TrustServerCertificate=<trustServerCertificate>;`

// mssql config example:
{
	server: 'localhost',
	port: 1433,
	database: 'master',
	user: 'SA',
	password: '...',
	pool: {
		max: 1,
	},
	options: {
		requestTimeout: 100_000,
		encrypt: true, // for azure
		trustServerCertificate: true,
	},
}

import 'dotenv/config';
import { AutoPool, waddler } from 'waddler/node-mssql';
import mssql from 'mssql';

// AutoPool can accept either a database url or an mssql config
const client = new AutoPool(process.env.DATABASE_URL);

const sql = waddler({ client: client });

// database url should be in the following format:
`Server=<server>,<port>;Database=<database>;User Id=<user>;Password=<password>;Encrypt=<encrypt>;TrustServerCertificate=<trustServerCertificate>;`

// mssql config example:
{
	server: 'localhost',
	port: 1433,
	database: 'master',
	user: 'SA',
	password: '...',
	pool: {
		max: 1,
	},
	options: {
		requestTimeout: 100_000,
		encrypt: true, // for azure
		trustServerCertificate: true,
	},
}

Step 4 - Create a table


(async () => {
  await sql.unsafe('CREATE SEQUENCE user_id_seq START WITH 1 INCREMENT BY 1;');

	await sql.unsafe(`CREATE TABLE [users] (
		[id] int primary key DEFAULT NEXT VALUE FOR user_id_seq,
		[name] varchar(255) NOT NULL,
    [age] int NOT NULL,
    [email] varchar(255) NOT NULL UNIQUE
	);
    `);
})()

Step 5 - Seed and Query the database

Let’s update the src/index.ts file with queries to create, read, update, and delete users

import 'dotenv/config';
import { waddler } from 'waddler/mssql';
  
const sql = waddler(process.env.DATABASE_URL!);

async function main() {
  const user = [
    'John',
    30,
    'john@example.com',
  ];

  await sql`insert into ${sql.identifier('users')} values ${sql.values([[sql.default, ...user]])};`;
  console.log('New user created!')

  const users = await sql`select * from ${sql.identifier('users')};`;
  console.log('Getting all users from the database: ', users)
  /*
  const users: {
    id: number;
    name: string;
    age: number;
    email: string;
  }[]
  */

  await sql`update ${sql.identifier('users')} set age = ${31} where email = ${user[2]};`;
  console.log('User info updated!')

  await sql`delete from ${sql.identifier('users')} where email = ${user[2]};`;
  console.log('User deleted!')
}

main();

Step 6 - Run index.ts file

To run any TypeScript files, you have several options, but let’s stick with one: using tsx

You’ve already installed tsx, so we can run our queries now

Run index.ts script

npm

yarn

pnpm

bun

npx tsx src/index.ts

yarn tsx src/index.ts

pnpm tsx src/index.ts

bun tsx src/index.ts

tips

We suggest using bun to run TypeScript files. With bun, such scripts can be executed without issues or additional settings, regardless of whether your project is configured with CommonJS (CJS), ECMAScript Modules (ESM), or any other module format. To run a script with bun, use the following command:

bun src/index.ts

If you don’t have bun installed, check the Bun installation docs