SvelteKit Turso Fly.io App Guide

April 12, 2024 • 25 min read

Hey! Thanks for stopping by! Just a word of warning, this post is 2 years old, . If there's technical information in here it's more than likely out of date.

I’ve been doing more stuff with Fly.io and Turso with SvelteKit. If you’re following my posts you’ll know I’ve got a real nerd boner for using Turso in anything I can cram it into at the moment!

Why Fly.io though? Just get a $5 VPS and use that! Skill issue! 😂 Well, sure, skill issue and time issue, insert “ain’t nobody got time for that” gif here! (I managed several company Linux boxes in the past). With Fly, I’m essentially shipping a Node container on their platform. This opens up the possibility of using Turso embedded replicas and multi-tenant apps! I’m not doing that now though!

What I want to go through here is the basics of what you’ll need to get a project scaffolded out to connect to a serverless database, display the data and deploy it to Fly.

The inspiration for doing this was a video from Philipp Hartenfeller that I caught on YouTube. You don’t want to use Turso? Check out his video!

This isn’t a CRUD app it’s an R app! 😂 This guide uses the chinook SQLite sample database. So there’s a load of tables and data to query. No need for user generated content and authentication, although this can be added in, it’s out of the scope of this and the time I can spend writing it.

Although I have used and like Drizzle ORM on other projects I really do prefer to just use SQL to query data.

So, no auth, no ORM, just raw dog SQL for getting data…

Still here? Good! So, this guide will go over the following:

Actually getting a database you can use without having to make all the tables and add data!
Use some Svelte 5 features, runes, yes
Adding a local SQLite database to Turso
Setting up queries to use in the project, including a full text search query
Push the project to Fly.io

Setting up the project

So, usual SvelteKit setup from the terminal. But, as I’ll be using Fly to deploy the finished project I’ll be using Bun as the runtime and package manager. Why? Well, deploying a SvelteKit Node project to Fly can sometimes cause issues with CJS/ESM compatibility not being handled and I found using Bun sidestepped this whole issue.

I’ll spin up a new app using the create svelte command for Bun:

1bun create svelte sveltekit-turso-flyio-app

I’ll pick the following options:

Skeleton project
using TypeScript syntax
All the additional options
- Add ESLint for code linting
- Add Prettier for code formatting
- Add Playwright for browser testing
- Add Vitest for unit testing
- Try the Svelte 5 preview (unstable!)

For connecting to the Turso database I’ll need to install the Turso client:

1bun i -D @libsql/client

Then because I’m using Bun I’ll want to uninstall the SvelteKit auto adapter and add in the Bun adapter:

1bun uninstall @sveltejs/adapter-auto
2bun i -D svelte-adapter-bun

Then configure the adapter in the svelte.config.js file:

