Getting Started with @xond/api

@xond/api is the backend engine of the Xond framework. It provides code generation, reverse engineering, and REST API scaffolding for rapid application development.

Prerequisites

Node.js 18+ and pnpm installed
A database (PostgreSQL, MySQL, or SQL Server)
Basic familiarity with Prisma and Express

Installation

@xond/api is typically installed as a dependency in your backend application:

pnpm add @xond/api

Or if you're working within the Nufaza monorepo:

pnpm --filter @xond/api install

Quick Start

The typical workflow for creating a new application with @xond/api follows these steps:

Design your database – Create tables, relationships, and constraints
Set up environment – Configure .env with database connection
Create database structure – Run the structure SQL file
Reverse engineer Prisma schema – Generate Prisma models from your database
Generate code – Create REST services, controllers, and frontend models
Insert dummy data – Load test data from Excel files

Let's walk through each step in detail.

Step-by-Step: Creating Your First App

1. Design Your Database

Start by designing your database schema. You can use tools like PowerDesigner, MySQL Workbench, or any database design tool. Export your schema as SQL DDL statements.

Database Naming Conventions

@xond/api follows strict naming conventions that ensure consistency across all projects:

1. snake_case for All Database Objects

All database objects (tables, columns, indexes, constraints) must use snake_case (lowercase with underscores):

-- ✅ Correct
CREATE TABLE user_account (
    user_id UUID PRIMARY KEY,
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    email_address VARCHAR(255),
    created_at TIMESTAMP DEFAULT NOW()
);

-- ❌ Incorrect (camelCase)
CREATE TABLE userAccount (
    userId UUID PRIMARY KEY,
    firstName VARCHAR(100)
);

-- ❌ Incorrect (PascalCase)
CREATE TABLE UserAccount (
    UserId UUID PRIMARY KEY,
    FirstName VARCHAR(100)
);

2. UUID as Primary Key Type

Always use UUID (Universally Unique Identifier) as the primary key type for all tables:

-- ✅ Correct
CREATE TABLE product (
    product_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(255),
    price DECIMAL(10, 2)
);

-- ❌ Incorrect (using INTEGER or SERIAL)
CREATE TABLE product (
    product_id SERIAL PRIMARY KEY,  -- Don't use this
    name VARCHAR(255)
);

Benefits of UUID:

Globally unique across all systems
No conflicts when merging data from different sources
Better for distributed systems
Works well with replication and sharding

3. Primary Key Naming: modelName_id

Primary keys must follow the format modelName_id where modelName is the singular form of the table name:

-- ✅ Correct
CREATE TABLE user (
    user_id UUID PRIMARY KEY,  -- Table: user → Primary key: user_id
    name VARCHAR(100)
);

CREATE TABLE product (
    product_id UUID PRIMARY KEY,  -- Table: product → Primary key: product_id
    name VARCHAR(255)
);

CREATE TABLE order_item (
    order_item_id UUID PRIMARY KEY,  -- Table: order_item → Primary key: order_item_id
    quantity INTEGER
);

-- ❌ Incorrect
CREATE TABLE user (
    id UUID PRIMARY KEY,  -- Should be: user_id
    name VARCHAR(100)
);

CREATE TABLE product (
    productId UUID PRIMARY KEY,  -- Should be: product_id (snake_case)
    name VARCHAR(255)
);

4. Foreign Key Naming

Foreign keys should follow the same pattern, referencing the primary key name:

-- ✅ Correct
CREATE TABLE order (
    order_id UUID PRIMARY KEY,
    user_id UUID REFERENCES user(user_id),  -- References user.user_id
    total_amount DECIMAL(10, 2)
);

CREATE TABLE order_item (
    order_item_id UUID PRIMARY KEY,
    order_id UUID REFERENCES order(order_id),  -- References order.order_id
    product_id UUID REFERENCES product(product_id),  -- References product.product_id
    quantity INTEGER
);

-- ❌ Incorrect
CREATE TABLE order (
    order_id UUID PRIMARY KEY,
    userId UUID REFERENCES user(user_id),  -- Should be: user_id (snake_case)
    total_amount DECIMAL(10, 2)
);

5. Table Naming

Table names should be:

snake_case (lowercase with underscores)
Singular form (e.g., user, not users)
Descriptive and clear

-- ✅ Correct
CREATE TABLE user (...);
CREATE TABLE product (...);
CREATE TABLE order_item (...);
CREATE TABLE user_role (...);

-- ❌ Incorrect
CREATE TABLE users (...);  -- Should be singular: user
CREATE TABLE UserAccount (...);  -- Should be snake_case: user_account
CREATE TABLE orderItem (...);  -- Should be snake_case: order_item

Summary of Naming Rules:

Object Type	Convention	Example
Table	snake_case, singular	`user`, `product`, `order_item`
Primary Key	`modelName_id` (UUID)	`user_id`, `product_id`, `order_item_id`
Foreign Key	`referencedTable_id` (UUID)	`user_id`, `product_id`
Column	snake_case	`first_name`, `email_address`, `created_at`
Index	snake_case	`idx_user_email`, `idx_product_name`

Why These Conventions?

snake_case: Standard database convention, works across all database systems
UUID: Ensures global uniqueness, better for distributed systems
modelName_id: Makes primary keys immediately identifiable and consistent
Consistency: All projects follow the same patterns, making code generation predictable

Following these conventions ensures that:

Reverse engineering produces consistent Prisma schemas
Code generation works correctly
Generated code follows predictable patterns
Team members can easily understand any project

2. Set Up Environment Variables

Create a .env file in your backend application root. Important: Store .env files in a shared cloud storage location (like Google Drive, OneDrive, or a shared network drive) so that:

