Using LibSQL with React and Next.js: A Step-by-Step Tutorial

In this tutorial, we'll guide you through integrating LibSQL with a React and Next.js application.

What is LibSQL?

LibSQL is a lightweight, efficient SQL database solution designed for modern web applications. It's open-source, highly customizable, and offers great performance, making it an excellent choice for developers looking to build robust, scalable apps.

Let's dive into the setup process and explore how to connect LibSQL to a Next.js project, complete with code samples for basic CRUD operations.

Prerequisites

Before we begin, ensure you have the following:

• Node.js and npm installed
• A LibSQL server set up (we’ll use Docker for a local instance)
• Basic knowledge of React and Next.js

Step 1: Setting Up LibSQL

First, let’s set up a LibSQL database using Docker. This will allow us to run a local instance easily.

Connect to the LibSQL database using psql:

psql -h localhost -U postgres

Now, create a database and a table for your application:

CREATE DATABASE myapp;
\c myapp
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100)
);

Run LibSQL using Docker:Open your terminal and run the following command:

docker run --name libsql -d -p 5432:5432 libsql/libsql

Step 2: Setting Up a Next.js Project

Next, we'll create a new Next.js project and install the necessary packages.

Install the pg package for PostgreSQL:

npm install pg

Create a new Next.js project:Run the following commands in your terminal:

npx create-next-app@latest my-next-app
cd my-next-app

Step 3: Connecting Next.js to LibSQL

We'll create a connection to the LibSQL database in our Next.js project.

Create a lib directory and add a db.js file:

// lib/db.js
const { Pool } = require('pg');

const pool = new Pool({
    user: 'postgres',
    host: 'localhost',
    database: 'myapp',
    password: 'yourpassword',
    port: 5432,
});

module.exports = {
    query: (text, params) => pool.query(text, params),
};

Step 4: Creating API Routes

Next, we’ll create API routes in Next.js to handle CRUD operations.

Create an API route to handle CRUD operations:

// pages/api/users.js
import db from '../../lib/db';

export default async (req, res) => {
    const { method } = req;

    switch (method) {
        case 'GET':
            try {
                const result = await db.query('SELECT * FROM users');
                res.status(200).json(result.rows);
            } catch (error) {
                res.status(500).json({ error: 'Database error' });
            }
            break;
        case 'POST':
            const { name, email } = req.body;
            try {
                const result = await db.query(
                    'INSERT INTO users (name, email) VALUES ($1, $2) RETURNING *',
                    [name, email]
                );
                res.status(201).json(result.rows[0]);
            } catch (error) {
                res.status(500).json({ error: 'Database error' });
            }
            break;
        case 'PUT':
            const { id, newName, newEmail } = req.body;
            try {
                const result = await db.query(
                    'UPDATE users SET name = $1, email = $2 WHERE id = $3 RETURNING *',
                    [newName, newEmail, id]
                );
                res.status(200).json(result.rows[0]);
            } catch (error) {
                res.status(500).json({ error: 'Database error' });
            }
            break;
        case 'DELETE':
            const { deleteId } = req.body;
            try {
                await db.query('DELETE FROM users WHERE id = $1', [deleteId]);
                res.status(204).json();
            } catch (error) {
                res.status(500).json({ error: 'Database error' });
            }
            break;
        default:
            res.setHeader('Allow', ['GET', 'POST', 'PUT', 'DELETE']);
            res.status(405).end(`Method ${method} Not Allowed`);
    }
};

Step 5: Creating the Frontend

Let's build the frontend in React to interact with our API.

Include the component in your Next.js page:

// pages/index.js
import UserList from '../components/UserList';

const Home = () => {
    return (
        <div>
            <UserList />
        </div>
    );
};

export default Home;

Create a new component to display and manage users:

// components/UserList.js
import { useState, useEffect } from 'react';

const UserList = () => {
    const [users, setUsers] = useState([]);
    const [name, setName] = useState('');
    const [email, setEmail] = useState('');

    useEffect(() => {
        fetchUsers();
    }, []);

    const fetchUsers = async () => {
        const res = await fetch('/api/users');
        const data = await res.json();
        setUsers(data);
    };

    const addUser = async () => {
        await fetch('/api/users', {
            method: 'POST',
            headers: {
                'Content-Type': 'application/json',
            },
            body: JSON.stringify({ name, email }),
        });
        setName('');
        setEmail('');
        fetchUsers();
    };

    return (
        <div>
            <h1>User List</h1>
            <ul>
                {users.map(user => (
                    <li key={user.id}>{user.name} - {user.email}</li>
                ))}
            </ul>
            <input
                type="text"
                placeholder="Name"
                value={name}
                onChange={(e) => setName(e.target.value)}
            />
            <input
                type="email"
                placeholder="Email"
                value={email}
                onChange={(e) => setEmail(e.target.value)}
            />
            <button onClick={addUser}>Add User</button>
        </div>
    );
};

export default UserList;

End note

Congratulations! You’ve set up a Next.js application with LibSQL, created API routes to handle CRUD operations, and built a simple frontend component to interact with your database.

This foundation can now be expanded upon and customized to fit your project's specific needs. With the power of LibSQL and the flexibility of Next.js, you’re well-equipped to build efficient and scalable web applications.