Interacting With the Database

For the WiEIT platform we utilise these core technologies for database management.

• PostgreSQL An advanced, open source and highly used relational database management system (DBMS).
• pg-promise A PostgreSQL interface for Node.js.

PostgreSQL

PostgreSQL is a versatile database management system known for its reliability, performance, and feature-richness. It's widely used for web applications, enterprise systems, and data analysis.

So why use it? Some key benefits include:

• Reliability: PostgreSQL has a proven track record of stability and can handle large datasets.
• Performance: It's optimised for speed and can scale to meet growing demands.
• Features: Offers a wide range of features like full-text search, JSON support, and spatial data handling.
• Open-source: Free and customisable, making it a popular choice for developers.
• Community: Has a large and active community, providing support and resources.
• Compatibility: Works well with various programming languages and tools.
• These factors make PostgreSQL a popular and powerful choice for many database needs.

Ok, it's relational... what does that mean?

Relational databases store data in tables with columns and rows. Each column represents a specific data attribute and each row represents an instance of that data.

You give each table a primary key—an identifier column that uniquely identifies the table. You use the primary key to establish relationships between tables. You use it to relate rows between tables as the foreign key in another table.

Once two tables are connected, you get data from them both with a single query. You write SQL queries to interact with the relational database.

Example of stored data For example, imagine that a retailer creates a table of all their products. In this table, you could have columns for the product names, descriptions, and price. Another table contains data about customers, their names, and what they have purchased.

The following tables demonstrate this approach.

Products

product_id (Primary key)	product_name	product_cost
P1	Product_A	$100
P2	Product_B	$50
P3	Product_C	$80

Customers

customer_id	customer_name	item_purchased (Foreign key)
C1	Customer_A	P2
C2	Customer_B	P1
C3	Customer_C	P3

(Source: AWS)

Query Examples

All Products

SELECT 
    product_id, product_name, product_cost 
FROM 
    products

product_id	product_name	product_cost
P1	Product_A	$100
P2	Product_B	$50
P3	Product_C	$80

All Customers

SELECT 
    customer_id, customer_name, item_purchased 
FROM 
    customers

customer_id	customer_name	item_purchased
C1	Customer_A	P2
C2	Customer_B	P1
C3	Customer_C	P3

All Customers + Their Purchases

The previous two queries were simple. But what if you're looking at the customers and want to see what the name of the product is from the item_purchased. You could make a join.

SELECT 
    c.customer_id, c.customer_name, c.item_purchased, p.product_name
FROM 
    customers as c
LEFT JOIN 
    products as p on p.product_id = c.item_purchased

customer_id	customer_name	item_purchased	product_name
C1	Customer_A	P2	Product_B
C2	Customer_B	P1	Product_A
C3	Customer_C	P3	Product_C

One Customer + Their Purchases

SELECT 
    c.customer_id, c.customer_name, c.item_purchased, p.product_name
FROM 
    customers as c
LEFT JOIN 
    products as p on p.product_id = c.item_purchased
WHERE
    c.customer_id = 'C1'

customer_id	customer_name	item_purchased	product_name
C1	Customer_A	P2	Product_B

Learn more SQL here

pg-promise

pg-promise is a Node.js library that provides a simple and efficient interface for interacting with PostgreSQL databases. It offers a variety of features that can simplify database operations and improve the overall development experience.

Why use pg-promise?

• Simplicity and Ease of Use: pg-promise has a clean and intuitive API, making it easy to learn and use, even for developers who are new to PostgreSQL.
• Performance: It is designed to be efficient and performant, offering fast query execution and minimal overhead.
• Features: pg-promise provides a wide range of features, including:
  • Query builders: Simplify the construction of complex SQL queries.
  • Promise-based API: Integrates seamlessly with asynchronous programming in Node.js.
  • Transaction management: Ensure data integrity and consistency.
  • Prepared statements: Prevent SQL injection attacks and improve performance.
  • Bulk inserts: Efficiently insert large amounts of data.
  • Custom type support: Handle custom data types defined in PostgreSQL.
• Community and Support: pg-promise has a strong and active community, providing extensive documentation, tutorials, and support.
• Flexibility: It offers a high degree of flexibility, allowing you to customise its behaviour to suit your specific needs.

pg-promise resources

Please ensure you have the pg-promise documentation open during development, you’ll be able to find answers to most of your concerns there as well as the Wiki and Stack Overflow

https://vitaly-t.github.io/pg-promise/ (Docs)

https://github.com/vitaly-t/pg-promise/wiki (Wiki)

The creator of the package vitaly-t is very active on stack overflow and your question has likely been asked before and answered by them.

Query Examples

All Products

If we expect more than 0 results, we can use many.

return db.many(`
SELECT 
    product_id, product_name, product_cost 
FROM 
    products
`);

Results

[
    {
        "product_id": "P1",
        "product_name": "Product_A",
        "product_cost": "$100"
    },
    {
        "product_id": "P2",
        "product_name": "Product_B",
        "product_cost": "$50"
    },
    {
        "product_id": "P3",
        "product_name": "Product_C",
        "product_cost": "$80"
    }
]

All Customers

return db.many(`
SELECT 
  customer_id, customer_name, item_purchased 
FROM 
  customers
`);

Results

[
    {
        "customer_id": "C1",
        "customer_name": "Customer_A",
        "item_purchased": "P2"
    },
    {
        "customer_id": "C2",
        "customer_name": "Customer_B",
        "item_purchased": "P1"
    },
    {
        "customer_id": "C3",
        "customer_name": "Customer_C",
        "item_purchased": "P3"
    }
]

All Customers + Their Purchases

The previous two queries were simple. But what if you're looking at the customers and want to see what the name of the product is from the item_purchased. You could make a join.

return db.many(`
    SELECT 
        c.customer_id, c.customer_name, c.item_purchased, p.product_name
    FROM 
        customers as c
    LEFT JOIN 
        products as p on p.product_id = c.item_purchased
);

Results

[
    {
        "customer_id": "C1",
        "customer_name": "Customer_A",
        "item_purchased": "P2",
        "product_name": "Product_B"
    },
    {
        "customer_id": "C2",
        "customer_name": "Customer_B",
        "item_purchased": "P1",
        "product_name": "Product_A"
    },
    {
        "customer_id": "C3",
        "customer_name": "Customer_C",
        "item_purchased": "P3",
        "product_name": "Product_C"
    }
]

One Customer + Their Purchases

As we expect a single result, we can use one from pg-promise to return an object instead of an array. In the case of 0 or multiple results from the query, an error will be thrown (please see oneOrNone).

return db.one(`
    SELECT 
        c.customer_id, c.customer_name, c.item_purchased, p.product_name
    FROM 
        customers as c
    LEFT JOIN 
        products as p on p.product_id = c.item_purchased
    WHERE
        c.customer_id = $1
`, ["C1"]);

Results

{
    "customer_id": "C1",
    "customer_name": "Customer_A",
    "item_purchased": "P2",
    "product_name": "Product_B"
}