Designers can work on database structures and update .env files
Developers can download the latest .env files before starting work
Team members stay synchronized with the latest database configuration

Required Environment Variables

The .env file must contain at minimum:

# Database Configuration (REQUIRED)
# Choose one based on your database type:
DATABASE_URL=postgresql://user:password@localhost:5432/mydb
# or
DATABASE_URL=mysql://user:password@localhost:3306/mydb
# or
DATABASE_URL=sqlserver://server;database=mydb;user=user;password=password;encrypt=true

# Environment (REQUIRED)
NODE_ENV=development

# Database File Paths (REQUIRED)
# These point to files in your shared cloud storage:
# Path to the initial database structure SQL file
DATABASE_STRUCTURE_FILE=/path/to/shared/storage/create-struct-0.0.1.sql

# Path to directory containing versioned update SQL files (update-struct-*.sql)
DATABASE_UPDATE_FILE=/path/to/shared/storage/update-struct

# Path to Excel file containing dummy/reference data
DATABASE_DUMMY_FILE=/path/to/shared/storage/Dummy Data & Reference.xlsx

# Optional: System user ID for audit logs
DEFAULT_SYSTEM_USER_ID=00000000-0000-0000-0000-000000000000

Example with OneDrive shared storage:

DATABASE_URL=postgresql://user:password@localhost:5432/mydb
NODE_ENV=development

# OneDrive shared library paths
DATABASE_STRUCTURE_FILE=/Users/username/Library/CloudStorage/OneDrive-SharedLibraries-nufaza/JDP - Documents/Analysis and Design/Database Modelling/create-struct-0.0.1.sql

DATABASE_UPDATE_FILE=/Users/username/Library/CloudStorage/OneDrive-SharedLibraries-nufaza/JDP - Documents/Analysis and Design/Database Modelling/update-struct

DATABASE_DUMMY_FILE=/Users/username/Library/CloudStorage/OneDrive-SharedLibraries-nufaza/JDP - Documents/Analysis and Design/Database Dummy Data & Reference/Aqal Master Data & Dummy v0.0.1.xlsx

Note: Paths can use absolute paths (as shown) or relative paths from your application root. Absolute paths work best when files are in cloud storage that syncs to different locations on different machines.

File Path Configuration

DATABASE_STRUCTURE_FILE – Points to a single SQL file containing the initial database structure (DDL). This file is executed by xond-api create.
DATABASE_UPDATE_FILE – Points to a directory containing versioned update SQL files. Files should be named update-struct-<version>.sql (e.g., update-struct-1.0.0.sql, update-struct-1.1.0.sql). These are executed in order by xond-api update.
DATABASE_DUMMY_FILE – Points to an Excel file containing dummy/reference data. Each worksheet represents a table, with the first row as column headers. This file is processed by xond-api insert.

Shared Storage Workflow

Designer workflow:
- Design database in PowerDesigner or similar tool
- Export structure SQL file (create-struct-0.0.1.sql)
- Create versioned update files (update-struct-1.0.0.sql, etc.) as needed
- Prepare Excel file with dummy/reference data
- Upload all files to shared cloud storage (OneDrive, Google Drive, etc.)
- Update .env file with paths pointing to shared storage locations
- Upload .env file to shared storage
Developer workflow:
- Download latest .env file from shared storage
- Place it in your application root directory
- Ensure cloud storage is synced locally (OneDrive sync, Google Drive sync, etc.)
- Verify paths in .env match your local sync location
- Run xond-api check to verify configuration
- Proceed with database creation and code generation

Note:

.env files are gitignored and should never be committed to version control
Use absolute paths in .env files for cloud storage locations
Ensure cloud storage is synced before running commands that need these files
Update .env paths if cloud storage sync location differs between team members

3. Prepare Database Files

Database files should be organized in your shared cloud storage (not in the repository):

Shared Cloud Storage/
├── Database Modelling/
│   ├── create-struct-0.0.1.sql     # Initial database structure (DDL)
│   └── update-struct/              # Directory for versioned updates
│       ├── update-struct-1.0.0.sql
│       ├── update-struct-1.1.0.sql
│       └── update-struct-2.0.0.sql
└── Database Dummy Data & Reference/
    └── Master Data & Dummy v0.0.1.xlsx  # Excel file with dummy/reference data

Note: The prisma/schema.prisma file will be generated locally in your application's prisma/ directory after reverse engineering. This file should be committed to version control.

4. Create Database Structure

Run the structure creation command:

pnpm --filter your-api-app xond-api create

This executes prisma/structure.sql against your database.

5. Reverse Engineer Prisma Schema

After creating the database structure, generate the Prisma schema:

pnpm --filter your-api-app prisma db pull
pnpm --filter your-api-app prisma format

This creates prisma/schema.prisma from your existing database.

6. Generate Code

Generate REST services, controllers, and frontend models:

pnpm --filter your-api-app xond-api generate

This creates:

src/modules/json/services/generated/*Service.ts – REST service implementations
src/generated/models/*Model.tsx – Frontend TypeScript models (if UI path is configured)
prisma/schema.ts – JSON representation of your schema

7. Insert Dummy Data

Load test data from your Excel file:

pnpm --filter your-api-app xond-api insert

This reads prisma/dummy.xlsx, generates JavaScript loaders in prisma/dummy/, and executes them to populate your database.

Getting Started with @xond/api

Prerequisites​

Installation​

Quick Start​

Step-by-Step: Creating Your First App​

1. Design Your Database​

Database Naming Conventions​

2. Set Up Environment Variables​

Required Environment Variables​

File Path Configuration​

Shared Storage Workflow​

3. Prepare Database Files​

4. Create Database Structure​

5. Reverse Engineer Prisma Schema​

6. Generate Code​

7. Insert Dummy Data​

Next Steps​