User and Group Management API Service

A RESTful API service built with Node.js, Express.js, and MySQL for managing users and groups.
This implementation uses raw SQL queries via the mysql2 library (without ORM) and includes features like input validation using Joi for request bodies, query parameters and pagination for listing users (GET /api/v1/users) and groups (GET /api/v1/groups), and integration tests of API endpoints using jest and axios.

Getting Started

Prerequisites

• Docker

Installation

1. Clone the Repository:

   git clone https://github.com/rvg07/user_management.git

2. Enter to the Project Directory:

   cd user_management

3. Configure Environment Variables:

   • Copy the example environment file .env.example in the root project:

     cp .env.example .env

   • In the .env file please pay close attention to:
     • APP_PORT: the port where our service will be accessible on our host machine.
     • DB_HOST=db: for Docker Compose networking. Important: you must keep this as db.
     • DB_HOST_PORT: the port used to connect directly to the MySQL db container.
     • DB_NAME: the name of the database schema that the MySQL db container.
     • DB_USER: the username that the MySQL db container will create and grant privileges to.
     • DB_PASSWORD: the password for the DB_USER.
     • DB_ROOT_PASSWORD: the root privilege password for our MySQL db container.

4. Build and Run with Docker Compose:

   docker-compose up --build -d

   • We use -d to run in detached mode.

5. Access the API:

   • The API should be running at http://localhost:<APP_PORT>/api/v1.
   • You can check the root endpoint: e.g. curl http://localhost:3000/api/v1.

Integration Tests

6. In a separate terminal run:

   npm test

   • jest finds and executes the *.test.js files.
   • Tests inside these files use axios to send HTTP requests.

API Documentation

Base URL

All API endpoints are prefixed with /api/v1. This is an example: http://localhost:3000/api/v1/users

Authentication

There is no authentication because not implemented. All endpoints are open.

---

Users (/users)

POST /users (Create New User)

Request Body

Requires a JSON request body with user details.

Field	Required	Data Type	Description	Example
name	Yes	string	User's first name	"Fatima"
surname	Yes	string	User's last name	"Hanna"
birth_date	Yes	string (YYYY-MM-DD)	User's date of birth	"2000-01-01"
sex	Yes	string ('male'|'female'|'other')	User's sex	"female"

Responses

