Get Started with Waddler and SQLite

Waddler has native support for SQLite connections with the libsql and better-sqlite3 drivers.

We will use libsql for this get started example. But if you want to find more ways to connect to SQLite check our SQLite 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 required packages

npm i waddler @libsql/client dotenv
npm i -D tsx

yarn add waddler @libsql/client dotenv
yarn add -D tsx

pnpm add waddler @libsql/client dotenv
pnpm add -D tsx

bun add waddler @libsql/client 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=

DATABASE_URL=file:local.db

DATABASE_URL=:memory:

Step 3 - Connect Waddler to the database

Create a index.ts file in the src directory and initialize the connection:

import 'dotenv/config';
import { waddler } from 'waddler/libsql';

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

import 'dotenv/config';
import { waddler } from 'waddler/libsql';

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

If you need to provide your existing driver:

import 'dotenv/config';
import { waddler } from 'waddler/libsql';
import { createClient } from '@libsql/client';

const client = createClient({ url: process.env.DATABASE_URL! });
const sql = waddler({ client });

Step 4 - Create a table

(async () => {
  await sql.unsafe(`create table if not exists users (
    id    integer primary key autoincrement,
    name  text    not null,
    age   integer not null,
    email text    not null unique
    );
  `).run();
})()

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/libsql';
  
const sql = waddler(process.env.DATABASE_URL!);

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

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

  const users = await sql`select * from ${sql.identifier('users')};`.all();
  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]};`.run();
  console.log('User info updated!')

  await sql`delete from ${sql.identifier('users')} where email = ${user[2]};`.run();
  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