Developer's Catalog

Database

OrientDB

Overview

OrientDB is an open-source multi-model database management system that integrates four data models - Graph, Document, Object, and Key-Value - within a single engine. Developers can choose the optimal model based on application requirements. It supports standard SQL query language, enabling easy migration from existing relational databases, and provides flexible data management in Java environments.

Details

OrientDB was developed by Luca Garulli in 2010. Its greatest strength lies in accomplishing with a single database engine what traditionally required combining multiple database products. This is not pseudo multi-model support through API layers, but native support for all four models at the engine level.

Key features of OrientDB:

• Native multi-model engine (Graph, Document, Object, Key-Value)
• Standard SQL language support (with extended SQL syntax)
• Full ACID compliance
• Flexible schema-less, schema-full, and mixed mode support
• Distributed processing and multi-master configuration
• High-speed record insertion performance (220,000 records/second)
• Full-text search and geospatial indexing
• Strong security features (user, role, predicate security)
• Reactive query support
• Apache 2.0 license

Pros and Cons

Pros

• Unified Data Models: Can consolidate multiple database products
• High Performance: Fast traversal without JOINs
• SQL Compatibility: Leverage existing SQL skills
• Flexible Schema: Support from schema-free to strict type definitions
• Cost Efficiency: Open source and free to use
• Java Integration: High affinity with Java applications
• Distributed Architecture: Scalable multi-master configuration

Cons

• Learning Curve: Need to understand multi-model concepts
• Community Size: Smaller compared to Neo4j or MongoDB
• Enterprise Support: Limited commercial support options
• Ecosystem: Limited third-party tool integration
• Large-scale Operations: Limited track record in ultra-large environments

Key Links

• Official Website
• Documentation
• GitHub
• Studio (Web Management Tool)

Code Examples

Installation & Setup

# Running with Docker
docker run --name orientdb-container \
  -p 2424:2424 -p 2480:2480 \
  -e ORIENTDB_ROOT_PASSWORD=rootpass \
  -v orientdb-data:/orientdb/databases \
  orientdb:latest

# Docker Compose configuration
version: '3.8'
services:
  orientdb:
    image: orientdb:latest
    ports:
      - "2424:2424"
      - "2480:2480"
    environment:
      - ORIENTDB_ROOT_PASSWORD=rootpass
    volumes:
      - orientdb-data:/orientdb/databases

# Server access
# OrientDB Studio: http://localhost:2480
# Binary protocol: localhost:2424

# Java driver dependency (Maven)
<dependency>
    <groupId>com.orientechnologies</groupId>
    <artifactId>orientdb-client</artifactId>
    <version>3.2.15</version>
</dependency>

# Python driver installation
pip install pyorient

# Node.js driver installation
npm install orientjs

Basic Operations (CRUD)

-- Create and connect to database
CREATE DATABASE remote:localhost/testdb admin admin;
CONNECT remote:localhost/testdb admin admin;

-- Create classes (tables)
CREATE CLASS Person EXTENDS V;
CREATE CLASS Company EXTENDS V;
CREATE CLASS WorksFor EXTENDS E;

-- Create as documents
INSERT INTO Person SET name = 'John Doe', age = 30, email = '[email protected]';
INSERT INTO Company SET name = 'Tech Corp', industry = 'IT', founded = 2010;

-- Create as graph vertices
CREATE VERTEX Person SET name = 'Jane Smith', age = 28, department = 'Development';
CREATE VERTEX Company SET name = 'Innovation Inc', industry = 'Software';

-- Create edges (relationships)
CREATE EDGE WorksFor FROM (SELECT FROM Person WHERE name = 'Jane Smith') 
TO (SELECT FROM Company WHERE name = 'Innovation Inc') 
SET position = 'Engineer', startDate = '2022-04-01';

-- Read data
SELECT FROM Person;
SELECT FROM Person WHERE age > 25;

