Get Started with Waddler and Nile

This guide assumes familiarity with:

dotenv - package for managing environment variables - read here
tsx - package for running TypeScript files - read here
Nile - PostgreSQL re-engineered for multi-tenant apps - read here

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 postgres package

npm

yarn

pnpm

bun

npm i waddler pg dotenv
npm i -D tsx @types/pg

yarn add waddler pg dotenv
yarn add -D tsx @types/pg

pnpm add waddler pg dotenv
pnpm add -D tsx @types/pg

bun add waddler pg dotenv
bun add -D tsx @types/pg

Step 2 - Setup connection variables

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

NILEDB_URL=

Step 3 - Connect Waddler to the database

node-postgres

node-postgres with config

your node-postgres driver

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

const sql = waddler(process.env.NILEDB_URL!);

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

// You can specify any property from the node-postgres connection options
const sql = waddler({ 
  connection: { 
    connectionString: process.env.NILEDB_URL!,
    ssl: true
  }
});

import 'dotenv/config';
import { waddler } from 'waddler/node-postgres';
import pg from 'pg';

const { Pool } = pg;

const pool = new Pool({
  connectionString: process.env.NILEDB_URL!,
});
const sql = waddler({ client: pool });

multi-tenancy

Nile provides virtual tenant databases. When you query Nile, you can set the tenant context and Nile will direct your queries to the virtual database for this particular tenant. All queries sent with tenant context will apply to that tenant alone (i.e. select * from table will result records only for this tenant).

warning

Nile database does not support pg-query-stream extension, so you cannot stream data from it at this time.

Step 4 - Create a table

Since Nile is Postgres for multi-tenant apps, your database will already have built-in table for tenants. We will also create a todos table with a tenant_id column (we refer to those as tenant-aware tables):

// This is structure of built-in "tenants" table. You don't need to create it.
// CREATE TABLE "tenants" (
//   "id" UUID NOT NULL DEFAULT public.uuid_generate_v7(),
//   "name" TEXT,
//   "created" TIMESTAMP NOT NULL DEFAULT LOCALTIMESTAMP,
//   "updated" TIMESTAMP NOT NULL DEFAULT LOCALTIMESTAMP,
//   "deleted" TIMESTAMP,
//   PRIMARY KEY ("id")
// );

(async () => {
  await sql.unsafe(`CREATE TABLE IF NOT EXISTS "todos" (
    "id" uuid DEFAULT gen_random_uuid(),
    "tenant_id" uuid,
    "title" varchar(256),
    "complete" boolean,
    CONSTRAINT todos_tenant_id_id PRIMARY KEY("tenant_id", "id")
);
  `);
})()

Step 5 - Seed and Query the database

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

import 'dotenv/config';
import {waddler} from 'waddler/node-postgres'
import pg from 'pg';

const {Client} = pg;

const main = async () => {
    const client = new Client(process.env['NILEDB_URL']!);
    await client.connect();
    const sql = waddler({client});

    const tenant = {
        name: 'AwesomeSauce Inc.',
    };

    await sql`insert into ${sql.identifier('tenants')}(${sql.identifier('name')}) values ${sql.values([[tenant.name]])};`;
    console.log('New tenant created!')

    const tenants = await sql<{id: string}>`select * from ${sql.identifier('tenants')};`;
    console.log('Getting all tenants from the database:', tenants)

    const todo = {
        tenant_id: tenants[0]!.id,
        title: 'Update pitch deck with AI stuff'
    };

    await sql`insert into ${sql.identifier('todos')}(${sql.identifier(['tenant_id', 'title'])}) values ${sql.values([[todo.tenant_id, todo.title]])};`;
    console.log('New todo created!')

    const todos = await sql<{id: string}>`select * from ${sql.identifier('todos')};`;
    console.log('Getting all todos from the database:', todos)

    await sql`SET nile.tenant_id = '${sql.raw(tenants[0]!.id)}';`;
    console.log("Set tenant context");

    // note the lack of tenant_id in the query
    const tenant_todos = await sql`select * from ${sql.identifier('todos')};`;
    console.log('Getting all todos from the tenant virtual database:', tenant_todos)

    await sql`update ${sql.identifier('todos')} set ${sql.identifier('complete')} = true where ${sql.identifier({table: 'todos', column: 'id'})} = ${todos[0]!.id};`;
    console.log('Todo marked as done!')

    await sql`delete from ${sql.identifier('todos')} where ${sql.identifier({table: 'todos', column: 'id'})} = ${todos[0]!.id};`;
    console.log('Todo deleted!')

    await client.end();
}

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