Backend-Integration mit KI: Server, APIs & Datenbanken¶

// Prompt: "Erstelle eine Express.js REST API für ein Blog-System mit:
// - CRUD für Blog-Posts
// - CRUD für Kommentare
// - JWT-Authentifizierung
// - Middleware für Error-Handling
// - TypeScript
// - MongoDB mit Mongoose"

// KI-generierter Code
import express from 'express';
import mongoose from 'mongoose';
import jwt from 'jsonwebtoken';
import bcrypt from 'bcryptjs';

// MongoDB Verbindung
mongoose.connect('mongodb://localhost:27017/blog', {
  useNewUrlParser: true,
  useUnifiedTopology: true
});

// Models
const UserSchema = new mongoose.Schema({
  username: { type: String, required: true, unique: true },
  email: { type: String, required: true, unique: true },
  password: { type: String, required: true },
  createdAt: { type: Date, default: Date.now }
});

const PostSchema = new mongoose.Schema({
  title: { type: String, required: true },
  content: { type: String, required: true },
  author: { type: mongoose.Schema.Types.ObjectId, ref: 'User', required: true },
  createdAt: { type: Date, default: Date.now },
  updatedAt: { type: Date, default: Date.now }
});

const CommentSchema = new mongoose.Schema({
  content: { type: String, required: true },
  author: { type: mongoose.Schema.Types.ObjectId, ref: 'User', required: true },
  post: { type: mongoose.Schema.Types.ObjectId, ref: 'Post', required: true },
  createdAt: { type: Date, default: Date.now }
});

const User = mongoose.model('User', UserSchema);
const Post = mongoose.model('Post', PostSchema);
const Comment = mongoose.model('Comment', CommentSchema);

// Middleware
const authenticate = async (req: any, res: any, next: any) => {
  try {
    const token = req.header('Authorization')?.replace('Bearer ', '');
    if (!token) throw new Error('Authentifizierung erforderlich');

    const decoded = jwt.verify(token, process.env.JWT_SECRET || 'secret') as any;
    const user = await User.findById(decoded._id);
    if (!user) throw new Error('Benutzer nicht gefunden');

    req.user = user;
    next();
  } catch (error) {
    res.status(401).json({ error: (error as Error).message });
  }
};

// Routes
const router = express.Router();

// Auth Routes
router.post('/users/register', async (req, res) => {
  try {
    const { username, email, password } = req.body;

    // Überprüfen, ob Benutzer existiert
    const existingUser = await User.findOne({ $or: [{ username }, { email }] });
    if (existingUser) {
      return res.status(400).json({ error: 'Benutzername oder E-Mail bereits vorhanden' });
    }

    // Passwort hashen
    const hashedPassword = await bcrypt.hash(password, 10);

    // Benutzer erstellen
    const user = new User({ username, email, password: hashedPassword });
    await user.save();

    // Token generieren
    const token = jwt.sign({ _id: user._id }, process.env.JWT_SECRET || 'secret', { expiresIn: '7d' });

    res.status(201).json({ user, token });
  } catch (error) {
    res.status(500).json({ error: (error as Error).message });
  }
});

router.post('/users/login', async (req, res) => {
  try {
    const { username, password } = req.body;
    const user = await User.findOne({ username });

    if (!user) {
      return res.status(401).json({ error: 'Ungültige Anmeldedaten' });
    }

    const isMatch = await bcrypt.compare(password, user.password);
    if (!isMatch) {
      return res.status(401).json({ error: 'Ungültige Anmeldedaten' });
    }

    const token = jwt.sign({ _id: user._id }, process.env.JWT_SECRET || 'secret', { expiresIn: '7d' });
    res.json({ user, token });
  } catch (error) {
    res.status(500).json({ error: (error as Error).message });
  }
});

// Post Routes
router.post('/posts', authenticate, async (req: any, res) => {
  try {
    const { title, content } = req.body;
    const post = new Post({ title, content, author: req.user._id });
    await post.save();
    res.status(201).json(post);
  } catch (error) {
    res.status(500).json({ error: (error as Error).message });
  }
});

router.get('/posts', async (req, res) => {
  try {
    const posts = await Post.find().populate('author', 'username').sort({ createdAt: -1 });
    res.json(posts);
  } catch (error) {
    res.status(500).json({ error: (error as Error).message });
  }
});

// Comment Routes
router.post('/posts/:postId/comments', authenticate, async (req: any, res) => {
  try {
    const { postId } = req.params;
    const { content } = req.body;

    const comment = new Comment({
      content,
      author: req.user._id,
      post: postId
    });
    await comment.save();

    res.status(201).json(comment);
  } catch (error) {
    res.status(500).json({ error: (error as Error).message });
  }
});

