Full-Stack Sapper & SQLite with Sequelize Pt.1

There are plenty tutorials on the internet with MongoDB/Mongoose as a database for Express. I have decided to explore relational databases. The first thing that came into my mind was SQLite. And to my surprise, I couldn't find anything useful that works with Sapper.

This tutorial consists of two parts, first one is concerned about the Express backend, SQLite DB, controllers and all the jazz, and the second part guides the integration of Svelte frontend in Sapper.

Here is the final result of my integration of these two minimal yet powerful technologies https://github.com/gevera/sapper_sqlite_crud You can take it for a spin with

npx degit gevera/sapper_sqlite_crud
npm i
npm run dev

---

Make it from scratch

Create a default Sapper project, convert it to TypeScript and install needed dependencies

npx degit "sveltejs/sapper-template#rollup" sapper_sqlite
cd sapper_sqlite
node scripts/setupTypeScript.js
npm i

First of all, let's add to our dependencies, Express instead of default polka, and SQLite database and sequelize ORM.

npm i express sqlite3 sequelize
npm remove polka @types/polka

Let's also add types for them

npm i -D @types/express @types/node @types/sqlite3 @types/sequelize

Create a folder in src folder called db. And create a sqliteDb.ts file in it. Here we are importing sequelize and exporting sequelize itself and an async db function that initializes our database in ./src/db folder under the name database.sqlite We will use this function a bit later when we change up our server file.

import { Sequelize } from 'sequelize';

export const sequelize = new Sequelize({
    dialect: 'sqlite',
    storage: './src/db/database.sqlite',
});

export const db = async () => {
    try {
        await sequelize.authenticate();
        console.log('Connection to sqliteDB has been established successfully.');
    } catch (error) {
        console.error('Unable to connect to the database:', error);
    }
};

Next, we need to create a user model in User.ts in Models folder

import { UUIDV4, DataTypes } from "sequelize";
import { sequelize } from "../sqliteDb";

export const UserModel = sequelize.define("User", {
  id: {
    type: DataTypes.UUID,
    defaultValue: UUIDV4,
    primaryKey: true,
  },
  name: {
    type: DataTypes.STRING,
    allowNull: false,
    unique: true,
  },
  profession: {
    type: DataTypes.STRING,
    defaultValue: "programmer",
  },
});

Note we are importing UUIDV4 to create unique primary id's for each user. It is very similar to mongoose schema.

We need controllers to handle our request, so let's create a controllers directory in src and add a userControllers.ts file. Here is where all the magic happens between our server and database. I do like sequelize syntax more then mongoose for some reason. I think it is straightforward and clean.

import { UserModel } from '../db/Models/User';
import { sequelize } from '../db/sqliteDb';

export const getAllUsers = async (req, res, next) => {
    try {
        await sequelize.sync();
        const users = await UserModel.findAll();
        res.json({ success: true, data: users }).status(200);

    } catch (e) {
        console.log(e);
        res.json({ success: false, message: 'Failed to find users' }).status(500);
    }
}

export const createAUser = async (req, res, next) => {

    try {
        await sequelize.sync();
        const { name, profession } = req.body;
        const newUser = await UserModel.create({ name, profession });
        res.json({ success: true, data: newUser }).status(200);
    } catch (e) {
        console.log(e);
        res.json({ success: false, message: 'Failed to create a new user' }).status(500);
    }
}

export const getAUser = async (req, res, next) => {
    try {
        await sequelize.sync();
        const user = await UserModel.findAll({
            where: {
                id: req.params.id
            }
        });
        if (user.length) {
            res.json({ success: true, data: user }).status(200);
        } else {
            res.json({ success: false, message: 'Failed to find user' }).status(404);
        }

    } catch (e) {
        console.log(e);
        res.json({ success: false, message: 'Failed to find user' }).status(500);
    }
}

export const deleteAUser = async (req, res, next) => {
    try {
        await sequelize.sync();
        const user = await UserModel.destroy({
            where: {
                id: req.params.id
            }
        });
        if (user) {
            res.json({ success: true, message: 'User deleted!' }).status(204);
        } else {
            res.json({ success: false, message: 'Failed to find user' }).status(404);
        }

    } catch (e) {
        console.log(e);
        res.json({ success: false, message: 'Failed to delete user' }).status(500);
    }
}

export const updateAUser  = async (req, res, next) => {
    try {
        await sequelize.sync();
        const user = await UserModel.update({ ...req.body }, {
            where: {
                id: req.params.id
            }
        });
        if (user.length) {
            res.json({ success: true, message: 'User updated!', data: user }).status(204);
        } else {
            res.json({ success: false, message: 'Failed to find user' }).status(404);
        }
    } catch (e) {
        console.log(e);
        res.json({ success: false, message: 'Failed to update user' }).status(500);
    }
}

Finally, lets switch to server.ts file in our src directory.
Switch polka to express. Import our db function and execute it. And also we need to enable json and urlencoded so we can parse the body for post/put requests.
Get the controllers created earlier, and apply them to the corresponding routes.

import sirv from "sirv";
import express from "express";
import compression from "compression";
import * as sapper from "@sapper/server";
import { db } from "./db/sqliteDb";
import {
    getAllUsers,
    createAUser,
    getAUser,
    updateAUser,
    deleteAUser
} from './controllers/userControllers';

const { PORT, NODE_ENV } = process.env;
const dev = NODE_ENV === "development";

db();

const app = express();

app.use(express.urlencoded({ extended: true }));
app.use(express.json());

app.get('/api', getAllUsers);
app.post('/api', createAUser);
app.get('/api/:id', getAUser);
app.put('/api/:id', updateAUser);
app.delete('/api/:id', deleteAUser);

app
  .use(
    compression({ threshold: 0 }),
    sirv("static", { dev }),
    sapper.middleware()
  )
  .listen(PORT, () => {
    console.log("Express is up and running!");
  });

At the first run, the database.sqlite file will be created in db folder. If you hit the http://localhost:3000/api we will get

{
  "success": true,
  "data": []
}

Great Success! Our Sapper App has now a persistent database and a full CRUD working REST API.

Comments

[Luca]

...and the part 2?

[Denis Donici]

And part two I have decided to postpone until Sveltekit aka Svelte 4 comes out and update the original post with Postgres. However, if you know svelte it should be fairly easy to render the lists with for each blocks and making basic operations. Let me know if you need help. ill be glad to assist you