Example Application - Base Setup

This tutorial walks you through setting up and running a complete full-stack web application built with Node.js, TypeScript, MariaDB, Prisma, and React. This application serves as the foundation for our upcoming technical blog posts covering advanced database and DevOps features.

🎯 What You'll Build

You'll create a functional full-stack web application for managing:

Customers: Customer information and contact details
Products: Product catalog with inventory tracking
Sales: Sales transactions with automatic stock management
Purchases: Purchase orders from suppliers with inventory updates

The application includes:

Backend: RESTful API built with Express.js and TypeScript
Frontend: Modern React application with TypeScript and Vite
Database: MariaDB with Prisma ORM for type-safe database access

🏗️ Architecture Overview

The application follows a clean, layered architecture with a clear separation between frontend and backend:

┌─────────────────────────────────────┐
│      Frontend (React + TypeScript)   │
│    Port: 5173                        │
│    - Dashboard                       │
│    - Customer Management             │
│    - Product Management              │
│    - Sales & Purchases               │
└─────────────────────────────────────┘
              │
              │ HTTP/REST API
              ▼
┌─────────────────────────────────────┐
│         REST API Layer                │
│    (Express.js + TypeScript)          │
│    Port: 3000                         │
└─────────────────────────────────────┘
              │
              ▼
┌─────────────────────────────────────┐
│      Controllers & Routes           │
│    (Request/Response Handling)      │
└─────────────────────────────────────┘
              │
              ▼
┌─────────────────────────────────────┐
│      Service Layer                   │
│    (Business Logic)                  │
└─────────────────────────────────────┘
              │
              ▼
┌─────────────────────────────────────┐
│      Prisma ORM                      │
│    (Database Abstraction)            │
└─────────────────────────────────────┘
              │
              ▼
┌─────────────────────────────────────┐
│      MariaDB Database                │
│    (Data Persistence)                │
└─────────────────────────────────────┘

📋 Prerequisites

Before starting, ensure you have:

Node.js (v20 or higher) - Download
npm or yarn package manager
MariaDB server (v10.6 or higher) - Running locally or on Filess.io
Git for version control
A code editor (VS Code recommended)

💡 Using Filess.io

If you don't have MariaDB installed locally, you can use Filess.io's managed MariaDB service. Create a database instance through the Filess Console and use the provided connection string.

🚀 Step 1: Project Setup

Clone or Navigate to the Project

The example application is located in the example-app directory. Navigate to it:

cd example-app

Install Dependencies

Install all required npm packages:

npm install

This will install:

Express.js - Web framework
Prisma - ORM for database access
TypeScript - Type-safe JavaScript
Helmet - Security middleware
CORS - Cross-origin resource sharing
And other dependencies

🔧 Step 2: Database Configuration

Create Environment File

Copy the example environment file:

cp env.example .env

Configure Database Connection

Edit the .env file and update the DATABASE_URL:

DATABASE_URL="mysql://username:password@localhost:3306/filess_example?schema=public"

For Filess.io users, your connection string will look like:

DATABASE_URL="mysql://user:[email protected]:3306/filess_example?schema=public"

🔐 Connection String Format

The connection string format is:

mariadb://[username]:[password]@[host]:[port]/[database]?schema=public

Replace:

username: Your database username
password: Your database password
host: Database host (localhost or Filess.io instance)
port: Database port (usually 3306)
database: Database name (e.g., filess_example)

Create the Database

If using a local MariaDB instance, create the database:

CREATE DATABASE filess_example;

For Filess.io, the database is created automatically when you provision an instance in shared mode.

Configure Shadow Database (Required for Shared Mode)

Prisma uses a shadow database during migrations to validate schema changes before applying them. This is especially important when using Filess.io in shared mode.

⚠️ Shared Mode Limitation

In shared mode, each database user only has access to a single database. Since Prisma needs a separate shadow database for migration validation, you must configure SHADOW_DATABASE_URL to point to a different database.

In dedicated mode, you have root access and can create multiple databases under the same user, so you can either:

Create a separate shadow database on the same instance
Or omit the shadow database URL (Prisma will try to use the same database)

For Filess.io Shared Mode:

