Databases and HTML Templates

Introduction to databases

Databases

Databases

  • Software that stores data on disk
    • Download, install, and run as a separate Programming
    • .. until we talk about Docker
  • Runs as a server and is communicated with via TCP sockets
  • Provides an API to store/retrieve data
    • The database handles the low-level file IO
    • Allows us to think about our data, not how to store it
  • Provides many optimizations

Databases

  • We'll look at 2 different databases
  • Both are pieces of software that must be downloaded, installed, ran, then connected to via TCP
  • MongoDB (Briefly)
    • An unstructured server based on document stores
  • PostgreSQL
    • A server implementing SQL (Structured Query Language)

MongoDB

MongoDB

  • Runs on port 27017 (By default)
  • A document-based database
  • Stores data in a structure very similar to JSON
  • In python/JS
    • Insert dictionaries/objects directly
  • Each object is stored in a collection

MongoDB - Connection

  • Download a connection library and use it to establish a connection with MongoDB
  • MongoDB is separated into several layers
    • Databases - Named by Strings; Contains collections
    • Collections - Where the data is stored; similar to a SQL table
  • Access your collections to insert/retrieve/update/delete data

MongoDB - Security

  • No Mongo injection attacks
  • Mongo does not rely on parsing statements as strings
  • Any injected code would be treated as values

PostgreSQL

PostgreSQL - Setup

  • Download, install, run the PostgreSQL server
    • Listens for TCP connections on port 5432 (By default)
  • Set your password so you can connect from your server
  • Install a library for your language that will connect to the PostgreSQL server
    • github.com/joho/godotenv
  • Create a connection in your code
  • When in doubt, follow the documentation
db, err := sql.Open(
  "pgx",
  "postgres://postgres:" + os.Getenv("POSTGRES_PASSWORD") + "@localhost:5432/cse312",
)

PostgreSQL - Tables

  • SQL is based on tables with rows and column
    • Similar in structure to CSV except the fields are typed
  • A table must be fully defined before data is stored
  • If creating tables on server startup, use IF NOT EXISTS
_, err := db.Exec(`
CREATE TABLE IF NOT EXISTS chat (
  id SERIAL PRIMARY KEY,
  author TEXT,
  content TEXT,
  updated BOOLEAN
)
`)

PostgreSQL - Insert Data

  • Insert a record into a table using INSERT INTO
  • If using inputs from the user, always use prepared statements!
    • Use placeholders in your statement and replace the values later
    • Prevents values from being interpreted as SQL code
_, err := db.Exec(`
INSERT INTO chat (author, content, updated) VALUES ($1, $2, $3)
`, "Jesse", "hello chat!", false)

SQL - Security

  • Not using prepared statements?
    • Vulnerable to SQL injection attacks
  • If you concatenate user inputs directly into your SQL statements
    • Attacker chooses a username of ';DROP TABLE chat;
    • You lose all your data
    • Even worse, they find a way to access the entire database and steal other users' data
    • SQL Injection is the most common successful attack on servers

PostgreSQL - Retrieve Data

  • Retrieve records from a table using SELECT ... FROM
  • SELECT * FROM table_name will return all fields from every record in table_name
records, err := db.Query("SELECT * FROM chat")

for records.Next() {
  var id int
  var author string
  var content string
  var updated bool
  records.Scan(&id, &author, &content, &updated)
}

MongoDB vs. SQL

  • MongoDB is unstructured
    • Can add objects in any format to a collection
    • Can mix formats in a single collection
      • Ie. In a single collection the documents can have different attributes
  • SQL is structured (That's what the S stands for)
    • Table columns must be pre-defined
      • All rows have the same attributes
      • Adding a column can be difficult
    • Fast!

MongoDB vs. SQL

  • Hot Take
    • MongoDB is best for prototyping when the structure of your data is constantly changing
      • Take advantage of the flexibility
    • SQL is best once your data has a defined structure
      • Take advantage of the efficiency

HTML Templates

HTML Templates

  • Instead of writing complete HTML files
    • Write HTML templates
  • An HTML template is an "incomplete" HTML file that is used to generate complete pages
  • Use additional markup to add placeholders in the HTML
  • Replace the placeholders with data at runtime

HTML Templates

  • Example template with 3 placeholders
  • The title, description, and image_filename will be replaced later
    • Provide values for these 3 placeholders to serve a response
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>This page was generated from a template</title>
</head>
<body>

<h1>{{title}}</h1>

<p>{{description}}</p>

<img src="{{image_filename}}"/>

</body>
</html>

HTML Templates

  • To substitute the placeholders
    • Use any string manipulation that gets the job done
    • Find/replace is the simplest solution
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>This page was generated from a template</title>
</head>
<body>

<h1>{{title}}</h1>

<p>{{description}}</p>

<img src="{{image_filename}}"/>

</body>
</html>

HTML Templates

  • On the HW:
    • layout.html contains all the HTML that is shown on every page (navigation, structure, etc)
    • layout.html has one placeholder {{content}} which is where you'll insert all the HTML for the specific page that was requested
    • To render a page, eg. index.html, replace {{content}} with everything read from index.html
    • Send the resulting rendered page to the client

Common Template Features

  • Loops
  • To add loops to your templates
    • Choose syntax for the start and end of the loop
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>This page was generated from a template</title>
</head>
<body>

{{loop}}
<h6>{{content_from_data_structure}}</h6>
{{end_loop}}

</body>
</html>

Common Template Features

  • Conditionals
  • Can use similar approach as loops
  • Choose syntax for the start and end of each block in the conditional
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>This page was generated from a template</title>
</head>
<body>

{{if cookie_set}}
<h6>Welcome back!</h6>
{{else}}
<h6>Welcome!</h6>
{{end_if}}

</body>
</html>

Further Reading