SLC-S23W5 - Database & Orm - Candidate Evaluation Platform

kafio (70)in #techclub • 6 days ago

We got to week 5 in our learning. Before, we learned about:

Making project plans
Using Docker containers
Building websites with React & TypeScript

This week, we will learn about databases. We will use SQL and something called ORM. These will help us with our Candidate Evaluation Platform project.

What You Will Learn

Basic SQL database ideas
How to use GORM with Golang
How to update databases
How data connects to other data
Real examples from our Candidly project

SQL Databases: Where We Store Data

A database keeps data in tables with rows and columns. We use SQL to talk to the database. Our project uses PostgreSQL, which is a free database system that many people use.

Important Things in Our Project

1. Database Plan

The database plan shows how we organize our data:

What tables we need
What information goes in each table
How tables connect to each other

_{Entity Relationship Diagram (created with mermaid)}

2. SQL Tables and Relationships

_{Dtabase Shcema Diagram (using entreprsie Architect}

Our application uses several interconnected tables:

-- From databaseInit.sql
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) NOT NULL,
    password VARCHAR(255) NOT NULL,
    role VARCHAR(255) NOT NULL,
    created_at TIMESTAMP NOT NULL,
    updated_at TIMESTAMP NOT NULL
);

CREATE TABLE tests (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    description TEXT NOT NULL,
    minimum_score INT NOT NULL,
    created_at TIMESTAMP NOT NULL,
    updated_at TIMESTAMP NOT NULL,
    user_id INT REFERENCES users(id)
);

CREATE TABLE questions (
    id SERIAL PRIMARY KEY,
    body TEXT NOT NULL,
    type VARCHAR(255) NOT NULL,
    time INT NOT NULL,
    created_at TIMESTAMP NOT NULL,
    updated_at TIMESTAMP NOT NULL,
    user_id INT REFERENCES users(id)
);

GORM: Object-Relational Mapping in Go

_{GORM Model Relationships}

GORM is a powerful ORM library for Golang that provides a high-level abstraction for database operations, making it easier to work with databases without writing raw SQL queries.

Database Connection Setup

From database.go:

// Connect to the database
func Connect() {
    var err error
    p := config.Config("DB_PORT")
    port, err := strconv.ParseUint(p, 10, 32)
    if err != nil {
        log.Println("Error parsing DB_PORT")
    }

    // Connection URL
    dsn := fmt.Sprintf("host=%s port=%d user=%s password=%s dbname=%s sslmode=disable", 
        config.Config("DB_HOST"), 
        port, 
        config.Config("DB_USER"), 
        config.Config("DB_PASSWORD"), 
        config.Config("DB_NAME"))

    // Connect to PostgreSQL
    DB, err = gorm.Open(postgres.Open(dsn), &gorm.Config{})
    if err != nil {
        panic("Failed to connect to database")
    }

    fmt.Println("Database connection established")
}

Data Models with GORM

GORM uses structs to define database models. Here's how we define the Test model:

From test.go:

type Test struct {
    gorm.Model
    Name         string     `json:"name" gorm:"not null"`
    Description  string     `json:"description" gorm:"not null"`
    MinimumScore int        `json:"minimum_score" gorm:"not null"`
    UserID       uint       `json:"user_id"`
    User         User       `json:"user" gorm:"foreignkey:UserID"`
    Questions    []Question `json:"questions" gorm:"many2many:test_questions;"`
}

The struct tags (json:"name" and gorm:"not null") provide metadata for JSON serialization and database schema generation.

Relationships in GORM

GORM supports various relationship types:

One-to-Many: A test has many questions
Many-to-Many: Tests and questions have a many-to-many relationship through a join table

// From models/test.go
Questions []Question `json:"questions" gorm:"many2many:test_questions;"`

// From models/question.go
Tests []Test `json:"tests" gorm:"many2many:test_questions;"`

Database Migrations

_{Database Migration Process}

GORM can automatically migrate your schema based on model definitions:

// From database/database.go
func Migrate() {
    // Perform automatic migrations
    DB.AutoMigrate(&models.User{})
    DB.AutoMigrate(&models.Test{})
    DB.AutoMigrate(&models.Question{})
    DB.AutoMigrate(&models.Choice{})
    DB.AutoMigrate(&models.Candidate{})
    DB.AutoMigrate(&models.TestCandidate{})
    DB.AutoMigrate(&models.Answer{})

    fmt.Println("Database migration completed")
}

Data Access Patterns

_{ORM Data Flow}

Repository Pattern

We use the repository pattern to abstract database operations:

From repositories/test_repository.go:

// Get all tests
func GetAllTests() ([]models.Test, error) {
    var tests []models.Test
    result := database.DB.Preload("User").Find(&tests)
    return tests, result.Error
}

