Making a serverless SvelteKit site that reads data from a local SQLite database

While you can use SvelteKit's template (npm create svelte@latest my-app), deferring to the template defeats the point of writing a blog post about it.

mkdir demo-sveltekit-sqlite
cd demo-sveltekit-sqlite
git init
echo "{}" > package.json

• Using Git, of course.
• In my opinion, because a project like this isn't supposed to be published as a package (i.e. required as a dependency by anything), it is only cumbersome to have to fill out the name and version, so what I often end up doing is to just create a package.json with an empty object in it.

Also, set up which files should be ignored by git. .gitignore:

node_modules/
.svelte-kit/

{
  "type": "module"
}

npm install -D @sveltejs/kit vite

I'm going to use mdsvex immediately. This is a Svelte preprocessor that lets you write Svelte components (and thus SvelteKit routes) in Markdown (MDX).

npm install -D mdsvex

I'm also going to use svelte-preprocess. This is another Svelte preprocessor, and it lets you write component logic in TypeScript or style in Sass, among other features.

npm install -D svelte-preprocess

Vite is like a supercharged dev server and a production bundler that figures out a bunch of stuff for you. In reality the production bundler part uses Rollup, but that is almost an implementation detail that you don't have to worry about unless you want to use Rollup plugins.

For example, using Tailwind CSS is just a matter of installing the tailwindcss package and importing your source CSS as usual. Vite knows what to do.

In the case of SvelteKit, Vite doesn't have support for it built in, so SvelteKit provides a Vite plugin to glue them together.

vite.config.js:

import { sveltekit } from "@sveltejs/kit/vite";

export default {
  plugins: [sveltekit()],
};

• This configuration grabs SvelteKit's Vite plugin and tells Vite to use it.
• See Vite's documentation for more configuration options. For example, I set clearScreen to false because it doesn't work well inside of Eshell or emacs-taskrunner.

svelte.config.js:

import { mdsvex } from "mdsvex";
import adapter from "@sveltejs/adapter-auto";
import preprocess from "svelte-preprocess";

export default {
  extensions: [".svelte", ".svelte.md", ".md", ".svx"],
  preprocess: [
    preprocess(),
    mdsvex({
      smartypants: {
        dashes: "oldschool",
      },
      extensions: [".svelte.md", ".md", ".svx"],
      layout: "src/lib/MarkdownLayout.svelte",
    }),
  ],

  kit: {
    adapter: adapter(),
  },
};

• In extensions, we tell Svelte which files should be considered as components. By default this is .svelte, but we add some more to then be recognized by mdsvex.
• We need an adapter, which turns an intermediate build result into code that can be deployed on different platforms, including, for example, Cloudflare, Netlify, and as a server side Node application. The default here is adapter-auto, which choose one to use depending on where the application is being built.
• In preprocess, we declare the Svelte preprocessors in order. We run it through svelte-preprocess first, then mdsvex — I'm not sure this is the best order though.

