Get Started with Waddler and PostgreSQL

Waddler has native support for PostgreSQL connections with the node-postgres and postgres.js drivers.

We will use node-postgres for this get started example. But if you want to find more ways to connect to postgresql check our PostgreSQL Connection page

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

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:

DATABASE_URL=

Step 3 - Connect Waddler to the database

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

const sql = waddler(process.env.DATABASE_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.DATABASE_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.DATABASE_URL!,
});
const sql = waddler({ client: pool });

import 'dotenv/config';
import { waddler } from 'waddler/node-postgres';
import { queryStream } from 'waddler/extensions/pg-query-stream';
import pg from 'pg';

const { Pool } = pg;

const pool = new Pool({
  connectionString: process.env.DATABASE_URL!,
});
const sql = waddler({ client: pool, extensions: [queryStream()] });

Step 4 - Create a table

(async () => {
  await sql.unsafe(`create table users (
    id integer primary key generated always as identity,
    name varchar(255) not null,
    age integer 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/node-postgres';
  
const sql = waddler(process.env.DATABASE_URL!);

async function main() {
  const user = [
    'John',
    30,
    '[email protected]',
  ];

  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();

Streaming

import 'dotenv/config';
import pg from 'pg';
import { waddler } from 'waddler/node-postgres';
import { queryStream } from 'waddler/extensions/pg-query-stream';

const { Client } = pg;

async function main() {
  const client = new Client(process.env.DATABASE_URL!);
	await client.connect();

	const sql = waddler({ client, extensions: [queryStream()] });

	const stream = sql`select * from users;`.stream();

	console.log('Streaming users one at a time from the database.')
    for await (const user of stream) {
        console.log(user)
        /*
        const user: {
          id: number;
          name: string;
          age: number;
          email: string;
        }
        */		
	}

	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

npx tsx src/index.ts

yarn tsx src/index.ts

pnpm tsx src/index.ts

bun tsx src/index.ts

bun src/index.ts