This guide covers the MariaDB-specific implementation in SveltyCMS using Drizzle ORM with the mysql2 driver. For database-agnostic architecture, see the Core Infrastructure documentation.

---

🎯 Implementation Status

MariaDB Version: 10.5+ (latest stable recommended)
ORM: Drizzle ORM 0.45+
Driver: mysql2 3.16+
Implementation Status: Planned - Not Yet Implemented

Current Status

✅ Implemented Components:

• Complete Drizzle schema (relational tables)
• Connection pool management with mysql2
• Automatic schema bootstrap (spec-rendered, engine-agnostic)
• Database seeding infrastructure
• Date conversion utilities (ISODateString compliance)
• Multi-tenant support (nullable tenantId columns)
• IDBAdapter interface implementation
• Auth methods (users, sessions, tokens, roles)
• Content methods (nodes, drafts, revisions)
• Media methods (files, folders)
• CRUD operations and batch processing
• System methods (virtual folders, preferences)
• Theme & Widget management
• Multi-tenant CRUD (tenants: create, getById, update, delete, list)
• TTL cleanup (cleanupExpiredData: expired sessions and consumed tokens)
• Atomic Versioning: Native getVersion and incrementVersion support
• Raw UPDATE…RETURNING fast path: MariaDB ≥10.5 supports RETURNING, but Drizzle’s mysql2 dialect does not expose .returning() — the adapter probes _returningSupported and runs a raw prepared UPDATE…RETURNING * for update() and _id-lookup upsert(), halving round trips vs the old UPDATE + findById re-read
• Backtick identifier quoting: the dynamic findMany path quotes identifiers per dialect; MariaDB’s default sql_mode has no ANSI_QUOTES, so double-quoted identifiers were a syntax error — fixed via the quoteIdentifier hook

---

📦 Architecture Overview

Drizzle ORM Integration

SveltyCMS uses Drizzle ORM for MariaDB, providing:

• Type-safe queries - Full TypeScript support
• Relational schema - Proper foreign keys and constraints
• Schema bootstrap - Boot-time DDL rendered from the shared system schema spec
• Connection pooling - Optimized mysql2 pool
• Multi-tenant support - Built-in tenantId filtering

Schema Design

Relational Tables (14 total)

- auth_users: User accounts with roles
- auth_sessions: Active user sessions
- auth_tokens: Verification/reset tokens
- roles: Permission-based roles
- content_nodes: Pages and collections
- content_drafts: Draft versions
- content_revisions: Version history
- media_items: File metadata
- system_virtual_folders: Folder organization
- themes: Theme configurations
- widgets: Widget instances
- system_preferences: System/user settings
- website_tokens: External API credentials (SHA-256 hash at rest; unique index on `token`; compound `{ tenantId, name }`; implemented in `relational-system.ts`)
- tenants: Multi-tenant management (name, owner, plan, quota, usage, settings)

---

🚀 Getting Started

Prerequisites

MariaDB/MySQL packages are included as optionalDependencies:

{
  "optionalDependencies": {
    "mysql2": "^3.16.0",
    "mariadb": "^3.4.5",
    "drizzle-orm": "^0.45.1"
  }
}

Packages are auto-installed when MariaDB is selected in the setup wizard.

Setup Wizard Configuration

1. Select Database Type: Choose “MariaDB” from dropdown

2. Enter Connection Details:

   • Host: localhost (or your MariaDB server)
   • Port: 3306 (default MariaDB port)
   • Database: Your database name
   • Username: Database user
   • Password: Database password

3. Test Connection: Wizard validates connection

4. Complete Setup: Tables are created automatically

---

📊 Database Schema

Example Table Definition

// Auth Users Table
export const authUsers = mysqlTable(
  "auth_users",
  {
    _id: varchar("_id", { length: 36 }).primaryKey(),
    email: varchar("email", { length: 255 }).notNull(),
    username: varchar("username", { length: 255 }),
    password: varchar("password", { length: 255 }),
    emailVerified: boolean("emailVerified").notNull().default(false),
    blocked: boolean("blocked").notNull().default(false),
    firstName: varchar("firstName", { length: 255 }),
    lastName: varchar("lastName", { length: 255 }),
    avatar: text("avatar"),
    roleIds: json("roleIds").$type<string[]>().notNull().default([]),
    tenantId: varchar("tenantId", { length: 36 }),
    createdAt: datetime("createdAt")
      .notNull()
      .default(sql`CURRENT_TIMESTAMP`),
    updatedAt: datetime("updatedAt")
      .notNull()
      .default(sql`CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP`),
  },
  (table) => ({
    emailIdx: index("email_idx").on(table.email),
    tenantIdx: index("tenant_idx").on(table.tenantId),
    emailTenantUnique: unique("email_tenant_unique").on(table.email, table.tenantId),
  }),
);