You'll need to create a second database instance for the shadow database, or use a different database name if your plan allows multiple databases. Update your .env file:

DATABASE_URL="mysql://user:[email protected]:3306/filess_example?schema=public"
SHADOW_DATABASE_URL="mysql://user:[email protected]:3306/filess_example_shadow?schema=public"

For Filess.io Dedicated Mode:

With root access, you can create a shadow database on the same instance:

CREATE DATABASE filess_example_shadow;

Then configure:

DATABASE_URL="mysql://root:[email protected]:3306/filess_example?schema=public"
SHADOW_DATABASE_URL="mysql://root:[email protected]:3306/filess_example_shadow?schema=public"

For Local Development:

Create both databases:

CREATE DATABASE filess_example;
CREATE DATABASE filess_example_shadow;

Then configure:

DATABASE_URL="mysql://user:password@localhost:3306/filess_example?schema=public"
SHADOW_DATABASE_URL="mysql://user:password@localhost:3306/filess_example_shadow?schema=public"

💡 Why Shadow Database?

Prisma's migration engine uses a shadow database to:

Validate schema changes before applying them
Detect drift between your schema and the actual database
Ensure migrations are safe to apply

Without a shadow database in shared mode, Prisma cannot perform these validations and migrations may fail.

🗄️ Step 3: Database Schema Setup

Generate Prisma Client

Generate the Prisma Client based on the schema:

npm run prisma:generate

This reads prisma/schema.prisma and generates TypeScript types and database access code.

Run Migrations

Create and apply the database schema:

npm run prisma:migrate

When prompted, name your migration (e.g., init). This will:

Create a migration file in prisma/migrations/
Apply the migration to your database
Create all tables: customers, products, sales, purchases, sale_items, purchase_items

✅ Migration Complete

After running migrations, your database will have:

6 tables with proper relationships
Foreign key constraints
Indexes for performance
Timestamps for audit trails

🌱 Step 4: Seed Sample Data

Populate the database with sample data for testing:

npm run prisma:seed

This creates:

3 customers with contact information
5 products with pricing and inventory
3 sales transactions with line items
2 purchases from suppliers

The seed script also demonstrates:

Automatic stock management (decrements on sales, increments on purchases)
Transaction handling for data consistency
Relationship management between entities

🏃 Step 5: Start the Application

Backend Only (API Server)

Run the backend API server with hot reload:

npm run dev

The server will start on http://localhost:3000 (or your configured port).

Frontend Only

To run just the frontend development server:

npm run dev:frontend

Or navigate to the frontend directory:

cd frontend
npm install  # First time only
npm run dev

The frontend will be available at http://localhost:5173.

💡 Frontend Configuration

The frontend automatically connects to the backend API at http://localhost:3000/api/v1. To change this, create a .env file in the frontend/ directory:

VITE_API_URL=http://localhost:3000/api/v1

Full Stack (Frontend + Backend)

Run both frontend and backend together:

npm run dev:all

This will start:

Backend API: http://localhost:3000
Frontend App: http://localhost:5173

🚀 Recommended Development Workflow

Use npm run dev:all for the best development experience. This runs both servers simultaneously with hot reload enabled.

Production Mode

For production builds:

Backend:

npm run build
npm start

Frontend:

cd frontend
npm run build
npm run preview  # Preview production build

✅ Step 6: Verify Installation

Backend Health Check

Test that the backend server is running:

curl http://localhost:3000/health

Expected response:

{
  "status": "ok",
  "timestamp": "2025-01-15T10:30:00.000Z",
  "service": "filess-example-app"
}

Test API Endpoints

List all customers:

curl http://localhost:3000/api/v1/customers

List all products:

curl http://localhost:3000/api/v1/products

List all sales:

curl http://localhost:3000/api/v1/sales

Frontend Verification

Open the frontend in your browser: http://localhost:5173
Verify the Dashboard shows statistics for:
- Number of customers
- Number of products
- Number of sales
- Number of purchases
Test the navigation by clicking through:
- Dashboard
- Customers (view, create, edit, delete)
- Products (view, create, edit, delete)
- Sales (view and create)
- Purchases (view and create)
Test CRUD operations:
- Create a new customer through the UI
- Edit an existing product
- Create a sale with multiple items
- Verify stock updates automatically