-- Graph traversal
SELECT FROM Person WHERE out('WorksFor').name = 'Tech Corp';

-- Query with relationships
SELECT person.name, company.name, edge.position 
FROM (
  MATCH {class: Person, as: person}-WorksFor{as: edge}-{class: Company, as: company}
  RETURN person, edge, company
);

-- Update data
UPDATE Person SET age = 31 WHERE name = 'John Doe';
UPDATE WorksFor SET position = 'Senior Engineer' WHERE out.name = 'John Doe';

-- Delete data
DELETE FROM Person WHERE name = 'John Doe';
DELETE EDGE WorksFor WHERE out.name = 'Jane Smith';

Multi-Model Operations

-- Document model
INSERT INTO User CONTENT {
  "name": "Robert Johnson",
  "profile": {
    "age": 25,
    "skills": ["Java", "Python", "JavaScript"],
    "address": {
      "city": "New York",
      "state": "NY"
    }
  },
  "projects": [
    {"name": "Project A", "status": "active"},
    {"name": "Project B", "status": "completed"}
  ]
};

-- Key-Value model
CREATE CLASS Configuration;
INSERT INTO Configuration SET key = 'app.timeout', value = '30000';
INSERT INTO Configuration SET key = 'app.debug', value = 'true';

-- Key-Value search
SELECT value FROM Configuration WHERE key = 'app.timeout';

-- Object model (Java integration)
CREATE CLASS Employee EXTENDS V;
INSERT INTO Employee SET 
  name = 'Michael Brown', 
  salary = 75000,
  hireDate = date('2023-01-15'),
  skills = ['Java', 'Spring Boot', 'Docker'];

-- Complex query (multi-model join)
SELECT 
  person.name,
  person.profile.skills,
  company.name as companyName
FROM Person person, Company company
WHERE person.out('WorksFor') = company
  AND person.profile.skills CONTAINS 'Java';

Indexing & Optimization

-- Create indexes
CREATE INDEX Person.name ON Person (name) NOTUNIQUE;
CREATE INDEX Person.email ON Person (email) UNIQUE;
CREATE INDEX Person.age_name ON Person (age, name) NOTUNIQUE;

-- Full-text index
CREATE INDEX Person.fulltext ON Person (name, email) FULLTEXT ENGINE LUCENE;

-- Geospatial index
CREATE CLASS Location EXTENDS V;
CREATE PROPERTY Location.coordinates EMBEDDED OPoint;
CREATE INDEX Location.coordinates ON Location (coordinates) SPATIAL ENGINE LUCENE;

-- Check indexes
SELECT FROM OIndexes;

-- Query plan
EXPLAIN SELECT FROM Person WHERE name = 'John Doe';

-- Profiling
PROFILE SELECT FROM Person WHERE age > 25;

-- Database statistics
SELECT FROM OFunction WHERE name = 'graph.info';

-- Performance settings
ALTER DATABASE CUSTOM useLightweightEdges = false;
ALTER DATABASE CUSTOM useClassForEdgeLabel = true;

Advanced Features

-- Transaction processing
BEGIN;
  INSERT INTO Person SET name = 'New User', email = '[email protected]';
  CREATE EDGE WorksFor FROM $lastRid TO (SELECT FROM Company WHERE name = 'Tech Corp');
COMMIT;

-- Function definition
CREATE FUNCTION getEmployeesByCompany "
  SELECT person.name 
  FROM Person person, Company company 
  WHERE person.out('WorksFor') = company 
    AND company.name = :companyName
" PARAMETERS [companyName] LANGUAGE SQL;

-- Function execution
SELECT getEmployeesByCompany('Tech Corp');

-- Trigger creation
CREATE TRIGGER PersonAudit AFTER INSERT ON Person 
FOR EACH ROW 
  INSERT INTO AuditLog SET 
    action = 'INSERT', 
    className = 'Person', 
    recordId = $new.@rid, 
    timestamp = sysdate();