// App erstellen
const app = express();
app.use(express.json());
app.use('/api', router);

const PORT = process.env.PORT || 3000;
app.listen(PORT, () => {
  console.log(`Server läuft auf Port ${PORT}`);
});

// KI-generierte Resolver
const resolvers = {
  Query: {
    getProducts: async (_, { category }, { dataSources }) => {
      return dataSources.productsAPI.getProducts(category);
    },
    getProduct: async (_, { id }, { dataSources }) => {
      return dataSources.productsAPI.getProduct(id);
    },
    getUser: async (_, { id }, { dataSources }) => {
      return dataSources.usersAPI.getUser(id);
    },
    getOrder: async (_, { id }, { dataSources }) => {
      return dataSources.ordersAPI.getOrder(id);
    },
    getOrdersByUser: async (_, { userId }, { dataSources }) => {
      return dataSources.ordersAPI.getOrdersByUser(userId);
    }
  },
  Mutation: {
    createProduct: async (_, { name, description, price, category }, { dataSources }) => {
      return dataSources.productsAPI.createProduct({ name, description, price, category });
    },
    updateProduct: async (_, { id, ...product }, { dataSources }) => {
      return dataSources.productsAPI.updateProduct(id, product);
    },
    deleteProduct: async (_, { id }, { dataSources }) => {
      return dataSources.productsAPI.deleteProduct(id);
    },
    createOrder: async (_, { userId, productIds }, { dataSources }) => {
      // Produkte abrufen
      const products = await Promise.all(
        productIds.map(id => dataSources.productsAPI.getProduct(id))
      );

      // Gesamtpreis berechnen
      const total = products.reduce((sum, product) => sum + product.price, 0);

      return dataSources.ordersAPI.createOrder({
        userId,
        products: productIds,
        total
      });
    }
  },
  Product: {
    category: async (product, _, { dataSources }) => {
      return dataSources.categoriesAPI.getCategory(product.categoryId);
    }
  },
  Order: {
    user: async (order, _, { dataSources }) => {
      return dataSources.usersAPI.getUser(order.userId);
    },
    products: async (order, _, { dataSources }) => {
      return Promise.all(
        order.productIds.map(id => dataSources.productsAPI.getProduct(id))
      );
    }
  }
};

// ❌ Nicht optimierte Abfrage
const users = await User.find({ age: { $gt: 18 } }).sort({ createdAt: -1 });

// ✅ KI-optimierte Abfrage
const users = await User.find({ age: { $gt: 18 } })
  .select('username email createdAt')  // Nur benötigte Felder
  .sort({ createdAt: -1 })
  .limit(100)  // Pagination
  .lean();  // Schnellere Ergebnisse

// Index erstellen (KI-Empfehlung)
await User.createIndex({ age: 1, createdAt: -1 });

// Aggregation-Pipeline mit KI
const stats = await User.aggregate([
  { $match: { createdAt: { $gte: new Date('2024-01-01') } } },
  { $group: {
      _id: { $dateToMonth: "$createdAt" },
      count: { $sum: 1 },
      avgAge: { $avg: "$age" }
    }
  },
  { $sort: { _id: 1 } }
]);

// KI-generierte Sequelize-Modelle
import { DataTypes } from 'sequelize';
import sequelize from '../config/database';

// User Model
const User = sequelize.define('User', {
  id: {
    type: DataTypes.INTEGER,
    primaryKey: true,
    autoIncrement: true
  },
  username: {
    type: DataTypes.STRING(50),
    allowNull: false,
    unique: true,
    validate: {
      notEmpty: true,
      len: [3, 50]
    }
  },
  email: {
    type: DataTypes.STRING(100),
    allowNull: false,
    unique: true,
    validate: {
      isEmail: true
    }
  },
  password: {
    type: DataTypes.STRING,
    allowNull: false
  },
  role: {
    type: DataTypes.ENUM('admin', 'user', 'guest'),
    defaultValue: 'user'
  },
  createdAt: {
    type: DataTypes.DATE,
    defaultValue: DataTypes.NOW
  },
  updatedAt: {
    type: DataTypes.DATE,
    defaultValue: DataTypes.NOW,
    onUpdate: DataTypes.NOW
  }
}, {
  timestamps: true,
  paranoid: true,  // Soft Delete
  indexes: [
    {
      unique: true,
      fields: ['username']
    },
    {
      unique: true,
      fields: ['email']
    }
  ]
});

