LayerbaseLayerbase

Getting Started with MySQL

MySQLDatabasesSQL

MySQL is the world's most popular open-source relational database. WordPress, Shopify, GitHub, and countless others run on it. It's fast at read-heavy workloads, simple to operate, and supported by every language, framework, and hosting provider you'll ever touch. If you need a relational database for a web app, MySQL is a safe, proven default.

We'll build a blog platform schema in one TypeScript file: authors, posts with JSON metadata, full-text search, and analytical queries using CTEs. Run everything locally, or provision a managed instance on Layerbase Cloud to skip setup.

Contents

Create a MySQL Instance

Local with SpinDB

SpinDB lets you run MySQL locally without Docker or manual installs. (What is SpinDB?)

Install SpinDB globally:

bash
npm i -g spindb    # npm
pnpm add -g spindb # pnpm

Or run it directly without installing:

bash
npx spindb create mysql1 -e mysql --start  # npm
pnpx spindb create mysql1 -e mysql --start # pnpm

If you installed globally, create and start a MySQL instance:

bash
spindb create mysql1 -e mysql --start

SpinDB downloads the MySQL binary, configures it, and starts the server. Verify it's running:

bash
spindb url mysql1
text
mysql://root@127.0.0.1:3306

Leave the server running.

Layerbase Cloud

Don't want to run anything locally? Layerbase Cloud gives you a managed MySQL instance in a few clicks. Connection details are in the Quick Connect panel.

Cloud instances use TLS, so the connection code is slightly different:

typescript
const db = await mysql.createConnection({
  host: 'cloud.layerbase.dev',
  port: 3306,
  user: 'your_user',
  password: 'your_password',
  database: 'your_database',
  ssl: { rejectUnauthorized: true },
})

Everything else in this guide works identically whether you're running locally or on Layerbase Cloud. Just swap in your connection details.

Set Up the Project

bash
mkdir mysql-blog-platform && cd mysql-blog-platform
pnpm init
pnpm add mysql2
pnpm add -D tsx typescript

Create a file called blog.ts. All the code in this post goes into that one file.

The Blog Platform Dataset

Five authors, 10 posts, 15 comments. This kind of content-heavy, read-heavy workload is exactly where MySQL excels:

typescript
const authors = [
  { name: 'Alice Chen', email: 'alice@example.com', bio: 'Backend engineer who writes about databases and distributed systems.' },
  { name: 'Marcus Rivera', email: 'marcus@example.com', bio: 'Full-stack developer and open source contributor.' },
  { name: 'Priya Sharma', email: 'priya@example.com', bio: 'DevOps engineer focused on infrastructure automation.' },
  { name: 'James Okafor', email: 'james@example.com', bio: 'Technical writer covering cloud-native architecture.' },
  { name: 'Sara Kim', email: 'sara@example.com', bio: 'Frontend engineer exploring the intersection of design and performance.' },
]

