Build and Run a Web App using Turso, Drizzle ORM, and Express on Koyeb

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 optimisation, 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 centralised 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 is a geographically distributed, edge-hosted database system based on libSQL, an SQLite fork. It strategically positions data in proximity to the code that interacts with it, thereby minimising 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, a TypeScript ORM for SQL databases.

You can deploy and preview the to-do application from this guide using the Deploy to Koyeb button below:

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).

You can consult the repository on GitHub 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.
A Koyeb 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:

Set up the database with Turso
Set up the project
Set up the database connection
Add new to-do items
View existing to-do items
Update to-do items
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 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:

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:

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:

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:

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:

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:

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:

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:

{
  "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:

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:

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.

import { sql } from 'drizzle-orm'
import { integer, sqliteTable, text, uniqueIndex } from 'drizzle-orm/sqlite-core'

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:

import { createClient } from '@libsql/client'
import 'dotenv/config'
import { drizzle } from 'drizzle-orm/libsql'

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:

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:

...
"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:

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:

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:

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:

import bodyParser from 'body-parser'
import 'dotenv/config'
import express, { Express, Request, Response } from 'express'
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:

<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:

<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:

<!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:

"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:

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:

...
// 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:

// 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:

<!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:

import { eq } from 'drizzle-orm'
import methodOverride from 'method-override'

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:

...
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:

// 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 utilises 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:

<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:

<!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:

// 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 utilise 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:

...
"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:

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, while on the Overview tab, click Create Web Service to begin:

Choose the GitHub deployment option.
In the repository drop-down menu, select the repository that contains your code. Alternatively, you can deploy from the example repository associated with this tutorial by entering https://github.com/koyeb/example-turso-drizzle in the Public GitHub repository field.
In the Environment variables section, click the Add Variable button. 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.
Choose a name for your App and Service and click Deploy.

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.

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