Working with SQLite in SvelteKit

Photo by Michael Dziedzic on Unsplash

SQLite + SvelteKit

There are many different backend providers that can be used with SvelteKit. Many of them offer their own SDKs for developers to work with. These work great for formal projects and applications, however this article isn’t fully meant for those who are trying to setup a backend for a formal application. The focus of this article will be learning how to setup a basic SQL service with the SvelteKit framework. We’re going to learn how to use all of the different features of SvelteKit by implementing a basic SQLite system.

This Guide Consists of:

• Implementing the sqlite3 module
• Using the SvelteKit hooks.server.ts file to provide our DB instance to our application
• Query data from the +page.server.ts files and send that data to the frontend.

Installing the SQLite Module

This can easily be done by running the following commands:

(npm/pnpm) install sqlite3

If you’re using TypeScript, you can install the types by running:

(npm/pnpm) install -D @types/sqlite3

Providing a Database to our Application

We’re going to create and store our Database instance on the server side of our application, SvelteKit offers hook files which can be used to do just that.

Step I) Create a hooks.server.ts file

You’ll want to create a new file located at src/hooks.server.ts and you’ll want to paste in the following.

import type { Handle } from '@sveltejs/kit';

export const handle: Handle = async ({ event, resolve }) => {
  if (!event.locals.db) {
    // This will create the database within the `db.sqlite` file.
    const db = new sqlite3.Database('db.sqlite', (err) => {
      if(err) {
        throw err;
      }
    });
    
    // Set the db as our events.db variable.
    event.locals.db = db
    
    // We can create a basic table in the db
    const query = "CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY AUTOINCREMENT, username TEXT)"
    db.run(query, (err) => {
      if(err) {
        throw err
      }
    })
  }
  const resp = await resolve(event);
  return resp;
};

Step II) Update the app.d.ts file

In order to type check our locals variable, you’ll want to make sure to update the app.d.ts file located at src/app.d.ts

import type {Database} from 'sqlite3'

declare global {
 namespace App {
  // interface Error {}

  // Uncomment the `interface Locals` and create a `db` property
  interface Locals {
   db: Database;
  }

  // interface PageData {}
  // interface PageState {}
  // interface Platform {}
 }
}

export {};

Step III) Create our types

We’ll want to create our types for our data structure, this can be done in a types.ts file at src/lib/types.ts

// types.ts file containing your types.
export interface User {
  id: number;
  username: string
}

Step IV) Create the +page.server.ts file

When we interact with our database, we will do so from the server side, this is done primarily within the +page.server.ts file within your route.

We’ll create a +page.server.ts file within our main route. The path of our file should be src/routes/+page.server.ts (or whichever route you want to use)

Note: You could also use +server.ts files to create API Endpoints to access data. This is probably something you’ll want to do later on as a part of your application so that you can offer API access to data within your database.

import type { PageServerLoad } from './$types';
import type { User } from "$lib/types.ts"

export const load: PageServerLoad = async ({ locals }) => {
  // Since `sqlite3` is a callback based system, we'll want to use a 
  // promise to return the data in an async manner.
  const loadDataPromise = new Promise<User[]>((resolve, reject) => {
    const db = locals.db;
    const query = "SELECT * FROM users";
    db.all<User>(query, (err: Error|null, rows: User[]) => {
      if(err) {
        reject(err);
        return;
      }
      resolve(rows)
    })
  })
  const rows = await loadDataPromise;
  return {
    users: rows
  };
};

Step V) Receive the data on the frontend

We can now access the data on the frontend in the +page.svelte file.

src/routes/+page.svelte

<script lang="ts">
  import type { PageServerData } from "./$types"
  
  // I am using Svelte 5 so you can use the export let syntax if you aren't
  let {data}: {data: PageServerData} = $props()
  
  const users = data.users
</script>

<div>
  {#if users.length > 0}
    {#each users as user}
      <div>
        <span>Username</span>
        <span>{user.username}</span>
      </div>
    {/each}
  {/if}
</div>

Further Information on the sqlite3 Module

Some of the most common functions you will need when using the sqlite3 module are listed below:

• sqlite3.all<T>(query: string, (err: Error|null, rows: T[]) => void)
• sqlite3.get<T>(query: string, (err: Error|null, row: T) => void)
• sqlite3.exec(query: string, (err: Error|null) => void)
• sqlite3.prepare(query: string, params: any, (err: Error|null) => void).run((err: Error|null) => void)