Jan 29, 2025

Deploy a SvelteKit App with SQLite & Drizzle to Fly.io

This post covers how to create a new SvelteKit app that uses SQLite and Drizzle and deploy that app to Fly.io.

Note that this does not cover using LiteFS - Distributed SQLite Ā· Fly Docs to replicate your SQLite database in multiple locations. Rather, this post covers the simplest implementation- a single volume on which your SQLite database file is stored and a SvelteKit app that is attached to it and that uses better-sqlite3 and Drizzle to read and write to/from the database.

Steps

1. Create app

Per the Svelte CLI docs, use the npx sv create command to scaffold out a new SvelteKit site.

When asked which integrations you want, check the box for Drizzle.

When asked which SQLite client you want to use, check the box for better-sqlite3. Since Fly.io is a ā€œserverfulā€ environment that runs Node.js, this is the appropriate package to choose.

This will accomplish the following things:

  • Install the NPM packages needed for Drizzle and better-sqlite3.
  • Add a DATABASE_URL=local.db environment variable to your .env file to specify where the SQLite database is located on disk.
  • Create a src/lib/server/db/index.ts file where a better-sqlite3 instance is created and a Drizzle client is exported for your app to use.
  • Create a src/lib/server/db/schema.ts file where you can define your database schema.

Update src/lib/server/db/schema.ts if youā€™d like to specify your database schema.

Run npm run db:push to use the database schema to generate a new database named local.db in the root of your project.

2. Configure your app to run on Fly.io

Update src/lib/server/db/index.ts to this:

import { drizzle } from 'drizzle-orm/better-sqlite3';
import Database from 'better-sqlite3';
import { building } from '$app/environment';
import { env } from '$env/dynamic/private';
import * as schema from './schema';

export let db: ReturnType<typeof drizzle>;

if (!building) {
	const client = new Database(env.DATABASE_URL);
	db = drizzle(client, { schema });
}

Hereā€™s why:

The code that the sv create command initially creates in src/lib/server/db/index.ts calls new Database(env.DATABASE_URL) as soon as the file is loaded and tries to connect to the database. Since Fly Machines do not have access to Volumes at buildtime however, leaving this code in place will result in a build error since better-sqlite3 will be unable to access the database on the volume. This new code confirms that a build is not in progress before attempting to connect to the database, which results in successful builds on Fly.

Also, passing in { schema } to drizzle() results in better TypeScript type inference and query validation.

Follow the Run a SvelteKit App page of the Fly.io docs to configure your SvelteKit app to run on their platform. By the end of that guide, you should have your app deployed to Fly.

A data volume should have also been automatically created and attached to your app. You can run fly volumes list to see a list of volumes and which virtual machine they are attached to. If you donā€™t see a data volume, you can create one manually by following Flyā€™s Add volumes to an existing app guide.

Remove the ENV DATABASE_URL="file:///data/sqlite.db" line from your Dockerfile, if present, since we donā€™t need a DATABASE_URL environment variable to be defined at buildtime.

3. Set DATABASE_URL environment variable

Run fly secrets set DATABASE_URL=/data/production.db to set a DATABASE_URL environment variable that specifies the location of your production database. See the secrets docs for more info.

With this in place, better-sqlite3 will be able to access the database at env.DATABASE_URL.

4. Create production database

At this point, we have created a Volume that is mounted to our appā€™s filesystem at /data and set a DATABASE_URL environment variable to /data/production.db to tell better-sqlite3 to look for the database at that location. No such file exist at this point, though, so letā€™s create it.

We need to be able to run Drizzleā€™s push command from inside of the Fly.io VM to create the database. Trying to do so at this point will fail though, since the drizzle-kit and @types/better-sqlite3 NPM packages are dev dependencies and get deleted during the build process.

Hereā€™s the approach I used for being able to run this one-time command on production:

Uninstall the drizzle-kit and @types/better-sqlite3 dev dependencies, then re-install them as regular, non-dev dependencies: npm uninstall drizzle-kit @types/better-sqlite3 && npm install drizzle-kit @types/better-sqlite3