✅ Frontend Features

The frontend provides a complete user interface with:

Dashboard: Overview with statistics
Customer Management: Full CRUD operations
Product Management: Full CRUD with stock indicators
Sales Management: Create sales with multiple line items
Purchase Management: Create purchase orders from suppliers
Responsive Design: Works on desktop and mobile devices

📚 Understanding the Codebase

Project Structure

example-app/
├── frontend/              # React frontend application
│   ├── src/
│   │   ├── components/   # Reusable React components
│   │   │   ├── Layout.tsx
│   │   │   └── Layout.css
│   │   ├── pages/         # Page components
│   │   │   ├── Dashboard.tsx
│   │   │   ├── Customers.tsx
│   │   │   ├── Products.tsx
│   │   │   ├── Sales.tsx
│   │   │   └── Purchases.tsx
│   │   ├── services/      # API client services
│   │   │   └── api.ts
│   │   ├── types/         # TypeScript type definitions
│   │   │   └── index.ts
│   │   ├── App.tsx        # Main app component with routing
│   │   └── main.tsx       # Entry point
│   ├── package.json       # Frontend dependencies
│   └── vite.config.ts     # Vite configuration
├── prisma/
│   ├── schema.prisma      # Database schema definition
│   └── seed.ts            # Sample data seeding script
├── src/                    # Backend source code
│   ├── controllers/       # Request handlers
│   │   ├── customer.controller.ts
│   │   ├── product.controller.ts
│   │   ├── sale.controller.ts
│   │   └── purchase.controller.ts
│   ├── routes/            # API route definitions
│   │   ├── customer.routes.ts
│   │   ├── product.routes.ts
│   │   ├── sale.routes.ts
│   │   └── purchase.routes.ts
│   ├── services/          # Business logic layer
│   │   ├── customer.service.ts
│   │   ├── product.service.ts
│   │   ├── sale.service.ts
│   │   └── purchase.service.ts
│   ├── lib/               # Shared utilities
│   │   └── prisma.ts      # Prisma client instance
│   ├── types/             # TypeScript type definitions
│   │   └── index.ts
│   └── index.ts           # Application entry point
├── package.json           # Backend dependencies and scripts
├── tsconfig.json         # TypeScript configuration
└── README.md             # Project documentation

🛠️ Useful Commands

Database Management

# Generate Prisma Client
npm run prisma:generate

# Create a new migration
npm run prisma:migrate

# Open Prisma Studio (database GUI)
npm run prisma:studio

# Reset database and reseed
npm run db:reset

Development

# Run backend only (with hot reload)
npm run dev

# Run frontend only
npm run dev:frontend

# Run both frontend and backend together
npm run dev:all

# Build backend for production
npm run build

# Run production backend
npm start

# Build frontend for production
cd frontend && npm run build

🎓 Key Concepts Demonstrated

1. Database transactions

Sales and purchases use transactions to ensure data consistency:

return this.prisma.$transaction(async (tx) => {
  const sale = await tx.sale.create({ /* ... */ });
  // Update stock atomically
  await tx.product.update({ /* ... */ });
  return sale;
});

2. Relationship management

Prisma handles complex relationships automatically:

const sale = await prisma.sale.findUnique({
  where: { id },
  include: {
    customer: true,
    items: {
      include: {
        product: true
      }
    }
  }
});

3. Error handling

Proper error handling with meaningful messages:

try {
  const customer = await customerService.create(req.body);
  res.status(201).json(customer);
} catch (error: unknown) {
  if (error.code === 'P2002') {
    res.status(409).json({ error: 'Email already exists' });
    return;
  }
  res.status(500).json({ error: 'Failed to create customer' });
}

🔮 What's Next?

This base application is ready for advanced features that will be covered in upcoming tutorials:

Database Triggers - Implement business logic at the database level
Automated Backups & PITR - Point-in-time recovery for human errors
Private Networking - Secure database access with Tailscale
Firewall Configuration - IP filtering and access control
SSH Tunneling - Secure database administration
Root/Superuser Access - Advanced MariaDB capabilities
Percona Monitoring and Management - Enterprise-grade monitoring with PMM
REST API Automation - Infrastructure as Code with Terraform
Maintenance Windows - Zero-downtime operations