// Get test by ID
func GetTestByID(id uint) (models.Test, error) {
    var test models.Test
    result := database.DB.Preload("User").Preload("Questions").First(&test, id)
    return test, result.Error
}

// Create a new test
func CreateTest(test models.Test) (models.Test, error) {
    result := database.DB.Create(&test)
    return test, result.Error
}

Complex Queries with GORM

_{GORM Query Execution}

GORM allows for complex queries with relationships:

// From repositories/answer_repository.go
func GetCandidateAnswers(testCandidateID uint) ([]models.Answer, error) {
    var answers []models.Answer
    result := database.DB.
        Where("test_candidate_id = ?", testCandidateID).
        Preload("Question").
        Preload("Choice").
        Find(&answers)
    return answers, result.Error
}

Using the Database in Controllers

Controllers use repositories to interact with the database:

From test.go:

// Create a new test
func CreateTest(c *fiber.Ctx) error {
    var data map[string]string
    if err := c.BodyParser(&data); err != nil {
        return err
    }

    // Create the test model
    test := models.Test{
        Name:         data["name"],
        Description:  data["description"],
        MinimumScore: strToInt(data["minimum_score"]),
        UserID:       getUserID(c),
    }

    // Save to database
    test, err := repositories.CreateTest(test)
    if err != nil {
        return c.Status(500).JSON(fiber.Map{
            "status":  "error",
            "message": "Could not create test",
            "data":    nil,
        })
    }

    return c.JSON(fiber.Map{
        "status":  "success",
        "message": "Test created successfully",
        "data":    test,
    })
}

Key Database Features Implemented

1. Test Management System

_{Test Evaluation Process}

The database schema supports managing tests, questions, and choices:

// From models/test.go
type Test struct {
    gorm.Model
    Name         string     `json:"name" gorm:"not null"`
    Description  string     `json:"description" gorm:"not null"`
    MinimumScore int        `json:"minimum_score" gorm:"not null"`
    UserID       uint       `json:"user_id"`
    User         User       `json:"user" gorm:"foreignkey:UserID"`
    Questions    []Question `json:"questions" gorm:"many2many:test_questions;"`
}

// From models/question.go
type Question struct {
    gorm.Model
    Body    string   `json:"body" gorm:"not null"`
    Type    string   `json:"type" gorm:"not null"` // MCQ, coding, etc.
    Time    int      `json:"time" gorm:"not null"` // seconds
    UserID  uint     `json:"user_id"`
    User    User     `json:"user" gorm:"foreignkey:UserID"`
    Choices []Choice `json:"choices"`
    Tests   []Test   `json:"tests" gorm:"many2many:test_questions;"`
    Skills  []Skill  `json:"skills" gorm:"many2many:question_skills;"`
}

2. Candidate Management and Test Results

The database stores candidate information and test results:

// From models/candidate.go
type Candidate struct {
    gorm.Model
    Name     string         `json:"name" gorm:"not null"`
    Email    string         `json:"email" gorm:"not null;unique"`
    Phone    string         `json:"phone"`
    UserID   uint           `json:"user_id"`
    User     User           `json:"user" gorm:"foreignkey:UserID"`
    TestsMap []TestCandidate `json:"tests_map"`
}

// From models/test_candidate.go
type TestCandidate struct {
    gorm.Model
    TestID       uint      `json:"test_id" gorm:"not null"`
    Test         Test      `json:"test" gorm:"foreignkey:TestID"`
    CandidateID  uint      `json:"candidate_id" gorm:"not null"`
    Candidate    Candidate `json:"candidate" gorm:"foreignkey:CandidateID"`
    Status       string    `json:"status" gorm:"not null"` // pending, in_progress, completed
    Score        float64   `json:"score"`
    StartTime    time.Time `json:"start_time"`
    EndTime      time.Time `json:"end_time"`
    InvitationID string    `json:"invitation_id" gorm:"not null;unique"`
    Answers      []Answer  `json:"answers"`
}

3. Answer Storage and Evaluation

The database stores candidate answers and calculates scores:

// From models/answer.go
type Answer struct {
    gorm.Model
    TestCandidateID uint          `json:"test_candidate_id" gorm:"not null"`
    TestCandidate   TestCandidate `json:"test_candidate" gorm:"foreignkey:TestCandidateID"`
    QuestionID      uint          `json:"question_id" gorm:"not null"`
    Question        Question      `json:"question" gorm:"foreignkey:QuestionID"`
    ChoiceID        uint          `json:"choice_id"`
    Choice          Choice        `json:"choice" gorm:"foreignkey:ChoiceID"`
    IsCorrect       bool          `json:"is_correct"`
    Text            string        `json:"text"` // For non-MCQ questions
}

