Build and Run a Web App using Turso, Drizzle ORM, and Express on Koyeb
28 minIntroduction
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:
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:
Executing the command above should yield an output similar to the one below:
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:
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:
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:
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:
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:
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:
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:
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:
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.
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
anddescription
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
andupdatedAt
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:
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:
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:
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:
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:
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:
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:
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:
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:
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:
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:
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:
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:
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:
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:
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:
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:
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:
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:
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:
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:
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:
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:
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.