AethX Express

A robust TypeScript-based Express API template with PostgreSQL database integration, featuring Knex migrations and Kysely query builder for type-safe database operations.

🚀 Quick Start

Installation

# Install globally
npm install -g aethx-express

# Create a new project
aethx-express my-api-project
cd my-api-project

# Or create in current directory
mkdir my-project && cd my-project
aethx-express .

Prerequisites

• Node.js (v18+)
• Docker and Docker Compose
• npm or yarn

Environment Setup

1. After project creation, review the .env file in your project root:

DB_HOST=localhost
DB_USER=postgres
DB_PASSWORD=postgres
DB_NAME=aethx_db
DB_PORT=5432

2. Install dependencies:

npm install

3. Change DB name in the init.sql

Running the Application

The application utilizes Docker for consistent development environments:

1. Fresh Start (Wipes DB and starts fresh):

npm run fresh

This will:

• Stop and remove all existing containers and volumes
• Rebuild and start the containers
• Start the application with hot-reloading

After running fresh, you need to run all migrations:

npm run migrate-up:all

2. Development Mode (Resume existing setup):

npm run dev

This will:

• Start PostgreSQL in a Docker container
• Build and start the API container
• Enable hot-reloading for development

⚠️ Note: The application requires Docker to run. Make sure Docker is installed and running on your system.

📚 Database Management

Database Architecture

The project uses a dual-layer database approach:

• Knex: Handles database migrations and schema changes
• Kysely: Provides type-safe query building and execution

Migrations

All migrations require Docker to be running. Make sure your Docker containers are up before running any migration commands.

Migration Workflow

1. Create a new migration:

npm run migrate:new -- migration_name

2. Run the new migration:

npm run migrate-up:once

3. To rollback (undo) the migration:

npm run migrate-down:once

⚠️ Note: All migration commands automatically regenerate TypeScript types for Kysely after execution.

Database Types

TypeScript types are automatically generated from your database schema after each migration. The types are stored in src/database/types.ts.

To manually regenerate types:

npm run generate-types

Query Examples

Using Kysely for type-safe queries:

import { db } from "@/database/kysely";
import type { Row, InsertRow, UpdateRow } from "@/database/types";

// Select
const users = await db
    .selectFrom("users")
    .selectAll()
    .where("id", "=", userId)
    .execute();

// Insert
const newUser: InsertRow<"users"> = {
    name: "John Doe",
    email: "[email protected]",
};
await db.insertInto("users").values(newUser).execute();

// Update
const updates: UpdateRow<"users"> = {
    name: "Jane Doe",
};
await db.updateTable("users").set(updates).where("id", "=", userId).execute();

// Transaction
await db.transaction().execute(async (trx) => {
    const user = await trx
        .insertInto("users")
        .values(newUser)
        .returning("id")
        .executeTakeFirst();
    
    await trx
        .insertInto("profiles")
        .values({
            userId: user.id,
            // ... other fields
        })
        .execute();
});

🏗️ Project Structure

my-api-project/
├── src/
│   ├── app.ts              # Application entry point
│   ├── database/           # Database configuration and utilities
│   │   ├── index.ts        # Database module entry point
│   │   ├── kysely.ts       # Kysely configuration
│   │   └── types.ts        # Generated database types
│   ├── routes/             # API routes
│   ├── controllers/        # Route controllers
│   ├── middleware/         # Express middleware
│   └── utils/              # Utility functions
├── migrations/
│   ├── scripts/            # Migration files
│   └── hooks.ts            # Migration hooks
├── scripts/
│   ├── create-migration.ts # Migration creation script
│   └── generate-db-types.ts # Type generation script
├── docker-compose.yml      # Docker configuration
├── Dockerfile.dev          # Development Dockerfile
├── knexfile.ts             # Knex configuration
└── package.json

🧱 File Structure Overview

The template follows a Xest-inspired pattern where responsibility flows from routes to controllers, actions, and queries.

• Router: Determines which controller handles each incoming request.
• Controller: Validates request payloads, orchestrates application logic, and decides what response to send back.
• Action: Encapsulates reusable business logic and talks to the database. Actions can be reused across controllers or other modules.
• Query: Contains SQL-focused helpers (via Kysely) for direct data access using primitives like submitQuery.

Naming Conventions

Use consistent verbs based on the CRUD operation you are implementing:

| Context | Controller | Action | Query | | ------- | ---------- | ------ | ----- | | Create | post | create | insert | | Read | get | fetch | select | | Update | put | modify | update | | Delete | delete | remove | delete |

For example, to create a new user, prefer postUsers-Controller, createUser-Action, and insertUser-Query. Reading a user should map to getUsers-Controller, fetchUser-Action, and selectUser-Query, and so on. This consistency keeps the codebase navigable for larger teams.

🔧 Development Workflow

1. Create a new feature branch:

git switch -c feature/your-feature

2. Make your changes

3. Test your changes:

npm run test

4. Start the application in development mode:

npm run dev

📝 Features

• Type-Safe Database Operations: Fully typed database queries with Kysely
• Migration System: Easy database schema management with Knex migrations
• Docker Integration: Consistent development environments
• Hot Reloading: Fast development iterations
• Express Middleware Structure: Organized middleware architecture
• Environment Configuration: Simple environment variable management
• Development Tooling: Scripts for common development tasks

📋 Available Scripts

• npm run dev: Start development server with hot reloading
• npm run fresh: Fresh start (rebuild containers and database)
• npm run build: Build the project
• npm run start: Start the built project
• npm run migrate:new -- name: Create a new migration
• npm run migrate-up:once: Run the next pending migration
• npm run migrate-up:all: Run all pending migrations
• npm run migrate-down:once: Rollback the last migration
• npm run generate-types: Generate database types
• npm run test: Run tests

🤝 Contributing

Contributions are welcome! Please feel free to submit a Pull Request.

📄 License

This project is licensed under the MIT License - see the LICENSE file for details.