// Product Model
const Product = sequelize.define('Product', {
  id: {
    type: DataTypes.INTEGER,
    primaryKey: true,
    autoIncrement: true
  },
  name: {
    type: DataTypes.STRING(100),
    allowNull: false
  },
  description: {
    type: DataTypes.TEXT,
    allowNull: true
  },
  price: {
    type: DataTypes.FLOAT,
    allowNull: false,
    validate: {
      min: 0
    }
  },
  stock: {
    type: DataTypes.INTEGER,
    defaultValue: 0
  },
  categoryId: {
    type: DataTypes.INTEGER,
    allowNull: false
  }
});

// Beziehungen
User.hasMany(Product, { foreignKey: 'sellerId' });
Product.belongsTo(User, { foreignKey: 'sellerId' });

// Hooks
User.beforeCreate(async (user) => {
  const salt = await bcrypt.genSalt(10);
  user.password = await bcrypt.hash(user.password, salt);
});

// Scopes
User.addScope('active', {
  where: { isActive: true }
});

// Methoden
User.prototype.validPassword = async function(password) {
  return await bcrypt.compare(password, this.password);
};

// Export
export { User, Product };

// ❌ Anfällig für SQL Injection
const getUser = (username, password) => {
  return db.query(
    `SELECT * FROM users WHERE username = '${username}' AND password = '${password}'`
  );
};

// ✅ Sicher mit Parameterized Queries
const getUser = (username, password) => {
  return db.query(
    'SELECT * FROM users WHERE username = ? AND password = ?',
    [username, password]
  );
};

// ✅ Noch besser mit ORM
const getUser = async (username) => {
  return await User.findOne({ where: { username } });
};

// ❌ Anfällig für XSS
app.get('/search', (req, res) => {
  const query = req.query.q;
  res.send(`<h1>Suchergebnisse für: ${query}</h1>`);
});

// ✅ Sicher mit Escaping
app.get('/search', (req, res) => {
  const query = req.query.q;
  res.send(`<h1>Suchergebnisse für: ${escapeHtml(query)}</h1>`);
});

// ✅ Noch besser mit Template Engine
app.get('/search', (req, res) => {
  const query = req.query.q;
  res.render('search', { query });
});

// Escape-Funktion
function escapeHtml(unsafe) {
  return unsafe
    .replace(/&/g, "&amp;")
    .replace(/</g, "&lt;")
    .replace(/>/g, "&gt;")
    .replace(/"/g, "&quot;")
    .replace(/'/g, "&#039;");
}

// Mit Express und csurf
const csrf = require('csurf');
const cookieParser = require('cookie-parser');

const csrfProtection = csrf({ cookie: true });

app.use(cookieParser());
app.use(csrfProtection);

// Formular mit CSRF-Token
app.get('/form', (req, res) => {
  res.render('form', { csrfToken: req.csrfToken() });
});

// Formular-Handling
app.post('/process', csrfProtection, (req, res) => {
  // Verarbeitung
});

import redis from 'redis';
import { promisify } from 'util';

// Redis Client
const client = redis.createClient();
const getAsync = promisify(client.get).bind(client);
const setAsync = promisify(client.set).bind(client);

// KI-gestützte Caching-Strategie
class SmartCache {
  constructor() {
    this.prefix = 'cache:';
  }

  async get(key) {
    try {
      const value = await getAsync(this.prefix + key);
      return value ? JSON.parse(value) : null;
    } catch (error) {
      console.error('Cache-Error:', error);
      return null;
    }
  }

  async set(key, value, ttl = 3600) {
    try {
      await setAsync(
        this.prefix + key,
        JSON.stringify(value),
        'EX',
        ttl
      );
    } catch (error) {
      console.error('Cache-Error:', error);
    }
  }

  // KI-gestützte TTL-Bestimmung
  getTTL(pattern) {
    // Musterbasierte TTL-Bestimmung
    const ttlPatterns = {
      '/api/users': 300,      // 5 Minuten
      '/api/posts': 600,      // 10 Minuten
      '/api/products': 1800,  // 30 Minuten
      '/api/static': 86400     // 1 Tag
    };

    for (const [pattern, ttl] of Object.entries(ttlPatterns)) {
      if (pattern.test(pattern)) {
        return ttl;
      }
    }

    return 3600; // Standard: 1 Stunde
  }
}

// Verwendung
const cache = new SmartCache();

// Middleware für Express
const cacheMiddleware = (pattern, ttl) => {
  return async (req, res, next) => {
    const key = req.originalUrl || req.url;
    const cached = await cache.get(key);

    if (cached) {
      return res.json(cached);
    }

    // Original Request Handler
    const originalSend = res.send;
    res.send = function(body) {
      if (res.statusCode === 200) {
        cache.set(key, body, ttl);
      }
      originalSend.call(this, body);
    };

    next();
  };
};

// Route mit Caching
app.get('/api/posts', cacheMiddleware(/\/api\/posts/, 600), getPosts);