SvelteKit with SQLite: Uploading, Storing and Retrieving Images

If you're looking for a more visual and interactive way to learn about the topic of this post, check out my YouTube video on the same subject.

My setup

I use SvelteKit 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 combined SvelteKit with SQLite as a database, as it is lightweight and easy to maintain and fast. You can read more about SvelteKit + SQLite in my previous post. As a node SQLite driver, I use better-sqlite3 as I like its synchronous API.

In this tutorial, we will store the images inside the SQLite database itself. This is totally fine for small or medium-sized apps, but not recommended for large apps. For large apps, you can use specific file storage services. For example, Motimize is an Open-Source image server that can compress and resize images and can be self-hosted.

The app I am working with has music data like artists, tracks, and albums. We will do a file upload for the album title image. You can check out the whole process of building this app in my YouTube playlist.

Upload Image Form

To allow users to upload an image, we create a form with a file input. We set the form enctype attribute to multipart/form-data to allow the browser to send the image as a binary file. Per default, only the filename would be transferred to the server handler. We also set the accept attribute to image/* to only allow images to be uploaded.

We add a change handler to the file input and call the function handleImageUpload. This function will get the image file from the event and create a temporary URL for it in the variable uploadedImage. We can use this URL as src attribute value for an img element to show the image to the user after he selected it.

Additionally, we can disable the form submit button until the user selected an image.

+page.svelte

1<script lang="ts">
2  import type { PageData } from './$types';
3  export let data: PageData;
4
5  let uploadedImage: string;
6
7  function handleImageUpload(e: Event) {
8    const image = (e.target as HTMLInputElement)?.files?.[0];
9    if (!image) return;
10    // URL.createObjectURL() creates a temporary URL for the image we can use as src for an img tag
11    uploadedImage = URL.createObjectURL(image);
12  }
13</script>
14
15<form method="post" enctype="multipart/form-data">
16  <input type="hidden" name="albumId" value="{data.album.albumId}" />
17  <input
18    type="file"
19    name="albumImage"
20    accept="image/*"
21    on:change="{handleImageUpload}"
22  />
23
24  {#if uploadedImage}
25  <div class="mt-4">
26    <img src="{uploadedImage}" style="max-width: 50ch;" alt="" />
27  </div>
28  {/if}
29
30  <div class="mb-6 mt-4">
31    <button
32      class="button is-primary is-disabled"
33      type="submit"
34      formaction="?/updateAlbumImage"
35      disabled="{!uploadedImage"
36      ??
37      null}
38    >
39      Upload Image
40    </button>
41  </div>
42</form>
43

The result looks like this:

Store Image in SQLite Database

To process the form submit, we can create a handler in the +page.server.ts file. We can access the image from the formData object with the type File. This object stores metadata about the image like the filename, the mime type, the file size and the last modified date. We can also access the file contents as an ArrayBuffer.

+page.server.ts

1export const actions: Actions = {
2  updateAlbumImage: async ({ request, locals }) => {
3    const data = await request.formData();
4
5    const albumIdStr = data.get('albumId')?.toString();
6    const albumId = albumIdStr ? parseInt(albumIdStr) : null;
7
8    if (!albumId) {
9      throw error(400, 'AlbumId missing');
10    }
11
12    const albumImage = data.get('albumImage')?.valueOf() as File;
13
14    console.log(
15      albumImage?.name, // filename
16      albumImage?.type, // mime type
17      albumImage?.size, // file size in bytes
18      albumImage?.lastModified // last modified date
19      // albumImage?.arrayBuffer() // ArrayBuffer with the file contents
20    );
21
22    // TODO: save image to database function
23    mergeAlbumImage(albumId, albumImage);
24  },
25};
26

To store the image, we first need to create a table in the database. The file itself will be stored in a BLOB column, and we add columns for each metadata value. This results in the following SQL statement:

1create table album_images (
2    img_album_id      integer primary key
3  , img_name          text not null
4  , img_mime_type     text not null
5  , img_last_modified integer not null default (strftime( '%s', 'now' ) * 1000)
6  , img_size          integer not null
7  , img_data          blob not null
8  , constraint img_album_id_fk foreign key (img_album_id) references albums (AlbumId)
9) strict;
10

Now we can create the function that saves the image in our database. First we convert the image to a buffer with the Buffer.from() function as we can store it in the BLOB column in this form. The SQL statement either inserts a new row, or updates an existing one, depending on if there is already an image for the album. We do that by using the on conflict clause.

database.ts

1export async function mergeAlbumImage(albumId: number, image: File) {
2  const arrayBuffer = await image.arrayBuffer();
3  const buffer = Buffer.from(arrayBuffer);
4
5  const sql = `
6  insert into album_images (
7    img_album_id
8  , img_name
9  , img_mime_type
10  , img_last_modified
11  , img_size
12  , img_data
13  )
14  values (
15    $albumId
16  , $filename
17  , $mimeType
18  , $lastModified
19  , $size
20  , $data
21  )
22  on conflict (img_album_id) do
23  update
24     set img_name = excluded.img_name
25       , img_mime_type = excluded.img_mime_type
26       , img_last_modified = excluded.img_last_modified
27       , img_size = excluded.img_size
28       , img_data = excluded.img_data 
29   where img_album_id = excluded.img_album_id
30  `;
31  const stmnt = db.prepare(sql);
32  stmnt.run({
33    albumId,
34    filename: image.name,
35    mimeType: image.type,
36    lastModified: image.lastModified,
37    size: image.size,
38    data: buffer,
39  });
40}
41

Retrieve and Display Image

To retrieve the image from the database, we create a new API endpoint. Aside from the albumId we pass the filename as a parameter. Theoretically, we can serve the image under any name, as we can find only with the albumId. I believe we should only serve it under its original name, so we will check that later in the database function.

It is important to set response headers so that the browser can show the image correctly. We set the Content-Type header to the mime type of the image, Content-Length to the file size, and Last-Modified to our saved last change date. In the end, we return the image as a Blob object.

src/routes/api/album/[albumId]/image/[imageName]/+server.ts

1import { getAlbumImage } from '$lib/server/db';
2import { error } from '@sveltejs/kit';
3import type { RequestHandler } from './$types';
4
5export const GET = (async ({ params, setHeaders }) => {
6  if (!params.albumId) {
7    throw error(404, {
8      message: 'Album not found',
9    });
10  }
11
12  const albumId = parseInt(params.albumId);
13  console.log('albumId', albumId);
14  if (!albumId) {
15    throw error(404, {
16      message: 'Album not found',
17    });
18  }
19
20  // TODO: greate getAlbumImage function
21  const img = getAlbumImage(albumId, params.imageName);
22
23  if (!img || !img.data) {
24    throw error(404, 'Image not found');
25  }
26
27  // set response headers for image
28  setHeaders({
29    'Content-Type': img.mimeType,
30    'Content-Length': img.size.toString(),
31    'Last-Modified': new Date(img.lastModified).toUTCString(),
32    'Cache-Control': 'public, max-age=600',
33  });
34
35  return new Response(img.data);
36}) satisfies RequestHandler;
37

Now we need to create the getAlbumImage function in the database.ts file. The query is basic as we just select all columns from the database. We just make sure the requested filename is the right one, so we only serve it under its original name. Afterward, we convert the image data to a Blob object that we can return as the response.