🐛 Troubleshooting

Database Connection Issues

Error: Can't reach database server

Solutions:

Check connection string in .env
Verify network connectivity to database host
Check firewall rules if using remote database

Migration Errors

Error: Migration failed

Solutions:

Ensure database exists: CREATE DATABASE filess_example;
Check user permissions
Verify connection string format
Try resetting: npm run db:reset

Shadow Database Errors

Error: Can't reach shadow database server or Access denied for shadow database

Solutions:

For Shared Mode: Ensure SHADOW_DATABASE_URL points to a different database instance or a separate database that your user has access to
For Dedicated Mode: Create the shadow database: CREATE DATABASE filess_example_shadow;
Verify the shadow database user has proper permissions
Check that SHADOW_DATABASE_URL is correctly set in your .env file
Ensure the shadow database connection string uses the same credentials format as DATABASE_URL

Error: P3009: Migration failed to apply. New migration detected.

This error often occurs when the shadow database is not properly configured. Ensure SHADOW_DATABASE_URL is set and accessible.

Port Already in Use

Error: Port 3000 is already in use

Solutions:

Change PORT in .env file
Kill the process using the port: lsof -ti:3000 | xargs kill

📖 Additional Resources

✅ Checklist

Before moving to advanced tutorials, ensure you can:

Backend

Start the backend API server successfully
Create, read, update, and delete customers via API
Create, read, update, and delete products via API
Create sales with automatic stock management via API
Create purchases with automatic stock updates via API
View all data through API endpoints (curl or Postman)
Understand the backend project structure
Access Prisma Studio to view database

Frontend

Once you've completed this checklist, you're ready to explore advanced database and DevOps features in the upcoming tutorials!

🎯 What You'll Build​

🏗️ Architecture Overview​

📋 Prerequisites​

🚀 Step 1: Project Setup​

Clone or Navigate to the Project​

Install Dependencies​

🔧 Step 2: Database Configuration​

Create Environment File​

Configure Database Connection​

Create the Database​

Configure Shadow Database (Required for Shared Mode)​

🗄️ Step 3: Database Schema Setup​

Generate Prisma Client​

Run Migrations​

🌱 Step 4: Seed Sample Data​

🏃 Step 5: Start the Application​

Backend Only (API Server)​

Frontend Only​

Full Stack (Frontend + Backend)​

Production Mode​

✅ Step 6: Verify Installation​

Backend Health Check​

Test API Endpoints​

Frontend Verification​

📚 Understanding the Codebase​

Project Structure​

🛠️ Useful Commands​

Database Management​

Development​

🎓 Key Concepts Demonstrated​

1. Database transactions​

2. Relationship management​

3. Error handling​

🔮 What's Next?​

🐛 Troubleshooting​

Database Connection Issues​

Migration Errors​

Shadow Database Errors​

Port Already in Use​

📖 Additional Resources​

✅ Checklist​

Backend​

Frontend​

🎯 What You'll Build

🏗️ Architecture Overview

📋 Prerequisites

🚀 Step 1: Project Setup

Clone or Navigate to the Project

Install Dependencies

🔧 Step 2: Database Configuration

Create Environment File

Configure Database Connection

Create the Database

Configure Shadow Database (Required for Shared Mode)

🗄️ Step 3: Database Schema Setup

Generate Prisma Client

Run Migrations

🌱 Step 4: Seed Sample Data

🏃 Step 5: Start the Application

Backend Only (API Server)

Frontend Only

Full Stack (Frontend + Backend)

Production Mode

✅ Step 6: Verify Installation

Backend Health Check

Test API Endpoints

Frontend Verification

📚 Understanding the Codebase

Project Structure

🛠️ Useful Commands

Database Management

Development

🎓 Key Concepts Demonstrated

1. Database transactions

2. Relationship management

3. Error handling

🔮 What's Next?

🐛 Troubleshooting

Database Connection Issues

Migration Errors

Shadow Database Errors

Port Already in Use

📖 Additional Resources

✅ Checklist

Backend

Frontend