1- import adapter from '@sveltejs/adapter-auto';
2+ import adapter from 'svelte-adapter-bun';
3import { vitePreprocess } from '@sveltejs/vite-plugin-svelte';
4
5/** @type {import('@sveltejs/kit').Config} */
6const config = {

I’ll also need to adjust the scripts in the package.json file to use Bun:

1"scripts": {
2+  "start": "bun ./build/index.js",
3-  "dev": "vite dev",
4+  "dev": "bun vite",
5-  "build": "vite build",
6+  "build": "bunx --bun vite build",
7-  "preview": "vite preview",
8+  "preview": "bun vite preview",
9  "test": "bun run test:integration && bun run test:unit",
10  "check": "svelte-kit sync && svelte-check --tsconfig ./tsconfig.json",

If you’re following along and you haven’t used Turso before, you’ll need to install the Turso CLI, it’s a one liner from the Turso quickstart page.

I’m using WSL so I’ll use the Linux install command:

1curl -sSfL https://get.tur.so/install.sh | bash

I’ll be going over all the Turso CLI commands in a later section.

Aight! Time to scaffold out the files for the server stuff, from the terminal I’ll add in the folder/directory and files with the following commands:

1mkdir src/lib/server
2touch src/lib/server/{client,index,queries}.ts

I’ll get that set up in another section, what I will need though is a .env file with the secrets for the Turso client to connect to the database, a one liner to create the file with:

1touch .env && echo -e "TURSO_DB_URL=\nTURSO_DB_AUTH_TOKEN=" >> .env

That’ll create a .env file in the root of the project with the TURSO_DB_URL and TURSO_DB_AUTH_TOKEN secrets ready for populating when I generate them.

Sweet! Now I’ll get the database set up on Turso!

Adding the database to Turso

So, the Turso CLI allows you to add a database via a file, so, I can download the chinook database zip from the SQLite tutorial site I linked earlier.

Then with the CLI using the --from-file flag and pointing to the extracted chinook.db file I can create a new database on Turso:

1turso db create sveltekit-turso-flyio-app --from-file /mnt/c/Users/scott/Downloads/chinook.db

I’m going to need the database URL to add to my project secrets (.env file), I can use the show command to get this:

1turso db show sveltekit-turso-flyio-app

Also, I’ll need to generate an auth token which I can do via the Turso CLI too:

1turso db tokens create sveltekit-turso-flyio-app

Add them to the .env file:

1TURSO_DB_URL=libsql://sveltekit-turso-flyio-app.turso.io
2TURSO_DB_AUTH_TOKEN=the-generated-auth-token

Right! I’m now ready to set up the Turso client so I can query data from the database!

Setting up the Turso client

In the src/lib/server/client.ts file I created, I’ll export a client function, essentially I could do this:

1import { env } from '$env/dynamic/private';
2import { createClient, type Client } from '@libsql/client';
3
4const { TURSO_DB_URL, TURSO_DB_AUTH_TOKEN } = env;
5
6export const turso_client = (): Client => {
7	return createClient({
8		url: TURSO_DB_URL as string,
9		authToken: TURSO_DB_AUTH_TOKEN as string,
10	});
11};

But, what I should do is the responsible thing and add in some error handling in there:

1import { env } from '$env/dynamic/private';
2import { createClient, type Client } from '@libsql/client';
3
4const { TURSO_DB_URL, TURSO_DB_AUTH_TOKEN } = env;
5
6export const turso_client = (): Client => {
7	const url = TURSO_DB_URL?.trim();
8	if (url === undefined) {
9		throw new Error('TURSO_DB_URL is not defined');
10	}
11
12	const auth_token = TURSO_DB_AUTH_TOKEN?.trim();
13	if (auth_token === undefined) {
14		if (!url.includes('file:')) {
15			throw new Error('TURSO_DB_AUTH_TOKEN is not defined');
16		}
17	}
18
19	return createClient({
20		url: TURSO_DB_URL as string,
21		authToken: TURSO_DB_AUTH_TOKEN as string,
22	});
23};

Ok, now, I’ll export this function from the src/lib/server/index.ts file, I’ll also export the queries from here too, more on them in the next section!

1export * from './client';
2export * from './queries';

Sweet! Now I can use the client to query some data! Now to get the data!

Setting up queries

I want to do a full text search query on the tracks table but also get information on the album, artist, genre and track.

First up though, I want to have some initial data to show on the index page, so I’ll set up an initial query in the src/lib/server/queries.ts file:

1SELECT t.TrackId AS TrackId,
2  t.Name AS Name,
3  a.AlbumId AS AlbumId,
4  a.Title AS Title,
5  at.ArtistId AS ArtistId,
6  at.Name AS ArtistName,
7  g.Name AS Genre,
8  g.GenreId AS GenreId
9FROM tracks t
10JOIN albums a ON t.AlbumId = a.AlbumId
11JOIN artists at ON a.ArtistId = at.ArtistId
12JOIN genres g ON t.GenreId = g.GenreId
13LIMIT 50;

So, loads of SQL joins and shiz! Right? I’m not going to go into a relational database fundamentals talk here, so, if you’re not sure what’s happening here essentially getting the names off of the related tables that includes the names for the album, artist and genre, so, let’s take a look at the data I retrieve if I did a straight up SELECT * FROM tracks LIMIT 3;. It looks like this:

1TRACKID  NAME                                     ALBUMID  MEDIATYPEID  GENREID  COMPOSER                                             MILLISECONDS  BYTES     UNITPRICE
21        For Those About To Rock (We Salute You)  1        1            1        Angus Young, Malcolm Young, Brian Johnson            343719        11170334  0.99
32        Balls to the Wall                        2        2            1        NULL                                                 342562        5510424   0.99
43        Fast As a Shark                          3        2            1        F. Baltes, S. Kaufman, U. Dirkscneider & W. Hoffman  230619        3990994   0.99

Whereas I want something a bit more descriptive, so, running the big ass query with all the joins I get something like this:

1TRACKID  NAME                                     ALBUMID  TITLE                                  ARTISTID  ARTISTNAME  GENRE  GENREID
21        For Those About To Rock (We Salute You)  1        For Those About To Rock We Salute You  1         AC/DC       Rock   1
36        Put The Finger On You                    1        For Those About To Rock We Salute You  1         AC/DC       Rock   1
47        Let's Get It Up                          1        For Those About To Rock We Salute You  1         AC/DC       Rock   1

I’m keeping the IDs as well for cross linking to other pages. More on that later!

Aight, so this is an SQL query, I’m not doing jack with this in a TypeScript file!

In the src/lib/server/queries.ts file I’ll import the Turso client and .execute that query.

1import { turso_client } from '.';
2
3const client = turso_client();
4
5export const get_initial_tracks = async (limit = 50) => {
6	const tracks = await client.execute({
7		sql: `SELECT t.TrackId AS TrackId,
8            t.Name AS Name,
9            a.AlbumId AS AlbumId,
10            a.Title AS Title,
11            at.ArtistId AS ArtistId,
12            at.Name AS ArtistName,
13            g.Name AS Genre,
14            g.GenreId AS GenreId
15          FROM tracks t
16          JOIN albums a ON t.AlbumId = a.AlbumId
17          JOIN artists at ON a.ArtistId = at.ArtistId
18          JOIN genres g ON t.GenreId = g.GenreId
19          LIMIT ?;`,
20		args: [limit],
21	});
22
23	return tracks.rows;
24};

Essentially the SQL is in backticks and given to the client as the SQL to run, the args are the arguments to pass to the SQL query, you may have noticed the LIMIT ?; at the end of the query, that ? will get substituted with the limit argument.

Ok, so, this is going on a bit now, but, what I want to do here is have a way for the user to be able to enter some text into an input box and do a search against all the data in the database.

With that big boi query, I only get the first 50 rows, so, I’ll need to set up a virtual table that I can search against for anything that’s in the database.

So, now, time for some more SQL’ing! I’m going to shell into the Turso database for this:

1turso db shell sveltekit-turso-flyio-app

Then from the shell I’ll create a virtual table that I can search against, I’ll do this in three steps.

Create the virtual (FTS5) table
Insert Data into the (FTS5) table (via big ass query)
Fuzzy query the (FTS5) table

First up, create the virtual table:

1CREATE VIRTUAL TABLE tracks_fts USING fts5 (
2  TrackId,
3  Name,
4  AlbumId,
5  Title,
6  ArtistId,
7  ArtistName,
8  Genre,
9  GenreId,
10  prefix = '2 3 4'
11);

The prefix ‘2 3 4’ is so that it’s optimised for searches that are that length, so searching for Jamiroquai if I enter jam I should get a result matching that.

Then insert the data into the virtual table:

1INSERT INTO
2  tracks_fts (
3    TrackId,
4    Name,
5    AlbumId,
6    Title,
7    ArtistId,
8    ArtistName,
9    Genre,
10    GenreId
11  )
12SELECT
13  t.TrackId,
14  t.Name,
15  a.AlbumId,
16  a.Title,
17  at.ArtistId,
18  at.Name,
19  g.Name,
20  g.GenreId
21FROM
22  tracks t
23  JOIN albums a ON t.AlbumId = a.AlbumId
24  JOIN artists at ON a.ArtistId = at.ArtistId
25  JOIN genres g ON t.GenreId = g.GenreId;

Then query the virtual table, so for Jamiroquai try:

1SELECT * FROM tracks_fts WHERE tracks_fts MATCH 'jam';

Success, ok now jag for Jagged Little Pill?? Nothing? Ok, so, I need to add in a * to the end of the search term to do a fuzzy search:

1SELECT * FROM tracks_fts WHERE tracks_fts MATCH 'jag*';

Then I get the result I’m looking for!

Cool! So, I just want to highlight that for now, as I’ll be coming back to that later!

For now I’ll concentrate on getting the data from Turso into the project!

Get the data from Turso

Cool! I’ll test out I’m getting data from the Turso database client now. So, because this is a SvelteKit project I can use the load function to go off and get data for the page on initial load.

Because the Turso client is server side I’ll need to create a +page.server.ts file at the root of the routes directory:

1touch src/routes/+page.server.ts

Then add in a load function to get the initial tracks and return them for use on the index page.

1import { get_initial_tracks } from '$lib/server';
2
3export const load = async () => {
4	const tracks = await get_initial_tracks();
5
6	return {
7		tracks,
8	};
9};

In the src/routes/+page.svelte file I can then get the props from the load function and display the data on the page.

As I’m just validating that I’m getting data through to the page I’ll use my trusty debug tool, the <pre>{JSON.stringify(data, null, 2)}</pre> of the data!

1<script lang="ts">
2	let { data } = $props();
3</script>
4
5<pre>{JSON.stringify(data, null, 2)}</pre>
6
7<h1>Welcome to SvelteKit</h1>
8<p>
9	Visit <a href="https://kit.svelte.dev">kit.svelte.dev</a> to read the
10	documentation
11</p>

That gives me some output that looks like this:

1{
2  "tracks": [
3    {
4      "TrackId": 1,
5      "Name": "For Those About To Rock (We Salute You)",
6      "AlbumId": 1,
7      "Title": "For Those About To Rock We Salute You",
8      "ArtistId": 1,
9      "ArtistName": "AC/DC",
10      "Genre": "Rock",
11      "GenreId": 1
12    },
13    {
14      "TrackId": 6,
15      "Name": "Put The Finger On You",
16      "AlbumId": 1,
17      "Title": "For Those About To Rock We Salute You",
18      "ArtistId": 1,
19      "ArtistName": "AC/DC",
20      "Genre": "Rock",
21      "GenreId": 1
22    },

Cool! So, when the page loads I’m getting the data from the database, so, what about searching for stuff? I’ll come onto that, soon, first better get this index page cleaned up a bit!

So, styling so far, I am a massive advocate for daisyUI however, so I’ll add that in:

1npx svelte-add@latest tailwindcss --tailwindcss-typography --tailwindcss-daisyui
2bun i

The bunx command currently doesn’t work with the svelte-add, so, I’m using the npx script then installing with bun.

That will configure Tailwind and the daisyUI plugin and add in the files needed for the project.

1├── src
2│   ├── routes
3│   │   └── +layout.svelte
4│   └── app.pcss
5├── .prettierrc
6├── package.json
7├── postcss.config.cjs
8├── svelte.config.js
9└── tailwind.config.cjs

Added files, +layout.svelte, app.pcss, postcss.config.cjs and tailwind.config.cjs are added to the project, with the other files configured for Tailwind.

Serious, if you’re following along and you have a stick up your butt about Tailwind, that’s cool! You can spend all the extra time you must have on your hands hand writing the CSS, I’m not about that life for an example app! 😂

Aight, I’ll get the initial page layout going on using a table and the handy styling utils from daisyUI:

1<script lang="ts">
2	let { data } = $props();
3</script>
4
5<svelte:head>
6	<title>Music Search - Chinook SvelteKit</title>
7</svelte:head>
8
9<p class="mb-2 text-xl font-light">
10	This is the initial 50 tracks from the Chinook database
11</p>
12
13<div class="overflow-x-auto">
14	<table
15		class="table table-pin-rows table-pin-cols table-xs md:table-lg"
16	>
17		<thead>
18			<tr class="text-xl">
19				<th>Track</th>
20				<th>Artist</th>
21				<th>Album</th>
22				<th>Genre</th>
23			</tr>
24		</thead>
25		<tbody>
26			{#each data.tracks as track (track.TrackId)}
27				<tr class:hover={'bg-base-200'}>
28					<td>{track.Name}</td>
29					<td>{track.ArtistName}</td>
30					<td>{track.Title}</td>
31					<td>{track.Genre}</td>
32				</tr>
33			{/each}
34		</tbody>
35	</table>
36</div>

The data from the load function in the +page.server.ts file is received into the +page.svelte file via the Svelte 5 props rune and then I’m looping through that with an each block to render out the table.

Actually whilst I’m on the subject of runes, I’ll swap out the slot in the +layout.svelte file for the props rune, so, from this:

1<script>
2	import '../app.pcss';
3</script>
4
5<slot />

To this:

1<script lang="ts">
2	let { children } = $props();
3	import '../app.pcss';
4</script>
5
6<main class="container mx-auto max-w-6xl flex-grow px-4">
7	<h1 class="mb-2 mt-4 text-5xl font-bold text-primary">
8		<a href="/">Chinook SQLite database</a>
9	</h1>
10	<ul class="mb-10 flex space-x-4 text-xl font-bold">
11		<li><a href="/genre" class="link link-primary">Genres</a></li>
12		<li><a href="/" class="link link-primary">Home</a></li>
13	</ul>
14	{@render children()}
15</main>

Adding in some nav links and a title to the layout file along with some tailwind container classes. The /genre link is a placeholder for a genre page I’ll add in later.

What I’ve done here is, instead of the slot I’m using the children prop to render out the children of the layout file like a snippet.

Cool! A page with 50 tracks on it from the database! Bit pants right! 😂

Ok, I’ll add in a search input now to use the full text search query.

Adding a search input

Now I want to be able to utilise that full text search table I created, so, I’m going to need to first make that query to the database form the Turso client.

Essentially that select query I made earlier validating the search on the search_track table, I’m going to group all the queries being used in the project in the src/lib/server/queries.ts file.

This is going to be the same setup, passing the SQL to the client with the argument for what is being searched.

Remember the * at the end of the search earlier, I’ll add that in now with some regex to escape any double quotes in the search term:

1export const search_tracks = async (search_term: string) => {
2	const escaped_search_term = `"${String(search_term).replace(/"/g, '""')}"*`;
3
4	const tracks = await client.execute({
5		sql: `SELECT * FROM tracks_fts WHERE tracks_fts MATCH ?;`,
6		args: [escaped_search_term],
7	});
8
9	return tracks.rows;
10};

So, now notice that the get_initial_tracks and the search_tracks return the same variable name?

This is so that they can be swapped interchangeably.

More on that in a bit, for now though I want to way to get that data from the server, so, I’ll add in a new route for the search query.

The convention is to stick API call in a src/routes/api directory, so I’ll add a new search folder with a +server.ts file in there:

1mkdir -p src/routes/api/search
2touch src/routes/api/search/+server.ts

Then bang out a get to run the search_tracks query:

1import { get_initial_tracks, search_tracks } from '$lib/server';
2import type { Row } from '@libsql/client';
3import { json } from '@sveltejs/kit';
4
5export const GET = async ({ url }) => {
6	const search_term = url.searchParams.get('search_term')?.toString();
7
8	let tracks: Row[] = [];
9
10	if (!search_term) {
11		tracks = await get_initial_tracks();
12	} else {
13		tracks = (await search_tracks(search_term)) ?? [];
14	}
15
16	return json(tracks);
17};

Now I’m going to need to call this from the index page via a client side fetch, I’m going to need to set up some state for the search term and the results.

So, I’ll add the data.tracks that comes in from the load function as props then add that to state along with the search_term:

1let tracks = $state(data.tracks);
2let search_term = $state('');

Then to fetch the data from the server I’ll add in a function to do that:

1const fetch_tracks = async () => {
2	const res = await fetch(`/api/search?search_term=${search_term}`);
3	const data = await res.json();
4	tracks = data;
5};

Because I’ve added the tracks to state I’ll also need to swap out the data.tracks from the each loop in the table:

1- {#each data.tracks as track (track.TrackId)}
2+ {#each tracks as track (track.TrackId)}

Now whatever is is state is what will be rendered on the table.

Back to fetching the data now, so I’m going to be hitting that fetch_tracks function to get the data from the server, so I’ll want to limit the amount of calls to the endpoint, so, I’ll add in a debounce function with a 300 millisecond delay to limit the amount of calls to the server.

I’ll need to add a timer variable to state as well, so I’ll update my state to accommodate that as well:

1let tracks = $state(data.tracks);
2let search_term = $state('');
3let timer: string | number | NodeJS.Timeout | undefined = $state(300);

Then I’ll add in the debounce function:

1const handle_search = (e: Event) => {
2	clearTimeout(timer);
3	timer = setTimeout(() => {
4		const target = e.target as HTMLInputElement;
5		search_term = target.value;
6		fetch_tracks();
7	}, 300);
8};

Ok, last up for the script stuff I’ll want to add something in to handle the input from the input box (which doesn’t exist yet! 😅):

1const handle_input = (e: Event) => {
2	const target = e.target as HTMLInputElement;
3	if (target.value === '') {
4		search_term = '';
5		tracks = data.tracks;
6	}
7};

Now for the markup, for the value of the input I’ll not bind that to state as it need to go through the handle_search debounce function first, so I’ll add in the on:input to update the state and on:keyup to run the search:

1<input
2	type="search"
3	placeholder="Search tracks, titles, albums, artists, genres..."
4	class="input input-bordered input-primary mb-10 w-full"
5	value={search_term}
6	on:keyup={handle_search}
7	on:input={handle_input}
8/>

Code wall incoming!

Here’s the full file:

1<script lang="ts">
2	let { data } = $props();
3
4	let tracks = $state(data.tracks);
5	let search_term = $state('');
6	let timer: string | number | NodeJS.Timeout | undefined =
7		$state(300);
8
9	const fetch_tracks = async () => {
10		const res = await fetch(`/api/search?search_term=${search_term}`);
11		const data = await res.json();
12		tracks = data;
13	};
14
15	const handle_search = (e: Event) => {
16		clearTimeout(timer);
17		timer = setTimeout(() => {
18			const target = e.target as HTMLInputElement;
19			search_term = target.value;
20			fetch_tracks();
21		}, 300);
22	};
23
24	const handle_input = (e: Event) => {
25		const target = e.target as HTMLInputElement;
26		if (target.value === '') {
27			search_term = '';
28			tracks = data.tracks;
29		}
30	};
31</script>
32
33<svelte:head>
34	<title>Music Search - Chinook SvelteKit</title>
35</svelte:head>
36
37<input
38	type="search"
39	placeholder="Search tracks, titles, albums, artists, genres..."
40	class="input input-bordered input-primary mb-10 w-full"
41	value={search_term}
42	on:keyup={handle_search}
43	on:input={handle_input}
44/>
45
46<p class="mb-2 text-xl font-light">
47	This is the initial 50 tracks from the Chinook database
48</p>
49
50<div class="overflow-x-auto">
51	<table
52		class="table table-pin-rows table-pin-cols table-xs md:table-lg"
53	>
54		<thead>
55			<tr class="text-xl">
56				<th>Track</th>
57				<th>Artist</th>
58				<th>Album</th>
59				<th>Genre</th>
60			</tr>
61		</thead>
62		<tbody>
63			{#each data.tracks as track (track.TrackId)}
64				<tr class:hover={'bg-base-200'}>
65					<td>{track.Name}</td>
66					<td>{track.ArtistName}</td>
67					<td>{track.Title}</td>
68					<td>{track.Genre}</td>
69				</tr>
70			{/each}
71		</tbody>
72	</table>
73</div>

Cool! Now I have a nice little search bar from the index page to find, title, tracks, artist, genres and albums!

Wiring up the rest of the project

I’ve gone over the basic pattern I’ll be using for the rest of the routes now. The pattern is, make a query to get the data, add the data to state, then render the data on the page via a page load.

This is quite a meaty section with a lot of code, so, only interested in deploying to Fly.io? Skip to the next section!

So, remember the big ass query and all the fields it returned? Currently I’m only using the names but I have the IDs for Track, Artist, Album and Genre too. So, this means that I can start listing more related stuff out from the initial search.

So, that’s going to be a route with a parameter passed to it so that can be used as an argument for a query, I’ll get the routes and files for that scaffolded out now with a load of terminal commands:

1mkdir -p "src/routes/album/[album_id]" "src/routes/artist/[artist_id]" "src/routes/genre/[genre_id]" "src/routes/track/[track_id]"
2touch src/routes/album/'[album_id]'/+page.{server.ts,svelte}
3touch src/routes/artist/'[artist_id]'/+page.{server.ts,svelte}
4touch src/routes/genre/'[genre_id]'/+page.{server.ts,svelte}
5touch src/routes/genre/+page.{server.ts,svelte}
6touch src/routes/track/'[track_id]'/+page.{server.ts,svelte}

I’ll also want to link to each of these routes, so, in the each block on the src/routes/+page.svelte page I’ll add in links for each one:

1{#each tracks as track (track.TrackId)}
2	<tr class:hover={'bg-base-200'}>
3		<td>
4			<a href={`/track/${track.TrackId}`} class="link link-primary">
5				{track.Name}
6			</a>
7		</td>
8		<td>
9			<a href={`/artist/${track.ArtistId}`} class="link link-primary">
10				{track.ArtistName}
11			</a>
12		</td>
13		<td>
14			<a href={`/album/${track.AlbumId}`} class="link link-primary">
15				{track.Title}
16			</a>
17		</td>
18		<td>
19			<a href={`/genre/${track.GenreId}`} class="link link-primary">
20				{track.Genre}
21			</a>
22		</td>
23	</tr>
24{/each}

At the moment clicking on these isn’t going to go anywhere so I’ll start adding in the content, as stated, these patterns have already been detailed, now it’s a case of repeating them for each route.

In the src/lib/server/queries.ts file I’ll add in the query that’s going to give me some detail on the album with some transformation on the milliseconds field to express the time in minutes and seconds:

1export const get_album_by_id = async (album_id: number) => {
2	const album = await client.execute({
3		sql: `SELECT 
4            a.Title AS AlbumTitle, 
5            t.TrackId, 
6            t.Name AS TrackName, 
7            at.Name AS ArtistName,
8            (t.Milliseconds / 60000) || 'm ' || ((t.Milliseconds % 60000) / 1000) || 's' AS Duration
9          FROM 
10            albums a
11          JOIN 
12            tracks t ON a.AlbumId = t.AlbumId
13          JOIN 
14            artists at ON a.ArtistId = at.ArtistId
15          WHERE 
16            a.AlbumId = ?;`,
17		args: [album_id],
18	});
19
20	return {
21		artist: album.rows[0].ArtistName,
22		album: album.rows[0].AlbumTitle,
23		tracks: album.rows,
24	};
25};

I’m also going to return the artist name and album title as well as the tracks for the album, so in the src/routes/album/[album_id]/+page.server.ts file:

1import { get_album_by_id } from '$lib/server/queries';
2
3export const load = async ({ params }) => {
4	const album_id = parseInt(params.album_id);
5
6	const { artist, album, tracks } = await get_album_by_id(album_id);
7
8	return {
9		artist,
10		album,
11		tracks,
12	};
13};

Then for the src/routes/album/[album_id]/+page.svelte file I’ll add in the props and render out the data, much like the src/routes/+page.svelte pretty much copy paste changing some of the details:

1<script lang="ts">
2	let { data } = $props();
3
4	const { artist, album, tracks } = data;
5</script>
6
7<svelte:head>
8	<title>{album} - Chinook SvelteKit</title>
9</svelte:head>
10
11<h1 class="mb-5 text-4xl font-bold text-primary">{album}</h1>
12<p class="mb-10 text-3xl font-bold tracking-widest text-secondary">
13	By {artist}
14</p>
15
16<div class="overflow-x-auto">
17	<table
18		class="table table-pin-rows table-pin-cols table-xs md:table-lg"
19	>
20		<thead>
21			<tr class="text-xl">
22				<th>#</th>
23				<th>Track</th>
24				<th>Duration</th>
25			</tr>
26		</thead>
27		<tbody>
28			{#each tracks as track, i}
29				<tr class:hover={'bg-base-200'}>
30					<td>{i + 1}</td>
31					<td>
32						<a
33							href={`/track/${track.TrackId}`}
34							class="link link-primary"
35						>
36							{track.TrackName}
37						</a>
38					</td>
39					<td>{track.Duration}</td>
40				</tr>
41			{/each}
42		</tbody>
43	</table>
44</div>

Notice that I’ve liked the tracks as well, once that route is set up I’ll be able to click on the track and get more information on that…

Next up though, I’ll do the artist by artist ID, so, in the src/lib/server/queries.ts file I’ll add in the query to get the artist by ID, also add in a subquery to get the number of tracks on the album:

1export const get_albums_by_artist_id = async (artist_id: number) => {
2	const albums = await client.execute({
3		sql: `SELECT 
4            a.AlbumId, 
5            a.Title AS AlbumTitle, 
6            at.Name AS ArtistName,
7            (SELECT COUNT(*) FROM tracks t WHERE t.AlbumId = a.AlbumId) AS TrackCount
8          FROM 
9            albums a
10          JOIN 
11            artists at ON a.ArtistId = at.ArtistId
12          WHERE 
13            a.ArtistId = ?;`,
14		args: [artist_id],
15	});
16
17	return {
18		artist: albums.rows[0].ArtistName,
19		albums: albums.rows,
20	};
21};

Same pattern for the src/routes/artist/[artist_id]/+page.server.ts file, call the query and return the data:

1import { get_albums_by_artist_id } from '$lib/server/queries';
2
3export const load = async ({ params }) => {
4	const artist_id = parseInt(params.artist_id);
5
6	const { artist, albums } = await get_albums_by_artist_id(artist_id);
7
8	return {
9		artist,
10		albums,
11	};
12};

Then for the src/routes/artist/[artist_id]/+page.svelte file I’ll add in the props and render out the data:

1<script lang="ts">
2	let { data } = $props();
3
4	const { artist, albums } = data;
5</script>
6
7<svelte:head>
8	<title>{artist} - Chinook SvelteKit</title>
9</svelte:head>
10
11<h1 class="mb-5 text-4xl font-bold text-primary">{artist}</h1>
12<p class="mb-10 text-3xl font-bold tracking-widest text-secondary">
13	Albums
14</p>
15
16<div class="overflow-x-auto">
17	<table
18		class="table table-pin-rows table-pin-cols table-xs md:table-lg"
19	>
20		<thead>
21			<tr class="text-xl">
22				<th>Title</th>
23				<th>Tracks</th>
24			</tr>
25		</thead>
26		<tbody>
27			{#each albums as album}
28				<tr class:hover={'bg-base-200'}>
29					<td>
30						<a
31							href={`/album/${album.AlbumId}`}
32							class="link link-primary"
33						>
34							{album.AlbumTitle}
35						</a>
36					</td>
37					<td>{album.TrackCount}</td>
38				</tr>
39			{/each}
40		</tbody>
41	</table>
42</div>

Again adding a link this time to the album page, so, I can click on the album and get more information on that.

Next up, the genre by genre ID, so, in the src/lib/server/queries.ts another query to get the albums by passing in the genre ID:

1export const get_albums_by_genre = async (genre_id: number) => {
2	const albums = await client.execute({
3		sql: `SELECT 
4            a.AlbumId, 
5            a.Title AS AlbumTitle, 
6            g.GenreId,
7            g.Name AS GenreName,
8            at.ArtistId,
9            at.Name AS ArtistName
10          FROM 
11            albums a
12          JOIN 
13            tracks t ON a.AlbumId = t.AlbumId
14          JOIN 
15            genres g ON t.GenreId = g.GenreId
16          JOIN 
17              artists at ON a.ArtistId = at.ArtistId
18          WHERE 
19            g.GenreId = ?
20          GROUP BY 
21            a.AlbumId, a.Title, g.GenreId, g.Name, at.ArtistId, at.Name;`,
22		args: [genre_id],
23	});
24
25	return {
26		genre: albums.rows[0].GenreName,
27		albums: albums.rows,
28	};
29};

In the src/routes/genre/[genre_id]/+page.server.ts file, call the query and return the data:

1import { get_albums_by_genre } from '$lib/server/queries.js';
2
3export const load = async ({ params }) => {
4	const genre_id = parseInt(params.genre_id);
5
6	const { albums, genre } = await get_albums_by_genre(genre_id);
7
8	return {
9		albums,
10		genre,
11	};
12};

Then the markup for the src/routes/genre/[genre_id]/+page.svelte file to render out the data:

1<script lang="ts">
2	let { data } = $props();
3
4	const { albums, genre } = data;
5</script>
6
7<svelte:head>
8	<title>{genre} - Chinook SvelteKit</title>
9</svelte:head>
10
11<h1 class="mb-5 text-4xl font-bold text-primary">
12	<a href="/genre">
13		{genre}
14	</a>
15</h1>
16
17<div class="overflow-x-auto">
18	<table
19		class="table table-pin-rows table-pin-cols table-xs md:table-lg"
20	>
21		<thead>
22			<tr class="text-xl">
23				<th>Album</th>
24				<th>Artist</th>
25			</tr>
26		</thead>
27		<tbody>
28			{#each albums as album}
29				<tr class:hover={'bg-base-200'}>
30					<td>
31						<a
32							href={`/album/${album.AlbumId}`}
33							class="link link-primary"
34						>
35							{album.AlbumTitle}
36						</a>
37					</td>
38					<td>
39						<a
40							href={`/artist/${album.ArtistId}`}
41							class="link link-primary"
42						>
43							{album.ArtistName}
44						</a>
45					</td>
46				</tr>
47			{/each}
48		</tbody>
49	</table>
50</div>

More links to the album and artist pages, so, I can click on the album and get more information on that and the artist.

Also I’ll add in an index for the genre, so, in the src/lib/server/queries.ts file a simple query to get all the genres with no arguments passed to it:

1export const get_genres = async () => {
2	const genres = await client.execute(
3		`SELECT GenreId, Name AS GenreName FROM genres ORDER BY Name;`,
4	);
5
6	return {
7		genres: genres.rows,
8	};
9};

Then another server side route in the src/routes/genre/+page.server.ts to call the query and return the data:

1import { get_genres } from '$lib/server/queries.js';
2
3export const load = async () => {
4	const { genres } = await get_genres();
5
6	return {
7		genres,
8	};
9};

Then the markup for the src/routes/genre/+page.svelte file to render out the data:

1<script lang="ts">
2	let { data } = $props();
3
4	const { genres } = data;
5</script>
6
7<svelte:head>
8	<title>Genres - Chinook SvelteKit</title>
9</svelte:head>
10
11<ul class="list-disc pl-10 text-xl">
12	{#each genres as { GenreName, GenreId }}
13		<li>
14			<a href={`/genre/${GenreId}`} class="link link-primary">
15				{GenreName}
16			</a>
17		</li>
18	{/each}
19</ul>

Just a simple list of the genres that links back to the /genre/${GenreId} route.

Finally the track by track ID, so, in the src/lib/server/queries.ts file a query to get the track by ID:

1export const get_track_by_track_id = async (track_id: number) => {
2	const track = await client.execute({
3		sql: `SELECT 
4            t.TrackId, 
5            t.Name AS TrackName, 
6            a.AlbumId,
7            a.Title AS AlbumTitle, 
8            at.ArtistId,
9            at.Name AS ArtistName,
10            g.GenreId,
11            g.Name AS GenreName,
12            (t.Milliseconds / 60000) || 'm ' || ((t.Milliseconds % 60000) / 1000) || 's' AS Duration,
13            mt.Name AS MediaType,
14            t.UnitPrice AS Price
15          FROM 
16            tracks t
17          JOIN 
18            albums a ON t.AlbumId = a.AlbumId
19          JOIN 
20            artists at ON a.ArtistId = at.ArtistId
21          JOIN 
22            genres g ON t.GenreId = g.GenreId
23          JOIN 
24            media_types mt ON t.MediaTypeId = mt.MediaTypeId
25          WHERE 
26            t.TrackId = ?;`,
27		args: [track_id],
28	});
29
30	return {
31		artist: track.rows[0].ArtistName,
32		track: track.rows,
33		track_name: track.rows[0].TrackName,
34	};
35};

Then the server side route in the src/routes/track/[track_id]/+page.server.ts file to call the query and return the data:

1import { get_track_by_track_id } from '$lib/server/queries.js';
2
3export const load = async ({ params }) => {
4	const track_id = parseInt(params.track_id);
5
6	const { artist, track, track_name } =
7		await get_track_by_track_id(track_id);
8
9	return {
10		track_name,
11		artist,
12		track,
13	};
14};

Then the markup for the src/routes/track/[track_id]/+page.svelte file to render out the data:

1<script lang="ts">
2	let { data } = $props();
3
4	const { artist, track, track_name } = data;
5</script>
6
7<h1 class="mb-5 text-4xl font-bold text-primary">{track_name}</h1>
8<p class="mb-10 text-3xl font-bold tracking-widest text-secondary">
9	<a href={`/artist/${track[0].ArtistId}`}>
10		{artist}
11	</a>
12</p>
13
14<svelte:head>
15	<title>{track_name} - Chinook SvelteKit</title>
16</svelte:head>
17
18<div class="text-xl">
19	<p><strong>Album Title:</strong> {track[0].AlbumTitle}</p>
20	<p><strong>Genre Name:</strong> {track[0].GenreName}</p>
21	<p><strong>Duration:</strong> {track[0].Duration}</p>
22	<p><strong>Media Type:</strong> {track[0].MediaType}</p>
23	<p><strong>Price:</strong> {track[0].Price}</p>
24</div>

Deploying to Fly.io

Ok, I’ve got a nice little example project I can share with the world now!

I’ve already installed the Fly CLI from following the instructions from the Fly.io docs.

Fly makes getting a project set up straightforward with the fly launch command:

1fly launch

This configures the project, installs the @flydotio/dockerfile package and creates several files, ones to note are the fly.toml and the Dockerfile.

I’m asked set up options for the project and I leave most of them as default apart from the primary region. It defaults to lhr (as I’m in the UK) but I change it to iad as I’ve had issues in the past with lhr.

Add in the Turso secrets at the top of the Dockerfile:

1ARG BUN_VERSION=1.1.0
2FROM oven/bun:${BUN_VERSION}-slim as base
3
4# Declare build arguments for secrets
5ARG TURSO_DB_URL
6ARG TURSO_DB_AUTH_TOKEN
7
8LABEL fly_launch_runtime="Bun"

Then after the COPY commands add in the secrets for building the app:

1# Copy application code
2COPY --link . .
3
4# Build application using build arguments
5RUN TURSO_DB_URL=$TURSO_DB_URL TURSO_DB_AUTH_TOKEN=$TURSO_DB_AUTH_TOKEN bun run build
6
7# Remove development dependencies
8RUN rm -rf node_modules && \
9    bun install --ci

I’m now ready to deploy the app, I’ll need to pass the build arguments in the terminal, I’ve got into the habit now of exporting the secrets to my terminal now so I can just use the variables in the command:

1export TURSO_DB_URL=libsql://sveltekit-turso-flyio-app.turso.io
2export TURSO_DB_AUTH_TOKEN=the-generated-auth-token

Then I can reference the variables from the terminal, so in the terminal I do:

1echo $TURSO_DB_URL

I’ll get back the secret:

1the-generated-auth-token

So with the fly command I can use the variables for the deploy command:

1fly deploy --build-arg TURSO_DB_URL=$TURSO_DB_URL --build-arg TURSO_DB_AUTH_TOKEN=$TURSO_DB_AUTH_TOKEN

The CLI gives me links to the Fly.io dashboard to inspect the build!

Done! 🎉

I’m not a Docker or Fly.io expert, this is from my trail and error dicking around with multiple configurations and endless docs and community posts searching.

If this is wrong or can be done better please get in touch I’d love to make things right.

Bonus! You want that CRUD?

Well, I’m not going to go into that here, but, I’ll give you a head-start!

Essentially all the tools you need to do that are in the project they’re just a form action away!

I’m not going down that path though as, like I stated at the beginning if you’re going to let any random person on the internet create and delete data on the database then you’re going to need to do a bit more than the basics I have gone over here 😅

Things like authentication, possibly data scoped per user or a database per user with something like the multi tennant approach in the Creating a multitenant SaaS service with Turso, Remix, and Drizzle on the Turso blog!

Have fun!

There's a reactions leaderboard you can check out too.

Useful? Share it on Bluesky.

✨ View the stats for this post ✨

Sign up for the newsletter

Want to keep up to date with what I'm working on?

Join other developers and sign up for the newsletter.

I care about the protection of your data. Read the Privacy Policy for more info.