Build a Searchable Blog Backend with MySQL and TypeScript

npm i -g layerbase
lbase create mysql-blog -e mysql --start
lbase url mysql-blog

mysql://root@127.0.0.1:3306/mysql_blog

mkdir mysql-blog-backend
cd mysql-blog-backend
pnpm init
pnpm add mysql2
pnpm add -D tsx typescript

import mysql, {
  type ResultSetHeader,
  type RowDataPacket,
} from 'mysql2/promise'

type AuthorRow = RowDataPacket & {
  id: number
  name: string
}

type PostRow = RowDataPacket & {
  title: string
  author: string
}

type SearchRow = RowDataPacket & {
  title: string
  relevance: number
}

const rawUrl =
  process.env.MYSQL_URL ?? 'mysql://root@127.0.0.1:3306/test'
const connectionUrl = new URL(rawUrl)
const isLocal = ['127.0.0.1', 'localhost'].includes(
  connectionUrl.hostname,
)

const db = await mysql.createConnection({
  host: connectionUrl.hostname,
  port: Number(connectionUrl.port || 3306),
  user: decodeURIComponent(connectionUrl.username),
  password: decodeURIComponent(connectionUrl.password),
  database: connectionUrl.pathname.slice(1) || 'test',
  ssl: isLocal ? undefined : { rejectUnauthorized: true },
})

// These names are reserved for this tutorial and are replaced on each run.
await db.execute('DROP TABLE IF EXISTS tutorial_posts')
await db.execute('DROP TABLE IF EXISTS tutorial_authors')

await db.execute(`
  CREATE TABLE tutorial_authors (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE
  )
`)

await db.execute(`
  CREATE TABLE tutorial_posts (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    author_id INT UNSIGNED NOT NULL,
    title VARCHAR(255) NOT NULL,
    body TEXT NOT NULL,
    status ENUM('draft', 'published') NOT NULL DEFAULT 'draft',
    published_at DATETIME NULL,
    CONSTRAINT fk_tutorial_post_author
      FOREIGN KEY (author_id)
      REFERENCES tutorial_authors(id),
    INDEX idx_tutorial_author_status (author_id, status),
    FULLTEXT INDEX ft_tutorial_title_body (title, body)
  )
`)

const authors = [
  { name: 'Alice Chen', email: 'alice@example.com' },
  { name: 'Marcus Rivera', email: 'marcus@example.com' },
  { name: 'Priya Sharma', email: 'priya@example.com' },
]

await db.beginTransaction()

try {
  const authorIds = new Map<string, number>()

  for (const author of authors) {
    const [result] = await db.execute<ResultSetHeader>(
      `
        INSERT INTO tutorial_authors (name, email)
        VALUES (?, ?)
      `,
      [author.name, author.email],
    )
    authorIds.set(author.name, result.insertId)
  }

  const posts = [
    {
      author: 'Alice Chen',
      title: 'Choose the Right Database Index',
      body: 'A practical guide to B-tree indexes, query plans, and avoiding unnecessary database indexes.',
      status: 'published',
      publishedAt: '2026-07-10 09:00:00',
    },
    {
      author: 'Alice Chen',
      title: 'Connection Pooling in Node.js',
      body: 'Reuse MySQL connections without opening a new connection for every request.',
      status: 'published',
      publishedAt: '2026-07-15 09:00:00',
    },
    {
      author: 'Marcus Rivera',
      title: 'A Draft About Background Jobs',
      body: 'Notes for a future article about workers and retry policies.',
      status: 'draft',
      publishedAt: null,
    },
    {
      author: 'Priya Sharma',
      title: 'Debug Slow SQL Queries',
      body: 'Use query plans and indexes to find database work that scans too many rows.',
      status: 'published',
      publishedAt: '2026-07-20 09:00:00',
    },
    {
      author: 'Priya Sharma',
      title: 'Use JSON Columns Carefully',
      body: 'Keep frequently joined and filtered values in regular columns, not opaque metadata.',
      status: 'published',
      publishedAt: '2026-07-18 09:00:00',
    },
  ] as const

  for (const post of posts) {
    await db.execute(
      `
        INSERT INTO tutorial_posts
          (author_id, title, body, status, published_at)
        VALUES (?, ?, ?, ?, ?)
      `,
      [
        authorIds.get(post.author),
        post.title,
        post.body,
        post.status,
        post.publishedAt,
      ],
    )
  }

  await db.commit()
  console.log(`Inserted ${authors.length} authors and ${posts.length} posts`)
} catch (error) {
  await db.rollback()
  throw error
}

const [published] = await db.execute<PostRow[]>(`
  SELECT
    p.title,
    a.name AS author
  FROM tutorial_posts p
  JOIN tutorial_authors a ON a.id = p.author_id
  WHERE p.status = 'published'
  ORDER BY p.published_at DESC, p.id
`)

console.log('\nPublished posts:')
for (const post of published) {
  console.log(`  ${post.title} by ${post.author}`)
}

const searchTerm = 'database indexes'
const [searchResults] = await db.execute<SearchRow[]>(
  `
    SELECT
      title,
      MATCH(title, body)
        AGAINST (? IN NATURAL LANGUAGE MODE) AS relevance
    FROM tutorial_posts
    WHERE status = 'published'
      AND MATCH(title, body)
        AGAINST (? IN NATURAL LANGUAGE MODE)
    ORDER BY relevance DESC, title
  `,
  [searchTerm, searchTerm],
)

console.log(`\nSearch results for "${searchTerm}":`)
for (const result of searchResults) {
  console.log(`  ${result.title}`)
}

await db.end()

MYSQL_URL="$(lbase url mysql-blog)" pnpm tsx blog.ts

Inserted 3 authors and 5 posts

Published posts:
  Debug Slow SQL Queries by Priya Sharma
  Use JSON Columns Carefully by Priya Sharma
  Connection Pooling in Node.js by Alice Chen
  Choose the Right Database Index by Alice Chen

Search results for "database indexes":
  Choose the Right Database Index
  Debug Slow SQL Queries

MYSQL_URL="mysql://user:password@your-host.cloud.layerbase.dev:PORT/database" \
pnpm tsx blog.ts