Next.js Quick Start Guide

This quick start guide will walk you through setting up a Next.js application that connects to and queries a SQLite Cloud database.


  1. Set up a SQLite Cloud account

    • If you haven’t already, sign up for a SQLite Cloud account and create a new project.
    • For this guide, we will use the sample datasets that come pre-loaded with SQLite Cloud.
  2. Create a Next.js app

    • Use create-next-app to set up a new Next.js project. The following command creates a minimal app with TypeScript and the latest App Router, keeping the focus on querying data.
npx create-next-app@latest sqlc-quickstart --ts --no-tailwind --eslint --app --src-dir --import-alias "@/*" --use-npm
  1. Install the SQLite Cloud SDK
cd sqlc-quickstart && npm install @sqlitecloud/drivers
  1. Configure the Database Connection
  • Create a .env.local file in the root of your Next.js project and add your SQLite Cloud connection string:
SQLITECLOUD_URL=sqlitecloud://abcd1234.global1.qwerty.sqlite.cloud:8860/chinook.sqlite?apikey=your-api-key
NEXT_PUBLIC_SQLITECLOUD_URL=sqlitecloud://abcd1234.global1.qwerty.sqlite.cloud:8860/chinook.sqlite?apikey=your-api-key
  • The database driver establishes a TLS connection in Node.js and a WebSocket connection in the browser.
  1. Set Up the Folder Structure
mkdir -p src/app/api/albums
mkdir -p src/app/components
mkdir -p src/constants

touch src/app/api/albums/route.ts
touch src/app/components/GetAlbumsClient.tsx
touch src/app/components/GetAlbumsServer.tsx
touch src/app/components/UpdateAlbumsClient.tsx
touch src/constants/queries.ts
touch src/types.ts
  1. Define Data Types
//
// src/type.ts (Server Component)
//

export interface Album {
  id: number;
  title: string;
  artist: string;
}
  1. Define Queries
//
// src/constants/queries.ts
//

export const GET_ALBUMS = `
  USE DATABASE chinook.sqlite; 
  SELECT albums.AlbumId AS id, albums.Title AS title, artists.Name AS artist
  FROM albums
  INNER JOIN artists ON albums.ArtistId = artists.ArtistId
  LIMIT 20;
`;

export const GET_LAST_TEN_ALBUMS = `
  USE DATABASE chinook.sqlite; 
  SELECT albums.AlbumId AS id, albums.Title AS title, artists.Name AS artist
  FROM albums
  INNER JOIN artists ON albums.ArtistId = artists.ArtistId
  ORDER BY albums.AlbumId DESC
  LIMIT 10;
`;

export const INSERT_ALBUM = `
  USE DATABASE chinook.sqlite; 
  INSERT INTO albums (Title, ArtistId) VALUES (?, ?);
`;
  1. Fetch Data via a Route Handler

You can create a route handler for handling GET and POST requests.

//
// src/app/api/albums/route.ts (Route Handler)
//

import { NextResponse } from "next/server";
import { Database } from "@sqlitecloud/drivers";
import { GET_LAST_TEN_ALBUMS, INSERT_ALBUM } from "@/constants/queries";

export async function GET() {
  let db;

  try {
    db = new Database(process.env.SQLITECLOUD_URL!);
    const result = await db.sql(GET_LAST_TEN_ALBUMS);

    return NextResponse.json(result);
  } catch (error) {
    let message = "An unknown error occurred";

    if (error instanceof Error) {
      message = error.message;
    }

    return NextResponse.json({ error: message }, { status: 500 });
  } finally {
    db?.close();
  }
}

export async function POST(req: Request) {
  const { title, artistId } = await req.json();
  let db;

  try {
    db = new Database(process.env.SQLITECLOUD_URL!);
    await db.sql(INSERT_ALBUM, ...[title, artistId]);

    return NextResponse.json({ success: true });
  } catch (error) {
    let message = "An unknown error occurred";

    if (error instanceof Error) {
      message = error.message;
    }

    return NextResponse.json({ error: message }, { status: 500 });
  } finally {
    db?.close();
  }
}
  1. Fetch Data in a Server Component

To fetch data directly from the server and render it in a Server Component:

//
// src/app/components/GetAlbumsServer.tsx (Server Component)
//

import { GET_ALBUMS } from "@/constants/queries";
import { Album } from "@/types";
import { Database } from "@sqlitecloud/drivers";
import { unstable_noStore as noStore } from "next/cache";

export default async function GetAlbumsServer() {
  noStore(); // Prevents Next.js from caching the database request
  let db;

  try {
    db = new Database(process.env.SQLITECLOUD_URL!);
    const result = await db.sql(GET_ALBUMS);

    return (
      <div>
        <h2 className="text-xl font-semibold mb-2">
          Albums (Server Component)
        </h2>
        <ul className="space-y-2">
          {result.map((album: Album) => (
            <li key={album.id} className="p-2 bg-gray-200 rounded-lg shadow-sm">
              {album.title} -{" "}
              <span className="font-medium">{album.artist}</span>
            </li>
          ))}
        </ul>
      </div>
    );
  } catch (error) {
    let message = "An unknown error occurred";

    if (error instanceof Error) {
      message = error.message;
    }
    return <p>Error loading albums: {message}</p>;
  } finally {
    db?.close();
  }
}
  1. Fetch Data in a Client Component Since the SQLite Cloud driver can run in the browser, you can use it directly in a Client Component without needing an API route.
//
// src/app/components/GetAlbumsClient.tsx (Client Component)
//

"use client";

