Build and Run a Web App using Turso, Drizzle ORM, and Express on Koyeb
Tags: serverless, webdev, tutorial, javascript
Published: 2023-10-12 11:33:31

## Introduction

As our increasingly interconnected world experiences rapid technological advancements, a growing necessity emerges to address a range of challenges, including issues like latency, scalability, reliability, and bandwidth optimization, amongst others. Edge computing aims to tackle these challenges by processing data in close proximity to its point of origin, departing from the conventional approach of centralized cloud servers. This approach leads to accelerated and more efficient data processing and transfers, ultimately enhancing flexibility and reliability across a wide range of software applications.

[Turso](https://turso.tech/) is a geographically distributed, edge-hosted database system based on [`libSQL`](https://github.com/tursodatabase/libsql), an SQLite fork.  It strategically positions data in proximity to the code that interacts with it, thereby minimizing query latency for applications and ensuring efficient and swift data retrieval.

This tutorial will demonstrate the ease of using edge-hosted databases in developing software by building a simple to-do app in Node.js using Turso and [Drizzle](https://orm.drizzle.team/), a TypeScript ORM for SQL databases.

You can deploy and preview the to-do application from this guide using the [Deploy to Koyeb](https://www.koyeb.com/docs/build-and-deploy/deploy-to-koyeb-button) button below:

[![Deploy to Koyeb](https://www.koyeb.com/static/images/deploy/button.svg)](https://app.koyeb.com/deploy?name=drizzle-turso-todo-app&type=git&repository=koyeb/example-turso-drizzle&branch=main&env[PORT]=8000&env[DATABASE_URL]=REPLACE_ME&env[DATABASE_AUTH_TOKEN]=REPLACE_ME)

**Note:** Remember to replace the values of the `DATABASE_URL` and `DATABASE_AUTH_TOKEN` environment variables with your own information (as described in the [section on setting up the database with Turso](#set-up-the-database-with-turso)).

You can consult the [repository on GitHub](https://github.com/koyeb/example-node-schedule) to find out more about the application that this guide builds.

## Requirements

- Node.js and `npm` installed. The demo app in this tutorial uses version 18.16.1 of Node.js.
- Git installed.
- A [Turso account](https://turso.tech/).
- A [Koyeb](https://app.koyeb.com/) account to deploy the application.

## Steps

This guide will cover how to build a to-do list application with Turso and Drizzle and deploy to Koyeb through the following steps:

1. [Set up the database with Turso](#set-up-the-database-with-turso)
2. [Set up the project](#set-up-the-project)
3. [Set up the database connection](#set-up-the-database-connection)
4. [Add new to-do items](#add-new-to-do-items)
    * [Set up the Express server](#set-up-the-express-server)
    * [Set up the client-side view](#set-up-the-client-side-view)
    * [Handle to-do form submission](#handle-to-do-form-submission)
5. [View existing to-do items](#view-existing-to-do-items)
6. [Update to-do items](#update-to-do-items)
    * [Fetch to-do items](#fetch-to-do-items)
    * [Set up to-do edit view](#set-up-to-do-edit-view)
    * [Handle to-do update](#handle-to-do-update)
7. [Deploy to Koyeb](#deploy-to-koyeb)

## Set up the database with Turso

Turso offers a Command Line Interface (CLI) for the creation and administration of Turso databases. To get started, please follow the [installation instructions](https://docs.turso.tech/tutorials/get-started-turso-cli/step-01-installation) to set up the Turso CLI.

Once you have confirmed the successful installation of the Turso CLI, proceed to create a logical database by executing the following command:

```bash
turso db create app-db
```

The command above creates a Turso logical database named `app-db`. Upon the successful execution of the command, your terminal window will display a "Congratulations" message. Now, you can copy the URL for your just created database by executing the following command:

```bash
turso db show app-db
```

Executing the command above should yield an output similar to the one below:

```
Name:           app-db
URL:            libsql://app-db-<your-github-username>.turso.io
ID:             <random numbers>
Locations:      <your db location>
Size:           12 kB
```

Copy the URL string in the terminal output and store it securely for future use. Finally, retrieve an authentication token for your database by running the command below:

```bash
turso db tokens create app-db
```

Running the Turso command above generates an authentication token string that you will need to configure a client for connecting to the database. Copy and store this token for later use.

With that last step completed, the database is ready for use. In the next section, you will set up the project and install the necessary libraries and dependencies.

## Set up the project

In this section, we will set up an `npm` project with TypeScript, install essential libraries and packages, and configure a server for the to-do app.

To get started, create a project root directory on your development machine by running the command below in your terminal window:

```bash
mkdir -p drizzle_turso_todo_app/src
```

The `drizzle_turso_todo_app` directory serves as the root directory for the demonstration application, and within it, the `src` directory will house the project code. Next, initialize a Git repository in your root directory using the commands below:

```bash
cd drizzle_turso_todo_app
git init
```

The initial command navigates to the `drizzle_turso_todo_app` directory, while the subsequent command initializes a Git repository within the directory.

Next, create a new `npm` project in the `drizzle_turso_todo_app` directory by executing the command below:

```bash
npm init -y
```

The above command generates an `npm` project with default settings, creating a `package.json` file in the root directory.

After setting up the `npm` project, install the necessary libraries and packages by executing the following commands:

```bash
npm install drizzle-orm @libsql/client express ejs method-override
npm install --D typescript drizzle-kit dotenv nodemon ts-node @types/express @types/method-override
```

The `npm` install command above installs the designated libraries and dependencies, with the `--D` flag indicating the installation of development-only libraries.

The dependencies installed include:

- `drizzle-orm`: A TypeScript ORM.
- `@libsql/client`: A TypeScript SDK for libSQL databases.
- `express`: A web framework for Node.js.
- `ejs`: A JavaScript templating engine.
- `method-override`: A library for overriding HTTP verbs.

The development specific libraries include:

- `typescript`: Enables the execution of TypeScript code.
- `drizzle`-kit: A CLI tool for the Drizzle ORM.
- `dotenv`: A library for handling environment variables.
- `nodemon`: Detects code changes to restart the application during development.
- `ts-node`: To execute and rebuild TypeScript efficiently.
- `@types/express`: Type definitions for express.
- `@types/method-override`: Type definitions for method-override

With the necessary libraries and dependencies now installed, create a `tsconfig.json` file in the project's root directory and insert the following code into the file:

```json
{
  "compilerOptions": {
    "target": "es2016",
    "module": "commonjs",
    "esModuleInterop": true,
    "forceConsistentCasingInFileNames": true,
    "strict": true,
    "skipLibCheck": true
  },
  "include": ["src/**/*.ts"],
  "exclude": ["node_modules"]
}
```

The `tsconfig.json` file is a configuration file for TypeScript that specifies how the TypeScript code should be transpiled in a project.

That final step concludes the project setup. In the next section, you'll establish a connection to the Turso database created in the previous section.

## Set up the database connection

Drizzle is a lightweight and edge-ready TypeScript ORM which boasts compatibility with multiple SQL languages and offers a type-safe approach to app-to-database connection. With zero dependencies, it empowers users to define a database schema and seamlessly apply schema adjustments directly to the database. Additionally, it facilitates a wide range of database operations. In this section, you will seamlessly connect your application to the Turso database using Drizzle.

Start by creating a `.env` file in your project's root directory and insert the following lines of code, substituting the placeholder values with your own:

```ini
DATABASE_URL="<YOUR_DATABASE_URL>"
DATABASE_AUTH_TOKEN="<YOUR_DATABASE_AUTH_TOKEN>"
```

The database credentials added to the `.env` file are secret values and should not be committed to Git. To ensure this, create a `.gitignore` file by running the command below:

```bash
printf "%s\n" ".env" "node_modules" "src/**/*.js" > .gitignore
```

The provided command generates a `.gitignore` file and includes the `.env` file, the `node_modules` directory, and all JavaScript files generated from our TypeScript, ensuring they are excluded from the Git version history.

Next, you will create a database schema for the demo application. To do this, create a `db` directory within the `src` directory. Within the `db` directory, create a `schema.ts` file and add the following code to the file.

```typescript
import {
  text,
  integer,
  sqliteTable,
  uniqueIndex,
} from "drizzle-orm/sqlite-core";
import { sql } from "drizzle-orm";

export const todos = sqliteTable(
  "todos",
  {
    id: integer("id").primaryKey(),
    title: text("title").notNull(),
    description: text("description").notNull(),
    dueDate: integer("due_date", {
      mode: "timestamp",
    }).notNull(),
    priority: text("priority", { enum: ["High", "Medium", "Low"] }).notNull(),
    status: text("status", {
      enum: ["To-Do", "In Progress", "Completed"],
    })
      .notNull()
      .default("To-Do"),
    createdAt: integer("created_at")
      .default(sql`CURRENT_TIMESTAMP`)
      .notNull(),
    updatedAt: integer("updated_at")
      .default(sql`CURRENT_TIMESTAMP`)
      .notNull(),
    completedAt: integer("completed_at", {
      mode: "timestamp",
    }),
  },
  (todos) => ({
    nameIdx: uniqueIndex("nameIdx").on(todos.title),
  })
);
```

The code above begins by importing essential SQLite column methods such as `text` and `integer`, along with the `sqliteTable` and `uniqueIndex` methods, all from Drizzle's SQLite module. It also imports Drizzle's `sql` method. These methods are used to define a `todos` database table, specifying the table's columns, their data types, and various constraints. The columns and their constraints include:

- `id` an integer primary key.
- `title` and `description` are text fields and cannot be null.
- `dueDate` is an integer (timestamp) field and cannot be null.
- `priority` is a text field with enumerated values ("High," "Medium," "Low") and cannot be null.
- `status` is a text field with enumerated values ("To-Do," "In Progress," "Completed") that defaults to "To-Do" and cannot be null.
- `createdAt` and `updatedAt` are integer fields with default values set to the current timestamp and cannot be null.
- `completedAt` is an optional integer (timestamp) field.

In addition, it defines a unique index named `nameIdx` on the "title" column for the "todos" table. This index ensures to-do titles are unique.

Next, create a Drizzle database client to connect the ORM to your Turso database. To do this, create a `db.ts` file in the `db` directory and add the code below to the file:

```typescript
import "dotenv/config";

import { drizzle } from "drizzle-orm/libsql";
import { createClient } from "@libsql/client";

const client = createClient({
  url: `${process.env.DATABASE_URL}`,
  authToken: process.env.DATABASE_AUTH_TOKEN,
});

export const db = drizzle(client);
```

The provided code above starts by importing the `dotenv` config module for accessing environment variables. It also imports the `drizzle` method from Drizzle's `libsql` module and the `createClient` method from `@libsql/client`. The Turso database URL and authentication token are passed to the `createClient` method to create a database client. This database client is used to create a Drizzle database instance, which allows the application to interact with the database using the Drizzle ORM.

With the demo application now capable of communicating with the Turso database, the next step is to create the `todos` table in the database according to the specifications outlined in the schema file. Drizzle has the capability to apply schema changes to the database through migrations. To do this, generate a `migrate.ts` file within the `db` directory and insert the following code into the file:

```typescript
import { migrate } from "drizzle-orm/libsql/migrator";
import { db } from "./db";

migrate(db, { migrationsFolder: "src/db/migrations" })
  .then(() => {
    console.log("Migrations completed!");
    process.exit(0);
  })
  .catch((err) => {
    console.error("Migrations failed!", err);
    process.exit(1);
  });
```

The code above imports Drizzle's `migrate` function from the `libsql` module, which is responsible for executing database migrations, along with the previously configured Drizzle database instance. The migrate function is then called with two arguments: the Drizzle database instance and the file path to the directory where the migration scripts should be created. During the migration process, Drizzle generates a migration SQL file along with associated metadata files, which are stored in the specified `migrationsFolder` path. The migration result (success or failure) is logged to the console, and the process is terminated.

To generate and execute the migration, add the following scripts to your `package.json` file:

```json
...
"scripts": {
    "test": "echo \"Error: no test specified\" && exit 1",
    "schema:gen": "drizzle-kit generate:sqlite --out src/db/migrations --schema src/db/schema.ts",
    "schema:migrate": "ts-node src/db/migrate"
  },
...

```

The `schema:gen` command generates the migration, while the `schema:migrate` command executes the migration. Run both commands in your terminal window as shown below:

```bash
npm run schema:gen
npm run schema:migrate
```

The first command above creates a `meta` directory and an SQL file in `src/db/migrations` while the second command executes the generated SQL script, creating a `todos` table in the database. On successful execution, the second command should display an output similar to the one below in your terminal window:

```bash
drizzle_turso_todo-app@1.0.0 schema:migrate
> ts-node src/db/migrate

Migrations completed!
```

You can confirm the creation of the database table by using the Turso CLI to view all currently existing database tables. Run the command below to view the tables in your Turso database:

```bash
turso db shell app-db
.tables
```

The initial command initiates the Turso SQL shell for the `app-db` database, while the second command presents a list of all tables existing within the `app-db` database. You should observe the `todos` table included in the displayed output. To exit the interactive shell, simply type `.quit`.

The application has successfully established a connection to the database, and a `todos` table has been successfully created. The next section will focus on implementing the ability to create new to-do items.

## Add new to-do items

Adding a to-do item involves inputting its details through a form and then saving those details to a database when the form is submitted. This process requires the ability to receive and handle HTTP requests effectively.

### Set up the Express server

To get started, create an `index.ts` file in your `src` directory and add the following code to the file:

```typescript
import "dotenv/config";
import express, { Express, Request, Response } from "express";
import bodyParser from "body-parser";
import path from "path";
import { db } from "./db/db";
import { todos } from "./db/schema";

const app: Express = express();
const port = process.env.PORT || 4000;

app.use(bodyParser.urlencoded({ extended: false }));
app.set("view engine", "ejs");
app.set("views", path.join(__dirname, "views"));

// display new to-do page
app.get("/new", (req: Request, res: Response) => {
  res.render("new");
});

app.listen(port, () => {
  console.log(`🔥🔥🔥: Server is running at http://localhost:${port}`);
});
```

The code above imports a few libraries. They include:

- `dotenv/config`, which configures the application to use environment variables.
- `express` along with types for Express, Request, and Response objects for setting up a web server.
- The `body-parser` middleware for parsing request body data.
- The `path` module for handling file paths.
- The drizzle database instance and the `todos` schema.

The code proceeds to create an instance of an Express application and configures the server's port. It uses the `PORT` environment variable but defaults to 4000 if the variable is undefined. The Express server also uses `body-parser` to process URL-encoded data from incoming requests. The view engine is set to `EJS`, with the views directory being set to a folder named `views` relative to the current directory.

Additionally, a route is defined to handle HTTP `GET` requests to the `/new` path, and when such a request is made, it renders the new view. Finally, the server is started and listens on the specified port. It logs a message indicating the server is running.

### Set up the client-side view

Next, create a `views` directory in your `src` directory and within it, create a `partials` directory. Lastly, create a `head.ejs` file in the `partials` directory and add the following code to it:

```html
<meta charset="UTF-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<title><%= title %> | To-Do App</title>
<style>
  body {
    font-family: Arial, sans-serif;
    background-color: #f0f0f0;
    margin: 0;
    padding: 0;
  }

  .container {
    max-width: 500px;
    margin: 0 auto;
    padding: 20px;
    background-color: #fff;
    box-shadow: 0 0 10px rgba(0, 0, 0, 0.2);
    border-radius: 5px;
    margin-top: 20px;
  }

  h1 {
    text-align: center;
    color: #333;
  }

  form {
    margin-top: 20px;
  }

  label {
    display: block;
    font-weight: bold;
    margin-bottom: 5px;
  }

  input[type="text"],
  textarea {
    width: 100%;
    padding: 10px;
    margin-bottom: 15px;
    border: 1px solid #ccc;
    border-radius: 3px;
    box-sizing: border-box;
  }

  textarea {
    max-height: 100px;
    resize: vertical;
  }

  input[type="date"] {
    padding: 10px;
    margin-bottom: 15px;
  }

  select {
    width: 100%;
    padding: 10px;
    margin-bottom: 15px;
    border: 1px solid #ccc;
    border-radius: 3px;
  }

  button[type="submit"] {
    background-color: #007bff;
    color: #fff;
    padding: 10px 20px;
    border: none;
    border-radius: 3px;
    cursor: pointer;
  }

  button[type="submit"]:hover {
    background-color: #0056b3;
  }

  .todo-list {
    list-style: none;
    padding: 0;
  }

  .todo-item {
    display: flex;
    justify-content: space-between;
    align-items: center;
    border: 1px solid #ccc;
    margin: 5px 0;
    padding: 10px;
  }

  .todo-item .task {
    flex-grow: 1;
  }

  .task a {
    text-decoration: none;
  }

  .todo-item .due-date,
  .todo-item .priority {
    margin-right: 10px;
    color: #888;
  }

  .priority.High {
    color: #c1121f;
  }

  .priority.Medium {
    color: #f48c06;
  }

  .priority.Low {
    color: #118ab2;
  }

  .link-button {
    display: inline-block;
    background-color: #007bff;
    color: white;
    border: none;
    padding: 5px 10px;
    margin-bottom: 20px;
    cursor: pointer;
    text-decoration: none;
  }
</style>
```

The code provided above includes HTML markup for the `<head>` section of the webpage and CSS styles that will be applied to all pages in the to-do application.

Creating and editing a to-do item requires the same set of fields, making it feasible to utilise a single HTML form for both scenarios. To add the HTML form, create a `form.ejs` file in the `partials` directory and add the following code to it:

```html
<form action="<%=action%>" method="POST">
  <label for="title">Title:</label>
  <input type="text" id="title" name="title" required />

  <label for="description">Description:</label>
  <textarea id="description" name="description" rows="4" required></textarea>

  <label for="dueDate">Due Date:</label>
  <input type="date" id="dueDate" name="dueDate" />

  <%if(action != '/submit') { %>
  <label for="status">Status:</label>
  <select id="status" name="status">
    <option value="To-Do">To-Do</option>
    <option value="In Progress">In Progress</option>
    <option value="Completed">Completed</option>
  </select>
  <% } %>

  <label for="priority">Priority:</label>
  <select id="priority" name="priority">
    <option value="High">High</option>
    <option value="Medium">Medium</option>
    <option value="Low">Low</option>
  </select>

  <button type="submit">Add To-Do</button>
</form>
```

The provided code above includes an HTML form with fields for `title`, `description`, `due date`, `status`, and `priority`, and a submit button. The form uses a `POST` HTTP method and accepts a dynamic submission URL through an `action` parameter. As each newly created to-do item will initially have the "To-Do" status, the status field is shown in the form only when the provided `action` parameter isn't `/submit`, which will be the URL for creating a new to-do item.

With the `head` and `form` template partials in place, create a `new.ejs` file in the `views` directory and add the following code to it:

```html
<!DOCTYPE html>
<html lang="en">
  <head>
    <%- include('./partials/head.ejs', {title: 'New'}); %>
  </head>

  <body>
    <div class="container">
      <h1>Add a To-Do</h1>
      <%- include('./partials/form.ejs', {action: '/submit'}); %>
    </div>
  </body>
</html>
```

The `new.ejs` template created above is displayed whenever the `GET /new` route matches a request. The file contains HTML markup, and using the EJS `include` statement, it renders the `head.ejs` partial with "New" supplied as the title value. Furthermore, the page body contains a `div` with an "Add a To-Do" header and includes the `form.ejs` partial to the page using the EJS include statement with "/submit" supplied as the action value.

To view the page in your browser, adjust the script section of your `package.json` file to include a command to run the application:

```json
"scripts": {
    "test": "echo \"Error: no test specified\" && exit 1",
    "schema:gen": "drizzle-kit generate:sqlite --out src/db/migrations --schema src/db/schema.ts",
    "schema:migrate": "ts-node src/db/migrate",
    "dev": "nodemon --watch './**/*.ts' --exec ts-node ./src/index.ts"
  },
```

The `dev` command added to the `scripts` section of your `package.json` file executes the code in your `index.ts` file using `nodemon` and `ts-node`. To execute it, run the command below in your terminal window:

```bash
npm run dev
```

Running the command should result in console output indicating that the server is up and running, along with the port it's operating on. To interact with the application, open your web browser and navigate to `http://localhost:<YOUR_PORT>/new`, where you'll find a page containing a form for creating a new ToDo.

### Handle to-do form submission

Finally, to enable the saving of a to-do item upon form submission, insert the following code into your `index.ts` file right before the line of code that initiates the Express server:

```typescript
...
// save to-do to the database
app.post("/submit", async (req: Request, res: Response) => {
  try {
    const { title, description, dueDate, priority } = req.body;
    const values = { title, description, dueDate: new Date(dueDate), priority };
    const todo = await db.insert(todos).values(values).returning();

    res.status(201).redirect("/");
  } catch (error: any) {
    console.error(error.message);
    return res.status(500).send({
      error: `An error occurred while adding the To-Do: ${error.message}`,
    });
  }
});
...
```

The added code block above establishes a route to manage HTTP `POST` requests at the `/submit` path. When triggered, the route retrieves the submitted values for `title`, `description`, `due date`, and `priority` from the request body and assembles them into a `values` object. Within this `values` object, the supplied `due date` value is transformed into a JavaScript Date object.

The to-do item values are inserted into the `todos` table using the Drizzle database instance, and then the newly created item is retrieved by calling the `.returning()` function. During the process of inserting the to-do item values into the database, Drizzle ensures that these values align with the fields and their respective data types as defined in the schema and throws an error if they don't.

To test the creation of a to-do item, complete the form in your web browser and click the submit button. You'll be automatically redirected to your application's root URL ("/"), where you will see a `Cannot GET /` message on the page because the route hasn't been configured yet.

In the next section, you will add the logic to view your existing to-do items.

## View existing to-do items

With to-do items stored in the database, you can now retrieve and display them within the application. Begin by incorporating the following code into your `index.ts` file:

```typescript
// fetch all to-do items from the database
app.get("/", async (req: Request, res: Response) => {
  const result = await db.select().from(todos);
  res.render("index", { todos: result });
});
```

The code above defines a route for handling HTTP `GET` requests to the root path ("/"). When the root path is visited, the Drizzle ORM performs a SELECT query on the `todos` table. It fetches a list of all the records from the table and stores them in a `result` variable. After the database query is executed, the server renders an `index` view template and passes the to-do items list to the template using a `todos` key.

Next, create an `index.ejs` file in the `views` directory and add the following code to it:

```html
<!DOCTYPE html>
<html lang="en">
  <head>
    <%- include('./partials/head.ejs', {title: 'Home' }); %>
  </head>

  <body>
    <div class="container">
      <a class="link-button" href="/new">Add new To-Do</a>
      <h1>To-Do List</h1>
      <% if(todos.length) { %>
      <ul class="todo-list">
        <%todos.forEach((todo)=> {%>
        <li class="todo-item">
          <span class="task"
            ><a href="/todo/<%= todo.id %>"><%= todo.title %></a></span
          >
          <span class="priority <%= todo.priority %>"
            >Priority: <%= todo.priority %></span
          >
          <% if(!todo.completedAt) { %>
          <span class="due-date"
            >Due on: <%= todo.dueDate.toLocaleDateString('en-GB') %></span
          >
          <%} else { %>
          <span class="due-date">Completed</span>
          <%} %>
        </li>
        <%});%>
      </ul>
      <%} %>
    </div>
  </body>
</html>
```

The code added to the `index.ejs` file encompasses the HTML structure for displaying the to-do items provided to the `index` view through the `GET / route` handler. The `head.ejs` template is included in the page's `head` section, and a `div` is created in the `body` section to display to-do items. When the array of to-do items received from the server contains elements, a loop iterates through each to-do item, rendering them as individual list items.

Each to-do list item displays the to-do's title as a clickable link to view the item. Additionally, it displays its priority and either the due date or a "Completed" string, depending on whether the to-do item has been marked as completed or not.

With the HTML structure to list to-do items now set up, open your web browser and navigate to `http://localhost:<YOUR_PORT>/`. You should observe your list of to-do items displayed on the page. In the next section, you'll add the functionality to edit to-do items.

## Update to-do items

The final functionality to be implemented is the ability to edit existing to-do items. This process involves fetching a specific to-do item from the database and subsequently updating its values as needed.

### Fetch to-do items

To begin, insert the imports below into the `index.ts` file:

```typescript
import methodOverride from "method-override";
import { eq } from "drizzle-orm";
```

The code above imports the `methodOverride` middleware function and the `eq` database operator function. Following that, include the following line of code in your `index.ts` file immediately after the line that sets up the server port:

```typescript
...
const port = process.env.PORT || 4000;
app.use(methodOverride("_method"));
...
```

The line of code added above configures the Express server to use the `method-override` middleware, which allows clients to specify an HTTP method using a query parameter or form field named `_method` in their requests, enabling HTTP request method override.

This HTTP request method override is required because, by default, HTTP forms only support `GET` and `POST` requests, and a PUT request is required to update a to-do item. With this modification, an HTTP form can initiate a `PUT` request by appending `_method=PUT` to the request URL of a `POST` request.

Next, update your `index.ts` file with the code below:

```typescript
// fetch a single to-do item from the database
app.get("/todo/:id", async (req: Request, res: Response) => {
  try {
    const { id } = req.params;
    const todo = await db
      .select()
      .from(todos)
      .where(eq(todos.id, Number(id)));

    res.render("todo", { todo: todo[0] });
  } catch (error: any) {
    console.error(error.message);
    return res.status(500).send({
      error: `An error occurred while fetching the To-Do: ${error.message}`,
    });
  }
});
```

The provided code above introduces a route handler for handling HTTP `GET` requests to the `/todo/:id` route, with `:id` indicating the unique identifier of a to-do item. Upon a successful match, the route handler retrieves the `id` parameter value from the request object and utilizes Drizzle to execute a database SELECT query, searching for the to-do item with an `id` matching the one provided in the request URL. The database query results in an array containing the corresponding to-do item, subsequently assigned to a variable named `todo`. Following the execution of the database query, the server proceeds to render a view template named `todo`, transmitting the first item from the `todo` variable to the template via the to-do key.

### Set up to-do edit view

To make the `form.ejs` template adaptable for editing to-do items, substitute the content in your `form.ejs` file with the provided code below:

```html
<form action="<%=action%>" method="POST">
  <label for="title">Title:</label>
  <input
    type="text"
    id="title"
    name="title"
    value="<%= typeof todo !== 'undefined' ? todo.title : '' %>"
    required
  />

  <label for="description">Description:</label>
  <textarea id="description" name="description" rows="4" required>
<%= typeof todo !== 'undefined' ? todo.description : '' %></textarea
  >

  <label for="dueDate">Due Date:</label>
  <input type="date" id="dueDate" name="dueDate"
  value="<%= typeof todo !== 'undefined' ? todo.dueDate.toISOString().split('T')[0] : '' %>"
   />

  <%if(action != '/submit') { %>
  <label for="status">Status:</label>
  <select id="status" name="status" required>
    <% ["To-Do", "In Progress", "Completed"].forEach((status) => { %>
    <option
    value="<%= status %>"
    <%= typeof todo !== 'undefined' && todo.status === status ? 'selected' : '' %>
    ><%= status %></option>
    <%})%>
  </select>
  <% } %>

  <label for="priority">Priority:</label>
  <select id="priority" name="priority" required>
    <% ["High", "Medium", "Low"].forEach((priority) => { %>
      <option
      value="<%= priority %>"
      <%= typeof todo !== 'undefined' && todo.priority === priority ? 'selected' : '' %>
      ><%= priority %></option>
      <%})%>
  </select>

  <button type="submit">
    <%= typeof todo !== 'undefined' ? 'Update To-Do' : 'Add To-Do' %>
  </button>
</form>
```

The `form.ejs` partial's HTML structure has been modified to include code that auto-fills the form with values for the to-do item data intended for editing. The updated partial now requires a `todo` parameter, which refers to the to-do item scheduled for editing and uses its values to populate the corresponding form fields.

Next, to create a page that displays the `form.ejs` template for editing to-do items, create a new file named `todo.ejs` within the `views` directory and insert the following code into it:

```html
<!DOCTYPE html>
<html lang="en">
  <head>
    <%- include('./partials/head.ejs', {title: 'To-Do' }); %>
  </head>

  <body>
    <div class="container">
      <a class="link-button" href="/">Home</a>
      <h1>Edit a To-Do</h1>
      <%- include('./partials/form.ejs', {action:
      `/submit/${todo.id}?_method=PUT`, todo: todo }); %>
    </div>
  </body>
</html>
```

The code added above contains HTML markup for editing to-do items, and as with the other pages, the `head.ejs` partial is rendered in the `head` section of the page. The `body` section of the page contains a `div` element that renders the now modified `form.ejs` partial. The code passes the `todo` object retrieved from the `GET /todo/:id` route handler to the `form.ejs` template along with the form action attribute, whose value is `/submit/${todo.id}?_method=PUT`.

The presence of the `_method=PUT` query parameter within the action attribute assigned to the `form.ejs` partial signals to the server that this request should be interpreted as a `PUT` request despite being initiated as a `POST` request.

### Handle to-do update

Lastly, to save the updates to to-do items upon submission, include this code in your `index.ts` file:

```typescript
// update a to-do item
app.put("/submit/:id", async (req: Request, res: Response) => {
  try {
    const { id } = req.params;
    const { title, description, dueDate, priority, status } = req.body;
    const values = {
      title,
      description,
      dueDate: new Date(dueDate),
      priority,
      status,
      completedAt: status === "Completed" ? new Date() : null,
    };

    await db
      .update(todos)
      .set(values)
      .where(eq(todos.id, Number(id)));

    res.status(204).redirect("/");
  } catch (error: any) {
    console.error(error.message);
    return res.status(500).send({
      error: `An error occurred while updating the To-Do: ${error.message}`,
    });
  }
});
```

The provided code introduces a route handler set up to manage HTTP `PUT` requests directed to the `/submit/:id` route, where the `:id` parameter serves as a reference to the specific to-do item to be updated. The route handler retrieves the `id` parameter's value from the request's `params` object. Additionally, it extracts the to-do item's values intended for update and prepares them within a `values` object. Within this `values` object, the provided due date value is transformed into a JavaScript Date object, while the `completedAt` value is set to the current date if the submitted status is "Completed"; otherwise, it defaults to null.

Subsequently, the Drizzle database instance locates the to-do item within the database that corresponds to the provided `id` value and proceeds to update its columns with the prepared to-do values. Upon successful updating, the server redirects to the root path; otherwise, it returns an error message with a 500 status code.

To test the to-do update feature, click on the title link of a to-do item from your root page. This action will lead you to the to-do editing page. After making the desired modifications to the to-do values, upon submission, you will be redirected to the root route, where you can observe the updated to-do item.

That's it! You have successfully created a to-do application that saves items to the database without requiring a complicated database setup. In the next section, you'll deploy the application online with ease.

## Deploy to Koyeb

In this section, we will deploy the to-do application on Koyeb. As the database is already set up on Turso, there is no need for additional setup to utilize the database during the app's deployment.

In preparation for deploying the demo app, modify the `scripts` section in your `package.json` file to incorporate the following lines:

```json
...
"scripts": {
  "test": "echo \"Error: no test specified\" && exit 1",
  "schema:gen": "drizzle-kit generate:sqlite --out src/db/migrations --schema src/db/schema.ts",
  "schema:migrate": "ts-node src/db/migrate",
  "dev": "nodemon --watch './**/*.ts' --exec ts-node ./src/index.ts",
  "build": "npm run schema:gen && npm run schema:migrate && npx tsc",
  "start": "node src/index.js"
},
...
```

The code above adds the `build` and `start` scripts to the `package.json` file. The `build` command makes sure the database has the latest schema changes and compiles the TypeScript code in your `index.ts` file into JavaScript, generating an `index.js` file. The `start` command then runs the compiled code.

Next, create a GitHub repository for your code and execute the following commands in your terminal to commit and push your code to the repository:

```bash
git add --all
git commit -m "Complete to-do application with Drizzle and Turso."
git remote add origin git@github.com/<YOUR_GITHUB_USERNAME>/<YOUR_REPOSITORY_NAME>.git
git branch -M main
git push -u origin main
```

Within the [Koyeb control panel](https://app.koyeb.com), while on the **Overview** tab, initiate the app creation and deployment process by clicking **Create App**. On the App deployment page:

1. Select the GitHub deployment option.
2. In the repository drop-down menu, select the GitHub repository that contains your code.  Alternatively, you can deploy from the [example repository](https://github.com/koyeb/example-turso-drizzle) associated with this tutorial by entering `https://github.com/koyeb/example-turso-drizzle` in the **Public GitHub repository** field.
3. Select the specific branch you wish to deploy (e.g., `main`).
4. Ensure that the **Web Service** service type is selected.
5. Click on the **Advanced** button to view additional options and then select the **Add Variable** button.
6. For each environment variable found in your `.env` file, input the variable name, choose the **Secret** type, and in the value field, opt for the **Create secret** choice. In the form that appears, provide the secret name and its associated value, and then click the **Create** button.
7. You can optionally give your app a custom name; otherwise, the default value will be used.
8. Finally, click the **Deploy** button to initiate the deployment process.

During the app deployment process, Koyeb identifies and employs the `build` and `start` scripts as defined in your `package.json` file to build and launch the application. You can keep track of the deployment progress via the displayed logs. Once the deployment concludes and all essential health checks succeed, your application will be up and running.

To access your live to-do application, simply click on your public URL.

## Conclusion

In this tutorial, you created a to-do application and harnessed the capabilities of Drizzle and Turso to establish a versatile yet robust database configuration for the application.

Turso provides a range of additional database features, including database replication and much more. To explore how Turso can assist you with your database requirements, be sure to refer to their [documentation](https://docs.turso.tech/).

Drizzle provides a good interface for interacting with the database from our code and helps manage schema changes as the project evolves.  Check out the [Drizzle documentation](https://orm.drizzle.team/docs/overview) learn more about how to work with SQLite and other databases with your TypeScript projects.

Since the application was deployed using the Git deployment method, any new push to the deployed branch will automatically initiate a new build for your application. Updates to your application will become live once the deployment successfully clears all the necessary health checks. In case of a deployment failure, Koyeb preserves the last functional production deployment, ensuring the continuous operation of your application.