const posts = [
  { author_id: 1, title: 'Understanding Database Indexes', slug: 'understanding-database-indexes', content: 'Database indexes are data structures that speed up read queries at the cost of slower writes. A B-tree index lets the database find rows without scanning the entire table. Every table should have a primary key index, and you should add secondary indexes on columns you frequently filter or sort by.', status: 'published', published_at: '2026-01-15 10:00:00', metadata: { reading_time: 8, category: 'databases', featured: true } },
  { author_id: 1, title: 'Connection Pooling in Node.js', slug: 'connection-pooling-nodejs', content: 'Opening a new database connection for every query is expensive. Connection pooling keeps a set of idle connections ready to reuse. In Node.js, most database drivers support pooling out of the box. Configure the pool size based on your expected concurrency and database limits.', status: 'published', published_at: '2026-01-22 09:00:00', metadata: { reading_time: 5, category: 'backend', featured: false } },
  { author_id: 2, title: 'Getting Started with TypeScript', slug: 'getting-started-typescript', content: 'TypeScript adds static type checking to JavaScript. It catches bugs at compile time that would otherwise appear at runtime. Start by adding a tsconfig.json to your project and renaming your files from .js to .ts. The compiler will guide you through adding type annotations.', status: 'published', published_at: '2026-02-01 11:00:00', metadata: { reading_time: 6, category: 'typescript', featured: true } },
  { author_id: 2, title: 'Building REST APIs with Express', slug: 'building-rest-apis-express', content: 'Express is the most popular Node.js web framework. Define routes, add middleware for authentication and validation, and return JSON responses. Keep your route handlers thin by moving business logic into service modules.', status: 'published', published_at: '2026-02-10 14:00:00', metadata: { reading_time: 10, category: 'backend', featured: false } },
  { author_id: 3, title: 'Docker for Local Development', slug: 'docker-local-development', content: 'Docker containers provide consistent development environments. Write a Dockerfile for your application, use docker-compose to orchestrate multiple services, and mount volumes for live code reloading. Your local setup will match production exactly.', status: 'published', published_at: '2026-02-15 08:00:00', metadata: { reading_time: 7, category: 'devops', featured: false } },
  { author_id: 3, title: 'CI/CD Pipeline Best Practices', slug: 'cicd-pipeline-best-practices', content: 'A good CI/CD pipeline runs tests on every commit, builds artifacts once, and deploys them through staging before production. Keep pipelines fast by caching dependencies and running tests in parallel. Fail early and provide clear error messages.', status: 'published', published_at: '2026-02-20 13:00:00', metadata: { reading_time: 9, category: 'devops', featured: true } },
  { author_id: 4, title: 'Microservices Communication Patterns', slug: 'microservices-communication', content: 'Microservices need reliable communication. Synchronous HTTP calls are simple but create tight coupling. Asynchronous messaging through queues like RabbitMQ or Kafka provides better resilience. Choose based on whether you need an immediate response.', status: 'published', published_at: '2026-03-01 10:00:00', metadata: { reading_time: 12, category: 'architecture', featured: false } },
  { author_id: 4, title: 'Kubernetes Networking Explained', slug: 'kubernetes-networking', content: 'Kubernetes networking connects pods, services, and the outside world. Every pod gets its own IP address. Services provide stable endpoints. Ingress controllers handle external traffic routing. Understanding this model is essential for debugging connectivity issues.', status: 'draft', published_at: null, metadata: { reading_time: 15, category: 'devops', featured: false } },
  { author_id: 5, title: 'Web Performance Optimization', slug: 'web-performance-optimization', content: 'Fast websites convert better. Start by measuring Core Web Vitals. Reduce bundle sizes with code splitting and tree shaking. Lazy load images below the fold. Use a CDN for static assets. Every millisecond of load time matters for user experience.', status: 'published', published_at: '2026-03-05 09:00:00', metadata: { reading_time: 8, category: 'frontend', featured: true } },
  { author_id: 5, title: 'Accessible Component Design', slug: 'accessible-component-design', content: 'Accessibility is not optional. Use semantic HTML elements, add ARIA labels where needed, ensure keyboard navigation works, and test with screen readers. Build accessibility into your component library from day one rather than retrofitting it later.', status: 'published', published_at: '2026-03-10 11:00:00', metadata: { reading_time: 6, category: 'frontend', featured: false } },
]