Key Design Principles

1. UUID Primary Keys: VARCHAR(36) for compatibility with DatabaseId type
2. Automatic Timestamps: createdAt and updatedAt with triggers
3. Multi-Tenant: Nullable tenantId columns with indexes
4. JSON Columns: For complex/nested data structures
5. Proper Indexes: Email, tenant, status, dates, foreign keys
6. InnoDB Engine: ACID transactions and foreign key support
7. UTF8MB4: Full Unicode support including emojis

---

🔧 Connection Management

Connection Pool Configuration

// Optimized mysql2 pool settings
{
  host: config.host,
  port: config.port,
  user: config.user,
  password: config.password,
  database: config.database,
  waitForConnections: true,
  connectionLimit: 10,      // Max concurrent connections
  maxIdle: 10,              // Keep connections warm
  idleTimeout: 60000,       // 60s idle timeout
  queueLimit: 0,            // Unlimited queue
  enableKeepAlive: true,
  keepAliveInitialDelay: 0
}

Health Monitoring

The adapter provides connection health checking:

const health = await dbAdapter.getConnectionHealth();
// Returns: { healthy: boolean, latency: number, activeConnections: number }

---

📈 Schema Bootstrap

Automatic Table Creation

On first connection, the system schema is rendered from the single declarative source of truth (src/databases/system-schema-spec.ts) and executed against the live pool by src/databases/core/system-schema-bootstrap.ts — there are no per-engine migration files to keep in sync, so the three relational engines can never drift.

sequenceDiagram
    participant S as Setup Wizard / System Init
    participant A as MariaDBAdapter
    participant B as Schema Bootstrap (core)
    participant D as MariaDB DB

    S->>A: connect()
    A->>B: bootstrapSystemSchema("mariadb", pool)
    B->>D: CREATE TABLE IF NOT EXISTS ...
    D-->>B: Tables Created/Verified
    B-->>A: { success: true }
    A-->>S: Ready

// src/databases/system-schema-spec.ts + core/system-schema-bootstrap.ts
- Creates all system tables if they don't exist (per-dialect types/defaults)
- Adds proper indexes and constraints
- Uses InnoDB engine with utf8mb4 charset
- Ensures idempotent operations (CREATE TABLE IF NOT EXISTS)
- Per-statement warn-and-continue execution (never aborts boot on one failure)

Future Migration Management

For schema changes after initial setup:

# Generate migration
npm run db:push

# View in Drizzle Studio
npm run db:studio

---

🌱 Database Seeding

Initial Data Creation

The seeding system creates essential data during setup:

- **Default Roles**: Admin (full system access), Editor (content management), User (read access).
- **Admin User**: Created with credentials from setup wizard, assigned Admin role, email verified automatically.
- **Default Theme**: SveltyCMS Default theme, active and set as default.
- **Root Virtual Folder**: Media folder organization, named "mediaFolder", parent for all media.

---

🔒 Multi-Tenant Support