-- Batch processing
BATCH
  INSERT INTO Person SET name = 'User1', age = 25;
  INSERT INTO Person SET name = 'User2', age = 30;
  INSERT INTO Person SET name = 'User3', age = 35;
END;

-- Distributed query (cluster environment)
SELECT FROM cluster:person_europe WHERE country = 'Germany';

Practical Examples

-- Social network analysis
SELECT person.name, 
       out('Follows').size() as following,
       in('Follows').size() as followers
FROM Person person
ORDER BY followers DESC
LIMIT 10;

-- Recommendation system (collaborative filtering)
SELECT DISTINCT recommendation.title, count(*) as score
FROM (
  SELECT expand(out('Purchased')) as products
  FROM User 
  WHERE name = 'John Doe'
) user_products
JOIN (
  SELECT in('Purchased') as users, @rid as product_id
  FROM Product
  WHERE @rid IN user_products
) similar_users
JOIN (
  SELECT expand(out('Purchased')) as recommendation
  FROM User
  WHERE @rid IN similar_users.users
    AND @rid <> (SELECT FROM User WHERE name = 'John Doe')
) recommendations
WHERE recommendation NOT IN user_products
GROUP BY recommendation
ORDER BY score DESC
LIMIT 5;

-- Shortest path search
SELECT shortestPath(
  (SELECT FROM Person WHERE name = 'Alice'),
  (SELECT FROM Person WHERE name = 'Bob'),
  'out',
  'Knows'
);

-- Fraud detection pattern
SELECT suspicious_account.name, 
       count(*) as transaction_count,
       sum(amount) as total_amount
FROM (
  SELECT expand(out('Transfer')) as transactions
  FROM Account
  WHERE @rid = #12:1
) account_transactions
WHERE transactions.timestamp > (sysdate() - 86400000)
  AND transactions.amount > 100000
GROUP BY transactions.in
HAVING count(*) > 5;

-- Geospatial query
SELECT name, coordinates
FROM Location
WHERE coordinates WITHIN circle(40.7128, -74.0060, 1000);

Java Usage Example

import com.orientechnologies.orient.core.db.ODatabaseSession;
import com.orientechnologies.orient.core.db.OrientDB;
import com.orientechnologies.orient.core.db.OrientDBConfig;
import com.orientechnologies.orient.core.record.OVertex;
import com.orientechnologies.orient.core.record.OEdge;

// Database connection
OrientDB orient = new OrientDB("remote:localhost", OrientDBConfig.defaultConfig());
ODatabaseSession db = orient.open("testdb", "admin", "admin");

try {
    // Begin transaction
    db.begin();
    
    // Create vertices
    OVertex person = db.newVertex("Person");
    person.setProperty("name", "John Doe");
    person.setProperty("age", 30);
    person.save();
    
    OVertex company = db.newVertex("Company");
    company.setProperty("name", "Tech Corp");
    company.setProperty("industry", "IT");
    company.save();
    
    // Create edge
    OEdge worksFor = person.addEdge(company, "WorksFor");
    worksFor.setProperty("position", "Engineer");
    worksFor.setProperty("startDate", new Date());
    worksFor.save();
    
    // Commit
    db.commit();
    
    // Execute query
    db.query("SELECT FROM Person WHERE name = ?", "John Doe")
      .stream()
      .forEach(result -> {
          System.out.println("Name: " + result.getProperty("name"));
          System.out.println("Age: " + result.getProperty("age"));
      });
    
} catch (Exception e) {
    db.rollback();
    e.printStackTrace();
} finally {
    db.close();
    orient.close();
}

// Asynchronous processing
CompletableFuture<List<OResult>> future = db.queryAsync("SELECT FROM Person");
future.thenAccept(results -> {
    results.forEach(result -> 
        System.out.println(result.getProperty("name"))
    );
});