const comments = [
  { post_id: 1, author_name: 'DevDave', body: 'Great explanation of B-tree indexes. Would love a follow-up on partial indexes.' },
  { post_id: 1, author_name: 'SQLFan', body: 'This helped me understand why my queries were slow. Added an index and got a 10x improvement.' },
  { post_id: 1, author_name: 'NewbieCoder', body: 'Clear and practical. Bookmarked for reference.' },
  { post_id: 2, author_name: 'NodeNinja', body: 'Connection pooling made a huge difference in our production app.' },
  { post_id: 3, author_name: 'JSLearner', body: 'Finally a TypeScript intro that does not assume prior knowledge of static typing.' },
  { post_id: 3, author_name: 'TypeFan', body: 'One tip: enable strict mode in tsconfig from the start. It saves headaches later.' },
  { post_id: 4, author_name: 'APIBuilder', body: 'Good advice on keeping route handlers thin. Service layer pattern is underrated.' },
  { post_id: 5, author_name: 'ContainerKing', body: 'Docker compose changed my local dev workflow completely.' },
  { post_id: 5, author_name: 'DevOpsDaily', body: 'Worth mentioning that volume mounts can be slow on macOS. Use mutagen or similar tools.' },
  { post_id: 6, author_name: 'PipelinePro', body: 'Caching dependencies cut our CI time in half. Great point about parallel test execution too.' },
  { post_id: 6, author_name: 'ShipItFast', body: 'We added a staging gate after reading this. Caught two bugs before they hit production.' },
  { post_id: 7, author_name: 'ArchitectAli', body: 'Event-driven messaging adds complexity but the resilience gains are worth it.' },
  { post_id: 9, author_name: 'PerfNerd', body: 'Code splitting alone cut our initial load time by 40 percent.' },
  { post_id: 9, author_name: 'FrontendFiona', body: 'Lazy loading images was the easiest win. Should be the default for every project.' },
  { post_id: 10, author_name: 'A11yAdvocate', body: 'Thank you for making the point that accessibility is not optional. More developers need to hear this.' },
]

Connect and Create Tables

AUTO_INCREMENT handles primary keys, ENUM constrains column values, and mysql2/promise gives us async/await:

typescript
import mysql from 'mysql2/promise'

const db = await mysql.createConnection({
  host: '127.0.0.1',
  port: 3306,
  user: 'root',
  database: 'test',
})

// Clean up from previous runs
await db.execute('DROP TABLE IF EXISTS comments')
await db.execute('DROP TABLE IF EXISTS posts')
await db.execute('DROP TABLE IF EXISTS authors')

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

await db.execute(`
  CREATE TABLE posts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    author_id INT NOT NULL,
    title VARCHAR(255) NOT NULL,
    slug VARCHAR(255) NOT NULL UNIQUE,
    content TEXT NOT NULL,
    status ENUM('draft', 'published') DEFAULT 'draft',
    published_at DATETIME,
    metadata JSON,
    FOREIGN KEY (author_id) REFERENCES authors(id)
  )
`)

await db.execute(`
  CREATE TABLE comments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    post_id INT NOT NULL,
    author_name VARCHAR(100) NOT NULL,
    body TEXT NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (post_id) REFERENCES posts(id)
  )
`)

console.log('Created tables: authors, posts, comments')

Some MySQL-specific details: AUTO_INCREMENT generates sequential IDs. ENUM('draft', 'published') constrains the status column to those two values at the database level. The JSON type on metadata stores structured data you can query with SQL operators. And foreign keys enforce referential integrity, so you can never have a post pointing to a nonexistent author.

Insert the Data

MySQL's execute method supports parameterized queries with ? placeholders. Insert the data:

typescript
for (const author of authors) {
  await db.execute(
    'INSERT INTO authors (name, email, bio) VALUES (?, ?, ?)',
    [author.name, author.email, author.bio],
  )
}
console.log(`Inserted ${authors.length} authors`)

for (const post of posts) {
  await db.execute(
    'INSERT INTO posts (author_id, title, slug, content, status, published_at, metadata) VALUES (?, ?, ?, ?, ?, ?, ?)',
    [post.author_id, post.title, post.slug, post.content, post.status, post.published_at, JSON.stringify(post.metadata)],
  )
}
console.log(`Inserted ${posts.length} posts`)

for (const comment of comments) {
  await db.execute(
    'INSERT INTO comments (post_id, author_name, body) VALUES (?, ?, ?)',
    [comment.post_id, comment.author_name, comment.body],
  )
}
console.log(`Inserted ${comments.length} comments`)

