Comprehensive comparison for Database technology in Software Development applications

const { Database, aql } = require('arangojs');
const express = require('express');
const app = express();

// Initialize ArangoDB connection with best practices
const db = new Database({
  url: process.env.ARANGO_URL || 'http://localhost:8529',
  databaseName: process.env.ARANGO_DB || 'software_dev_db',
  auth: {
    username: process.env.ARANGO_USER || 'root',
    password: process.env.ARANGO_PASSWORD
  }
});

const COLLECTIONS = {
  PROJECTS: 'projects',
  ISSUES: 'issues',
  USERS: 'users',
  ASSIGNED_TO: 'assigned_to'
};

// Initialize collections and indexes
async function initializeDatabase() {
  try {
    const collections = await db.listCollections();
    const collectionNames = collections.map(c => c.name);

    // Create document collections if they don't exist
    for (const [key, name] of Object.entries(COLLECTIONS)) {
      if (!collectionNames.includes(name)) {
        if (name === 'assigned_to') {
          await db.createEdgeCollection(name);
        } else {
          await db.createCollection(name);
        }
      }
    }

    // Create indexes for performance
    const issuesCollection = db.collection(COLLECTIONS.ISSUES);
    await issuesCollection.ensureIndex({
      type: 'persistent',
      fields: ['projectId', 'status'],
      name: 'idx_project_status'
    });

    await issuesCollection.ensureIndex({
      type: 'persistent',
      fields: ['createdAt'],
      name: 'idx_created_at'
    });

    console.log('Database initialized successfully');
  } catch (error) {
    console.error('Database initialization failed:', error);
    throw error;
  }
}

// API endpoint: Get project with issues and assigned users
app.get('/api/projects/:projectId/dashboard', async (req, res) => {
  const { projectId } = req.params;
  const { status, limit = 50, offset = 0 } = req.query;

  try {
    // Validate input
    if (!projectId || projectId.length === 0) {
      return res.status(400).json({ error: 'Invalid project ID' });
    }

    // Complex AQL query with graph traversal and filtering
    const query = aql`
      LET project = DOCUMENT(${COLLECTIONS.PROJECTS}, ${projectId})
      
      RETURN project ? {
        project: project,
        issueStats: (
          FOR issue IN ${db.collection(COLLECTIONS.ISSUES)}
            FILTER issue.projectId == ${projectId}
            ${status ? aql`FILTER issue.status == ${status}` : aql``}
            COLLECT status = issue.status WITH COUNT INTO count
            RETURN { status, count }
        ),
        recentIssues: (
          FOR issue IN ${db.collection(COLLECTIONS.ISSUES)}
            FILTER issue.projectId == ${projectId}
            ${status ? aql`FILTER issue.status == ${status}` : aql``}
            SORT issue.createdAt DESC
            LIMIT ${parseInt(offset)}, ${parseInt(limit)}
            LET assignees = (
              FOR v, e IN 1..1 OUTBOUND issue ${db.collection(COLLECTIONS.ASSIGNED_TO)}
                RETURN {
                  userId: v._key,
                  name: v.name,
                  email: v.email
                }
            )
            RETURN MERGE(issue, { assignees })
        )
      } : null
    `;

    const cursor = await db.query(query);
    const result = await cursor.next();

    if (!result) {
      return res.status(404).json({ error: 'Project not found' });
    }

    res.json({
      success: true,
      data: result
    });

  } catch (error) {
    console.error('Error fetching project dashboard:', error);
    
    // Handle specific ArangoDB errors
    if (error.code === 404) {
      return res.status(404).json({ error: 'Resource not found' });
    }
    
    if (error.code === 1203) {
      return res.status(500).json({ error: 'Database connection failed' });
    }

    res.status(500).json({ 
      error: 'Internal server error',
      message: process.env.NODE_ENV === 'development' ? error.message : undefined
    });
  }
});

// API endpoint: Create issue with transaction
app.post('/api/issues', express.json(), async (req, res) => {
  const { projectId, title, description, assigneeIds = [] } = req.body;

  try {
    // Validate required fields
    if (!projectId || !title) {
      return res.status(400).json({ error: 'Missing required fields' });
    }

    // Use transaction for atomic operations
    const result = await db.executeTransaction(
      [COLLECTIONS.ISSUES, COLLECTIONS.ASSIGNED_TO, COLLECTIONS.USERS],
      async (step) => {
        // Create issue document
        const issueData = {
          projectId,
          title,
          description,
          status: 'open',
          createdAt: new Date().toISOString(),
          updatedAt: new Date().toISOString()
        };

        const issueResult = await step(() => 
          db.collection(COLLECTIONS.ISSUES).save(issueData)
        );

        // Create edges to assigned users
        const edges = [];
        for (const userId of assigneeIds) {
          const edgeResult = await step(() =>
            db.collection(COLLECTIONS.ASSIGNED_TO).save({
              _from: issueResult._id,
              _to: `${COLLECTIONS.USERS}/${userId}`,
              assignedAt: new Date().toISOString()
            })
          );
          edges.push(edgeResult);
        }

        return { issue: issueResult, assignments: edges };
      }
    );

    res.status(201).json({
      success: true,
      data: result
    });

  } catch (error) {
    console.error('Error creating issue:', error);
    res.status(500).json({ error: 'Failed to create issue' });
  }
});

// Graceful shutdown
process.on('SIGTERM', async () => {
  console.log('Closing database connection...');
  await db.close();
  process.exit(0);
});

// Start server
const PORT = process.env.PORT || 3000;
initializeDatabase().then(() => {
  app.listen(PORT, () => {
    console.log(`Server running on port ${PORT}`);
  });
}).catch(error => {
  console.error('Failed to start server:', error);
  process.exit(1);
});