Create a todo list with Turso and Next.js

A tutorial about creating a todo list app using Next.js and Turso, an Edge database based on SQLite.

Creating a simple todo list is always an effective way to get started with new technologies. In this article, we will see how to build a to-do list application using Turso, a database built by the team behind libSQL that offers the developer experience of SQLite at the Edge, and the popular frontend framework Next.js.

In the process, we’ll see how easily Turso can be integrated with Next.js to create applications.

This todo list app will allow a user to add a task to a list of todo items and delete it if it’s completed or not required anymore.

Getting Started

Turso is currently available in private beta, and you can sign up here if you haven’t already. Once you have the access to private beta, only then will you be able to use it for building the project.

To follow along with the tutorial, here is the GitHub repo of the project. You can clone the repo and follow the next instructions.

Installing Turso

To get started, run the following command in your terminal to download the Turso CLI.

# On macOS or Linux with Homebrew
brew install chiselstrike/tap/turso

# Manual scripted installation
curl -sSfL https://get.tur.so/install.sh | bash

Once installed, you need to log into the Turso CLI.

$ turso auth login

When setting this up for the first time, you’ll be asked to permit Turso to use your GitHub account.

Next, you will be redirected to the Github authentication page. Sign in with your GitHub account.

Setting up the database

Create a database named “todo” and use the Turso shell to issue statements to the database

# Create the database
$ turso db create todo

# Open the database on the Turso shell
$ turso db shell todo

Create a table in the database
Now that we have the shell running, let’s create a table to store the tasks. The following SQL query will create a table named todoswith two columns, id, and text.

Copy and execute the following statement in the shell.

CREATE TABLE IF NOT EXISTS todos (
  id integer primary key,
  text text not null
);

Quit the Shell

.quit

Get the unique Turso Database URL using the following command, and store it for later use.

turso db show --url todo

Setting up the project

Once you’ve cloned the above repo, this is how your project will look locally.

├── README.md
├── next.config.js
├── node_modules
├── package.json
├── yarn-error.log
├── yarn.lock
└──src 
|── pages
│   ├── _app.js
│   ├── _document.js
│   ├── api
│   │   ├── turso.js
│   │   └── todos
│   │       ├── add.js
│   │       ├── delete.js
│   │       └── index.js
│   └── index.js
├── styles
│   ├── Home.module.css
│   └── global.css
└── utils
    └── index.js

Rename the .env_sapmle to .env and assign the database URL we obtained previously to the NEXT_PUBLIC_DB_URL environmental variable.

NEXT_PUBLIC_DB_URL={Turso Database URL}

Next.js comes with built-in support for environment variables, so no more setup is needed to use the above variable within our project.

In the file src/pages/api/turso.js, we will export a new database client instance db and import it whenever we need to perform database transactions.

import { createClient } from "@libsql/client";

const config = {
  url: process.env.NEXT_PUBLIC_DB_URL,
};

const db = createClient(config);

module.exports = {
  db
};

Add and Delete todo tasks

Inside thesrc/pages/api/todos/add.js file, let’s implement logic that adds a task to the Todo List while making sure empty submissions are validated against.

import { db } from '../turso'

export default async function handler(req, res) {
  
  if (req.method !== "POST") {
    res.status(403).json({
      message: "Only supports POST method!",
    });
    return;
  }

  const { text } = req.body;

  if (!text) {
    res.status(400).json({
      message: "Fields cannot be empty",
    });
    return;
  }

  // Inserting tasks into ‘todos’ table
  try {
    // Perform the query
    await db.execute("INSERT INTO todos (text) VALUES (?)", [text]);

    // Handle valid query result
    res.status(201).send("todo created successfully");
  } catch (err) {
    // Handle query error
    console.error(err);
    res.status(500).send("Internal Server Error");
  }
}

In the src/pages/api/todos/delete.js file, write the following SQL query to delete a todo from the database.

import { db } from '../turso'

export default async function handler(req, res) {

 const { id } = req.body;

 if (req.method !== "POST") {
  res.status(403).json({
   message: "Only supports POST method!",
  });
  return;
 }
 const todo = await db.execute(`SELECT * FROM todos WHERE id=?`, [
  id,
 ]);

 if (todo.rows?.length === 0) {
  res.status(400).json({ 
   message: "Unable to find todo" 
  });
  return;
 }

 try {
  // Perform the query
  await db.execute(
   `
   DELETE FROM todos WHERE id=?
   `,
   [id]
  );
  // Handle valid query result
  res.status(201).send('todo deleted successfully');
 } catch (err) {
  // Handle query error
  res.status(500).send("Internal server error");
 };
}

Fetching todo tasks

In the src/pages/api/todos/index.js file, we are only allowing HTTP GET requests from the front-end to fetch the todo list data queried from the database.

import { serializeData } from "../../../utils";
import { db } from '../turso'

export default async function handler(req, res) {

 if (req.method !== "GET") {
  res.status(403).json({
   message: "Only supports GET method!"
  });
 }

 // Fetching tasks from ‘todos’ table
 try {
  const todos = await db.execute(
   `select * from todos`
  );
  const result = serializeData(todos);
  res.status(200).json({
   data: result ? result : [],
  });
 } catch (err) {

  // Handle query error
  console.error(err);
  res.status(500).send('Internal Server Error');
 };
}

The serializeData(todos) is the function we created to convert the queried data object into an array of objects, where each object in the array represents a row of data.

Making API requests from the client

We have the todo list ready but it’s of no use if the web application is not displaying anything, right? Thus, we’ll use Axios to make HTTP requests from the client to the server, so that we can display the todo list on our web application.

In the src/pages/index.js file we will make basic CRUD requests using Axios.

import axios from 'axios';

//Add a todo task
const addTodo = async (todo) => {
 if(!todo) {
   alert("Input is empty")
  return
 }
 await axios.post("/api/todos/add", {
  text: todo
 })
}

//Submit a todo task
const handleSubmit = async (e) => {
  e.preventDefault()

  await addTodo(userInput)
  await getTodos()
  setUserInput('')
}

//Delete a todo task
const handleDelete = async (id) => {
   await axios.post(`/api/todos/delete`, {
    id: id
  })
   await getTodos()
}

Congratulations, now you have a fully functional app built on Next.js and Turso. Further, you can try to add more features or explore the database functionality.

For precedence, I’m adding the link to the GitHub repo of the project again. You can either use the same code or build a new project using this article and the source code for reference.

Turso is currently available in private beta, and you sign up here if you haven’t already.
And please do let us know on Discord and Twitter if you build something cool with Turso!