Parameterized queries prevent SQL injection. Never interpolate user input into SQL strings. The ? placeholders handle escaping for you.

Query with JOINs

A small helper to run queries and print results:

typescript
async function query<T>(sql: string, label: string, params: unknown[] = []): Promise<T[]> {
  const [rows] = await db.execute(sql, params)
  const results = rows as T[]
  console.log(`\n${label}`)
  for (const row of results) {
    console.log(' ', row)
  }
  return results
}

Published Posts with Author Names

The bread-and-butter blog query: join posts with their authors and order by publication date.

typescript
await query(
  `SELECT
     p.title,
     a.name AS author,
     p.status,
     p.published_at
   FROM posts p
   JOIN authors a ON p.author_id = a.id
   WHERE p.status = 'published'
   ORDER BY p.published_at DESC
   LIMIT 5`,
  'Latest 5 published posts:',
)
text
Latest 5 published posts:
  { title: 'Accessible Component Design', author: 'Sara Kim', status: 'published', published_at: '2026-03-10 11:00:00' }
  { title: 'Web Performance Optimization', author: 'Sara Kim', status: 'published', published_at: '2026-03-05 09:00:00' }
  { title: 'Microservices Communication Patterns', author: 'James Okafor', status: 'published', published_at: '2026-03-01 10:00:00' }
  { title: 'CI/CD Pipeline Best Practices', author: 'Priya Sharma', status: 'published', published_at: '2026-02-20 13:00:00' }
  { title: 'Docker for Local Development', author: 'Priya Sharma', status: 'published', published_at: '2026-02-15 08:00:00' }

Posts with Comment Counts

Join posts with a count of their comments. LEFT JOIN ensures posts with zero comments still appear:

typescript
await query(
  `SELECT
     p.title,
     a.name AS author,
     COUNT(c.id) AS comment_count
   FROM posts p
   JOIN authors a ON p.author_id = a.id
   LEFT JOIN comments c ON c.post_id = p.id
   WHERE p.status = 'published'
   GROUP BY p.id, p.title, a.name
   ORDER BY comment_count DESC`,
  'Posts by comment count:',
)
text
Posts by comment count:
  { title: 'Understanding Database Indexes', author: 'Alice Chen', comment_count: 3 }
  { title: 'Getting Started with TypeScript', author: 'Marcus Rivera', comment_count: 2 }
  { title: 'Docker for Local Development', author: 'Priya Sharma', comment_count: 2 }
  { title: 'CI/CD Pipeline Best Practices', author: 'Priya Sharma', comment_count: 2 }
  { title: 'Web Performance Optimization', author: 'Sara Kim', comment_count: 2 }
  { title: 'Connection Pooling in Node.js', author: 'Alice Chen', comment_count: 1 }
  { title: 'Building REST APIs with Express', author: 'Marcus Rivera', comment_count: 1 }
  { title: 'Microservices Communication Patterns', author: 'James Okafor', comment_count: 1 }
  { title: 'Accessible Component Design', author: 'Sara Kim', comment_count: 1 }

JSON Columns

MySQL 8+ has native JSON columns with operators for querying structured data in SQL. -> extracts a JSON value (as JSON), ->> extracts it as plain text:

typescript
await query(
  `SELECT
     title,
     metadata->>'$.category' AS category,
     metadata->>'$.reading_time' AS reading_time,
     metadata->>'$.featured' AS featured
   FROM posts
   WHERE metadata->>'$.featured' = 'true'
   ORDER BY title`,
  'Featured posts (from JSON metadata):',
)
text
Featured posts (from JSON metadata):
  { title: 'CI/CD Pipeline Best Practices', category: 'devops', reading_time: '9', featured: 'true' }
  { title: 'Getting Started with TypeScript', category: 'typescript', reading_time: '6', featured: 'true' }
  { title: 'Understanding Database Indexes', category: 'databases', reading_time: '8', featured: 'true' }
  { title: 'Web Performance Optimization', category: 'frontend', reading_time: '8', featured: 'true' }

