MySQL Is Still a Fine DefaultMySQL Is Still a Fine DefaultMySQL Is Still a Fine Default

There is a certain developer ritual: you open a blank repo, you need a database, and suddenly three people on the internet are telling you that your choice is wrong. MySQL is legacy. SQLite does not scale. Postgres is the only serious option. Neon is the future. I have watched this loop for years. On NearYou, I picked MySQL 8.4 with Prisma 7 and the mariadb adapter. It works perfectly. Here is the full reasoning.

Boring Is a Feature

MySQL has been running production workloads since 1995. That is three decades of edge cases, failure modes, and fixes baked into the codebase. When something breaks at 2 AM, you will find a Stack Overflow thread from 2014 that already solved it. That is not a small thing. Novel databases are exciting in blog posts and exhausting in production.

MySQL 8.4 is an LTS release. Bug fixes land on a predictable schedule. The storage engine is InnoDB, which gives you row-level locking, foreign key enforcement, and ACID transactions by default. You do not need to configure any of this. It is on. The optimizer handles most query plans correctly without hints. The replication story is mature. If you need read replicas later, you add them without changing application code.

The best database for a new project is the one you never have to think about again.

UTF8MB4 From the Start

One genuine historical MySQL mistake was the utf8 charset, which was actually a 3-byte encoding that could not store most CJK characters and all emoji. That encoding is now called utf8mb3 and is deprecated. The replacement is utf8mb4, which is real 4-byte UTF-8 and stores everything: Arabic, Hebrew, Japanese, Korean, Thai, and yes, every emoji your users will throw at you.

NearYou has users in South Tyrol, so content arrives in German, Italian, Ladin, and Romanian. Setting charset and collation once at the database level means you never think about it again at the application level.

A Container Next to the App

For development and for small production deployments, MySQL runs in a Docker container on the same host as the application. Network latency drops to sub-millisecond. There is no managed service to bill you for idle time. The setup is four lines of Docker Compose.

services:
  db:
    image: mysql:8.4
    restart: unless-stopped
    environment:
      MYSQL_ROOT_PASSWORD: ${DB_ROOT_PASSWORD}
      MYSQL_DATABASE: nearyou
      MYSQL_USER: app
      MYSQL_PASSWORD: ${DB_PASSWORD}
    volumes:
      - db_data:/var/lib/mysql
    command: >
      --character-set-server=utf8mb4
      --collation-server=utf8mb4_unicode_ci
      --default-authentication-plugin=caching_sha2_password
    healthcheck:
      test: ["CMD", "mysqladmin", "ping", "-h", "localhost", "-u", "root", "-p${DB_ROOT_PASSWORD}"]
      interval: 10s
      timeout: 5s
      retries: 5

  app:
    build: .
    depends_on:
      db:
        condition: service_healthy
    environment:
      DATABASE_URL: mysql://app:${DB_PASSWORD}@db:3306/nearyou

volumes:
  db_data:

The healthcheck matters. Without it, the app container can start before MySQL finishes its init sequence and the connection pool blows up on the first query. depends_on with condition: service_healthy solves that cleanly.

Prisma 7: Schema, Migrations, Types

Prisma is the reason this stack is genuinely pleasant to work with. You write one schema file. Prisma generates SQL migrations from it, runs them against the database, and generates a TypeScript client with types that match every model exactly. No runtime type assertions. No hand-rolled SQL strings. No ORM magic that silently fires N+1 queries.

Prisma 7 added first-class support for the mariadb provider, which covers MySQL 8.4 with better compatibility than the legacy mysql provider for certain edge cases around datetime precision and JSON handling. On NearYou we use it explicitly.

// schema.prisma
generator client {
  provider        = "prisma-client-js"
  previewFeatures = []
}

datasource db {
  provider = "mariadb"
  url      = env("DATABASE_URL")
}

model Place {
  id          String    @id @default(cuid())
  name        String    @db.VarChar(255)
  description String?   @db.Text
  lat         Decimal   @db.Decimal(10, 8)
  lng         Decimal   @db.Decimal(11, 8)
  category    Category
  ownerId     String
  owner       User      @relation(fields: [ownerId], references: [id], onDelete: Cascade)
  events      Event[]
  createdAt   DateTime  @default(now())
  updatedAt   DateTime  @updatedAt

  @@index([lat, lng])
  @@index([ownerId])
}

