Basic SQLite example

In this example, a sqlite database is used for storing user email addresses. Listing, adding and deleting users are done on the same page by use of http method handlers for GET, POST and DELETE respectively. Deleting a user is performed by use of an island component.

Create a database helper file

Add a /db.ts file to the application root directory, with the content below.

// /db.ts

import { DatabaseSync } from "node:sqlite";
const db = new DatabaseSync("test.db");
db.exec(`CREATE TABLE IF NOT EXISTS users (
	  id INTEGER PRIMARY KEY AUTOINCREMENT,
	  email TEXT      
	);`);

//-------------------------------------------------------
// Helper functions to interact with the database
const getUsers = () => {
  const rows = db.prepare("SELECT id, email FROM users").all();
  const users: { id: number; email: string }[] = rows.map((row: any) => ({
    id: row.id,
    email: row.email,
  }));
  return users;
};

const addUser = (email: string) => {
  db.prepare(`INSERT INTO users (email) VALUES (?);`).run(email);
};

const deleteUser = (id: number) => {
  db.prepare(`DELETE FROM users WHERE id = ?;`).run(id);
};

export { addUser, deleteUser, getUsers };

Adding the users page

Create a /routes/users.tsx file with the following content:

// /routes/users.ts

import { define } from "@/utils.ts";
import { page } from "fresh";
import { addUser, deleteUser, getUsers } from "@/db.ts";
// import DeleteUser from "@/islands/DeleteUser.tsx";

//-------------------------------------------------------
// Handlers preparing data for `/routes/users` page
export const handler = define.handlers({
  GET(ctx) {
    // get users from db
    let users = getUsers();
    return page({ users });
  },

  async POST(ctx) {
    const email = (await ctx.req.formData()).get("email")!.toString();
    if (email) {
      // add user to db
      addUser(email);
    }
    return page({ users: getUsers() });
  },

  async DELETE(ctx) {
    const id = (await ctx.req.formData()).get("id")!.toString();
    if (id) {
      const parsedId = parseInt(id, 10);
      // delete user from db
      deleteUser(parsedId);
    }
    return page({ users: getUsers() });
  },
});

//-------------------------------------------------------
// Generate html for `/routes/users` page
export default define.page<typeof handler>(
  (props) => {
    const users: { id: number; email: string }[] = props.data.users;

    return (
      <>
        <h3>List users</h3>
        {users && users.length === 0 && <p>No users found.</p>}
        <ul>
          {users.map((user) => (
            <li key={user.id}>
              <div>
                {user.id} : {user.email} {/*<DeleteUser id={user.id} />*/}
              </div>
            </li>
          ))}
        </ul>

        <h3>Add user</h3>
        <form method="POST">
          <input type="email" name="email" placeholder="Email" required />
          <button type="submit">Add</button>
        </form>
      </>
    );
  },
);

When navigating to the /users page, the GET handler is run, wich selects all the user emails from the db (if any), and forwards them to the page for later rendering.

When using the Add users form, formdata containing the new email address is send to the page itself using the POST method, and the POST handler stores the new user to the db.

Deleting a user

The DELETE method can't be used in a standard html form, but we can use a client side preact component for making a DELETE method request. Create a /islands/DeleteUser.tsx file with the following content:

// /islands/DeleteUser.tsx

export default function DeleteUser(props: { id: number }) {
  return (
    <a
      href=""
      onClick={async () => {
        const formData = new FormData();
        formData.append("id", props.id.toString());
        await fetch("/users", {
          method: "DELETE",
          body: formData,
        });
      }}
    >
      delete
    </a>
  );
}

In the /routes/user.tsx file, uncomment the DeleteUser island component import statement. Change from...

// import DeleteUser from "@/islands/DeleteUser.tsx";

...to

import DeleteUser from "@/islands/DeleteUser.tsx";

Also, uncomment the use of the DeleteUser component in the List users section. Change...

<div>
  {user.id} : {user.email} {/* <DeleteUser id={user.id} /> */}
</div>;