SvelteKit with Prisma and SQLite

Whether you're building a chat application, a blog, or a complex CRM, you'll likely have to use a database at some point. In JavaScript land, one of the most common ways to connect and interact with a database is through an ORM.

And  Prisma  is one of the most popular ORMs in this ecosystem.

This is a tutorial with a step-by-step guide on how to connect a SvelteKit application to a SQLite database using Prisma.

If you like to use Drizzle ORM instead of Prisma, checkout SvelteKit with SQLite and Drizzle .

What is Prisma, and How Does It Work?

Prisma  is a modern ORM (Object-Relational Mapping) tool for TypeScript and Node.js. It simplifies database access and management by providing a type-safe query builder and migration system.

Prisma is essentially composed of these three main components:

1. Prisma Client : An auto-generated, type-safe query builder that allows you to interact with your database. It provides a fluent API for performing CRUD (Create, Read, Update, Delete) operations and complex queries.

2. Prisma Migrate : A migration tool that helps you evolve your database schema over time. It allows you to create, apply, and manage database migrations in a straightforward manner.

3. Prisma Studio : A web-based GUI for managing your database. It provides a visual interface to view and edit your data, making it easier to work with your database during development.

With Prisma, you usually start by defining your data model in a  schema.prisma  file, and then generating the corresponding Prisma Client, and finally running and applying migrations.

Setting up SvelteKit

For this tutorial, we are going to build a simple blog application using SvelteKit. The concepts for this are the same as in the  SvelteKit with SQLite and Prisma  article, so feel free to look at that if needed.

Let’s set up a new SvelteKit project, with TypeScript. We will also add Tailwind CSS for styling and use  Shadcn Svelte  as our component library.

pnpx create-svelte@latest sveltekit-prisma-sqlite

pnpx @svelte-add/tailwindcss@latest --typography false
pnpm dlx shadcn-svelte@latest init
pnpm dlx shadcn-svelte@latest add button input textarea label

Adding Prisma to your SvelteKit project

Let’s install by installing the Prisma CLI package in the project:

pnpm install prisma -D

We can now initialize Prisma in our project by running the following command:

pnpm prisma init --datasource-provider sqlite

This command will do the following:

• Create a  prisma  folder in the root of the project

• Create a  schema.prisma  file inside the  prisma  folder

• Create or update the  .env  file with the path to the database file

At this point, our Prisma schema looks like this:

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "sqlite"
  url      = env("DATABASE_URL")
}

Let’s update it with our blog posts model:

model Post {
  id        String   @id @default(cuid())
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  slug    String  @unique
  title   String
  content String?
}

Now, we can run the  pnpm prisma generate  command. This will first install the  @prisma/client  package in our project, and then generate the Prisma client for us.

Let’s use this client to create our config file for Prisma:

// src/lib/server/prisma.ts

import { PrismaClient } from "@prisma/client";
const prisma = new PrismaClient();

export { prisma };

Later one, we will use this client to query the database.

Push and migrations

Prisma provides a couple of options for applying schema changes to a database.

The first one is  prisma db push, which will directly apply changes in the schema file to the database. It’s very useful during  development / prototyping, when your schema is not yet finalized, and you would rather not generate and run migrations for every change.

The next option is  prisma migrate . This command will generate a migration file for the changes in the schema file, and then apply the migration to the database.

In development, you’d likely use  prisma migrate dev  and  prisma migrate reset  (to erase all data, reset the database, and re-apply all migrations). In production, you’d use  prisma migrate deploy  to apply all pending (not yet applied) migrations to the database.

Let’s use the  prisma migrate dev  command to create a migration file for our blog posts model:

pnpm prisma migrate dev --name initial_migration

Once that’s done, Prisma will generate a migration file in the  migrations  folder (inside  ./prisma ), and immediately apply it to the database.

In addition to that, Prisma will also create a new  _prisma_migrations  table in the database, which will keep track of all migrations that have been applied to the database.

The generated migration file looks like this:

-- CreateTable
-- CreateTable
CREATE TABLE "Post" (
    "id" TEXT NOT NULL PRIMARY KEY,
    "createdAt" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updatedAt" DATETIME NOT NULL,
    "slug" TEXT NOT NULL,
    "title" TEXT NOT NULL,
    "content" TEXT
);

-- CreateIndex
CREATE UNIQUE INDEX "Post_slug_key" ON "Post"("slug");

CRUD operations

At this point, we are ready to start using Prisma to perform CRUD operations on our blog posts. Let’s create a few functions to handle CRUD operations:

// src/lib/server/post.ts

import type { Prisma } from "@prisma/client";
import { prisma } from "./prisma";

const createNewPost = async (post: Prisma.PostCreateInput) => {
  return await prisma.post.create({
    data: post,
  });
};

const getPostBySlug = async (slug: string) => {
  return await prisma.post.findUnique({
    where: {
      slug,
    },
  });
};

