Introduction

This is a real repo that I used to quickly generate boilerplate stacks for customers that wanted small to medium prototype projects. It has since been simplified to suit a general audience.

The objective of this project was to save time when building simple data pipelines, so that more time and effort could be put into building frontend systems.

Authorisation systems have been removed from this project as the solution was tightly coupled to my AWS account, and it was determined that the stack can be better used as a template with no authorisation included. This allows users to build out authorisation protocols to best suit their needs.

System Patterns and Authorial Intent

The repo uses a structure of my own design that mimics a small monorepo. This structure is designed to scale to medium-sized projects, but has the capacity to handle autoscaling groups horizontally from behind an application load balancer post-deployment.

Because the docker-compose handles the database connection autonomously by generating the db models at deploy time, it is trivial to migrate the database structure to a relational cloud storage solution (such as RDS).

The database autogeneration system follows a preprocessing code-gen CRUD framework to quickly build strongly opinionated data contracts between the API and DB with minimal requirement from the user.

The generation code is fully transparent and was written with expansion in mind, making it ideal for frontend focussed projects.

Unique mappings are supported by the way of including operations alongside the autooperations, and the database connection is natively SQL first.

The objective was to give a strong launchpad for a project without limiting developer freedom when working on a fullstack solution. With that in mind, the framework should be treated as a sidecar functionality and should not act to constrain the user's functionality.

The concept was to keep the ecosystem constrained to a single container instance for rapid prototyping; with a reverse proxy to simplify expansion of the docker-compose and obfuscate port bindings and IP systems behind an internal network.

Items for upgrade, in order of importance, would be:

1. Database Migrations
2. Testing for API endpoint autooperations
3. Authentication endpoint support including OAuth, JWT, and PAT options added to the autooperations
4. Webhook Receivers
5. Cloudformation autodeployment options
6. OpenAPI/Swagger specification support
7. MacOS support (I dont have an iOS device to test functionality, and bat is notoriously Windows-coded)

Repo Structure

File Tree

├─ api
│  ├─ autooperations/        # Autogenerated endpoint operation files
│  ├─ functions/
│  │  ├─ ConnectToDB.js      # Database connection using .env
│  │  └─ LoadOperations.js   # Dynamically loads operations and autooperations
│  ├─ operations/            # User-defined API endpoint operations
│  ├─ Dockerfile             # Build instructions for API container
│  ├─ express.js             # Express application entry point
│  └─ package.json
│
├─ db
│  ├─ init.sh                # Autogenerated DB init script (table creation)
│
├─ db-models/                # User-defined DB models → operations
│
├─ frontend
│  ├─ public/                # Public assets
│  ├─ src/                   # Frontend source
│  ├─ eslint.config.js
│  ├─ index.html
│  ├─ package.json
│  └─ vite.config.js
│
├─ proxy
│  ├─ mime.types
│  └─ nginx.conf             # Reverse proxy routing
│
├─ docker-compose.yml
├─ deploy.bat                # Rebuild script for autooperations (dev)
├─ DataGenerator.js          # Shared data-generation utilities
├─ compile-models.js         # Compiles db-models into autooperations
└─ .env                      # Environment variables

Usage

Setup

1. Add Database Models to db-models
2. Add Any Custom Operations needed to the API/operations folder
3. Update .env with your parameters
4. run deploy

Stack Infrastructure

API: Express API

Frontend: Vite React

Proxy: NGINX

DB: Postgres

Creating Models

Models are populated from the db-models directory

A model should be a json file representing a table and its attributes.

Currently no system for DB functions exists

The system is built to generate the following on each deploy, for each model present:

• A DB Table
• An Upsert (Create and Update) Operation
• A Delete Operation
• An Upsert Frontend Function
• A Delete Frontend Function

Model Format

Models are a json formatted list of SQL keywords, with a table name ALWAYS corresponding to the file name.

The repo includes a sample model in db-models/ for immediate reference.

The first item is recommended to be the Primary Key, which should be a UUID ( but is not strictly required to be a UUID ).

A standard Primary Key for a users table would be:

{
  "user_id": ["UUID", "PRIMARY KEY", "DEFAULT gen_random_uuid()"],
}

gen_random_uuid() comes from the pgcrypto extension which is included in the package by default.

Currently the general format expectation of the keywords is:

{
  <column_name>: [<sql_type>, [...<sql_keywords>]]
}

More specifically, the generators use the sql keywords to make deductions about your needs. Most notably using Foreign Key Constraints, which require special formats, as so:

{
  <column_name>: ["<sql_type>", "FOREIGN KEY", "<modelName>", "(<key_restraint>)"]
}

Take note of the curly brackets in the key_restraint field, as these are both necessary and expected.

As such, an example foreign key would be:

{
  user_group: ["UUID", "FOREIGN KEY", "UserGroups", "(user_group_id)"]
}

ENV File

This project intentionally keeps the ENV surface minimal to preserve its role as a rapid-prototyping base

POSTGRES_USER=(Your User)
POSTGRES_PASSWORD=(Your Password)
POSTGRES_DB=(Your DB Name)

Versioning Information

Node v18 Postgres 4 Docker-Compose v2