This is the second in a two-part series of posts demonstrating how to use Prisma to access and manage data on SvelteKit. You can read the first post here.
Update November 2022: As Heroku has eliminated their free tier, the demo app is now being hosted at Railway. Procfiles are supported out of the box, so there shouldn’t need to be any code changes. Migrating the app was as easy as deploying it, setting environment variables, and adding a Postgres service.
Welcome back! In my last post, I went over setting up a development environment for running the SvelteKit demo app with Prisma ORM. If you would like to follow along and want to set the project up as well, or need a refresher on what SvelteKit and/or Prisma is, be sure to check out the first part.
In my first post, we covered the setup process. Here, we’ll build on that with:
- Preparation
- Overview of the Todo app mechanics
- Working with Prisma
- Defining data models
- Creating database tables
- Setting up the Prisma client
- Updating database models the Prisma way
- Wrapping Up
- Deploying our app to production
Let’s dive into the mechanics of the demo app!
Preparation
Overview Of The Todo App Mechanics
First things first, we need to map out how the database tables should be structured. In order to do that, we need to familiarize ourselves with the application itself. Running the application and playing around with the Todo page, we can observe the following functionality:
- Add new Todos to a list
- View Todos
- Edit Todos
- Mark Todos as being “done”
- Delete Todos
Note: The following information is based on a Svelte Demo App built with create-svelte version 2.0.0-next.123. Projects built with a different version may have a different file structure. If you want to access the version of the demo app used in this blog, pull it down from this GitHub repo.
Looking at the code for a moment, our base within the app will be /src/routes/todos. In this directory are three files:
1. index.svelte
This file contains the template markup, CSS, and some scripting. Form elements are set up with their respective actions and methods, and are also decorated with a use:enhance action, that allows you to progressively enhance a <form> that works without JS.
The most interesting thing for us is the form action and method attributes, that call the /todos.js endpoints. The method will always be post, but the action will include a more specific ?_method= query param if it differs from the method (i.e. ?_method=PATCH and ?_method=DELETE).
These represent the calls to our routing endpoints, which live in the file index.js. Endpoint files export corresponding HTTP methods, and allow pages to read/write data from a server.
2. index.js
This endpoint file contains all of our exported functions for our API calls (get(), post(), patch() and delete()). These functions correspond to calls made to the API endpoint, defined in _api.js.
3. _api.js
The common theme in all of our endpoint functions is the api() function call, which is exported from _api.js, and is used as a utility function to fetch() data from api.svelte.dev. The leading underscore indicates that this is a private module, and any attempts to directly access it (i.e. /todos/_api/) will return a 404. The other great thing about a private module is that it won’t be exposed to the client. If you run the demo app, then look at your DevTools’ sources tab, you will see your endpoint files, but you won’t see the _api.js file.
Also notice that you won’t see the endpoint files, either.
Working with Prisma
Defining Data Models
Since _api.js is only used to issue fetch() requests, our plan is to delete this file and update index.js with our Prisma requests. But, before we do that, we need to set up our database schema. The endpoint file already has functions for each of our CRUD operations and is already set up to properly format the responses. The only thing we need to do is to replace api() calls with Prisma’s client functions. First things first–we need to define our data models.
Creating the data models
Since api() is a generic function, we’ll want to look at our index.js endpoint file to determine the data we need for each of our CRUD operations.
GET
The get() function fetches all records for a given userid (generated when the user first visits the page and stored in a cookie). Given this information, we should have a table called User. User will need to contain two columns: One to hold the userid, and a one-to-many relation to all of this user’s Todos.
Note that while the functions get called with a locals object, some of the form actions will reference todo.uid. Since this is our todo object, we will name the id field in our User model uid so it corresponds with the correct todo data.
In prisma/schema.prisma, define the User model by adding the following to the end of the file:
model User {
uid String @id @unique
todos todo[]
}
uid is a scalar field, defined as a String that serves as the @id that is @unique. We do not set any @default() or @default(autoIncrement()) values since the userId is generated and set in a cookie by our Svelte app (see the handle() method within /src/hooks.js).
The Todo[] is Prisma’s way of indicating a one-to-many relationship to a Todo model, which we’ll start to set up next.
POST
The post() function will accept a request object that contains FormData. The only property we set at this point is text, which was pulled from request.formData().
In prisma/schema.prisma, add the following above where you added the User model:
model Todo {
uid Int @id @default(autoincrement())
text String?
}
This starts our Todo model out with its own uid field that we indicate is an @id field with a @default(autoincrement()) value. We will let the database handle id generation since we aren’t specifying that in our api() call.
We also add a text field with a type of String?. The question mark at the end makes this value optional. We could remove that question mark, but that would mark the field as NOT NULL within the database, requiring the Todo to always have a text string. This would involve adding some additional checks to the app to ensure that a user isn’t creating a blank Todo, or creating a record then removing any text in an update. So, for now, we’ll make text optional.
One thing we are missing from our Todo model is a way to map a Todo record back to a specific user. Update the Todo model to include a user field:
model Todo {
uid Int @id @default(autoincrement())
text String?
user User @relation(fields: [userId], references: [uid])
userId String
}
Looks a bit wonky, but this sets up a user field as a @relation to the User model (set as the type). We’ve also defined a userId field as a String that will hold the userId of the Todo that will map to the uid in the User table. This is handled by the @relation(). fields is the field in the Todo table we want to match to the reference field in the User table.
Note: There is one more data field that we need to add to the Todo model, but we’re purposefully ignoring that for now. We’ll circle back to this in a bit.
PATCH and DELETE
The PATCH and DELETE requests will modify the data we set up already. Since we have enough information to generate our database models, we can move on to the next step of setting up the tables in our database and start creating some records.
Your saved /prisma/schema.prisma file should look like this:
// This is your Prisma schema file,
// learn more about it in the docs: https://pris.ly/d/prisma-schema
generator client {
provider = "prisma-client-js"
}
datasource db {
provider = "postgresql"
url = env("DATABASE_URL")
}
model Todo {
uid Int @id @default(autoincrement())
text String?
user User @relation(fields: [userId], references: [uid])
userId String
}
model User {
uid String @id @unique
todos Todo[]
}
Creating Database Tables
Now that we’ve defined our data models, we need to make sure our database is set up to serve this data. In your terminal, stop your server if it’s running, ensure Docker is running, and run npx prisma migrate dev --name init:
% npx prisma migrate dev --name init
Environment variables loaded from .env
Prisma schema loaded from prisma/schema.prisma
Datasource "db": PostgreSQL database "sveltekit_demo_dev", schema "public" at "localhost:5432"
Applying migration `20220205043104_init`
The following migration(s) have been created and applied from new schema changes:
migrations/
└─ 20220304170447_init/
└─ migration.sql
Your database is now in sync with your schema.
Running generate... (Use --skip-generate to skip the generators)
added 2 packages, and audited 162 packages in 5s
20 packages are looking for funding
run `npm fund` for details
found 0 vulnerabilities
✔ Generated Prisma Client (3.10.0 | library) to ./node_modules/@prisma/client in 57ms
If you see Error: P1001: Can't reach database server at 'localhost':'5432', then your Docker container isn’t running. Start it up, and rerun the npx command.
Looking at our log output, we initialized our database with an initial database migration (found in /prisma/migrations/) and ran the generated commands against our database. We can use the included Prisma Studio or another database client to see our tables within the database.
To use Prisma Studio, run npx prisma studio in your terminal at the root of your project. It will start the Prisma Studio server and autolaunch the site in your browser.
You can click on a model name in the list and it will open up that table to show you the data. You can manipulate data here like you would with your database tools, but without the need to install any additional tools. It may be a bit limited, but if all you really need to do is look at records, modify a record field or two, or manually add a new record, then this should be enough to get you started.
Setting Up The Prisma Client
As mentioned before, we are going to limit all of our changes to just the index.js file. Since this is a simple app with only one endpoint, our specific database calls can reside in this file. Other scenarios may warrant further abstraction, but we’ll keep it simple for now.
Before we start, we need to import the PrismaClient and set a new reference to it. Replace the import { api } from './api'; on line 1 with the following:
- import { api } from './_api';
+ import { PrismaClient } from '@prisma/client';
+
+ const prisma = new PrismaClient();
export const get = async ({ locals }) => {...});
Note: If we were integrating Prisma into more than one file, we could move those lines to a db.js file and add export default prisma; to the end. Then in each file where we need the Prisma Client, we would just import prisma from './db' (the file path will depend on where you created the file).
Updating GET
What we want to do is simply fetch all Todo records for a given User. Since we have a todos relation on our User model, we can query the User model for a given uid and return all todos associated with that user. Equating this to SQL, we would need to make a SELECT query. Prisma calls this a Select field.
Update the get() function to start adding some logic to fetch todo records for a given user and return it:
export const get = async ({ locals }) => {
// locals.userid comes from src/hooks.js
const response = await prisma.user.findUnique({
include: {
todos: true
},
where: {
uid: locals.userid
}
});
if (!response) {
// user hasn't created a todo list.
// create user entry in the User table
await prisma.user.create({
data: {
uid: locals.userid
}
});
// start with an empty array
return {
body: {
todos: []
}
};
}
return {
body: {
todos: await response.todos
}
};
};
The include here is important, as .findUnique() will only return the user record we are looking for without any relations. Adding the include section will also return this data to us.
One thing to note is that Prisma returns plain old JavaScript objects (POJOs). If there is nothing to return, then null will come back. So we add some logic to handle the case where the user does not have a corresponding user record in our database via the model’s user.create() method, and return an empty array. If we do have a user record, then we use the response.todos property as the response body.
If you wanted to check if things were working, you would add a console.log('User: ', response); before our if (!response) {...} block in the get() function, then start the server and visit the Todo page. You would see a user record has been created:
12:16:14 AM [vite] page reload src/routes/todos/index.js
User: { uid: '094b3c29-326a-4c94-ad59-edf78b757215', todos: [] }
We have a todos property on the response as a userId cookie was set when we first viewed the Todo app. If you clear the userId cookie out of your browser and reload the page, you won’t see anything initially, but should if you reload the page again:
12:16:14 AM [vite] page reload src/routes/todos/index.js
User: { uid: '094b3c29-326a-4c94-ad59-edf78b757215', todos: [] }
User: null
User: { uid: 'f72c7e41-4a75-49ea-b7c0-bd0cb9189d91', todos: [] }
Updating POST
Now that we have a user record to relate Todos for, let’s add the ability to create those records! Creating a Todo is a lot like creating a new user record–use the model’s .create() method. Update the post() function to create a new todo based on the data object passed into the api() call. In our case, all we need to provide is text and userId:
export const post = async ({ request, locals }) => {
const form = await request.formData();
await prisma.todo.create({
data: {
text: form.get('text'),
userId: locals.userid
}
});
return {};
};
One question you may have is why are we returning an empty object? The original demo app code was already doing this, so, as they say, “If it isn’t broken, don’t fix it!” In the POST form action in src/routes/todos/index.svelte, you’ll see the input form being “enhanced”, so returning an empty object as a response simply triggers the form.reset().
use:enhance={{
result: async ({ form }) => {
form.reset();
}
}}
If you still have the console.log on the GET case, once you add a record to the database you should see it included in the response once you refresh the page after adding a Todo:
12:31:19 AM [vite] page reload src/routes/todos/index.js
User: { uid: 'f72c7e41-4a75-49ea-b7c0-bd0cb9189d91', todos: [] }
User: {
uid: 'f72c7e41-4a75-49ea-b7c0-bd0cb9189d91',
todos: [
{
uid: 1,
text: 'Write a blog post',
userId: 'f72c7e41-4a75-49ea-b7c0-bd0cb9189d91'
}
]
}
Updating PATCH
Let’s go ahead and update our patch() function:
export const patch = async ({ request }) => {
const form = await request.formData();
await prisma.todo.update({
data: {
text: form.has('text') ? form.get('text') : undefined
},
where: {
uid: parseInt(form.get('uid'))
}
});
return redirect;
};
We see that our data model has an .update() method that we can use to edit existing records. Since we only want to update a specific record, we add the where object and indicate that we want to match the uid of the todo record, since that is passed in as part of our event object. The caveat to this is that the uid is passed in as a string, and the database expects this to be an integer, which is why we need to wrap the value in a parseInt().
One big difference between the original code is that we’re not using locals.userid. Each Todo uid is expected to be unique, so we should be confident that we’re not updating the wrong data. Besides, attempting to add userId in as a part of the where clause will cause a TypeScript error:
Type '{ uid: number; userId: string; }' is not assignable to type 'TodoWhereUniqueInput'.
Object literal may only specify known properties, and 'userId' does not exist in type 'TodoWhereUniqueInput'.ts(2322)
Note: Even though we’re not setting our project up with TypeScript, Prisma uses it, which explains why we’re seeing a TypeScript error.
Now we’re getting some use out of the console.log on our GET case: If we modify the Todo we created in the last step and reload the page, we’ll see the changes applied.
12:41:14 AM [vite] page reload src/routes/todos/index.js (x2)
User: {
uid: 'f72c7e41-4a75-49ea-b7c0-bd0cb9189d91',
todos: [
{
uid: 1,
text: 'Write a blog post',
userId: 'f72c7e41-4a75-49ea-b7c0-bd0cb9189d91'
}
]
}
User: {
uid: 'f72c7e41-4a75-49ea-b7c0-bd0cb9189d91',
todos: [
{
uid: 1,
text: 'Write a blog post today',
userId: 'f72c7e41-4a75-49ea-b7c0-bd0cb9189d91'
}
]
}
Updating DELETE
Last stop on the CRUD train is deleting records. As you can expect, our data model also has a .delete() method that we can use. Include the where directive to specify the record to delete, and we should be in business:
export const del = async ({ request }) => {
const form = await request.formData();
await prisma.todo.delete({
where: {
uid: parseInt(form.get('uid'))
}
});
return redirect;
};
The console log should show the effects of the delete after we delete our record and the app refreshes the page:
12:53:13 AM [vite] page reload src/routes/todos/index.js (x3)
User: {
uid: 'f72c7e41-4a75-49ea-b7c0-bd0cb9189d91',
todos: [
{
uid: 1,
text: 'Write a blog post today',
userId: 'f72c7e41-4a75-49ea-b7c0-bd0cb9189d91'
}
]
}
User: { uid: 'f72c7e41-4a75-49ea-b7c0-bd0cb9189d91', todos: [] }
Updating Database Models The Prisma Way
If you recall way back when we first defined our Todo model, there was one thing we were leaving out for later. Well, the time has come to find out what that was all about.
Using the Todo app, go ahead and add a Todo. Once it’s added to your list, you’ll notice a circle to the left of your Todo. Click it, and it looks like the Todo is marked as being done, but refresh your page and the Todo displays as being active. Oh no, we never added the done flag to our Todo model!
Not to worry, Prisma Migrate to the rescue! One of the benefits of Prisma is creating and managing database migrations. If you’ve ever worked with Django, then this works a lot like South.
Across the life of a project, database tables will mutate and grow, and migrations help to keep things up to date. If someone hasn’t touched the project in a while, or needs to spin up a fresh instance, then Prisma will ensure that any unrun migrations will be applied when starting the project.
The process to create a new migration is straightforward:
-
Update
/prisma/schema.prismawith your desired model changes -
Run
npx prisma migrate dev --name <migration name>where<migration name>is a snake cased migration name.
Your migration name should be something that relates to the change you are making, so when looking at the migration files you can easily know what a file does without having to open it.
So, for our app, update the Todo model to include a done field set as a Boolean with a @default(false) value:
model Todo {
uid Int @id @default(autoincrement())
text String?
user User @relation(fields: [userId], references: [uid])
userId String
done Boolean @default(false)
}
Save that file and run npx prisma migrate dev --name added_todo_done_field:
% npx prisma migrate dev --name added_todo_done_field
Environment variables loaded from .env
Prisma schema loaded from prisma/schema.prisma
Datasource "db": PostgreSQL database "sveltekit_demo_dev", schema "public" at "localhost:5432"
Applying migration `20220304182054_added_todo_done_field`
The following migration(s) have been created and applied from new schema changes:
migrations/
└─ 20220304182054_added_todo_done_field/
└─ migration.sql
Your database is now in sync with your schema.
✔ Generated Prisma Client (3.10.0 | library) to ./node_modules/@prisma/client in 33ms
We also need to update our Prisma call in our patch() function to include our done property:
export const patch = async ({ request }) => {
....
await prisma.todo.update({
data: {
- text: form.has('text') ? form.get('text') : undefined
+ text: form.has('text') ? form.get('text') : undefined,
+ done: form.has('done') ? !!form.get('done') : undefined
},
where: {
uid: parseInt(form.get('uid'))
}
});
....
};
Now, if you re-run the app, check your existing Todo as being “done”, and refresh the page, the “done” flag should persist.
User: {
uid: 'f72c7e41-4a75-49ea-b7c0-bd0cb9189d91',
Todos: [
{
uid: 2,
text: 'Another todo',
userId: 'f72c7e41-4a75-49ea-b7c0-bd0cb9189d91',
done: true
}
]
}
Wrapping Up
Deploying Our App To Production
It’s all well and good to have something running locally, but what if we wanted to let the world see our work? Well, you’d want to put it out on a public server, of course. There are a bunch of ways to do this, and lots of services to choose from. I’m going to go with Heroku for this demonstration, mainly because both the file and database servers are together in one place. And for smaller hobby projects–it’s free!
First things first, start a new server on Heroku. Go to Heroku, click Log In (or Sign Up if you don’t have an account yet). Once at your dashboard, select New > Create New App. Give it a name, then click Create App.
To make things easier, we’ll use GitHub as our deploy method. On the Deploy tab, in the Deployment Method section, click on GitHub, then search for your Svelte app repo, and click Connect.
Next, on the Resources tab, search for the “Heroku Postgres” addon. Hobby Dev is what we’re looking for (the free tier), and should already be selected, so click Submit Order Form and it will be added to your project.
Now, let’s head over to the Settings tab. We’ll want to add a buildpack. Click the “Add Buildpack” button, and click the nodejs button. It’ll prefill the text box above with heroku/nodejs. Click Save Changes.
There is one thing left to do–Heroku needs to know how to set up our Prisma database, and how to start the app. This can be accomplished by adding a Procfile to our app. Heroku looks for the Procfile first, and then falls back to a start script in your package.json file. Since we want Prisma to update the database with any release migrations and tell the server how to start the app, we’ll use the Procfile.
-
In the root of the repository, add a new file called
Procfilewithout any file extensions -
Edit that file to have the following lines:
web: node build/index.js --port $PORT --host 0.0.0.0
release: npx prisma migrate deploy
Save the file, and commit it to the repo.
Deploying on Heroku
Go back to the Deploy tab on the Heroku dashboard, and manually deploy your app. Scroll to the bottom, and in the Manual Deploy section, click Deploy Branch. Once the app says it’s been successfully deployed, click the View button to have your application open up in another browser tab.
record scratch Well, that was unexpected. We’re seeing an error page! Checking the logs (More > View Logs at the top right corner of your dashboard), we aren’t able to see much:
2022-03-04T20:21:53.616866+00:00 app[web.1]: Error: Cannot find module '/app/build/index.js'
2022-03-04T20:21:53.616869+00:00 app[web.1]: at Function.Module._resolveFilename (node:internal/modules/cjs/loader:933:15)
2022-03-04T20:21:53.616872+00:00 app[web.1]: at Function.Module._load (node:internal/modules/cjs/loader:778:27)
2022-03-04T20:21:53.616873+00:00 app[web.1]: at Function.executeUserEntryPoint [as runMain] (node:internal/modules/run_main:77:12)
2022-03-04T20:21:53.616876+00:00 app[web.1]: at node:internal/main/run_main_module:17:47 {
2022-03-04T20:21:53.616879+00:00 app[web.1]: code: 'MODULE_NOT_FOUND',
2022-03-04T20:21:53.616882+00:00 app[web.1]: requireStack: []
2022-03-04T20:21:53.616882+00:00 app[web.1]: }
2022-03-04T20:21:53.770047+00:00 heroku[web.1]: Process exited with status 1
2022-03-04T20:21:53.853427+00:00 heroku[web.1]: State changed from starting to crashed
When you deploy a SvelteKit app, it needs an adapter to generate the build output for a given deployment environment. By default, SvelteKit configures the auto adapter, which currently only supports Cloudflare, Netlify, and Vercel. Looking in the SvelteKit packages repo, we do see SvelteKit ships with a Node adapter, which would work in Heroku’s environment.
Install the Node adapter by running npm i @sveltejs/adapter-node@next in the root of your project repo. Then, in svelte.config.js, update the adapter-auto import to use adapter-node :
-import adapter from '@sveltejs/adapter-auto';
+import adapter from '@sveltejs/adapter-node';
/** @type {import('@sveltejs/kit').Config} */
const config = {...};
export default config;
Save your changes, commit/push them, then perform another manual deploy.
Click the View button, and, voila! You have an app running! So far, so good. Now click on the Todo tab and…wwwhhhhhaaaaatttttt? Another error page?
Error: Failed to load data
at it (https://svelte-blog-demo-app.herokuapp.com/_app/start-8ba59c0f.js:1:16243)
at async Ie (https://svelte-blog-demo-app.herokuapp.com/_app/start-8ba59c0f.js:1:13542)
Back to the logs, we see:
2022-03-04T20:30:32.835592+00:00 app[web.1]: SyntaxError: Named export 'PrismaClient' not found. The requested module '@prisma/client' is a CommonJS module, which may not support all module.exports as named exports.
2022-03-04T20:30:32.835599+00:00 app[web.1]: CommonJS modules can always be imported via the default export, for example using:
2022-03-04T20:30:32.835599+00:00 app[web.1]:
2022-03-04T20:30:32.835600+00:00 app[web.1]: import pkg from '@prisma/client';
2022-03-04T20:30:32.835600+00:00 app[web.1]: const { PrismaClient } = pkg;
Well, that’s fun. Locally, everything was running great. But because Prisma is still exporting CommonJS modules, it blows up in a Node env. Not to worry, we can fix this!
Back in our src/routes/todos/index.js file, replace our Prisma client import (the top 2-3 lines) with:
- import { PrismaClient } from '@prisma/client';
+ import pkg from '@prisma/client';
+ const { PrismaClient } = pkg;
const prisma = new PrismaClient();
All of the code examples have you importing the client using ESModules, however our Node build does not like the CommonJS export, so we have to do it this way until Prisma updates its client to export ESM. You could try to come up with some if/else logic to choose which import, but import pkg, { PrismaClient } from '@prisma/client' will still throw errors when you try to view the Todos page on your production server. Since the method above works for both production and dev environments, it’s easiest to just do this for now.
Make your changes, push them to your repo, then re-deploy your app. Click the Todos app and…success! Try adding/editing/deleting some Todos, as well as marking them as done. If you want to see my project in action, you can find it here.
Hopefully this post has been informative in both how to convert an app to use a local database via the Prisma client, as well as some of the challenges you may encounter deploying your application out to production.