You can also filter by numeric JSON values:

typescript
await query(
  `SELECT
     title,
     metadata->>'$.reading_time' AS reading_time
   FROM posts
   WHERE JSON_EXTRACT(metadata, '$.reading_time') > 8
   ORDER BY JSON_EXTRACT(metadata, '$.reading_time') DESC`,
  'Long reads (over 8 minutes):',
)
text
Long reads (over 8 minutes):
  { title: 'Kubernetes Networking Explained', reading_time: '15' }
  { title: 'Microservices Communication Patterns', reading_time: '12' }
  { title: 'Building REST APIs with Express', reading_time: '10' }
  { title: 'CI/CD Pipeline Best Practices', reading_time: '9' }

JSON columns work well for semi-structured data that varies between rows: metadata, settings, feature flags. For data you filter or join on frequently, regular columns are still faster.

Full-Text Search

MySQL has built-in full-text search, no extensions needed. Add a FULLTEXT index and use MATCH ... AGAINST:

typescript
await db.execute(
  'ALTER TABLE posts ADD FULLTEXT INDEX ft_posts (title, content)',
)

await query(
  `SELECT
     title,
     MATCH(title, content) AGAINST('database indexes query') AS relevance
   FROM posts
   WHERE MATCH(title, content) AGAINST('database indexes query')
   ORDER BY relevance DESC`,
  'Full-text search for "database indexes query":',
)
text
Full-text search for "database indexes query":
  { title: 'Understanding Database Indexes', relevance: 2.43 }
  { title: 'Connection Pooling in Node.js', relevance: 0.31 }

The relevance score reflects how well each row matches. MySQL handles stemming and stop words automatically.

For finer control, boolean mode supports + (must include), - (must exclude), and * (wildcard):

typescript
await query(
  `SELECT title
   FROM posts
   WHERE MATCH(title, content) AGAINST('+typescript -express' IN BOOLEAN MODE)`,
  'Boolean search (+typescript -express):',
)
text
Boolean search (+typescript -express):
  { title: 'Getting Started with TypeScript' }

For a blog or documentation site, this is often enough. No need to bolt on a separate search engine.

Common Table Expressions

CTEs make complex queries readable by breaking them into named steps. MySQL 8+ supports them with WITH.

Post with Most Comments

typescript
await query(
  `WITH comment_counts AS (
     SELECT
       post_id,
       COUNT(*) AS total
     FROM comments
     GROUP BY post_id
   )
   SELECT
     p.title,
     a.name AS author,
     cc.total AS comments
   FROM comment_counts cc
   JOIN posts p ON p.id = cc.post_id
   JOIN authors a ON a.id = p.author_id
   ORDER BY cc.total DESC
   LIMIT 1`,
  'Post with most comments:',
)
text
Post with most comments:
  { title: 'Understanding Database Indexes', author: 'Alice Chen', comments: 3 }

Authors Ranked by Post Count

typescript
await query(
  `WITH author_stats AS (
     SELECT
       author_id,
       COUNT(*) AS post_count,
       SUM(CASE WHEN status = 'published' THEN 1 ELSE 0 END) AS published_count
     FROM posts
     GROUP BY author_id
   )
   SELECT
     a.name,
     s.post_count,
     s.published_count,
     RANK() OVER (ORDER BY s.published_count DESC) AS rank_position
   FROM author_stats s
   JOIN authors a ON a.id = s.author_id
   ORDER BY rank_position`,
  'Authors ranked by published post count:',
)
text
Authors ranked by published post count:
  { name: 'Alice Chen', post_count: 2, published_count: 2, rank_position: 1 }
  { name: 'Marcus Rivera', post_count: 2, published_count: 2, rank_position: 1 }
  { name: 'Priya Sharma', post_count: 2, published_count: 2, rank_position: 1 }
  { name: 'Sara Kim', post_count: 2, published_count: 2, rank_position: 1 }
  { name: 'James Okafor', post_count: 2, published_count: 1, rank_position: 5 }