import { useEffect, useState } from "react";
import { Database } from "@sqlitecloud/drivers";
import { Album } from "@/types";
import { GET_ALBUMS } from "@/constants/queries";

export default function GetAlbumsClient() {
  const [albums, setAlbums] = useState<Album[]>([]);
  const [error, setError] = useState<string | null>(null);

  useEffect(() => {
    async function fetchAlbums() {
      let db;
      try {
        console.log(process.env.NEXT_PUBLIC_SQLITECLOUD_URL);
        db = new Database(process.env.NEXT_PUBLIC_SQLITECLOUD_URL!);
        const result = await db.sql(GET_ALBUMS);
        setAlbums(result);
      } catch (error) {
        let message = "An unknown error occurred";

        if (error instanceof Error) {
          message = error.message;
        }
        setError(message);
      } finally {
        db?.close();
      }
    }

    fetchAlbums();
  }, []);

  if (error) return <p>Error: {error}</p>;

  return (
    <div>
      <h2 className="text-xl font-semibold mb-2">Albums (Client Component)</h2>
      {error ? (
        <p className="text-red-500">Error: {error}</p>
      ) : (
        <ul className="space-y-2">
          {albums.map((album) => (
            <li key={album.id} className="p-2 bg-gray-200 rounded-lg shadow-sm">
              {album.title} -{" "}
              <span className="font-medium">{album.artist}</span>
            </li>
          ))}
        </ul>
      )}
    </div>
  );
}
  1. Update Data in a Client Component You can also update data directly from a Client Component:
//
// src/app/components/UpdateAlbumsClient.tsx (Client Component)
//

"use client";

import { useState, useEffect } from "react";

export default function UpdateAlbumsClient() {
  const [albums, setAlbums] = useState<
    { id: number; title: string; artist: string }[]
  >([]);
  const [loading, setLoading] = useState(false);

  // Function to fetch albums from the API route
  async function fetchAlbums() {
    try {
      const res = await fetch("/api/albums");
      if (!res.ok) throw new Error("Failed to fetch albums");
      const data = await res.json();
      setAlbums(data);
    } catch (error) {
      console.error("Error fetching albums:", error);
    }
  }

  // Function to add a new album and then reload the albums list
  async function addAlbum() {
    setLoading(true);

    try {
      // Generate a random album name
      const randomAlbumTitle = `Album ${Math.random()
        .toString(36)
        .substring(7)}`;

      // Generate a random artist ID between 1 and 100
      const randomArtistId = Math.floor(Math.random() * 100) + 1;

      const res = await fetch("/api/albums", {
        method: "POST",
        headers: { "Content-Type": "application/json" },
        body: JSON.stringify({
          title: randomAlbumTitle,
          artistId: randomArtistId,
        }),
      });

      if (!res.ok) throw new Error("Failed to add album");

      await fetchAlbums(); // Refresh album list after adding
    } catch (error) {
      console.error("Error adding album:", error);
    } finally {
      setLoading(false);
    }
  }

  // Fetch albums when component mounts
  useEffect(() => {
    fetchAlbums();
  }, []);

  return (
    <div className="">
      <button
        onClick={addAlbum}
        disabled={loading}
        className="bg-blue-500 text-white px-4 py-2 rounded-lg shadow-md hover:bg-blue-600 transition disabled:bg-gray-400"
      >
        {loading ? "Adding..." : "Add Album"}
      </button>

      <h2 className="text-xl font-semibold mt-4">Latest Albums</h2>
      {albums.length === 0 ? (
        <p className="text-gray-500">No albums found.</p>
      ) : (
        <ul className="mt-2 space-y-2">
          {albums.map((album) => (
            <li key={album.id} className="p-2 bg-gray-200 rounded-lg shadow-sm">
              {album.title} -{" "}
              <span className="font-medium">{album.artist}</span>
            </li>
          ))}
        </ul>
      )}
    </div>
  );
}
  1. Create a Page to Display Components

Replace the content of page.tsx with:

//
// src/app/page.tsx (Unified Page)
//

import GetAlbumsClient from "./components/GetAlbumsClient";
import GetAlbumsServer from "./components/GetAlbumsServer";
import UpdateAlbumsClient from "./components/UpdateAlbumsClient";

export default function page() {
  return (
    <div className="min-h-screen bg-gray-100 text-gray-900 p-6">
      <div className="max-w-3xl mx-auto bg-white shadow-lg rounded-lg p-6">
        <h1 className="text-2xl font-bold mb-4 text-center">Albums Overview</h1>

        <div className="border-b pb-4 mb-4">
          <UpdateAlbumsClient />
        </div>

        <div className="border-b pb-4 mb-4">
          <GetAlbumsServer />
        </div>

        <div className="border-b pb-4 mb-4">
          <GetAlbumsClient />
        </div>
      </div>
    </div>
  );
}

Replace the content of layout.tsx with:

//
// src/app/layout.tsx
//

export default function RootLayout({
  children,
}: {
  children: React.ReactNode;
}) {
  return (
    <html lang="en">
      <head>
        {/* ✅ Add Tailwind CDN */}
        <link
          rel="stylesheet"
          href="https://cdn.jsdelivr.net/npm/tailwindcss@2.2.19/dist/tailwind.min.css"
        />
      </head>
      <body className="bg-gray-100 text-gray-900">{children}</body>
    </html>
  );
}
  1. Run Your App
npm run dev
  1. View Your App
  • Open your browser and navigate to the provided localhost link to see your app in action.

Congratulations! You’ve successfully built a Next.js app that interacts with a SQLite Cloud database.