Add the commands below to your Dockerfile after the FROM base statement. The RUN command creates a /app/src/lib/server/db directory on the VM if one doesnā€™t already exist. The COPY commands copies a few files into that directory that are needed for Drizzleā€™s push command: the drizzle.config.ts and schema.ts files.

# Create necessary directories
RUN mkdir -p /app/src/lib/server/db

COPY --from=build /app/drizzle.config.ts /app/drizzle.config.ts
COPY --from=build /app/src/lib/server/db/schema.ts /app/src/lib/server/db/schema.ts

Run fly deploy to re- deploy your app.

Run fly status and ensure that your VM has a STATE of started. If it doesnā€™t, copy the machineā€™s ID and then run fly machine start <machine-id> to start it.

Run fly ssh console to SSH into your Fly machine.

From inside of the /app directory, run DATABASE_URL=/data/production.db npm run db:push to create the new SQLite database file.

After the database is created, you can undo the changes you made above:

  • Run npm uninstall drizzle-kit @types/better-sqlite3 && npm install -D drizzle-kit @types/better-sqlite3 to re-install the drizzle-kit and @types/better-sqlite3 NPM packages as dev dependencies.
  • Delete the lines you added to your Dockerfile and save your changes.
  • Run fly deploy again to re-deploy your app.

As an alternative approach, you could try to copy the database file from your local machine and save it as /data/production.db in your Fly Volume with steps like this:

  • Run fly status and ensure that your VM has a STATE of started. If it doesnā€™t, copy the machineā€™s ID and then run fly machine start <machine-id> to start it.
  • Run fly ssh sftp shell to SSH into your Fly machine.
  • Run put local.db /data/production.db to copy your local database to production.
  • Run ls -l /data to confirm the file was uploaded successfully.

Your app should now be able to access the database.

5. Write queries

Add queries to your app to read and write data to/from the database.

You can implement code similar to whatā€™s shown below to test your app if youā€™d likeā€”just adjust it so that the database table and field names match some of those in your schema.

Put the code below in a src/routes/api/add-user/+server.ts file to create a new API endpoint in your Svelte app that writes a new user to the database.

import type { RequestHandler } from './$types';
import { db } from '$lib/server/db';
import { user } from '$lib/server/db/schema';

export const GET: RequestHandler = async () => {
	try {
		await db.insert(user).values({
			id: 1,
			age: 39
		});
	} catch {
		return new Response('Could not add user 1');
	}

	return new Response('User added');
};

Put this code in a src/routes/get-user/+page.server.ts file to fetch user data from the database and send it to the get-user page component as props.

import { eq } from 'drizzle-orm';
import type { PageServerLoad } from './$types';
import { db } from '$lib/server/db';
import { user } from '$lib/server/db/schema';

export const load: PageServerLoad = async () => {
	const userResponse = db
		.select({
			id: user.id,
			age: user.age
		})
		.from(user)
		.where(eq(user.id, 1))
		.get();

	return {
		user: userResponse
	};
};

Put this code in a src/routes/get-user/+page.svelte file to render the user data to the /get-user page.

<script lang="ts">
	import type { PageProps } from './$types';

	let { data }: PageProps = $props();
</script>

<h1>User Details:</h1>

{#if data.user}
	<pre>{JSON.stringify(data.user, null, 2)}</pre>
{:else}
	<p>No user found.</p>
{/if}

If you add code like this to your app and deploy it with fly deploy, you should then be able to visit the /api/add-user in a browser to add a new row to your production database, then visit /get-user to see that userā€™s data rendered to the page. If everything works as expected, that proves that database reads and writes are working as expected.

Other resources

You can copy the production database to your local machine using flyā€™s sftp command:

flyctl ssh sftp get /data/production.db ./local.db

I found this blog post helpful when I was getting this set up: Using SQLite for a Django application on fly.io