SvelteKit 1 with SQLite: Displaying, Changing and Searching Data

SvelteKit 1.0 released in December 2022. As it now has a stable API I took a peek at how data-driven applications can be built with it. I am impressed by its simplicity and how fast you can achieve great results.

Together, SvelteKit and SQLite make a great combo because they both have a small footprint and are easy to set up and use. SQLite can be easily integrated into a SvelteKit application, allowing developers to easily store and retrieve data in a structured way, making it easy to build CRUD web applications.

SQLite is serverless (not the new cloud buzzword) meaning that it runs within the same process from the consuming application. Traditional relational client/server databases like MySQL or Postgres run in their own process and lead to the burden of having to maintain them. This approach can have big advantages if you have a high amount clients directly connecting to the database. In our SvelteKit app approach, this is not the case, as only the SvelteKit backend is directly accessing the database.

I use SvelteKit in combination with TypeScript and highly recommend you do this as well. We can type our data shapes in the backend and later reuse them in the frontend to safely access the data in the UI and make our lives easier in case of type changes.

I will use better-sqlite3 as the Node.js SQLite driver as I like its synchronous API.

Instead of setting up a schema with data myself, I used the chinook sample database that includes music data with tables like artists, albums, and tracks.

The example database has a table called tracks. We will query this table to display the first 50 tracks on our home page.

As the database file only sits on the server, we can create the main DB file in lib/server. lib is the place to put your code that does not strictly belong to any route and can be imported with the $lib prefix.

Any code in the server subfolder cannot be imported in the frontend. So any code in there is hidden from the eyes of the users. You can read more about the project structure in the docs.

I created another subfolder db with an index.ts. We can import Database from better-sqlite3 and use its constructor to start our database. Afterward, we can outsource the path to my database file in a .env file with the content DB_PATH=./data/chinook.db.

We start with the function getInitialTracks that just returns the first 50 tracks from the corresponding table. I store the query in a variable and later call db.prepare with it. The prepared statement can then be executed with the .all function as we want to get multiple rows. This function also receives an object of the parameters we want to bind to the query. In our case, this is the limit amount we can now provide as a function parameter. We don’t want to manually concatenate that input into the SQL-Query as this can lead to nasty SQL-Injection vulnerabilities. The whole file now looks like this:

1import Database from 'better-sqlite3';
2import { DB_PATH } from '$env/static/private';
3import type { Track } from './types';
4
5const db = new Database(DB_PATH, { verbose: console.log });
6
7export function getInitialTracks(limit = 50): Track[] {
8  const sql = `
9  select t.TrackId as trackId
10  , t.Name as trackName
11  , a.AlbumId as albumId
12  , a.Title as albumTitle
13  , at.ArtistId as artistId
14  , at.Name as artistName
15  , g.Name as genre
16from tracks t
17join albums a
18 on t.AlbumId = a.AlbumId
19join artists at
20 on a.ArtistId = at.ArtistId
21join genres g
22 on t.GenreId = g.GenreId
23limit $limit  
24  `;
25  const stmnt = db.prepare(sql);
26  const rows = stmnt.all({ limit });
27  return rows as Track[];
28}
29

As I used TypeScript we also need to add a definition for our Track type. I created a types.ts next to the database index.ts with the following content:

1export type Track = {
2  trackId: number;
3  trackName: string;
4  albumId: number;
5  albumTitle: string;
6  artistId: number;
7  artistName: string;
8  genre: string;
9};
10

To show the data on our home page we need to create a +page.server.ts in src/routes. In there we can add a load function that simply returns the data of our function:

1import { getInitialTracks } from '$lib/server/db';
2import type { PageServerLoad } from './$types';
3
4export const load = (() => {
5  const tracks = getInitialTracks();
6
7  return {
8    tracks,
9  };
10}) satisfies PageServerLoad;
11

These few lines are enough for us to consume the data in our frontend svelte file. In the neighboring +page.svelte we can consume and display the data:

