Write to a local Database file (sqlite)

[christiankozalla]

Hey everyone! After days of debugging I'm wondering if its even possible to write to a local Database file with Next.js? I am using SQLite and my database lives inside /database/db.sqlite. I have one API route api/posts/[id]/index.js with one GET and one PUT request handler:

import db from "../../../../lib/db";

export default async function (req, res) {
  if (req.method === "GET") {
    const id = req.query.id;

    db.get(
      "SELECT * FROM posts WHERE id=$id",
      {
        $id: id,
      },
      (err, row) => {
        if (err) {
          console.error(err);
        }
        res.json({ post: row });
      }
    );
  } else if (req.method === "PUT") {
    const id = req.query.id;
    const column = req.body.column;

    db.run(
      `UPDATE posts SET ${column}=${column}+1 WHERE id=$id`,
      {
        $id: id,
      },
      (err) => {
        if (err) {
          res.status(400).json({ error: err.message });
        }
        res.json({ message: "Updated DB" });
      }
    );
  }
}

// /lib/db.js
import sqlite from "sqlite3";
import path from "path";

const pathToDb = path.join(process.cwd(), "database");
const db = new sqlite.Database(`${pathToDb}/postdb.sqlite`);

export default db;

I just want to implement a view/like couter for my blog posts, where I store post IDs along with "views" and "likes" in the local database file. Additionally I have one "Like"-Button which fires a PUT request to increment likes for an individual post. GET request handler works fine, I am fetching data on the client-side with useSWR. But upon PUT requests I am receiving a "502 Bad Gateway" Console Output.

When I am in development, GET and PUT work fine. So I am out of ideas how to fix this.

Looking forward to yours answers! Thank you!
Christian

[kimgiutae]

Maybe this is what you are looking for: https://github.com/prisma/prisma-examples/tree/master/typescript/rest-nextjs

[christiankozalla]

Thanks for the hint! I recently used AWS DynamoDB to fix my problem with SQLite. But I'll definitely take a look at this prisma setup, later =) Jonathan Kim (김규태) <[email protected]> schrieb am Di., 18. Aug. 2020, 15:23:

…

Maybe this is what you are looking for: https://github.com/prisma/prisma-examples/tree/master/typescript/rest-nextjs — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub <#15339 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AOCRLRG3LVMF47UALXWNZCTSBJ6GVANCNFSM4PCS4TWA> .

[kimgiutae]

No welcome. I was looking for a way to manage small databases all inside the same next app. And the example above works for me very well.

You need to install the prisma-cli. Have the sqlite database in your prisma folder, and run prisma introspect, and prisma generate to build the prisma-client inside your dependencies.

And you are fine to go, use the prisma client inside your next api, to GET, POST, DELETE and PUT rows.
The prisma-client api is very friendly, very similar to Knex.

The only hard part is to create and setup the database (SQL). Prisma is agnostic about this.
It has a command for migrations, that works ok, but is experimental.
But I'm sure that it can be done with simple sqlite3 commands.