Express-Database

---

Architektur

• Kein JDBC ⟹ jede Db eigenes API

---

PgPool

export class PgPool {

  private static instance:Pool|undefined;

  static getInstance():Pool {
    if(!this.instance) {
      console.log("created new db-pool")
      PgPool.instance = new Pool(); // configuration with .env
    }
    return <Pool>this.instance;
  }
}

# .env
# Database configuration
# https://node-postgres.com/features/connecting
PGHOST=localhost
PGPORT=5432
PGDATABASE=postgres
PGUSER=nodepg
PGPASSWORD=nodepgpw

---

select

async find(year: number, id: number): Promise<Student | null> {
  try {
    const query = {
      text: 'select * from students where year = $1 and id = $2',
      values: [year, id]  // $i ==> values[i - 1] SQL 😡
    };
    
    // db operations take time ==> await
    let results = await PgPool.getInstance().query<Student>(query);
    if (!results.rows)  // maybe none found
      return null;

    return results.rows[0]; // only single student expected
  } catch (error) {
    throw new Error(`Failed to find student ${id}: ${error}`);
  }
}

---

⚠️ SQL Injection ⚠️

async find(name: string): Promise<Student[]> {
  try {
    const query = `
      select first_name 
      from students where 
      first_name like ${name}`;

    let results = await PgPool.getInstance().query<Student>(query);

    return results.rows;
  } catch (error) {
    throw new Error(`Failed to find students named ${name}: ${error}`);
  }
}

find('fred') => [Alfred, Manfred]

find("Alfred union select * from information_schema.tables") 
    => [Alfred, pg_user, pg_tables, ...]

---

insert

async add(student: Student): Promise<Student> {
  try {
    const query = {
      text: `insert into students(name, year) 
                         values ($1, $2) 
                         returning id`,
      values: [student.name, student.year]
    };
    
    let result = await PgPool.getInstance().query<{id: number}>(query);
    student.id = result.rows[0].id

    return student;
  } catch (error) {
    throw new Error(`Adding ${student} failed: ${error}`);
  }
}

---

delete

async remove(id: number): Promise<boolean> {
  try {
    const query = {
      text: `delete from students where id = $1`,
      values: [id]
    };
    
    let result = await PgPool.getInstance().query(query);
    let affectedRows = result.rowCount; // ⚠️ != result.rows.length ⚠️
    
    return affectedRows == 1;
  } catch (error) {
    throw new Error(`Deleting id ${id} failed: ${error}`);
  }
}

---

update

async remove(id: number): Promise<boolean> {
  try {
    const query = {
      text: `update students set deleted = true where id = $1`,
      values: [id]
    };
    
    let result = await PgPool.getInstance().query(query);
    
    return result.rowCount == 1;
  } catch (error) {
    throw new Error(`Deleting id ${id} failed: ${error}`);
  }
}