1<script lang="ts">
2    import type { PageData } from './$types';
3    export let data: PageData;
4<script />
5
6<h1 class="is-size-1">Tracks</h1>
7
8    <table class="table">
9        <thead>
10            <tr>
11                <th>Track</th>
12                <th>Artist</th>
13                <th>Album</th>
14                <th>Genre</th>
15            </tr>
16        </thead>
17        <tbody>
18            {#each data.tracks as track}
19                <tr>
20                    <td>{track.trackName}</td>
21                    <td>{track.artistName}</td>
22                    <td>{track.albumTitle}</td>
23                    <td>{track.genre}</td>
24                </tr>
25            {/each}
26        </tbody>
27    </table>
28

As I included bulma to get some modern initial styles the result looks the following:

A neat addition to the app is an album details page that shows some basic information about any album with a tracklist.

As SvelteKit uses a filesystem-based router we can create new folders to allow for an album detail route. Inside src/routes we can create album/[albumId]. We put the albumId in squared brackets so that SvelteKit treats this as a parameter so that different content is loaded for different IDs.

In the newly created folders, we can add empty +page.server.ts and +page.svelte files.

Back in our database index.ts we can add following two functions for the new page:

1export function getAlbumById(albumId: number): Album {
2  const sql = `
3  select a.AlbumId as albumId
4     , a.Title as albumTitle
5     , at.ArtistId as artistId
6     , at.Name as artistName
7  from albums a
8  join artists at on a.AlbumId = at.ArtistId
9 where a.AlbumId = $albumId;
10  `;
11  const stmnt = db.prepare(sql);
12  const row = stmnt.get({ albumId });
13  return row as Album;
14}
15
16export function getAlbumTracks(albumId: number): AlbumTrack[] {
17  const sql = `
18  select t.TrackId as trackId
19     , t.Name as trackName
20     , t.Milliseconds as trackMs
21  from tracks t
22 where t.AlbumId = $albumId
23order by t.TrackId
24`;
25  const stmnt = db.prepare(sql);
26  const rows = stmnt.all({ albumId });
27  return rows as AlbumTrack[];
28}
29

In the types.ts file, we then add the newly used types:

1export type Album = {
2  albumId: number;
3  albumTitle: string;
4  artistId: number;
5  artistName: string;
6};
7
8export type AlbumTrack = {
9  trackId: number;
10  trackName: string;
11  trackMs: number;
12};
13

In the new +page.server.ts file, we can then define our load function. We can receive the albumId of the URL from the params object. The value is named the same as we defined in the folder name ([albumId]).

1export const load = (({ params }) => {
2  const albumId = parseInt(params.albumId);
3
4  const album = getAlbumById(albumId);
5  const tracks = getAlbumTracks(albumId);
6
7  return {
8    album,
9    tracks,
10  };
11}) satisfies PageServerLoad;
12

It is now trivial to display the loaded data on the page:

1<script lang="ts">
2  import type { PageData } from './$types';
3  export let data: PageData;
4</script>
5
6<div class="px-4">
7  <h1 class="is-size-1">{data.album.albumTitle}</h1>
8  <p class="is-size-4">By {data.album.artistName}</p>
9
10  <table class="mt-6 table">
11    <thead>
12      <tr>
13        <th>#</th>
14        <th>Track</th>
15        <th>Duration</th>
16      </tr>
17    </thead>
18    <tbody>
19      {#each data.tracks as track, i}
20      <tr>
21        <td>{i + 1}</td>
22        <td>{track.trackName}</td>
23        <td>{Math.floor(track.trackMs / 1000)} s</td>
24      </tr>
25      {/each}
26    </tbody>
27  </table>
28</div>
29

We call our album route with any non-existing album ID we get a default unstyled error page that says “500 Internal Error”.

The 500 error code is not great as 404 “not found” would be better as the error was caused by an unknown album ID. We can manually fix this by adding error handling mechanisms in our +page.server.ts. We can check after parsing the parameter as an integer if we have any value left as parsing only characters would result in no value. If this is the case we can throw the error function provided by SvelteKit. We can then define our wanted HTTP code and the message. We do the same when our database function still returns without a value:

1import { error, type Actions } from '@sveltejs/kit';
2
3export const load = (({ params }) => {
4  const albumId = parseInt(params.albumId);
5
6  if (!albumId) {
7    throw error(404, 'Album not found');
8  }
9
10  const album = getAlbumById(albumId);
11
12  if (!album) {
13    throw error(404, 'Album not found');
14  }
15
16  const tracks = getAlbumTracks(albumId);
17
18  return {
19    album,
20    tracks,
21  };
22}) satisfies PageServerLoad;
23

We may want this error page to match our page style or add more information. We can achieve this by adding a +error.svelte file next to the page file. Note that we could also create a global error file as SvelteKit will use the closest file while traversing up the file tree.

For a really simple page, we can just display the HTTP status code and a message by importing both from the page store:

1<script>
2  import { page } from '$app/stores';
3</script>
4
5<h1 class="is-size-1 has-text-danger">Ooopsie Whooopsie</h1>
6<p>{$page.status}: {$page?.error?.message}</p>
7

The result looks the following:

To cover sending data to the backend to write data back to the database I decided to allow updating the album name.

We have to extend our database functions again with a new one that runs the update statement. We only need our primary key column, the AlbumId, and the new value for the Title column. We can add a prepared statement as before and pass the values as an object:

1export function updateAlbumTitle(albumId: number, albumTitle: string): void {
2  const sql = `
3  update albums
4     set Title = $albumTitle
5   where AlbumId = $albumId
6`;
7  const stmnt = db.prepare(sql);
8  stmnt.run({ albumId, albumTitle });
9}
10

The easiest way to send data to the backend and start an operation is by utilizing forms. We can add one to the album page and set the HTTP method to POST instead of GET as this makes sense for writing operations.

We add a hidden input field for the album ID that is prefilled by our load data. Additionally, we create a text input field for the new album title. Both items need the name property so we can access the values later on.

We can then add a button that submits the form. We can add the formaction property and set a function name that is then triggered in the +page.server.ts file. This way we can do different operations based on a single form (e. g. Create, Update, Delete). Note that we prefix the function name with a question mark and slash to make it a relative path.

1<!-- ... -->
2<h2 class="is-size-3 mb-4 mt-6">Update Album Name</h2>
3<form method="post">
4  <input
5    class="input"
6    type="text"
7    name="albumTitle"
8    value="{data.album.albumTitle}"
9    style="max-width: 50ch;"
10  />
11  <input type="hidden" name="albumId" value="{data.album.albumId}" />
12  <button
13    class="button is-primary"
14    type="submit"
15    formaction="?/updateAlbumTitle"
16  >
17    Update
18  </button>
19</form>
20

We can now handle the form submission in our +page.server.ts file. For that, we create a const called actions. In there we define a function called the same as our formaction attribute on our button.

We can access the form data by calling request.formData() and then grab the values by their name. Now we call our database function and pass the values:

1export const actions: Actions = {
2  updateAlbumTitle: async ({ request }) => {
3    const data = await request.formData();
4
5    const albumIdStr = data.get('albumId')?.toString();
6    const albumId = albumIdStr ? parseInt(albumIdStr) : null;
7
8    const albumTitle = data.get('albumTitle')?.toString();
9
10    if (!(albumId && albumTitle)) {
11      throw error(400, 'AlbumId or AlbumTitle missing');
12    }
13
14    updateAlbumTitle(albumId, albumTitle);
15  },
16};
17

Back on our home page, we want to make the tracks searchable. I think the most user-convenient way is to implement a search while you type. So this time we can not use any load function as we have to send a request to our backend while the user types and not on the initial page load.

We have to create a new DB function that takes a search term. We can then use the like operator to search for the term in the track name. By using the lower function to make the search case insensitive:

1export function searchTracks(searchTerm: string, limit = 50): Track[] {
2  const sql = `
3  select t.TrackId as trackId
4  , t.Name as trackName
5  , a.AlbumId as albumId
6  , a.Title as albumTitle
7  , at.ArtistId as artistId
8  , at.Name as artistName
9  , g.Name as genre
10from tracks t
11join albums a
12 on t.AlbumId = a.AlbumId
13join artists at
14 on a.ArtistId = at.ArtistId
15join genres g
16 on t.GenreId = g.GenreId
17where lower(t.Name) like lower('%' || $searchTerm || '%')
18limit $limit  
19  `;
20  const stmnt = db.prepare(sql);
21  const rows = stmnt.all({ searchTerm, limit });
22  return rows as Track[];
23}
24

So instead of calling the new function in our load, we can make use of API routes. These endpoints don’t support any page and thus need to be called +server.ts (instead of +page.server.ts). I think it makes sense to create an api folder so my API route sits in src/routes/api/searchTracks/+server.ts.

We can create a function GET as we want it to be triggered by the HTTP GET method. The search term should be passed as a query parameter. We can access it by calling url.searchParams.get('searchTerm')?.toString();. If there is no search term we can return the initial tracks. Otherwise, we can pass our array to the json function and return it.

1import { getInitialTracks, searchTracks } from '$lib/server/db';
2import type { Track } from '$lib/server/db/types';
3import { json } from '@sveltejs/kit';
4import type { RequestHandler } from './$types';
5
6export const GET = (({ url }) => {
7  const searchTerm = url.searchParams.get('searchTerm')?.toString();
8  console.log('searchTerm', searchTerm);
9
10  let tracks: Track[] = [];
11
12  if (!searchTerm) {
13    tracks = getInitialTracks();
14  } else {
15    tracks = searchTracks(searchTerm) ?? [];
16  }
17
18  return json(tracks);
19}) satisfies RequestHandler;
20

We add an input of type search to our home page. We can then add an on:keyup and on:search event listener. The on:keyup event is triggered on every key press. The on:search event is triggered when the user presses the enter key or clicks the ‘x’ to reset the input:

1<input
2  type="search"
3  placeholder="Search..."
4  class="input mb-5"
5  style="max-width: 80ch;"
6  value="{searchTerm}"
7  on:keyup="{handleSearch}"
8  on:search="{handleSearch}"
9/>
10

Then handleSearch function receives the event and we can access the input value by calling e.target.value. I recommend to debounce the search to throttle the number of requests send to the server. So we don’t actually filter while the user types, only after he has not typed in 300 ms. We can do this by using timeouts.

The real request is done in the fetchTracks function. It uses the fetch API to make a GET request to our API route. We pass the search term as a query parameter. After the request is done we can parse the response as JSON and set the result to the new tracks variable.

1<script lang="ts">
2  // ...
3  let timer: NodeJS.Timeout;
4  let searchTerm = '';
5  function fetchTracks() {
6    fetch(`/api/searchTracks?searchTerm=${searchTerm}`)
7      .then((res) => res.json())
8      .then((data) => {
9        tracks = data;
10      });
11  }
12  function handleSearch(e: Event) {
13    clearTimeout(timer);
14    timer = setTimeout(() => {
15      const target = e.target as HTMLInputElement;
16      searchTerm = target.value;
17      fetchTracks();
18    }, 300);
19  }
20  let tracks = data.tracks;
21</script>
22

We still need to change the table loop to accessing the tracks variable instead of data.tracks so that it updates after a search:

1{#each tracks as track}
2    <tr>
3        <td>{track.trackName}</td>
4        <td>{track.artistName}</td>
5        <td><a href={`/album/${track.albumId}`}>{track.albumTitle}</a></td>
6        <td>{track.genre}</td>
7    </tr>
8{/each}
9

Now we are done and can search for tracks. You can find the final code on GitHub. Let me know in the comments if you build something with SvelteKit and SQLite and if you like the combination,