const getAllPosts = async () => {
  return await prisma.post.findMany({
    orderBy: {
      createdAt: "desc",
    },
  });
};

export { createNewPost, getPostBySlug, getAllPosts };

As you can see, the Prisma query builder is quite intuitive and straightforward. It can also handle complex queries, such as filtering, sorting, pagination, relations, and more. Take a look at  the documentation  for more.

Blog UI

Let’s now create the UI for our blog.

// src/routes/blog/+page.server.ts

import { getAllPosts } from "$lib/server/post";

export const load = async () => {
  const posts = await getAllPosts();

  return {
    posts,
  };
};

<!-- src/routes/blog/+page.svelte -->

<script lang="ts">
  export let data;
</script>

<svelte:head>
  <title>Blog</title>
</svelte:head>

<div class="max-w-5xl mx-auto px-4 py-16 space-y-8">
  <div class="space-y-4">
    <h1 class="text-3xl font-bold">Blog</h1>

    {#if data.posts && data.posts.length}
      <div class="space-y-2">
        {#each data.posts as post (post.id)}
          <h2 class="text-2xl font-bold">
            <a href="/blog/{post.slug}">{post.title}</a>
          </h2>
        {/each}
      </div>
    {/if}
  </div>
</div>

Here, we are just returning all the blog posts from the database in the  load  function, then displaying them in the UI.

Let’s add a form to create a new blog post:

<!-- src/routes/blog/+page.svelte -->

<form class="space-y-4" method="post" use:enhance>
  <div class="space-y-2">
    <Label for="title">Title</Label>
    <Input
      name="title"
      id="title"
      type="text"
      placeholder="Title"
      required
    />
  </div>

  <div class="space-y-2">
    <Label for="slug">Slug</Label>
    <Input name="slug" id="slug" type="text" placeholder="Slug" required />
  </div>

  <div class="space-y-2">
    <Label for="content">Content</Label>
    <Textarea name="content" id="content" placeholder="Content" rows={10} />
  </div>

  <Button type="submit">Create New Post</Button>
</form>

The form has the title and the slug fields are required, and the content field is optional.

We can now create the form action that’s associated with that form:

// src/routes/blog/+page.server.ts

import { createNewPost } from "$lib/server/post";
import { fail, redirect } from "@sveltejs/kit";

export const actions = {
  default: async ({ request }) => {
    const formData = Object.fromEntries(await request.formData());
    const { title, slug, content } = formData as {
      title: string | undefined;
      slug: string | undefined;
      content: string | undefined;
    };

    if (!title || !slug || slug.trim().length === 0) {
      return fail(400, { message: "Missing required fields" });
    }

    const newPost = await createNewPost({
      title,
      slug,
      content,
    });

    redirect(302, `/blog/${newPost.slug}`);
  },
};

Here, we are getting the data from the form action, and then we do some basic validation to make sure that the title and slug fields are not empty. If they are, we return a 400 error with a message.

We can take this validation a bit further, by providing some feedback on the frontend for the user. I’m not going to expand a lot on this here, but check out “ Frontend feedback for errors ” for more details on how to do that.

Notice that we are also redirecting the user to the new post page after they submit the form. For now, this page doesn’t exist yet, so let’s create it:

// src/routes/blog/[slug]/+page.server.ts

import { getPostBySlug } from "$lib/server/post";
import { error } from "@sveltejs/kit";

export const load = async ({ params }) => {
  const { slug } = params;
  const post = await getPostBySlug(slug);

  if (!post) {
    error(404, "Post not found");
  }

  return {
    post,
  };
};

SvelteKit dynamic routes feature allows us here to get the slug from the URL. We can then use this slug to fetch the post from the database and return it to the UI.

The single post page UI will look like this:

<!-- src/routes/blog/[slug]/+page.svelte -->

<script lang="ts">
  export let data;
</script>

<svelte:head>
  <title>{data.post.title}</title>
</svelte:head>

<div class="max-w-5xl mx-auto px-4 py-16 space-y-8">
  <div class="space-y-4">
    <h1 class="text-3xl font-bold">{data.post.title}</h1>

    <div class="space-y-2">
      {@html data.post.content}
    </div>
  </div>
</div>

Here is a quick video demo of the blog app in action:

Wrapping up

Connecting a SvelteKit application to a SQLite database using Prisma streamlines your database interactions, providing type safety and an intuitive API.

With Prisma's powerful migration tools, you can easily manage your database schema over time. Following this guide, you can set up your SvelteKit app, define your data models, and implement CRUD operations efficiently.

Whether you're building a blog, a chat app, or a CRM, Prisma and SvelteKit offer a robust and scalable solution.

For more detailed guides and additional features, take a look at the official Prisma documentation .

If you’re interested in a comprehensive course that teaches how to build and deploy full-stack web applications using SvelteKit , take a look at my upcoming course at fullstacksveltekit.com.