Get Started with Waddler and MySQL

To use Waddler with a MySQL database, you should use the mysql2 driver

According to the official website, mysql2 is a MySQL client for Node.js with focus on performance.

Waddler natively supports mysql2 with waddler/mysql2 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 mysql2 package

npm i waddler mysql2 dotenv
npm i -D tsx

yarn add waddler mysql2 dotenv
yarn add -D tsx

pnpm add waddler mysql2 dotenv
pnpm add -D tsx

bun add waddler mysql2 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

import 'dotenv/config';
import { waddler } from "waddler/mysql2";

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

import 'dotenv/config';
import { waddler } from "waddler/mysql2";

// You can specify any property from the mysql2 connection options
const sql = waddler({ connection: { uri: process.env.DATABASE_URL }});

import 'dotenv/config';
import { waddler } from "waddler/mysql2";
import mysql from "mysql2/promise";
  
const poolConnection = mysql.createPool({
  host: "host",
  user: "user",
  database: "database",
});
const sql = waddler({ client: poolConnection });

// or if you need client connection
async function main() {
  const connection = await mysql.createConnection({
    host: "host",
    user: "user",
    database: "database",
  });
  const sql = waddler({ client: connection });
}
main();

Step 4 - Create a table

(async () => {
  await sql.unsafe(`CREATE TABLE users (
				id serial,
				name varchar(255) NOT NULL,
        age int NOT NULL,
        email varchar(255) NOT NULL UNIQUE,
				CONSTRAINT PRIMARY KEY(id)
			);
    `);
})()

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/mysql2';
  
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();

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