A Node.js Fastify API service with Inversify dependency injection that reads Excel files and updates a SQL Server database with PTO (Paid Time Off) records.
- Fastify - Fast and low overhead web framework
- Inversify - Powerful IoC container for dependency injection
- TypeScript - Type-safe development
- Excel Processing - Read data from Excel files using xlsx library
- SQL Server Integration - Connect and update SQL Server database
- Clean Architecture - Organized folder structure with separation of concerns
├── src/
│ ├── config/ # Configuration files
│ │ ├── interfaces.ts # Configuration interfaces
│ │ └── config.ts # Configuration implementations
│ ├── controllers/ # Request handlers
│ │ └── pto.controller.ts
│ ├── routers/ # Route definitions
│ │ └── pto.router.ts
│ ├── services/ # Business logic
│ │ ├── excel.service.ts
│ │ ├── database.service.ts
│ │ └── pto.service.ts
│ ├── container.ts # Inversify DI container setup
│ ├── types.ts # Dependency injection symbols
│ └── index.ts # Application entry point
├── data/ # Excel files directory
├── .env.example # Environment variables template
├── package.json
└── tsconfig.json
- Node.js (v18 or higher)
- npm or yarn
- SQL Server database
- Excel file with ID and PTO columns
- Clone the repository and install dependencies:
npm install- Copy
.env.exampleto.envand configure your settings:
cp .env.example .env- Update the
.envfile with your database credentials and Excel file configuration:
# Server Configuration
PORT=3000
HOST=0.0.0.0
# Database Configuration
DB_SERVER=your_sql_server
DB_PORT=1433
DB_DATABASE=your_database
DB_USER=your_username
DB_PASSWORD=your_password
DB_ENCRYPT=true
DB_TRUST_SERVER_CERTIFICATE=true
# Excel File Configuration
EXCEL_FILE_PATH=./data/employee_data.xlsx
EXCEL_SHEET_NAME=Sheet1
EXCEL_ID_COLUMN=ID
EXCEL_PTO_COLUMN=PTO
# Database Table Configuration
DB_TABLE_NAME=EmployeePTOCreate the PTO table in your SQL Server database:
CREATE TABLE EmployeePTO (
ID VARCHAR(50) PRIMARY KEY,
PTO DECIMAL(10, 2) NOT NULL,
CreatedAt DATETIME2 DEFAULT GETDATE(),
UpdatedAt DATETIME2 DEFAULT GETDATE()
);
-- Create index for better performance
CREATE INDEX IX_EmployeePTO_ID ON EmployeePTO(ID);Your Excel file should have at minimum two columns:
| ID | PTO |
|---|---|
| E001 | 15.5 |
| E002 | 20.0 |
| E003 | 12.5 |
- ID Column: Unique identifier for each employee
- PTO Column: PTO hours/days (numeric value)
Place your Excel file in the ./data/ directory (or specify a different path in .env).
npm run devnpm run build
npm startnpm run watchEndpoint: POST /api/pto/update
Description: Reads the Excel file and updates the database with PTO records. If a record with the ID exists, it updates the PTO value; otherwise, it creates a new record.
Request: No parameters required
Response:
{
"status": "success",
"data": {
"message": "Successfully processed 150 records from Excel",
"recordsProcessed": 150,
"recordsInserted": 25,
"recordsUpdated": 125
}
}Example using curl:
curl -X POST http://localhost:3000/api/pto/updateExample using PowerShell:
Invoke-RestMethod -Uri "http://localhost:3000/api/pto/update" -Method POSTEndpoint: GET /api/pto/health
Description: Check if the PTO service is running
Response:
{
"status": "healthy",
"service": "PTO Update Service",
"timestamp": "2025-01-15T10:30:00.000Z"
}Endpoint: GET /health
Description: Check if the server is running
Response:
{
"status": "ok",
"timestamp": "2025-01-15T10:30:00.000Z",
"uptime": 3600.5
}-
Excel Reading: The
ExcelServicereads the specified Excel file and extracts ID and PTO values from the configured sheet and columns. -
Data Validation: The service validates each row, skipping rows with missing or invalid data.
-
Database Connection: The
DatabaseServiceestablishes a connection to SQL Server using the provided credentials. -
Upsert Operation: For each record:
- If the ID exists in the database, the PTO value is updated
- If the ID doesn't exist, a new record is created
-
Response: The API returns a summary showing how many records were processed, inserted, and updated.
The application uses Inversify for dependency injection, which provides:
- Loose Coupling: Components depend on interfaces, not concrete implementations
- Testability: Easy to mock dependencies for unit testing
- Maintainability: Clear separation of concerns
- Flexibility: Easy to swap implementations without changing dependent code
The DI container is configured in src/container.ts and binds:
- Configuration classes
- Service implementations
- Controllers
- Routers
The application includes comprehensive error handling:
- Invalid Excel file paths
- Missing sheets or columns
- Database connection failures
- Invalid data in Excel rows
- SQL Server errors
All errors are logged and returned in a structured format.
The application uses Fastify's built-in logger with pretty printing for development. All operations are logged including:
- Server startup
- Database connections
- Excel file reading
- Record processing
- Errors and warnings
- Store sensitive credentials in
.envfile - Never commit
.envto version control - Use encrypted connections to SQL Server
- Validate and sanitize all data from Excel files
- Implement authentication/authorization for production use
- Add authentication middleware
- Implement rate limiting
- Add batch processing for large files
- Support multiple Excel file formats
- Add file upload endpoint
- Implement transaction rollback on errors
- Add comprehensive unit tests
- Add integration tests
- Implement audit logging
MIT