model User {
  id        String   @id @default(cuid())
  email     String   @unique @db.VarChar(320)
  name      String   @db.VarChar(255)
  places    Place[]
  createdAt DateTime @default(now())
}

enum Category {
  RESTAURANT
  CAFE
  SHOP
  EVENT_VENUE
  OUTDOOR
  OTHER
}

Once the schema is in place, two commands handle everything.

# Create and apply a migration (give it a descriptive name)
npx prisma migrate dev --name add-place-category-index

# Regenerate the TypeScript client after any schema change
npx prisma generate

The migration file is SQL. It lives in version control. You can read it, review it in a PR, roll it back manually if something goes wrong. There is no hidden state. The prisma migrate deploy command runs pending migrations in CI or on server boot. The whole flow is predictable.

How an AI Agent Works This Stack

I use Claude Code as an AI coding agent on NearYou. The agent writes Prisma schemas, generates migrations, queries the database via the client, and debugs connection issues. For this to work reliably across sessions, the agent needs persistent memory of the exact setup: which provider, which MySQL version, which conventions we follow, which gotchas have already burned us.

I solve this with a project-level AGENTS.md file that the agent reads on every session. It is not documentation for humans. It is a compact, precise context file that tells the agent how to behave with this specific stack. The agent does not need to re-discover that we use mariadb as the Prisma provider, or that migrations live in prisma/migrations/, or that the DSN format for our Docker Compose setup uses the service name db as the hostname.

Here is the actual database section from the NearYou AGENTS.md. It is short. It is precise. Every line earns its place.

## Database (MySQL 8.4 + Prisma 7)

Provider: `mariadb` (not mysql — use this in schema.prisma)
DSN env var: `DATABASE_URL`
DSN format: `mysql://app:<pass>@db:3306/nearyou`
Schema file: `prisma/schema.prisma`
Migrations dir: `prisma/migrations/`

### Commands
- Dev migration: `npx prisma migrate dev --name <descriptive-name>`
- Deploy migrations: `npx prisma migrate deploy`
- Regenerate client: `npx prisma generate`
- Open Studio: `npx prisma studio`
- Reset dev DB: `npx prisma migrate reset` (DESTROYS DATA)

### Conventions
- All string PKs use cuid() not uuid() — smaller index footprint.
- All money values use Decimal, never Float.
- lat/lng use Decimal(10,8) and Decimal(11,8) respectively.
- Compound index on [lat, lng] on every Place-like model.
- onDelete: Cascade on all user-owned relations.

### Gotchas
- MySQL 8.4 default auth is caching_sha2_password. Old clients fail without upgrading.
- utf8mb4_unicode_ci is case-insensitive. Use utf8mb4_bin for case-sensitive fields like tokens.
- JSON columns in MySQL 8.4 do not support partial updates via Prisma — read, merge, write.
- Never run migrate reset in production. It drops and recreates all tables.

With this file present, the agent never asks which provider to use, never guesses the DSN format, and never repeats a mistake we already documented. It reads the file, internalizes the constraints, and operates the stack correctly on the first try. The AGENTS.md becomes the long-term memory that survives context window resets.

The One Rule: Real, Tested Backups

Everything above is comfortable. The one non-negotiable is backups. Not a backup cron that you assume works. Actual tested restores.

• Run mysqldump --single-transaction --routines --triggers daily at minimum. The --single-transaction flag avoids table locks on InnoDB.
• Ship the dump offsite immediately. Same-host backups are not backups, they are a false comfort.
• Test the restore monthly. Spin up a fresh container, pipe the dump in, verify row counts on critical tables.
• If you are on a managed host (Railway, PlanetScale, Render), check whether their point-in-time recovery actually covers your data retention requirement. Do not assume.
• Prisma migrations are not a backup. They recreate schema but not data.

The Verdict

MySQL 8.4 with Prisma 7 and the mariadb adapter is not glamorous. It does not have a flashy new paper to cite. It has thirty years of production hardening, a mature ecosystem, excellent tooling, and zero surprises at scale. For NearYou, it is the right call. For most new projects, it is a completely defensible default. Pick it, configure utf8mb4 from day one, write a real AGENTS.md so your AI agent knows the setup cold, and make sure your backups are tested. Then stop thinking about the database and build the product.