part_1_settingDB_backend.md

Setting Up an Express Backend with PostgreSQL: A Step-by-Step Guide

This guide walks you through setting up an Express backend connected to a PostgreSQL database, creating a table, seeding it, and testing endpoints with Tableplus.

1. Initial Project Setup

Why? Every project starts with an initial setup. This step creates the project structure and installs necessary packages.

Initialize a new Node.js project:

mkdir school-backend
cd school-backend
npm init -y

Install required packages:
```
npm install express knex pg
```

2. Setting up Database Configuration with Knex

Why? Database configuration helps Knex understand how to connect and interact with your PostgreSQL database.

Initialize a new Knex configuration:
```
npx knex init
```

Open knexfile.js and configure the development environment to connect to PostgreSQL:

module.exports = {
  development: {
    client: 'pg',
    connection: {
      host: '127.0.0.1',
      user: 'your_username',
      password: 'your_password',
      database: 'schoolDB'
    },
    migrations: {
      tableName: 'knex_migrations',
      directory: './migrations'
    },
    seeds: {
      directory: './seeds'
    }
  }
};

3. Database Creation using TablePlus

Why? Before you can interact with a database, you need to create one.

Open TablePlus.
Connect to your PostgreSQL server.
Create a new database named schoolDB.

4. Migrations with Knex

Why? Migrations allow you to define and manage the structure of your database in a version-controlled manner.

Create a new migration file for the students table:
```
npx knex migrate:make create_students_table
```

In the newly created migration file in the migrations directory, define the structure of the students table:

exports.up = function(knex) {
  return knex.schema.createTable('students', (table) => {
    table.increments('id').primary();
    table.string('name').notNullable();
    table.integer('age').notNullable();
  });
};

exports.down = function(knex) {
  return knex.schema.dropTable('students');
};

Run the migration to apply it to the database:
```
npx knex migrate:latest
```

5. Seeding Data with Knex

Why? Seeding provides a way to populate your database with sample data, which can be useful for testing.

Create a seed file for the students table:
```
npx knex seed:make students_seed
```

In the created seed file within the seeds directory, add sample student data:

exports.up = function(knex) {
  return knex.schema.createTable('students', (table) => {
      table.increments('id').primary();
      table.string('name').notNullable();
      table.integer('age').notNullable();
    });
};

Run the seed to populate the database:
```
npx knex seed:run
```

6. Setting up Express Server & Endpoints

Why? Express is a minimalist web framework for Node.js, making it easier to create and manage API endpoints.

Create a file named server.js:
```
touch server.js
```

In server.js, set up an Express server:

const express = require('express');


const app = express();
const PORT = 3000;

// ... [Endpoints will be defined here]

app.listen(PORT, () => {
  console.log(`Server is running on port ${PORT}`);
});

Create an endpoint to retrieve all students:

app.get('/students', async (req, res) => {
  try {
    const students = await pool('students').select();
    res.json(students);
  } catch (error) {
    res.status(500).json({ message: 'Internal server error' });
  }
});

7. Testing Endpoints with Postman

Why? Postman is a popular tool for testing APIs. It allows you to simulate requests to your server without a frontend.

Start your server:
```
node server.js
```
Open Postman.
Create a new GET request to http://localhost:3000/students.
Send the request

and you should see the seeded student data as a response.

CastonPursuit/part_1_settingDB_backend.md

Setting Up an Express Backend with PostgreSQL: A Step-by-Step Guide