MariaDB Implementation

Complete MariaDB implementation guide for SveltyCMS using Drizzle ORM, covering schema design, connection pooling, migrations, and performance optimization.

6/22/2026

11 min read

Edit on GitHub

On this page

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

Warning

The MariaDB adapter is planned but not yet implemented. The content below represents the architectural blueprint and implementation plan, not a shipped feature. For production-ready adapters, use SQLite, PostgreSQL, or MongoDB.

🟡 Planned Components:

Complete Drizzle schema (13 relational tables)
Connection pool management with mysql2
Automatic migration system
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

📦 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
Migration system - Version-controlled schema changes
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

Select Database Type: Choose “MariaDB” from dropdown
Enter Connection Details:
- Host: localhost (or your MariaDB server)
- Port: 3306 (default MariaDB port)
- Database: Your database name
- Username: Database user
- Password: Database password
Test Connection: Wizard validates connection
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

UUID Primary Keys: VARCHAR(36) for compatibility with DatabaseId type
Automatic Timestamps: createdAt and updatedAt with triggers
Multi-Tenant: Nullable tenantId columns with indexes
JSON Columns: For complex/nested data structures
Proper Indexes: Email, tenant, status, dates, foreign keys
InnoDB Engine: ACID transactions and foreign key support
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 }

📈 Migration System

Automatic Table Creation

On first connection, all tables are created automatically:

sequenceDiagram
    participant S as Setup Wizard / System Init
    participant A as MariaDBAdapter
    participant M as Migrations
    participant D as MariaDB DB

    S->>A: ensureSystem()
    A->>M: runMigrations(conn)
    M->>D: CREATE TABLE IF NOT EXISTS ...
    D-->>M: Tables Created/Verified
    M-->>A: { success: true }
    A-->>S: Ready

// migrations.ts
- Creates all 13 tables if they don't exist
- Adds proper indexes and constraints
- Uses InnoDB engine with utf8mb4 charset
- Ensures idempotent operations (CREATE TABLE IF NOT EXISTS)

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

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

🔐 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

Phase 1: Foundation 🟡 (Planned)

Phase 2: Core Adapter 🟡 (Planned)

Phase 3: Content & Media 🟡 (Planned)

Phase 4: Advanced Features 🟡 (Planned)

Status: 🟡 Planned (Not yet implemented)

See MARIADB_IMPLEMENTATION.md in repository root for detailed implementation guide.

Performance Benchmarks

Note

Performance benchmarks and optimization details for the MariaDB adapter will be available once implementation is complete. The following outlines the planned optimization strategy.

The planned MariaDB adapter will be optimized for relational data integrity and high-concurrency connection pooling. For current performance metrics, please refer to the Performance Benchmarks document.

Key planned optimizations include:

Relational Efficiency: Optimized schema with secondary indexes on tenantId, status, and createdAt.
mysql2 Pooling: High-performance connection pooling for sub-millisecond handshakes.
Idempotent Migrations: Zero-downtime schema updates.

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

Repository Files

src/databases/mariadb/schema/index.ts - Schema definitions
src/databases/mariadb/connection.ts - Connection management
src/databases/mariadb/migrations.ts - Migration system
src/databases/mariadb/seed.ts - Seeding logic
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:

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