Full-Stack Sapper & SQLite with Sequelize Pt.1
Tags: svelte, sapper, sql, fullstack
Published: 2020-10-13 11:01:35

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](https://github.com/gevera/sapper_sqlite_crud) You can take it for a spin with 

```console
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

```console
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.

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

Let's also add types for them

```console
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.

```javascript
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 

```javascript
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.
```javascript
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.

```javascript
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
```javascript
{
  "success": true,
  "data": []
}
```

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