LayerbaseLayerbase

Getting Started with MariaDB

MariaDBDatabasesSQL

MariaDB is the community-driven fork of MySQL, created by MySQL's original author Michael "Monty" Widenius after Oracle acquired Sun Microsystems in 2010. For most workloads it's a drop-in replacement: same SQL syntax, same wire protocol, same tools. But MariaDB has added features MySQL lacks, including sequences, system-versioned (temporal) tables, and Oracle compatibility mode.

If you know MySQL, you already know MariaDB. The interesting part is what comes on top of that shared foundation.

We'll build an inventory management system in one TypeScript file: tables, inserts, queries, then the features that set MariaDB apart. Run everything locally, or grab a managed instance on Layerbase Cloud to skip local setup.

Contents

Create a MariaDB Instance

Local with SpinDB

SpinDB handles the binary download and configuration for you. No Docker needed. (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 maria1 -e mariadb --start  # npm
pnpx spindb create maria1 -e mariadb --start # pnpm

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

bash
spindb create maria1 -e mariadb --start

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

bash
spindb url maria1
text
mysql://root@127.0.0.1:3306/test

Leave the server running.

Layerbase Cloud

If local setup isn't your thing, Layerbase Cloud provisions a managed MariaDB instance. Select MariaDB, and your connection details will be in the Quick Connect panel.

Cloud instances use TLS, so the connection code includes an SSL option:

typescript
const conn = await mariadb.createConnection({
  host: 'cloud.layerbase.dev',
  port: 3306,
  user: 'your_user',
  password: 'your_password',
  database: 'your_database',
  ssl: 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 mariadb-inventory && cd mariadb-inventory
pnpm init
pnpm add mariadb
pnpm add -D tsx typescript

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

The mariadb package is the official Node.js connector: promise-based, fully typed, and built for MariaDB (though it works with MySQL too).

The Inventory Dataset

Two tables: products and stock movements. Here's the data:

typescript
const products = [
  { name: 'Mechanical Keyboard', sku: 'KB-001', category: 'peripherals', price: 129.99, stock: 45 },
  { name: 'USB-C Hub', sku: 'HB-001', category: 'peripherals', price: 49.99, stock: 120 },
  { name: '27" 4K Monitor', sku: 'MN-001', category: 'displays', price: 449.99, stock: 30 },
  { name: 'Wireless Mouse', sku: 'MS-001', category: 'peripherals', price: 39.99, stock: 200 },
  { name: 'Standing Desk', sku: 'DK-001', category: 'furniture', price: 599.99, stock: 15 },
  { name: 'Webcam HD', sku: 'WC-001', category: 'peripherals', price: 79.99, stock: 85 },
  { name: 'Desk Lamp', sku: 'LM-001', category: 'furniture', price: 34.99, stock: 60 },
  { name: 'Laptop Stand', sku: 'LS-001', category: 'furniture', price: 44.99, stock: 75 },
  { name: 'Noise-Canceling Headphones', sku: 'HP-001', category: 'audio', price: 249.99, stock: 50 },
  { name: 'Portable Speaker', sku: 'SP-001', category: 'audio', price: 89.99, stock: 40 },
]

const movements = [
  { sku: 'KB-001', change: -3, reason: 'sale' },
  { sku: 'KB-001', change: -1, reason: 'sale' },
  { sku: 'KB-001', change: 20, reason: 'restock' },
  { sku: 'HB-001', change: -10, reason: 'sale' },
  { sku: 'HB-001', change: -5, reason: 'sale' },
  { sku: 'MN-001', change: -2, reason: 'sale' },
  { sku: 'MN-001', change: -1, reason: 'adjustment' },
  { sku: 'MN-001', change: 10, reason: 'restock' },
  { sku: 'MS-001', change: -15, reason: 'sale' },
  { sku: 'MS-001', change: 3, reason: 'return' },
  { sku: 'DK-001', change: -2, reason: 'sale' },
  { sku: 'DK-001', change: -1, reason: 'sale' },
  { sku: 'WC-001', change: -8, reason: 'sale' },
  { sku: 'WC-001', change: 50, reason: 'restock' },
  { sku: 'HP-001', change: -5, reason: 'sale' },
  { sku: 'HP-001', change: -3, reason: 'sale' },
  { sku: 'HP-001', change: 2, reason: 'return' },
  { sku: 'SP-001', change: -4, reason: 'sale' },
  { sku: 'LM-001', change: -6, reason: 'sale' },
  { sku: 'LS-001', change: -12, reason: 'sale' },
]

Ten products across four categories, 20 stock movements covering sales, restocks, returns, and adjustments. Small enough to follow by hand, rich enough for real queries.

Connect and Create Tables

typescript
import mariadb from 'mariadb'

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

// Clean up from previous runs
await conn.query('DROP TABLE IF EXISTS stock_movements')
await conn.query('DROP TABLE IF EXISTS products')

await conn.query(`
  CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    sku VARCHAR(50) NOT NULL UNIQUE,
    category VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    stock_quantity INT NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
  )
`)

await conn.query(`
  CREATE TABLE stock_movements (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product_id INT NOT NULL,
    quantity_change INT NOT NULL,
    reason ENUM('restock', 'sale', 'adjustment', 'return') NOT NULL,
    moved_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (product_id) REFERENCES products(id)
  )
`)

console.log('Tables created')

A few details worth noting:

  • AUTO_INCREMENT generates sequential IDs (same as MySQL)
  • DECIMAL(10, 2) stores exact currency values, no floating-point rounding
  • ENUM restricts a column to a defined set of values, enforced by the database

Insert Data

typescript
for (const p of products) {
  await conn.query(
    `INSERT INTO products (name, sku, category, price, stock_quantity)
     VALUES (?, ?, ?, ?, ?)`,
    [p.name, p.sku, p.category, p.price, p.stock],
  )
}

for (const m of movements) {
  await conn.query(
    `INSERT INTO stock_movements (product_id, quantity_change, reason)
     VALUES ((SELECT id FROM products WHERE sku = ?), ?, ?)`,
    [m.sku, m.change, m.reason],
  )
}

console.log(`Inserted ${products.length} products and ${movements.length} stock movements`)

The mariadb package uses ? placeholders for parameterized queries, escaping values automatically.

Query the Data

JOIN: Current Stock with Movement History

typescript
const stockReport = await conn.query(`
  SELECT
    p.name,
    p.sku,
    p.stock_quantity AS initial_stock,
    COALESCE(SUM(sm.quantity_change), 0) AS total_change,
    p.stock_quantity + COALESCE(SUM(sm.quantity_change), 0) AS current_stock
  FROM products p
  LEFT JOIN stock_movements sm ON sm.product_id = p.id
  GROUP BY p.id
  ORDER BY current_stock ASC
`)

console.log('\nStock Report:')
for (const row of stockReport) {
  console.log(`  ${row.sku}  ${row.name}: ${row.initial_stock} → ${row.current_stock} (${row.total_change >= 0 ? '+' : ''}${row.total_change})`)
}
text
Stock Report:
  DK-001  Standing Desk: 15 → 12 (-3)
  MN-001  27" 4K Monitor: 30 → 37 (+7)
  SP-001  Portable Speaker: 40 → 36 (-4)
  HP-001  Noise-Canceling Headphones: 50 → 44 (-6)
  LM-001  Desk Lamp: 60 → 54 (-6)
  KB-001  Mechanical Keyboard: 45 → 61 (+16)
  LS-001  Laptop Stand: 75 → 63 (-12)
  WC-001  Webcam HD: 85 → 127 (+42)
  HB-001  USB-C Hub: 120 → 105 (-15)
  MS-001  Wireless Mouse: 200 → 188 (-12)

Aggregation: Revenue by Category

typescript
const revenue = await conn.query(`
  SELECT
    p.category,
    COUNT(DISTINCT p.id) AS product_count,
    SUM(ABS(sm.quantity_change) * p.price) AS total_movement_value
  FROM products p
  JOIN stock_movements sm ON sm.product_id = p.id
  WHERE sm.reason = 'sale'
  GROUP BY p.category
  ORDER BY total_movement_value DESC
`)

console.log('\nSales Value by Category:')
for (const row of revenue) {
  console.log(`  ${row.category}: $${Number(row.total_movement_value).toFixed(2)} (${row.product_count} products)`)
}
text
Sales Value by Category:
  furniture: $2,549.79 (3 products)
  peripherals: $2,509.58 (4 products)
  audio: $2,359.88 (2 products)
  displays: $899.98 (1 products)

Standard SQL so far. Now for the features that make MariaDB different from MySQL.

Sequences

MySQL gives you AUTO_INCREMENT and that's it. MariaDB supports sequences: standalone named counters you can use anywhere. Useful for order numbers, invoice IDs, or any value that needs to be sequential but isn't tied to one table's primary key.

typescript
await conn.query('DROP SEQUENCE IF EXISTS order_seq')

await conn.query(`
  CREATE SEQUENCE order_seq
    START WITH 1000
    INCREMENT BY 1
`)

const orders = []
for (let i = 0; i < 5; i++) {
  const [row] = await conn.query('SELECT NEXT VALUE FOR order_seq AS order_number')
  orders.push(row.order_number)
}

console.log('\nGenerated Order Numbers:', orders)
text
Generated Order Numbers: [ 1000, 1001, 1002, 1003, 1004 ]

Why bother when you have AUTO_INCREMENT?

  • Multiple tables can share one sequence
  • You control the start value, increment, min/max, and cycling
  • You can peek at the current value without consuming it: SELECT PREVIOUS VALUE FOR order_seq

In MySQL, you'd need a dedicated single-row table and SELECT ... FOR UPDATE to get the same behavior. MariaDB just has it.

System-Versioned Tables

This is the biggest differentiator. MariaDB can track every historical version of every row, automatically, without triggers or audit tables. It's called system versioning, and it's built into the storage engine.

Enable it on the products table and make some changes:

typescript
await conn.query('ALTER TABLE products ADD SYSTEM VERSIONING')

console.log('\nSystem versioning enabled on products table')

// Record the timestamp before making changes
const [{ now: beforeUpdate }] = await conn.query('SELECT NOW(6) AS now')

// Simulate a price increase
await conn.query(`UPDATE products SET price = price * 1.10 WHERE category = 'peripherals'`)

// Simulate removing a product
await conn.query(`DELETE FROM products WHERE sku = 'SP-001'`)

console.log('Updated prices and deleted a product')

Now we can query the table as it existed before the changes:

typescript
const historical = await conn.query(
  `SELECT name, sku, price
   FROM products FOR SYSTEM_TIME AS OF ?
   WHERE category = 'peripherals'
   ORDER BY sku`,
  [beforeUpdate],
)

const current = await conn.query(
  `SELECT name, sku, price
   FROM products
   WHERE category = 'peripherals'
   ORDER BY sku`,
)

console.log('\nPeripherals before price increase:')
for (const row of historical) {
  console.log(`  ${row.sku}  ${row.name}: $${Number(row.price).toFixed(2)}`)
}

console.log('\nPeripherals after price increase:')
for (const row of current) {
  console.log(`  ${row.sku}  ${row.name}: $${Number(row.price).toFixed(2)}`)
}
text
Peripherals before price increase:
  HB-001  USB-C Hub: $49.99
  KB-001  Mechanical Keyboard: $129.99
  MS-001  Wireless Mouse: $39.99
  WC-001  Webcam HD: $79.99

Peripherals after price increase:
  HB-001  USB-C Hub: $54.99
  KB-001  Mechanical Keyboard: $142.99
  MS-001  Wireless Mouse: $43.99
  WC-001  Webcam HD: $87.99

You can also see the deleted product by querying the past:

typescript
const deleted = await conn.query(
  `SELECT name, sku, price
   FROM products FOR SYSTEM_TIME AS OF ?
   WHERE sku = 'SP-001'`,
  [beforeUpdate],
)

console.log(`\nDeleted product (recovered from history): ${deleted[0].name}, $${Number(deleted[0].price).toFixed(2)}`)
text
Deleted product (recovered from history): Portable Speaker, $89.99

No audit table. No triggers. No application-level change tracking. The database records the history itself. Invaluable for compliance, debugging, and "what changed?" investigations that would otherwise require a pile of custom infrastructure.

Window Functions for Running Totals

Window functions compute values across rows related to the current one. Here's a running stock balance for a single product:

typescript
const runningTotals = await conn.query(`
  SELECT
    p.name,
    sm.reason,
    sm.quantity_change,
    SUM(sm.quantity_change) OVER (
      PARTITION BY sm.product_id
      ORDER BY sm.moved_at
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_change
  FROM stock_movements sm
  JOIN products p ON p.id = sm.product_id
  WHERE p.sku = 'KB-001'
  ORDER BY sm.moved_at
`)

console.log('\nRunning Stock Changes for Mechanical Keyboard:')
for (const row of runningTotals) {
  const sign = row.quantity_change >= 0 ? '+' : ''
  console.log(`  ${row.reason.padEnd(12)} ${sign}${row.quantity_change}  (cumulative: ${row.running_change})`)
}
text
Running Stock Changes for Mechanical Keyboard:
  sale         -3  (cumulative: -3)
  sale         -1  (cumulative: -4)
  restock      +20  (cumulative: 16)

OVER (PARTITION BY ... ORDER BY ...) computes a running sum per product. Without window functions, you'd need a correlated subquery or application-side logic for the same result.

Recursive CTEs

Recursive CTEs are useful for hierarchical data and sequence generation. Here's a practical one: generating a date series for a daily stock movement report, including days with zero activity.

typescript
const dailyReport = await conn.query(`
  WITH RECURSIVE dates AS (
    SELECT CAST('2026-03-14' AS DATE) AS day
    UNION ALL
    SELECT day + INTERVAL 1 DAY
    FROM dates
    WHERE day < CAST('2026-03-14' AS DATE) + INTERVAL 6 DAY
  )
  SELECT
    d.day,
    COALESCE(SUM(CASE WHEN sm.reason = 'sale' THEN ABS(sm.quantity_change) END), 0) AS units_sold,
    COALESCE(SUM(CASE WHEN sm.reason = 'restock' THEN sm.quantity_change END), 0) AS units_restocked,
    COUNT(sm.id) AS total_movements
  FROM dates d
  LEFT JOIN stock_movements sm ON DATE(sm.moved_at) = d.day
  GROUP BY d.day
  ORDER BY d.day
`)

console.log('\nDaily Stock Movement Report:')
for (const row of dailyReport) {
  const day = new Date(row.day).toISOString().split('T')[0]
  console.log(`  ${day}  sold: ${row.units_sold}, restocked: ${row.units_restocked}, movements: ${row.total_movements}`)
}

The recursive CTE generates seven consecutive dates. The LEFT JOIN ensures every date appears, even days with no movement. Without the CTE, quiet days would just be missing from the results.

MariaDB vs MySQL

For basic CRUD, they're interchangeable. The differences matter when you need specific features:

MariaDB advantages:

  • Sequences: first-class CREATE SEQUENCE support, no workarounds needed
  • System-versioned tables: built-in temporal queries, no triggers or audit tables
  • Oracle compatibility mode: SET SQL_MODE='ORACLE' for easier migrations from Oracle DB
  • Community governance: open development process under the MariaDB Foundation

MySQL advantages:

  • JSON path support: more mature JSON_TABLE, JSON_SCHEMA_VALID, and multi-valued indexes on JSON arrays
  • Enterprise backing: Oracle provides commercial support, Oracle Cloud integration, and the MySQL HeatWave analytics engine
  • Wider managed hosting: available on every major cloud provider as a first-party service

Both are excellent. The choice comes down to whether you need MariaDB's extras (sequences, temporal tables, Oracle mode) or MySQL's JSON capabilities and cloud platform integration.

When to Use MariaDB

I'd pick MariaDB when:

  • You'd otherwise use MySQL: it's wire-compatible, and most MySQL tools, ORMs, and drivers work without modification
  • You need temporal data: track historical changes, comply with audit regulations, or answer "what did this row look like at time X?" without building custom audit infrastructure
  • You need sequences: order numbers, invoice IDs, or any sequential identifier that spans multiple tables
  • You're migrating from Oracle: compatibility mode eases the transition
  • Open-source governance matters: the database engine's license and development process matter to your organization

Wrapping Up

One TypeScript file: an inventory system with JOINs and aggregations, sequential order numbers via sequences, historical price tracking via system versioning, and running totals via window functions. Everyday SQL plus the features that distinguish MariaDB from MySQL.

The MariaDB documentation covers Galera Cluster for multi-primary replication, Columnstore for analytics, and the full temporal query syntax.

To manage your local MariaDB instance:

bash
spindb stop maria1    # Stop the server
spindb start maria1   # Start it again
spindb connect maria1 # Open a MariaDB shell
spindb list           # See all your database instances

SpinDB handles 20+ database engines from one CLI, so you can run MariaDB next to PostgreSQL, MongoDB, or Redis without juggling separate installs. Layerbase Desktop is available too if you'd rather use a macOS GUI.

Something not working?