Database-Driven Web Development Tutorial

1. Setting Up the Database

CREATE DATABASE mywebsite;
\c mywebsite;
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100)
);

2. Backend (Flask Middleware)

from flask import Flask, jsonify, request, Response
from flask_socketio import SocketIO
from flask_sse import sse
from flask_graphql import GraphQLView
import psycopg2
import xml.etree.ElementTree as ET
from graphene import ObjectType, String, Int, List, Schema

app = Flask(__name__)
socketio = SocketIO(app)
app.config["REDIS_URL"] = "redis://localhost:6379"
app.register_blueprint(sse, url_prefix='/stream')

def connect_db():
    return psycopg2.connect(
        dbname='mywebsite',
        user='postgres',
        password='yourpassword',
        host='localhost'
    )

@app.route('/users', methods=['GET'])
def get_users():
    conn = connect_db()
    cur = conn.cursor()
    cur.execute('SELECT * FROM users')
    users = cur.fetchall()
    cur.close()
    conn.close()
    
    if request.headers.get('Accept') == 'application/xml':
        root = ET.Element("users")
        for u in users:
            user_elem = ET.SubElement(root, "user")
            ET.SubElement(user_elem, "id").text = str(u[0])
            ET.SubElement(user_elem, "name").text = u[1]
            ET.SubElement(user_elem, "email").text = u[2]
        xml_data = ET.tostring(root, encoding='utf-8')
        return Response(xml_data, mimetype='application/xml')
    
    return jsonify([{'id': u[0], 'name': u[1], 'email': u[2]} for u in users])

@app.route('/notify')
def notify_clients():
    sse.publish({"message": "New user added!"}, type='user_update')
    return "Message sent!"

class UserType(ObjectType):
    id = Int()
    name = String()
    email = String()

class Query(ObjectType):
    users = List(UserType)

    def resolve_users(self, info):
        conn = connect_db()
        cur = conn.cursor()
        cur.execute('SELECT * FROM users')
        users = [UserType(id=u[0], name=u[1], email=u[2]) for u in cur.fetchall()]
        cur.close()
        conn.close()
        return users

schema = Schema(query=Query)
app.add_url_rule("/graphql", view_func=GraphQLView.as_view("graphql", schema=schema, graphiql=True))

if __name__ == '__main__':
    socketio.run(app, debug=True)

3. AJAX Approach

<script>
function fetchData(format) {
    const xhr = new XMLHttpRequest();
    xhr.open("GET", "/users", true);
    xhr.setRequestHeader("Accept", format);
    xhr.onreadystatechange = function() {
        if (xhr.readyState === 4 && xhr.status === 200) {
            let output = '
';
            if (format === "application/json") {
                const data = JSON.parse(xhr.responseText);
                data.forEach(user => {
                    output += `
${user.name} - ${user.email}
`;
                });
            } else if (format === "application/xml") {
                const xml = xhr.responseXML;
                const users = xml.getElementsByTagName("user");
                for (let i = 0; i < users.length; i++) {
                    const name = users[i].getElementsByTagName("name")[0].textContent;
                    const email = users[i].getElementsByTagName("email")[0].textContent;
                    output += `
${name} - ${email}
`;
                }
            }
            output += '
';
            document.getElementById("user-list").innerHTML = output;
        }
    };
    xhr.send();
}
</script>

4. WebSockets Approach

<script>
const socket = io();
socket.on("message", function(data) {
    alert("New update: " + data);
});
</script>

5. Server-Sent Events (SSE) Approach

<script>
const eventSource = new EventSource("/stream");
eventSource.onmessage = function(event) {
    console.log("SSE message received: ", event.data);
};
</script>

6. GraphQL Approach

<script>
async function fetchGraphQL() {
    const response = await fetch("/graphql", {
        method: "POST",
        headers: { "Content-Type": "application/json" },
        body: JSON.stringify({ query: "{ users { id name email } }" })
    });
    const data = await response.json();
    console.log(data);
}
fetchGraphQL();
</script>

Conclusion

You've successfully created a database-driven website using PostgreSQL, Flask, AJAX, WebSockets, SSE, and GraphQL!