Database Migration and Initialization

The databaseInit.sql file creates the initial database schema:

-- Create database
CREATE DATABASE candidly;

-- Connect to the database
\c candidly;

-- Create tables
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) NOT NULL,
    password VARCHAR(255) NOT NULL,
    role VARCHAR(255) NOT NULL,
    created_at TIMESTAMP NOT NULL,
    updated_at TIMESTAMP NOT NULL
);

-- Additional tables omitted for brevity...

-- Create initial admin user
INSERT INTO users (email, password, role, created_at, updated_at)
VALUES ('[email protected]', '$2a$10$NqWzcMj7x5K.VsdzQFkCou.zLxVELJ1GCt6RWou3RZmfjbVOIZ34C', 'admin', NOW(), NOW());

What I Learned from This Project

Developing the database layer for Candidly taught me several valuable lessons:

Schema Design: Proper schema design is crucial for application performance and maintainability.
ORM Benefits: ORMs like GORM simplify database operations but require understanding their conventions and limitations.
Data Relationships: Modeling complex relationships (one-to-many, many-to-many) requires careful planning.
Repository Pattern: Separating database access logic from business logic makes the code more testable and maintainable.
Migrations: Automating schema migrations simplifies development and deployment.

Next Steps

In Week 6, we'll explore backend development with Golang, showing how the database layer connects with the API endpoints to create a complete application. We'll see how the models and repositories we've examined this week interact with controllers to handle client requests.

Stay tuned for next week's topic: Backend Development and Integration with Golang.

GitHub Repositories

Frontend: TalentLens
Backend: Candidly

Project Resources

Complete System Overview: Google Drive Link
Project Presentation Video:

_{Authentication interface}

_{Question type selection interface}

_{Interface for adding questions to a test}

_{Interface for adding an MCQ-type question}

_{Interface for inviting a candidate to a test}

_{Candidate welcome interface}

What can you share in the club?

Our club is all about technology and development including:

Web & Mobile Development
AI & Machine Learning
DevOps & Cloud Technologies
Blockchain & Decentralized Applications
Open-source Contributions
Graphic Design & UI/UX

Any posts related to technology, reviews, information, tips, and practical experience must include original pictures, real-life reviews of the product, the changes it has brought to you, and a demonstration of practical experience

The club is open to everyone. Even if you're not interested in development, you can still share ideas for projects, and maybe someone will take the initiative to work on them and collaborate with you. Don't worry if you don't have much IT knowledge, just share your great ideas with us and provide feedback on the development projects. For example, if I create an extension, you can give your feedback as a user, test the specific project, and that will make you an important part of our club. We encourage people to talk and share posts and ideas related to Steemit.

For more information about the #techclub you can go through A New Era of Learning on Steemit: Join the Technology and Development Club. It has all the details about posting in the "#techclub" and if you have any doubts or needs clarification you can ask.

Our Club Rules

To ensure a fair and valuable experience, we have a few rules:

No AI generated content. We want real human creativity and effort.
Respect each other. This is a learning and collaborative space.
No simple open source projects. If you submit an open source project, ensure you add significant modifications or improvements and share the source of the open source.
Project code must be hosted in a Git repository (GitHub/GitLab preferred). If privacy is a concern, provide limited access to our mentors.
Any post in our club that is published with the main tag "#techclub" please mention the mentors @kafio @mohammadfaisal @alejos7ven
Use the tag #techclub, #techclub-s23w5, "#country", other specific tags relevant to your post.
In this week's "#techclub" you can participate from Monday, March 17, 2025, 00:00 UTC to Sunday, March 23, 2025, 23:59 UTC.
Post the link to your entry in the comments section of this contest post. (Must)
Invite at least 3 friends to participate.
Try to leave valuable feedback on other people's entries.
Share your post on Twitter and drop the link as a comment on your post.

Each post will be reviewed according to the working schedule as a major review which will have all the information about the post such as AI, Plagiarism, Creativity, Originality and suggestions for the improvements.

Other team members will also try to provide their suggestions just like a simple user but the major review will be one which will have all the details.

Rewards System

Sc01 and Sc02 will be visiting the posts of the users and participating teaching teams and learning clubs and upvoting outstanding content. Upvote is not guaranteed for all articles. Kindly take note.

Each week we will select Top 4 posts which has outperformed from others while maintaining the quality of their entry, interaction with other users, unique ideas, and creativity. These top 4 posts will receive additional winning rewards from SC01/SC02.

Note: If we find any valuable and outstanding comment than the post then we can select that comment as well instead of the post.

Best Regards
Technology and Development Club Team