Building a Minimal Blog with Query

This tutorial will guide you through creating a basic blog application using Query. You'll see how Query simplifies the development process by handling the database, backend, and frontend in one cohesive system.

Step 1: Set up a new Query project

First, create a new Query project:

pnpm dlx @qery/query create

Select the minimal project template and follow the prompts to create a new project. The minimal template provides a simple starting point for building simple web applications with Query.

Step 2: Define the database schema

Query uses SQLite databases for data storage. SQLite is a lightweight, file-based database well-suited for small—to medium-sized applications. It provides a simple and efficient way to store and retrieve data without the need for a separate database server.

In this example, we'll define the schema for our blog application using SQLite. Create a file migrations/blog.sql with the following contents:

CREATE TABLE IF NOT EXISTS post (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    content TEXT NOT NULL,
    created_at INTEGER DEFAULT (strftime('%s', 'now'))
);

This SQLite statement creates a table named post with the following columns:

id: An auto-incrementing integer that serves as the primary key for each post.
title: A text column to store the post's title.
content: A text column to store the content of the post.
created_at: An integer column to store the creation timestamp of the post, defaulting to the current Unix timestamp.

You can define your database schema using SQL files and manage its evolution over time by creating additional migration files.

Step 3: Create the views

In this project, we are using a combination of HTML and TypeScript files for server-side rendering. HTML files define the static structure of the page, while TypeScript files handle dynamic content and data fetching.

Create a file src/pages/index.html:

<div>
  <h1>My Blog</h1>

  <a href="/new">Create New Post</a>
</div>

<main>
  <!-- POSTS -->
</main>

Create a file src/pages/get.index.ts:

import { Database } from "query:database";

import html from "./index.html";

interface Post {
    title: string;
    content: string;
    created_at: number;
}

export async function handleRequest(_: Request) {
    const db = new Database("blog.sql");
    const posts: Post[] = db.query("SELECT * FROM post ORDER BY created_at DESC");

    const body = html.replace("<!-- POSTS -->", generatePostsHTML(posts));

    return new Response(body, {
        status: 200,
        headers: {
            "Content-Type": "text/html; charset=utf-8",
        },
    });
}

function generatePostsHTML(posts: Post[]) {
    return posts
        .map((post) => `
            <article>
                <h2>${post.title}</h2>
                <p>${post.content}</p>
                <p>Posted on ${new Date(post.created_at * 1000).toLocaleString()}</p>
            </article>
        `,
        ).join("");
}

In the get.index.ts file, we import the Database class from query:database to interact with the SQLite database. Inside the handleRequest function, we create a new instance of the Database class and use the query method to fetch all posts from the post table, ordering them by the created_at column in descending order.

The generatePostsHTML function takes the fetched posts and generates the corresponding HTML markup for each post. The generated HTML is then used to replace the  placeholder in the HTML template.

Finally, the handleRequest function returns a Response object with the generated HTML as the body and the appropriate headers.

Create files src/pages/new/index.html and src/pages/new/get.index.ts:

<h1>Create New Post</h1>

<form action="/api/post" method="POST">
    <input type="text" name="title" placeholder="Title" required>
    <textarea name="content" placeholder="Content" required></textarea>
    <button type="submit">Create Post</button>
</form>

import html from "./index.html";

export async function handleRequest(_: Request) {
    return new Response(html, {
        status: 200,
        headers: {
            "Content-Type": "text/html; charset=utf-8",
        },
    });
}

The src/pages/new/get.index.ts file is another TypeScript file that handles the rendering of the "Create New Post" page. It simply returns the HTML content imported from index.html as the response.

Step 4: Create API endpoints

In Query, API endpoints are defined using JavaScript or TypeScript files with a specific naming convention. The file name prefix determines the HTTP method (delete, get, patch, post, put), and the folder structure defines the route.

Create a file src/api/post/post.index.ts:

import { Database } from "query:database";

export async function handleRequest(req: Request) {
    const formData = await req.formData();
    const title = formData.get("title") as string;
    const content = formData.get("content") as string;

    const db = new Database("blog.sql");
    db.query("INSERT INTO post (title, content) VALUES (?, ?)", [title, content]);

    const url = new URL(req.url);
    return Response.redirect(`${url.origin}/`, 303);
}

The handleRequest function is Query's entry point for each function route. It receives a Request object as a parameter and returns a Response. The Request object contains information about the incoming HTTP request, such as the URL, headers, and body. You can use this information to process the request and generate an appropriate response.

Inside the handleRequest function, you can access the database using the Database class imported from query:database. To create a new instance of the Database class, you need to provide the name of the database file. We use blog.sql as the database file name in this example.

The Database class provides a query method that allows you to execute SQL queries against the SQLite database. You can use placeholders (?) in your SQL queries to prevent SQL injection attacks. The placeholder values are passed as an array in the second argument of the query method.

Step 5: Run the application

Start the Query development server:

query dev

Visit http://localhost:3000 in your browser to see your blog in action!

Conclusion

This example shows how Query simplifies web development by providing an integrated approach to handling databases, API routes, and server-side rendering.

Query uses SQLite databases, which are lightweight, file-based databases that are easy to set up and use. You can define your database schema using SQL files and control their version with your application code.

The handleRequest function is the entry point for each API route and allows you to process incoming requests and generate appropriate responses. By importing the Database class from query:database, you can easily interact with the SQLite database and execute SQL queries using the query method.

Query's combination of HTML and TypeScript enables you to define the static structure of your pages and handle dynamic content and data fetching. The handleRequest function in TypeScript files can access the database, fetch data, and generate the final HTML response.

With Query, you can focus on building your application's functionality without worrying about setting up and managing separate services for the database, backend, and frontend.

Blog