CTEs really pay off when a query needs the same subresult multiple times. Without them, you'd duplicate the subquery or nest it into something nobody wants to debug.

Generated Columns

MySQL generated columns compute their value automatically from other columns. Useful for derived data you want to query or index without maintaining it by hand:

typescript
await db.execute(`
  ALTER TABLE posts
  ADD COLUMN word_count INT
  GENERATED ALWAYS AS (LENGTH(content) - LENGTH(REPLACE(content, ' ', '')) + 1) STORED
`)

await query(
  `SELECT
     title,
     word_count
   FROM posts
   ORDER BY word_count DESC
   LIMIT 5`,
  'Posts by word count (generated column):',
)
text
Posts by word count (generated column):
  { title: 'Understanding Database Indexes', word_count: 49 }
  { title: 'Connection Pooling in Node.js', word_count: 44 }
  { title: 'CI/CD Pipeline Best Practices', word_count: 42 }
  { title: 'Web Performance Optimization', word_count: 42 }
  { title: 'Docker for Local Development', word_count: 35 }

STORED means the value is computed on write and saved to disk. VIRTUAL computes on every read, using no disk space. Both can be indexed in InnoDB, but STORED is simpler when you plan to sort or filter by the value often.

MySQL vs. PostgreSQL

Both are excellent. Here's my take on the tradeoffs:

MySQL is simpler to operate and faster for read-heavy workloads, particularly straightforward lookups and joins. Replication is easy to set up, and managed MySQL is on every cloud provider.

PostgreSQL has richer data types (arrays, hstore, custom types), more powerful extensions (PostGIS, pg_trgm, pgvector), and stricter SQL standards compliance. If you need advanced querying, custom functions, or specialized extensions, PostgreSQL is the stronger pick.

Both support JSON, full-text search, CTEs, window functions, and stored procedures. For most web apps, either works. Choose based on your team's experience and the ecosystem you're building on.

When to Reach for MySQL

MySQL fits naturally into a lot of common setups:

  • Read-heavy web apps: blogs, content sites, e-commerce storefronts. MySQL's read replication and buffer pool make it fast when reads far outnumber writes
  • Content management systems: WordPress, Drupal, and most CMS platforms default to MySQL. The ecosystem of tools, plugins, and hosting support is unmatched
  • E-commerce platforms: Shopify, Magento, WooCommerce. Transaction support handles orders, inventory, and payments reliably
  • Ecosystem-first teams: every ORM, every hosting provider, every monitoring tool supports MySQL. You'll never struggle to find docs, tutorials, or managed hosting
  • Teams that value simplicity: backups, replication, and upgrades follow well-documented paths. No surprises

If you need a relational database with massive ecosystem support and you're not relying on PostgreSQL-specific extensions, MySQL is a very solid default.

Wrapping Up

One file covers the core of MySQL development: tables with AUTO_INCREMENT, ENUM, and foreign keys; parameterized inserts; JOINs; JSON columns with ->> operators; full-text search; CTEs with window functions; and generated columns. That same feature set scales from a personal blog to a platform serving millions of readers.

The MySQL documentation covers stored procedures, triggers, partitioning, replication, and the performance schema for query optimization.

Close the connection at the end of your script:

typescript
await db.end()

To manage your local MySQL instance:

bash
spindb stop mysql1    # Stop the server
spindb start mysql1   # Start it again
spindb list           # See all your database instances

SpinDB supports 20+ database engines, so you can run MySQL, PostgreSQL, and Redis side by side from one CLI. There's also Layerbase Desktop if you prefer a GUI on macOS.

Something not working?