Easy example: Create a Nuxt 3 API endpoint to store data into an SQLite database

/**
 * This is the main package we need. We can download it easily via the command line
 * using `yarn add sqlite3` or `bun add sqlite3`. DO NOT use the `nuxi` command since
 * that one is only for MODULES, not PACKAGES.
 *
 * Once downloaded (check `package.json` if it's in there),
 * we must import the `sqlite3` object from that package with the same name.
 */
import sqlite3      from 'sqlite3'

/**
 * This is optional. It gives us more details for the
 * `readBody()` function that we will use to read what the
 * web browser has sent us.
 */
import { readBody } from 'h3'

/**
 * This is also optional. Just like we used it in other files before,
 * this interface only dictates the structure, i.e. what methods are available
 * (like `_id`, or `amount`).
 */
import { Product }  from '~/utils/types'

/**
 * This is the actual start of the function. When we make a POST request to `/api/log`,
 * this function will be executed.
 */
export default defineEventHandler(async (event) => {
  /**
   * Easy. We create a new instance of `sqlite3` and tell it to create
   * the database file in the folder '/private/db.sqlite'.
   */
  const db = new sqlite3.Database('private/db.sqlite')

  /**
   * Our first SQL query. We create a variable and use backticks instead of apostrophes.
   * This allows us to format the code more nicely and more readable.
   *
   * The SQL code does the following: It checks if a database with the name "purchases"
   * already exists. If yes, then there's nothing else to do.
   *
   * If it doesn't exist, though, it will create the database. Since databases are simply
   * tables, we must specify each column and what kind of content we expect to put inside
   * these columns, row by row.
   */
  const createQuery = `
  CREATE TABLE IF NOT EXISTS purchases (
      id            INTEGER PRIMARY KEY AUTOINCREMENT, // 1st column is an automatic internal ID
      date          TIME, // 2nd column will be the date the data was saved at
      product_id    TEXT, // 3rd column is our product ID, i.e. "content:products:1.peaches.md"
      amount        INTEGER // 4th column is the amount/how many items of this product was bought
    )
  `

  /**
   * We use `db.run()` to execute the SQL query we've saved in the `createQuery` variable.
   * The SQL stuff comes into the first parameter of `run()`, the second parameter is a function
   * that will be executed if anything goes wrong. For us, we simply say `saved: false` and
   * add the error message to `error`. Since this a `return` statement, the code stops
   * here and the rest of the function won't be executed.
   */
  db.run(createQuery, (err) => {
    if (err) {
      return {
        saved: false, // Not saved in the DB :(
        error: err.message // The error message
      }
    }
  })

  /**
   * Now it's time for the ACTUAL data. `readBody()` returns the BODY
   * part of a POST request. You can see it in `cart.vue`; we send the values
   * that we want to access here inside the `body` part.
   */
  const data = await readBody(event)

  /**
   * A little semantics. The data are actually products, that's why we rename them
   * accordingly. We also give them the interface structure and, if no data was sent,
   * fill it with just an empty array so the next loop doesn't crash.
   */
  const products: Product[] = data ?? []

  /**
   * Here we go: the loop. We go through every individual product that has been sent to us
   * from the front-end, create the SQL code and then run it. Running in this case means
   * saving the data to the database, but in the right columns.
   *
   * I arranged the text with tabs and spaces a bit so it makes more sense. Imagine
   * it as a simple table, with the first row being the header row, and the ones
   * underneath being the rows with the values. But make sure the position is correct!
   */
  for (const product of products) {
    const insertQuery = `
    INSERT INTO purchases   (date,              product_id, amount) // The column names
    VALUES                  (datetime('now'),   ?,          ?) // Respective values for this column
    `

    /**
     * We use `?` as placeholders for the product ID and the amount. They will be filled
     * with the right value right here, when we run the SQL query.
     */
    db.run(insertQuery, [
      product._id, // The first `?`
      product.amount // The second `?`
      /**
       * Again, if something fucks up, we execute this function that will
       * let us know that it was NOT saved, and the actual error message.
       * Since it's a `return` statement, the code will end here.
       */
    ], (err) => {
      if (err) {
        return {
          saved: false, // Not saved in the DB :(
          error: err.message // Error text message
        }
      }
    })
  }

  /**
   * This is what we want! Saved? Yes! Error? Nothing.
   * This object will be thrown back to the checkout page where we can read it.
   * And if we check if `saved` is `true`, and it's the case, we can show the green
   * checkmark to let the user know everything went fine.
   */
  return {
    saved: true,
    error: ''
  }
}) // End of the function