[FEAT]Specification for User Table Database Plan with Seeding and Migration Support using TypeORM

[posh1001]

Description:

Design and implement a structured database schema for managing user data in a Nest.js application using TypeORM. Include functionality to seed initial user data and ensure the database setup supports easy configuration switching and migration management.

Acceptance Criteria

• The User table is successfully created in PostgreSQL using Nest.js and TypeORM.
• Initial user data is seeded into the database.
• Migrations are written and successfully applied to update the database schema.
• Comprehensive documentation is provided for setup, configuration, and maintenance of the database schema, migrations, and seeding process within a Nest.js application.

Requirements:

1. Database Schema Design:

• Create a User table schema with the following fields
  • id: Primary key, auto-incremented integer.
  • username: Unique username of the user.
  • email: Unique email address of the user.
  • password: Hashed password of the user.
  • createdAt: Timestamp indicating when the user record was created.
  • updatedAt: Timestamp indicating when the user record was last updated.

Column Name	Data Type	Constraints
id UUID	Primary Key	Default: UUID Generate
username	VAR(50)	Unique, Not Null
email	VAR(20)	Unique, Not Null
password	VAR(50)	Not Null
firstName	VAR(50)	Not Null
lastName	VAR(50)	Not Null
createdAt	TIMESTAMP	Default: Current Timestamp
updatedAt	TIMESTAMP	Default: Current Timestamp, On Update

• Create a Organisations table schema with the following fields
  • id: Primary key, auto-incremented integer.
  • name: Unique name of the organisation.
  • email: Unique email address of the organisation.
  • description: Description of the organisation.
  • industry: Industry of organisation.
  • type: Type of organisation.
  • address: Address of the organisation.
  • country: Country of residence of the organisation.
  • state: State of residence of the organisation.
  • createdAt: Timestamp indicating when the organisation record was created.
  • updatedAt: Timestamp indicating when the organisation record was last updated.

Column Name	Data Type	Constraints
id UUID	Primary Key	Default: UUID Generate
name	VAR(255)	Unique, Not Null
email	VAR(20)	Unique, Not Null
description	VAR(50)	Null
industry	VAR(50)	Not Null
type	VAR(50)	Not Null
country	VAR(50)	Null
state	VAR(50)	Null
address	VAR(50)	Null
createdAt	TIMESTAMP	Default: Current Timestamp
updatedAt	TIMESTAMP	Default: Current Timestamp, On Update

• Create a Products table schema with the following fields
  • id: Primary key, auto-incremented integer.
  • product_name: Unique name of the product.
  • description: Description of the product.
  • price: Price of the product.
  • categoryId: Foreign key pointing to the category of the product.
  • createdAt: Timestamp indicating when the product record was created.
  • updatedAt: Timestamp indicating when the product record was last updated.

Column Name	Data Type	Constraints
id	UUID	Primary Key, Default: UUID Generate
name	VAR(255)	Unique, Not Null
description	VAR(20)	Unique, Not Null
price	VAR(50)	Null
categoryId	VAR(50)	Foreign Key, Default: UUID Generate
createdAt	TIMESTAMP	Default: Current Timestamp
updatedAt	TIMESTAMP	Default: Current Timestamp, On Update

2. Nest.js and TypeORM Setup:

• Integrate Nest.js with TypeORM for seamless database connectivity.
• Configure Nest.js application to connect to PostgreSQL.
• Utilize environment variables for flexible database configuration (e.g., host, port, username, password)

Database Configuration Data

import { DataSource } from 'typeorm';
import { User } from './entities/User';
const AppDataSource = new DataSource ({
   ***type***:  'postgres', // Change to your desired database type
   ** *host***:  process.env.DB_HOST || 'localhost',
   ***port***:  Number(process.env.DB_PORT) || 5432,
   ***username***:  process.env.DB_USER || 'user',
   ***password***:  process.env.DB_PASSWORD || 'password',
   ***database***:  process.env.DB_NAME || 'mydatabase',
   ***entities***:  [User],
   ***synchronize***:  true, // Set to false in production
   ***logging***:  true,
});

3. Seeding Initial Data:

• Implement a seeding mechanism to populate initial user data into the User table.
• Seed at least one or two users for testing and development purposes.
• Ensure the seeding process is automated and reproducible, leveraging Nest.js seeders or migrations.

Seeding Script

import AppDataSource from './data-source'; // Adjust import as necessary
import { User } from './entities/User';

const seedUsers = async () => {
  const userRepository = AppDataSource.getRepository(User);

  const users = [
    {
      username: 'poche',
      email: 'poche@gmail.com',
      password: 'securepassword', // Use hashed password in production
      firstName: 'paul',
      lastName: 'Orife',
    },
    {
      username: 'Fraciss',
      email: 'francis@yahoo.com',
      password: 'securepassword', // Use hashed password in production
      firstName: 'Francis',
      lastName: 'Smith',
    },
  ];

  await AppDataSource.initialize();

  for (const user of users) {
    await userRepository.save(user);
  }

  console.log('Seeded users successfully');
  await AppDataSource.destroy();
};

4. Migration Strategy:

• Utilize TypeORM migration capabilities for managing database schema changes.
• Develop migrations to create the initial User table schema and handle future schema.
• Ensure migrations are unchanged and can be executed sequentially to update the database schema from any previous state.

5. Environment Flexibility:

• Design database setup to facilitate easy switching of default PostgreSQL configurations.
• Document environment variables required for configuring different database environments (e.g., development, testing, production).
• Provide scripts or instructions for initializing the database and running migrations in different environments.

6. Testing and Documentation:

• [ ] Implement comprehensive unit tests for database operations (CRUD on User table, seeding process).
• [ ] Include troubleshooting steps and best practices for maintaining the database schema and managing migrations within a Nest.js application context.

6. Additional Considerations:

• Performance Optimization: Optimize database queries and indexing for efficient User table operations.
• Security: Implement secure password hashing and validation procedures.
• Scalability: Design the schema to accommodate future enhancements or additional user-related features.

7. Dependencies:

• Nest.js (latest version)
• TypeORM (latest version)
• PostgreSQL (or compatible database)
• Node.js environment

References:

• Nest.js Documentation: [ https://docs.nestjs.com/ ]
• TypeORM Documentation: [ https://orkhan.gitbook.io/typeorm/docs/entities ]
• PostgreSQL Documentation: [ https://www.postgresql.org/docs/current/ ]

[markessien]

All db tasks are not testable