Tenant Isolation {#multi-tenant-isolation}

All tables include nullable tenantId columns:

// Queries automatically filter by tenantId when provided
const users = await db.select().from(authUsers).where(eq(authUsers.tenantId, currentTenantId));

Cross-Tenant Operations

// Admin operations can work across tenants
const allUsers = await db.select().from(authUsers);
// No tenantId filter = all tenants

---

🎯 Data Type Mapping

TypeScript ↔ MariaDB

TypeScript Type	MariaDB Type	Notes
DatabaseId (UUID)	VARCHAR(36)	Standard UUID format
ISODateString	DATETIME	Converted at boundaries
string	VARCHAR(n) or TEXT	Based on length
number	INT	Integer values
boolean	BOOLEAN	True/False
object / array	JSON	Native JSON column type

Date Handling

Important: All dates are converted to/from ISODateString at adapter boundaries:

// Database stores: DATETIME (2026-01-06 12:00:00)
// Adapter returns: ISODateString ("2026-01-06T12:00:00.000Z")

// Conversion helpers
utils.dateToISO(date); // Date → ISODateString
utils.isoToDate(isoString); // ISODateString → Date
utils.convertDatesToISO(row); // Convert all dates in object

---

🔍 Query Examples

Basic CRUD

// Create
const id = generateId();
await db.insert(authUsers).values({
  _id: id,
  email: "user@example.com",
  password: hashedPassword,
  roleIds: [admin - roleId],
  createdAt: new Date(),
  updatedAt: new Date(),
});

// Read
const [user] = await db
  .select()
  .from(authUsers)
  .where(eq(authUsers.email, "user@example.com"))
  .limit(1);

// Update
await db
  .update(authUsers)
  .set({ emailVerified: true, updatedAt: new Date() })
  .where(eq(authUsers._id, userId));

// Delete
await db.delete(authUsers).where(eq(authUsers._id, userId));

Complex Queries

// Multi-condition query with tenant filtering
const activeUsers = await db
  .select()
  .from(authUsers)
  .where(
    and(
      eq(authUsers.tenantId, tenantId),
      eq(authUsers.blocked, false),
      eq(authUsers.emailVerified, true),
    ),
  );

// Join example (when implemented)
const usersWithRoles = await db
  .select({
    user: authUsers,
    role: roles,
  })
  .from(authUsers)
  .leftJoin(roles, eq(authUsers.roleIds, roles._id));

---

⚡ Performance Optimization

Indexing Strategy

All tables include strategic indexes:

-- Email lookup (frequent operation)
INDEX email_idx (email)

-- Tenant filtering (multi-tenant)
INDEX tenant_idx (tenantId)

-- Unique constraint (data integrity)
UNIQUE INDEX email_tenant_unique (email, tenantId)

-- Status queries (content filtering)
INDEX status_idx (status)

-- Date range queries (reporting)
INDEX created_at_idx (createdAt)

Connection Pooling Benefits

• Reduced Latency: Reuses existing connections (no TCP handshake)
• Resource Efficiency: Limited concurrent connections
• Load Balancing: Distributes queries across pool
• Graceful Degradation: Queues requests when pool is full

Query Optimization Tips

1. Use Indexes: Ensure WHERE clauses use indexed columns
2. Limit Results: Always use .limit() for large tables
3. Select Specific fields: Avoid SELECT * when possible
4. Batch Operations: Use batch insert/update for bulk operations
5. Connection Reuse: Let pool manage connections

Raw Fast Paths (shipped)

• Raw single INSERT (no RETURNING — Drizzle’s mysql2 dialect has no .returning()): INSERT 219–271 → ~310 RPS (+15–40%). The returned row is synthesized from prepared values + column defaults with MariaDB int-boolean parity (isDeleted → 0/1) — identical to a subsequent read.
• Raw multi-VALUES insertMany: prepared multi-row statement per chunk; rows synthesized from prepared values (multi-row RETURNING is materialized slowly by MariaDB — measured 62 vs 190 RPS). skipReturning (outbox/seeds) returns values untouched. Bulk is engine-bound (~4.6ms/100 rows ≈ 217 RPS ceiling on the benchmark host) so it holds parity with Drizzle while removing the AST cost.
• Timestamp defaults fixed: createModel DDL now declares DEFAULT CURRENT_TIMESTAMP on createdAt/updatedAt (previously NULL — the Drizzle def had defaults but the physical DDL did not); inserts also fill both timestamps explicitly so pre-existing tables are covered.
• Physical column name resolution: every raw write path (insert, update, upsert, insertMany, rawInsertReturning) resolves Drizzle property names to physical column names via getColumn — e.g. plugin_storage.collectionName → `collection` — so Drizzle defs whose property names differ from the DDL column never hit a Unknown column error.
• Transaction-aware deferral: inside an outer transaction the raw pool paths (insert / insertMany / update) defer to the txn-aware base Drizzle path (options.transaction), so writes join the caller’s transaction and roll back correctly instead of committing immediately on the pool connection.
• mysql2 result unwrap: executeDynamicSql unwraps mysql2’s [rows, fields] tuple (rows are the first element), keeping the dynamic findMany path shape-consistent with PostgreSQL/SQLite.
• createModel cache invalidation + covering index: createModel deletes every tableRegistry key variant (logical id, dash-stripped name, collection_ prefixes) so a stale pre-DDL table def never silently drops materialized columns from later reads, and provisions the composite index (tenantId, status, updatedAt) serving the canonical tenant list query (WHERE tenantId=? AND status=? AND isDeleted=0 ORDER BY updatedAt DESC LIMIT n).

---

🔐 Security Considerations

SQL Injection Prevention

Drizzle ORM uses parameterized queries automatically:

// ✅ Safe - Parameters are escaped
await db.select().from(authUsers).where(eq(authUsers.email, userInput)); // Automatically parameterized

// ❌ Never do this
await db.execute(sql`SELECT * FROM users WHERE email = '${userInput}'`);

Password Security

// Use argon2 for password hashing (already in dependencies)
import argon2 from "argon2";

const hashedPassword = await argon2.hash(plainPassword);
const isValid = await argon2.verify(hashedPassword, plainPassword);

Tenant Isolation {#tenant-isolation-2}

// Always apply tenant filter for user queries
const applyTenantFilter = (conditions, tenantId) => {
  if (tenantId) {
    return and(conditions, eq(table.tenantId, tenantId));
  }
  return conditions;
};

---

🧪 Testing

Connection Testing

// Test database connection
const { success, latency } = await testConnection();
console.log(`Connection ${success ? "OK" : "Failed"} (${latency}ms)`);

Data Validation

All data should be validated using Valibot schemas before database operations:

import { safeParse } from "valibot";
import { userSchema } from "./schemas";

const result = safeParse(userSchema, userData);
if (!result.success) {
  // Handle validation errors
  console.error(result.issues);
}

---

🐛 Troubleshooting

Common Issues

Connection Refused

Error: connect ECONNREFUSED 127.0.0.1:3306
Solution: Ensure MariaDB is running and port 3306 is accessible

Authentication Failed

Error: ER_ACCESS_DENIED_ERROR
Solution: Verify username/password and user has proper grants

Table Already Exists

Warning: Table already exists
Solution: This is normal - migrations are idempotent

Date Format Issues

Error: Invalid date format
Solution: Ensure using utils.dateToISO() for date conversions

Debug Mode

Enable query logging for debugging:

const db = drizzle(connection, {
  schema,
  mode: "default",
  logger: true, // Logs all SQL queries
});

---

📚 Implementation Roadmap

All four phases are complete — the adapter is production-ready and runs in the CI DB matrix (see Current Status above).

Phase 1: Foundation ✅ (Complete)

• Drizzle schema definitions
• Connection pool management
• Schema bootstrap (spec-rendered, engine-agnostic)
• Database seeding
• Utility functions

Phase 2: Core Adapter ✅ (Complete)

• Auth methods implementation
• System preferences methods
• Theme management methods
• Virtual folder methods
• Widget methods

Phase 3: Content & Media ✅ (Complete)

• Content node operations
• Draft management
• Revision tracking
• Media file operations
• Folder management

Phase 4: Advanced Features ✅ (Complete)

• Generic CRUD operations
• Batch operations
• Transaction support
• Query builder
• Performance monitoring
• Cache integration

Status: ✅ Complete (production-ready)

Performance Benchmarks

MariaDB is a production SQL adapter (SqlAdapterCore + mysql2). For full matrix metrics see Performance Benchmarks and Performance Architecture.

Shared list / count contract

Feature	Implementation
findPage	limit + 1 hasMore
count estimate	information_schema.TABLES.TABLE_ROWS (DATABASE())
Count cache	30s L1 (shared wrapper)

Measured (2026-08-04, Docker MariaDB, no Redis): FIND PAGE 0.660 ms vs LIST+COUNT 0.928 ms (~1.4×); COUNT ESTIMATE 0.423 ms vs exact 0.779 ms; COUNT CACHED 0.024 ms (~32× vs exact).

Key Planned Optimizations

• Relational Efficiency: Optimized schema with secondary indexes on tenantId, status, and createdAt ensures fast filtering even without cache hits.

• mysql2 Pooling: Leverages the high-performance mysql2 driver with pre-warmed connection pools for sub-millisecond handshake in high-traffic environments.

• Typed Collection Proxy: Fully-typed access via locals.cms.collections.typed.Posts.find().

• Background Index Optimizer: Automatically manages MariaDB indexes based on evolving schemas.

• Idempotent Migrations: Automatic schema synchronization ensures zero-downtime updates and predictable deployment cycles.

• TTL-Equivalent Cleanup: cleanupExpiredData() method purges expired sessions and consumed tokens — replicating MongoDB’s TTL indexes for SQL.

• Multi-Tenant CRUD: Full tenant lifecycle management (create, read, update, delete, list) with JSON serialization for quota/usage/settings.

---

🔗 Related Documentation

• Core Infrastructure - Database-agnostic architecture
• MongoDB Implementation - MongoDB-specific guide
• Authentication System - Auth infrastructure
• Cache System - Caching layer

---

📖 Additional Resources

External Documentation

• Drizzle ORM Documentation
• mysql2 Driver
• MariaDB Documentation

Repository Files

• src/databases/mariadb/schema/index.ts - Schema definitions
• src/databases/mariadb/connection.ts - Connection management
• src/databases/system-schema-spec.ts - Declarative system schema (single source of truth)
• src/databases/core/system-schema-bootstrap.ts - Boot-time renderer/executor
• src/databases/mariadb/utils.ts - Helper functions
• MARIADB_IMPLEMENTATION.md - Implementation guide

---

🤝 Contributing

This module is considered feature-complete (v1.0). Future contributions should focus on:

1. Performance optimization (indexing, query caching)
2. Advanced features (connection pooling tweaking)
3. Extended testing coverage

---

Related

• Database Architecture
• Performance Benchmarks
• Architecture Overview