W05D03 SQL from our Apps

To Do

Create a database and query it using psql terminal
Perform BREAD actions on database from command line
Perform BREAD actions on database from the browser

node-postgres

We are going to use node-postgres (pg) node package to interact with our database.

In order to connect with our database, we pass configuration options to the pg client:

const pg = require('pg');

const config = {
    user: '<user name>',
    password: '<password>',
    database: '<db>',
    host: '<host>'
};

const client = new pg.Client(config);

Then we tell our client to connect to the database and we execute queries using the client:

client.connect();
client.query('SELECT * FROM <table>', (err, result) => console.log(err, result));

NOTE: pg uses "error first" callbacks meaning that the first argument will always be the error (if any) or null and the second argument will be the return value from our query.

SQL Syntax Review

Browse

SELECT * FROM <table>;

Read

SELECT * FROM <table> WHERE id = <id>;

Edit

UPDATE <table> SET <column> = <value> WHERE id = <id>;

Add

INSERT INTO <table> (<column1>, <column2>) VALUES (<value1>, <value2>);

Delete

DELETE FROM <table> WHERE id = <id>;

Sanitization

We always want to sanitize any user-defined parameters in our SQL before running the query to prevent possible SQL injections.

In pg, we use prepared statements and pass an array of values as the second argument to client.query():

client.query('SELECT * FROM <table> WHERE id = $1', [<id>], (err, result) => console.log(err, result));

In the above example, the id from the array will be interpolated into the SQL query wherever $1 appears.

andydlindsay/nov25-w05d03