Lab 4: SvelteKit Database

Enda Lee 2025

PDF version

Getting Started

1. Download or clone this repository.
2. Open the app folder in Visual Studio Code.
3. In the terminal, run npm install to install all dependencies.
4. Start the application using npm run dev.

Overview

In this lab, you’ll add a database and server-side functionality to your SvelteKit application.
You’ll use SQLite as the database and Drizzle as the ORM (Object Relational Mapper).

The ORM handles the mapping between data in your application (objects) and the database (tables).

This application will store locations or Points of Interest (POIs).
In this lab, you’ll focus on setting up the database and creating the first two tables.

1. Install Dependencies and Configure the App

1.1 Install SQLite

This lab uses SQLite as the database.
The easiest way to install it on Windows is via DB Browser for SQLite.
The DB Browser includes a graphical user interface that lets you view and edit database files.

On Linux, install SQLite using your package manager.

1.2 Install Drizzle ORM

You can install Drizzle using npm, but because it’s officially supported as a SvelteKit add-on, it’s easier to use the command npx sv add.

Step 1: Add Drizzle to your app

Run the command and select drizzle from the list of add-ons.

Step 2: Choose your database

Select SQLite from the available database options.

Step 3: Choose the SQLite client

Select libSQL as the SQLite client.

Step 4: Complete installation

Choose npm as the package manager.
When installation completes, a confirmation message will appear.

2. The Drizzle Database Schema

Drizzle installs a sample schema for a user, written in TypeScript.
You can find it in src/lib/server/db/schema.ts.

This schema defines a user data model and maps it to a SQLite table.
For details on available data types and schema options, see the Drizzle Schema Documentation:
https://orm.drizzle.team/docs/column-types/sqlite

The folder src/lib/server/db/ also contains index.ts, which defines the database connection.

Environment Variables (.env)

The DATABASE_URL value is stored in a file named .env, located in the project root.
This file stores configuration values, keys, and secrets and acts as a local proxy for environment variables.
These values should never be hard-coded in your code.

3. Creating the Database

The Drizzle installation adds several database migration scripts to package.json.

Script	Description
db:push	Pushes your Drizzle schema directly to the database. Learn more.
db:generate	Generates SQL migration files from your Drizzle schema. Learn more.
db:migrate	Applies generated SQL migration files to your database. Learn more.
db:pull	Reads your database schema and converts it into Drizzle models. Learn more.
db:studio	Launches Drizzle Studio, a web-based UI for exploring your database. Learn more.

Source: https://orm.drizzle.team/docs/kit-overview

The generate and migrate commands let you view migration SQL before applying changes.
The push command applies schema changes directly without previewing the SQL.

3.1 Generate Database Tables

To create a table in your database using the user schema:

1. Run npm run db:push.
2. Review the SQL Drizzle generates (such as CREATE TABLE statements).
3. Type “YES” and press ENTER to confirm.

3.2 Open Drizzle Studio

Drizzle ORM includes a web-based interface for viewing and editing database tables.
Start it with:

npm run db:studio

Then open https://local.drizzle.studio in your browser.
You should see the user table listed.

Your SQLite database file (local.db) is stored in the project root by default (check .env to confirm).
You can open this file directly in DB Browser for SQLite or use a VS Code extension like SQLite to view it inside the editor.

4. Add the Category and Location Tables

The next step is to define the category and location tables, along with a one-to-many relationship between them.
Use the Drizzle documentation as a reference for data types, primary keys, relationships, and other options.

4.1 Define the category Table

Add this table definition to src/lib/server/db/schema.ts.

This section defines the category table for SQLite. Observe the field names, data types, and attributes.

4.2 Define the location Table

The location table definition uses both real and text data types.
Modify the import statement at the top of your schema file to include them:

import { sqliteTable, integer, real, text } from 'drizzle-orm/sqlite-core';

4.3 Define the relations

Import the relations function (just after the existing import statement):

import { relations } from 'drizzle-orm';

Based on the schema design:

• A category has many locations
• A user has many locations

(these relationships are represented by the category_id and user_id fields in the location table).

Define the one-to-many relationships in the schema:

Define the reverse relationships

Each location belongs to one category and one user.

4.4 Update the user Table

You’ll make some changes to the user table.
Although this table will be used more extensively in future labs, it’s included now because of its relationship with location.

Modify the user definition:

4.5 Add Sample Data

Run the following SQL commands to insert sample data into your database:

INSERT INTO user(id, username, email, password_hash, firstname, lastname) VALUES
(1, 'admin', 'bob@admin.com', 'e7cf3ef4f17c3999a94f2c6f612e8a888e5b1026878e4e19398b23bd38ec221a', 'Bob', 'Roberts');

INSERT INTO category(id, name, description) VALUES
(1, 'Restaurant', 'Places to eat');
INSERT INTO category(id, name, description) VALUES
(2, 'EV Fast Chargers', 'Places to charge quickly');
INSERT INTO category(id, name, description) VALUES
(3, 'Scenic', 'Places with nice views');
INSERT INTO category(id, name, description) VALUES
(4, 'Shopping', 'Places to shop');
INSERT INTO category(id, name, description) VALUES
(5, 'Hospitals', 'Public and Private');
INSERT INTO category(id, name, description) VALUES
(6, 'Education', 'Schools, Colleges, Universities');
INSERT INTO category(id, name, description) VALUES
(7, 'Parks', 'Recreational spaces');
INSERT INTO category(id, name, description) VALUES
(8, 'Other', 'Uncategorised');

INSERT INTO location(id, category_id, user_id, name, description, longitude, latitude, shared, favourite) VALUES
(1, 6, 1, 'TU Dublin Tallaght Campus', 'Old Blessington Road, Tallaght, Dublin 24, Ireland', -6.36352, 53.29109, true, true);

To execute these statements, open your database in DB Browser for SQLite.
After executing the commands, make sure to Write Changes from the File menu — otherwise, your work will not be saved.

5. Access Data and Display

In this step, you’ll retrieve data from the database using Drizzle and display it on a page.
This is a simple test version — in future labs, you’ll separate the data access function from the page itself.

5.1 Add the Route

1. Create the route

Add a new route named location, and include both a page and a server script.

2. Use server-side data access

Data access should only happen on the server side, so use +page.server.js.

3. Display data in the page

Access and use the data in +page.svelte.

6. Exercises

1. Add additional location records for at least three new categories.
2. Display categories and their corresponding locations on the page. Use the products example as a guide.
   Display each location as a Bootstrap Card.
3. Add icons to show whether shared or favourite is set to true for a location.
4. Add a filter to display only locations from a selected category.
5. Include a link to OpenStreetMap to display the location on a map:
   https://www.openstreetmap.org/search?query={latitude}%2C{longitude}#map=17/{latitude}/{longitude}

Advanced:
Display maps directly on each location card.
Example:
https://svelte.dev/playground/0ccd678e25fb466b99c57444df238b44?version=5.39.10

---

Enda Lee 2025