HTTP Code	Content-Type	Response Body Example	Description
201	application/json	{status":"success", "message":"User created successfully!", "userId": 123}	User created.
400	application/json	{"status":"error", "code":"INVALID_PARAMS", "message":"..."}	Invalid input.
409	application/json	{"status":"error", "code":"ER_DUP_ENTRY", "message":"...already exists!"}	Duplicate user.
500	application/json	{"status":"error", "code":"INTERNAL_ERROR", "message":"..."}	Internal server error.

Example cURL

curl -X POST \
  -H "Content-Type: application/json" \
  -d '{"name":"Fatima","surname":"Hanna","birth_date":"2000-01-01","sex":"female"}' \
  http://localhost:3000/api/v1/users

GET /users (List Users - Paginated)

Query Parameters

Parameter	Required	Data Type	Default	Max	Description	Example
page	No	integer	1	N/A	Page number to retrieve.	2
limit	No	integer	10	100	Number of users per page.	20

Responses

HTTP Code	Content-Type	Response Body Example	Description
200	application/json	{"status":"success", "data":[user...], "pagination":{ "totalItems": ..., "totalPages":.., "currentPage":..., "pageSize": ...}}	List of users with pagination
500	application/json	{"status":"error", "code":"INTERNAL_ERROR", "message":"..."}	Internal server error.

Example cURL

Get page 2 with 5 users per page:

curl -X GET -i "http://localhost:3000/api/v1/users?page=2&limit=5"

Get first page (as default defined):

curl -X GET -i "http://localhost:3000/api/v1/users"

GET /users/{id} (Get User by ID)

Path Parameters

Parameter	Required	Data Type	Description
id	Yes	integer	ID of the user to retrieve

Responses

HTTP Code	Content-Type	Response Body Example	Description
200	application/json	{ id: 1, name: "Test", surname: "User", ... }	User found.
400	application/json	{"status":"error", "code":"INVALID_PARAMS", "message":"..."}	Invalid userId format
404	application/json	{"status":"error", "code":"NOT_FOUND", "message":"..."}	userId not found.
500	application/json	{"status":"error", "code":"INTERNAL_ERROR", "message":"..."}	Internal server error.

Example cURL

Get user with userId 1:

curl -X GET -i "http://localhost:3000/api/v1/users/1"

PUT /users/{id} (Update User)

Path Parameters

Parameter	Required	Data Type	Description
id	Yes	integer	ID of the user to update.

Request Body

Requires a JSON request body containing at least one field to update.

Field	Required	Data Type	Description	Example
name	No	string	User's first name	"Fatima"
surname	No	string	User's last name	"Hanna"
birth_date	No	string (YYYY-MM-DD)	User's date of birth	"2001-01-01"
sex	No	string ('male'|'female'|'other')	User's sex	"female"

Responses

HTTP Code	Content-Type	Response Body Example	Description
200	application/json	{"status":"success", "message":"User updated successfully!", "user": userObject}	User updated successfully.
400	application/json	{"status":"error", "code":"INVALID_PARAMS", "message":"..."	Invalid body data.
404	application/json	{"status":"error", "code":"NOT_FOUND", "message":"..."}	userId not found.
409	application/json	{"status":"error", "code":"ER_DUP_ENTRY", "message":"...already exists!"}	Update caused a conflict with the unique constraint.
500	application/json	{"status":"error", "code":"INTERNAL_ERROR", "message":"..."}	Internal server error.

Example cURL

Update only the name for userId 1:

curl -X PUT \
  -H "Content-Type: application/json" \
  -d '{"birth_date": "2000-01-01"}' \
  http://localhost:3000/api/v1/users/1

DELETE /users/{id} (Delete User)

Path Parameters

Parameter	Required	Data Type	Description
id	Yes	integer	ID of the user to delete

Responses

HTTP Code	Content-Type	Response Body Example	Description
204	(No Content)	(Empty)
400	application/json	{"status":"error", "code":"INVALID_PARAMS", "message":"..."}	Invalid userId format
404	application/json	{"status":"error", "code":"NOT_FOUND", "message":"..."}	userId not found.
500	application/json	{"status":"error", "code":"INTERNAL_ERROR", "message":"..."}	Internal server error.

Example cURL

Delete user with ID 1:

curl -X DELETE -i "http://localhost:3000/api/v1/users/1"

---

Associations (/associations)

These endpoints manage the association between users and groups.

POST /associations (Create Association between User and Group)

Request Body

Requires a JSON request body specifying the userId and groupId.

Field	Required	Data Type	Description	Example
userId	Yes	integer	ID of user.	1
groupId	Yes	integer	ID of group.	1

Responses

HTTP Code	Content-Type	Response Body Example	Description
201	application/json	{"status":"success", "message":"Association created between userId: 123 and groupId: 1"}	Association created.
400	application/json	{"status":"error", "code":"INVALID_PARAMS", "message":"...", "params": [...]}	Invalid params.
404	application/json	{"status":"error", "code":"NOT_FOUND", "message":"UserId: ... not found!"}	userId does not exist.
404	application/json	{"status":"error", "code":"NOT_FOUND", "message":"GroupId: ... not found!"}	groupId does not exist.
409	application/json	{"status":"error", "code":"ER_DUP_ENTRY", "message":"Association name already exists!!"}	Association already existed.
500	application/json	{"status":"error", "code":"INTERNAL_ERROR", "message":"..."}	Internal server error.

Example cURL

Association between userId 1 with groupId 1:

curl -X POST \
  -H "Content-Type: application/json" \
  -d '{"userId": 1, "groupId": 1}' \
  http://localhost:3000/api/v1/associations

GET /associations (List Users/Groups)

Query Parameters

Note: Provide EITHER userId OR groupId, but NOT BOTH.

Parameter	Required	Data Type	Description	Example
userId	Conditional	integer	Get the groups of a specific user is part of.	1
groupId	Conditional	integer	Get users in a specific group.	1

Responses

HTTP Code	Content-Type	Response Body Example	Description
200	application/json	{status: "success", groups}	Success if query is done by userId and returns list of Group objects.
200	application/json	{status: "success", users}	Success if query is done by groupId and returns list of User objects.
400	application/json	{"status":"error", "code":"INVALID_PARAMS", "message":"..."}	Missing query param, both provided or invalid params format.
404	application/json	{"status":"error", "code":"NOT_FOUND", "message":"..."}	UserId or GroupId not found.
500	application/json	{"status":"error", "code":"INTERNAL_ERROR", "message":"..."}	Internal server error

Example cURL

Get groups for userId 1:

curl -X GET -i "http://localhost:3000/api/v1/associations?userId=1"

Get users for groupId 1:

curl -X GET -i "http://localhost:3000/api/v1/associations?groupId=1"

DELETE /associations (Delete Association User from Group)

Query Parameters

Note: Requires userId.

Parameter	Required	Data Type	Description	Example
userId	Yes	integer	ID of the user in the association.	1

Responses

HTTP Code	Content-Type	Response Body Example	Description
200	application/json	{"status":"success", "message":"The userId: ... is removed from group!"}	Association deleted.
400	application/json	{"status":"error", "code":"INVALID_PARAMS", "message":"..."}	Missing or invalid query param.
404	application/json	{"status":"error", "code":"NOT_FOUND", "message":"Association... not found."}	The association does not exist.
500	application/json	{"status":"error", "code":"INTERNAL_ERROR", "message":"..."}	Internal server error.

Example cURL

Remove userId: 1:

curl -X DELETE -i "http://localhost:3000/api/v1/associations?userId=1"

---

Groups (/groups)

POST /groups (Create New Group)

Request Body

Requires a JSON request body with group details.

Field	Required	Data Type	Description	Example
name	Yes	string	Group's name	"Gli Invincibili"

Responses

HTTP Code	Content-Type	Response Body Example	Description
201	application/json	{status":"success", "message":"Group created successfully!", "groupId": 1}	User created.
400	application/json	{"status":"error", "code":"INVALID_PARAMS", "message":"..."}	Invalid input.
409	application/json	{"status":"error", "code":"ER_DUP_ENTRY", "message":"...already exists!"}	Duplicate user.
500	application/json	{"status":"error", "code":"INTERNAL_ERROR", "message":"..."}	Internal server error.

Example cURL

curl -X POST \
  -H "Content-Type: application/json" \
  -d '{"name":"Gli Invincibili"}' \
  http://localhost:3000/api/v1/groups

GET /groups (List Groups - Paginated)

Query Parameters

Parameter	Required	Data Type	Default	Max	Description	Example
page	No	integer	1	N/A	Page number to retrieve.	2
limit	No	integer	10	100	Number of users per page.	20

Responses

HTTP Code	Content-Type	Response Body Example	Description
200	application/json	{"status":"success", "data":[group...], "pagination":{ "totalItems": ..., "totalPages":.., "currentPage":..., "pageSize": ...}}	List of groups with pagination
500	application/json	{"status":"error", "code":"INTERNAL_ERROR", "message":"..."}	Internal server error.

Example cURL

Get page 2 with 5 groups per page:

curl -X GET -i "http://localhost:3000/api/v1/groups?page=2&limit=5"

Get first page (as default defined):

curl -X GET -i "http://localhost:3000/api/v1/groups"

GET /groups/{id} (Get Group by ID)

Path Parameters

Parameter	Required	Data Type	Description
id	Yes	integer	ID of the group to retrieve

Responses

HTTP Code	Content-Type	Response Body Example	Description
200	application/json	{ id: 1, name: "Gli Invincibili", ... }	Group found.
400	application/json	{"status":"error", "code":"INVALID_PARAMS", "message":"..."}	Invalid groupId format
404	application/json	{"status":"error", "code":"NOT_FOUND", "message":"..."}	groupId not found.
500	application/json	{"status":"error", "code":"INTERNAL_ERROR", "message":"..."}	Internal server error.

Example cURL

Get user with groupId 1:

curl -X GET -i "http://localhost:3000/api/v1/groups/1"

PUT /groups/{id} (Update Group)

Path Parameters

Parameter	Required	Data Type	Description
id	Yes	integer	ID of the group to update.

Request Body

Requires a JSON request body containing at least one field to update.

Field	Required	Data Type	Description	Example
name	No	string	Group's new name	"Gli Invincibili 2"

Responses

HTTP Code	Content-Type	Response Body Example	Description
200	application/json	{"status":"success", "message":"Group updated successfully!", "group": groupObject}	Group updated successfully.
400	application/json	{"status":"error", "code":"INVALID_PARAMS", "message":"..."	Invalid body data.
404	application/json	{"status":"error", "code":"NOT_FOUND", "message":"..."}	groupId not found.
409	application/json	{"status":"error", "code":"ER_DUP_ENTRY", "message":"...already exists!"}	Update caused a conflict with the unique constraint.
500	application/json	{"status":"error", "code":"INTERNAL_ERROR", "message":"..."}	Internal server error.

Example cURL

Update only the name for groupId 1:

curl -X PUT \
  -H "Content-Type: application/json" \
  -d '{"name": "Gli Invincibili 2"}' \
  http://localhost:3000/api/v1/groups/1

DELETE /groups/{id} (Delete Group)

Path Parameters

Parameter	Required	Data Type	Description
id	Yes	integer	ID of the group to delete

Responses

HTTP Code	Content-Type	Response Body Example	Description
204	(No Content)	(Empty)
400	application/json	{"status":"error", "code":"INVALID_PARAMS", "message":"..."}	Invalid groupId format
404	application/json	{"status":"error", "code":"NOT_FOUND", "message":"..."}	groupId not found.
500	application/json	{"status":"error", "code":"INTERNAL_ERROR", "message":"..."}	Internal server error.

Example cURL

Delete group with ID 1:

curl -X DELETE -i "http://localhost:3000/api/v1/groups/1"