• We tell mdsvex that

  • We want smart dashes in the style where two dashes become an en-dash and three become an em-dash
  • Markdown files should act as if they are wrapped by the component exported by src/lib/MarkdownLayout.svelte (we'll create it later)

adapter-auto looks at the build time environment to determine which adapter it should use. But this only works if you're building on the same platform as you are deploying. For building, I prefer to use GitHub Actions instead of using, say, Cloudflare Pages or Netlify or Vercel's build (CI) system.

So I prefer to just use adapter-netlify (for example) directly.

npm install -D @sveltejs/adapter-netlify

import { mdsvex } from "mdsvex";
// I prefer to name it like this
import adapterNetlify from "@sveltejs/adapter-netlify";
import preprocess from "svelte-preprocess";

export default {
  extensions: [".svelte", ".svelte.md", ".md", ".svx"],
  preprocess: [
    preprocess(),
    mdsvex({
      smartypants: {
        dashes: "oldschool",
      },
      extensions: [".svelte.md", ".md", ".svx"],
      layout: "src/lib/MarkdownLayout.svelte",
    }),
  ],

  kit: {
    adapter: adapterNetlify(),
  },
};

Prettier formats code for you so you never have to worry about it again.

As it doesn't support Svelte syntax out of the box, we have to install it with prettier-plugin-svelte.

npm install -D prettier prettier-plugin-svelte

Then register it in .prettierrc (I prefer YAML):

overrides:
  - files: "*.svelte"
    options:
      parser: "svelte"

Let's finally create some pages. Create the directory structure:

mkdir src/
mkdir src/lib
mkdir src/routes

Create src/app.html. This is SvelteKit's root template.

<!DOCTYPE html>
<html lang="en">
  <head>
    <meta charset="utf-8" />
    <meta name="referrer" content="no-referrer" />
    <meta
      name="viewport"
      content="width=device-width, initial-scale=1, shrink-to-fit=no"
    />
    %sveltekit.head%
    <link rel="icon" href="%sveltekit.assets%/favicon.ico" />
  </head>
  <body>
    <div data-sveltekit-reload class="container">%sveltekit.body%</div>
  </body>
</html>

• See the documentation for an explanation for the %sveltekit.*% stuff.
• I'm adding data-sveltekit-reload to a containing element so that it will apply to every a tag in the app. This disables client side routing across the entire app, and is equivalent to router = false in previous versions of SvelteKit. A normal app should not have this — I added this here to demonstrate how you would achieve this because it took me way too long to figure out. Normally you'd keep the router on, and also use the preload tags to get navigation to appear snappier.

Create src/routes/+layout.svelte. Layouts are components that are wrapped around every page that it applies to. src/routes/+layout.svelte applies to every page, while src/routes/abc/+layout.svelte would only apply to pages under abc/.

The difference between this file (the root layout) and app.html is that this is still a Svelte component, so you can import files in a script tag, use components, etc.

src/routes/+layout.svelte:

<script>
  // import "../src.css";
  // We'll add the CSS later
</script>

<slot />

src/lib/MarkdownLayout.svelte (the component we declared above to wrap all Markdown files):

<div class="prose">
  <slot />
</div>

At this point we can already start the dev server.

npx vite dev

Note that I'm not using the npm scripts that Vite ships in its templates. Is there really a point to saying npm run dev instead of npx vite dev? In terms of using consistent task names across projects, sure, but not when explaining how things work. We can add it later.

Now let's add the root page.

src/routes/+page.svelte:

<script>
  const description = "Here's my description!";
</script>

<svelte:head>
  <meta name="description" content={description} />
  <title>Here's a title!</title>
</svelte:head>

<h1>Example reading data from a local SQLite database</h1>
<p>Hello World!</p>

I find that, when using a component framework / templating system, Tailwind CSS really allows you to be more confident that changing parts of your CSS isn't going to break some random thing elsewhere.

Install it:

npm install -D tailwindcss

Create a postcss.config.cjs (because Tailwind's config and PostCSS plugins use CommonJS):

module.exports = {
  plugins: [
    // You don't need to install this separately: you already depend
    // on postcss-import when installing tailwindcss.
    require("postcss-import"),
    require("tailwindcss/nesting"),
    // This must come last
    require("tailwindcss"),
  ],
};

Vite will detect the presence of the PostCSS config and process the CSS with PostCSS automatically. Because Tailwind is actually a PostCSS plugin (that happens to have its own CLI), this means it will be processed by Tailwind as specified above.

Then create a tailwind.config.cjs:

module.exports = {
  content: ["./src/**/*.{svelte,md,ts,js,html}"],
};

• This file needs to exist, otherwise Tailwind will emit an error.
• content specifies where Tailwind utility classes might be referenced. Utility classes that are referenced will be included in the final output, and those that aren't won't. If you don't specify content, no utility tags will be emitted, so it's de-facto necessary.
• With Vite, you don't have to worry about where that output goes: during development it is inlined, and in a production build it is placed in the build output, with the relevant <link> tags added automatically.
• See Tailwind's docs on configuration for what other options are available here.

Now create the CSS file:

src/src.css:

@tailwind base;
@tailwind components;
@tailwind utilities;

And actually import the CSS file in the root layout, src/routes/+layout.svelte:

<script>
 import "../src.css";
</script>

<slot />

Now use them in the root route, src/routes/+page.svelte:

<script>
  const description = "Here's my description!";
</script>

<svelte:head>
  <meta name="description" content={description} />
  <title>Here's a title!</title>
</svelte:head>

<main class="w-[95%] max-w-2xl mx-auto mt-48">
<h1 class="text-3xl font-bold">Example reading data from a local SQLite database</h1>
<p>Hello World!</p>
</main>

We're ready to start creating pages based on data from a database. For this I'll use a sample database from codecrafters.io. Download the superheros.db and just place it in project root for now.

Unless you want to dabble with sending both a WebAssembly build of SQLite and the full database to your user, we'll have to read the database on the server side. (I don't know how to do that anyways.)

In case this is confusing: the server side of a “serverless” application is the serverless functions.

This server side can be a +page.server.js (pages only rendered on the server side) or a +server.js (API routes that return raw data and not HTML). In this example I'm going to use an API route, then fetch from it on the client side, but this isn't necessary.

We need better-sqlite3 for this.

npm install better-sqlite3

Note that currently better-sqlite3 does not install for Node 19. On Arch Linux, you can move to an LTS by installing, say, the nodejs-lts-hydrogen package, which is Node 18.

Also note that this means we have a hard dependency on NodeJS itself, so we cannot deploy to Cloudflare Pages because Cloudflare Workers has its own runtime that isn't compatible.

I'll put the API route in /heroes, with the API /heroes?q=<query>; it will return a list of heroes from the database whose names match the query.

In src/routes/heroes/+server.js:

import { json, error } from "@sveltejs/kit";

import Database from "better-sqlite3";
import fs from "node:fs";

let db = new Database(fs.readFileSync("superheroes.db"));

export function GET({ url }) {
  const query = url.searchParams.get("q");
  if (!query) {
    throw error(401, "Query (`?q=`) is required");
  }
  const stmt = db.prepare("select * from superheroes where name like ?");
  return json(stmt.all(`%${query}%`));
}

• See server.js docs for details on GET, json, etc.
• See better-sqlite3's docs for how we're interacting with the database, particularly Database#prepare(), Statement#all(), and Binding Parameters.
• We're creating the database by passing it the file contents. This creates an in-memory database, changes to which will be lost. That's fine because the whole premise is that we're reading from a preexisting database that we don't write to.

• You could make the database writable by passing the file path to the constructor directly, but keep in mind that this will not work in a serverless function, because each invocation is getting its own copy and the modified versions will not be merged together. You need to use a database service or abandon the premise of running your app on serverless platforms.

• The file path is relative to the project root, i.e. where you started Vite.
• On Netlify, in a monorepo (i.e. your project root is not your repository root), the file path will be relative to the project root during build as expected, but during runtime of the serverless function it will be relative to repository root instead. This feels like a bug, but we'll just have to work around it later.

Now test the API route out: navigate to localhost:5173/heroes?q=aqua, and you should see a JSON array of heroes with “aqua” in their names.

Create src/lib/components. The name “components” is not special, but “lib” is: things under this folder can be imported as $lib/… instead of having to use relative paths.

Then create src/lib/components/SuperHeroes.svelte:

<script>
  export let items = [];
</script>

<ul>
  {#each items as item}
    <li class="w-full flex justify-between">
      {item.name}{#if item.first_appearance_year}<span
          >First appearance: {item.first_appearance_year}</span
        >{/if}
    </li>
  {/each}
</ul>

• export let <name> is Svelte's way of declaring input arguments for components. We set it to an empty array by default, because the each below requires it to be an array.
• Each item gets a list item; Tailwind classes are used liberally.

<script>
  import SuperHeroes from "$lib/components/SuperHeroes.svelte"; // 1
  const description = "Here's my description!";
  async function update(value) {
    if (value.trim().length > 0) {
      items = await fetch(`/heroes?q=${value.trim()}`).then((v) => v.json());
    } else {
      items = [];
    }
  } // 6
  let items = [];
  let value;
  $: update(value); // 5
</script>

<svelte:head>
  <meta name="description" content={description} />
  <title>Here's a title!</title>
</svelte:head>

<main class="w-[95%] max-w-2xl mx-auto mt-48">
  <h1 class="text-3xl font-bold">
    Example reading data from a local SQLite database
  </h1>
  <p>Search for superheroes below</p>
  <input
    class="p-2"
    type="search"
    autocomplete="off"
    placeholder="Search"
    bind:value
  /> <!-- 2, 4 -->
  <SuperHeroes {items} /> <!-- 3 -->
</main>

1. We have to import the component.
2. The value of the input element is always written into the value variable of the component. This is done with bind:. Because the variable and the element field have the same name, we can write bind:value instead of bind:value={value}. In fact, the former seems to be the preferred style according to the formatter (Prettier and prettier-plugin-svelte).
3. Similarly, <SuperHeroes {items} /> is the preferred shorthand for <SuperHeroes items={items} />.
4. Also note that there doesn't seem to be a way to add comments in the element properties part.
5. Whenver value is updated, we run update(value). This behavior is enabled by the $:. Svelte analyzes the right hand side to find variables that are depended on, and if they change the statement will be re-run.
6. The update function fetches the result from the API route and sets items directly, triggering a rerender of SuperHeroes if it changed. As a special case, if the input is empty, we clear the items array.

This does a lot of network requests, but I think it's fine as a demonstration.

• Just use gz
• new Database(zlib.gunzipSync(fs.readFileSync("data.db.gz")))

• Install deps
• Build (with correct Node version)
• Install netlify-cli
• Deploy

TODO: actually test this on our demo repository

To deploy to Netlify, we need to:

• Use adapter-netlify (or use adapter-auto and satisfy its requirements for building for Netlify)

• Make sure we install native dependencies — just better-sqlite3 here — in the same Node version as would be used to run the serverless function. This is because Netlify copies the build-time node_modules to the context of where the serverless functions would be run.

  • The build time Node version is controlled by the NODE_VERSION on Netlify's build servers
  • The run time Node version is controlled by AWS_LAMBDA_JS_RUNTIME; the latest version as of writing is Node 16, specified as “nodejs16.x”
• Make sure the database is actually copied to the right place

netlify.toml:

[functions]
included_files = ["superheroes.db"]

On Netlify, in a monorepo (i.e. your project root is not your repository root), the file path will be relative to the project root during build as expected, but during runtime of the serverless function it will be relative to repository root instead. This feels like a bug, but we can work around it.

Instead of

let db = new Database(fs.readFileSync("superheroes.db"));

assuming the project lives at ./subdir under the repository, you could do

function readDB(path) {
    return new Database(fs.readFileSync(path));
}

let db;
try {
    db = readDB("superheroes.db");
} catch (e) {
    if (e instanceof Error && e.code === "ENOENT") {
        db = readDB("subdir/superheroes.db")
    } else {
        throw e
    }
}