Skip to main content

SIMS MSSQL2oData - SQL Server to OData REST API

Open Source (MIT)
SIMS MSSQL2oData - SQL Server to OData REST API

Transform your Microsoft SQL Server databases into fully compliant OData 4.0 REST APIs. Production-ready solution built with Nest.js, enabling seamless integration with Excel, Power BI, and modern applications.

SQL Server: Bridging Legacy Databases and Modern APIs

Modern applications expect REST APIs, but many organizations have valuable data locked in SQL Server databases. Building custom APIs for each database is time-consuming, expensive, and often results in inconsistent interfaces. Excel and Power BI users need direct database access, but exposing databases directly raises security concerns.

🔌

The Challenge

Organizations need to expose SQL Server data as modern REST APIs for integration with Excel, Power BI, web applications, and microservices—without building custom endpoints for each table or database.

The Solution: OData 4.0 REST API

SIMS MSSQL2oData transforms your SQL Server database into a fully compliant OData 4.0 REST API. Built with Nest.js and TypeScript, it provides a production-ready solution that works out of the box with your existing databases—no schema changes required.

How It Works

The solution connects to your SQL Server database and automatically:

  1. Discovers Schema: Reads table structures and relationships from your database
  2. Generates Metadata: Creates EDMX metadata documents for OData clients
  3. Exposes Endpoints: Creates REST endpoints for each table with full CRUD support
  4. Handles Queries: Processes OData query parameters ($filter, $orderby, $top, $skip, $select, $count)
  5. Validates Access: Enforces whitelist/blacklist rules for table-level security

All operations use parameterized queries to prevent SQL injection, and the system validates all inputs before database operations.

Key Capabilities

🚀 Full OData 4.0 Compliance

✅

Standard Compliance

Complete support for OData 4.0 specification (OASIS standard), ensuring compatibility with any OData-compatible client.

📊

Excel & Power BI Ready

Works out of the box with Excel and Power BI. Connect directly without additional tools or configuration.

📋

EDMX Metadata

Automatic EDMX metadata generation enables automatic schema discovery by OData clients.

🔍

Standard Query Parameters

Full support for $filter, $orderby, $top, $skip, $select, and $count parameters.

🔧 Comprehensive CRUD Operations

⚙️

Full REST API Support

GET Retrieve data with advanced filtering and pagination
POST Create new records
PUT Full record updates
PATCH Partial record updates
DELETE Remove records
OPTIONS Full CORS support

🔍 Advanced Query Capabilities

  • 🔎
    Complex Filtering

    Support for operators: eq, ne, gt, ge, lt, le, and, or, not, contains, startswith, endswith

  • 📊
    Multi-field Sorting

    Order by multiple fields with ascending/descending direction

  • 📄
    Efficient Pagination

    Handle large datasets with $top and $skip parameters

  • 🎯
    Field Selection

    Return only required fields with $select to reduce payload size

  • 🔢
    Count Queries

    Get total record counts for pagination and reporting

🔐 Security & Access Control

🛡️

Enterprise-Grade Security

  • ✅ Whitelist/Blacklist Tables: Granular control over which tables are exposed
  • ✅ SQL Injection Protection: Parameterized queries and table name validation
  • ✅ Data Validation: Automatic validation before insert/update operations
  • ✅ Nullable Field Checks: Prevents invalid NULL values in required fields
  • ✅ CORS Configuration: Control cross-origin requests

💻 Developer Experience

📚

Swagger UI

Interactive API documentation with automatic endpoint discovery

📝

Structured Logging

JSON-formatted logs for integration with ELK, Loki, Grafana

⚡

Metadata Caching

Optimized performance with intelligent caching of table schemas

⚡ Performance Optimized

  • 🚀
    Single-Query Metadata

    No N+1 problems—metadata generated in a single database query

  • 🔗
    Connection Pooling

    Efficient database connection management

  • 💾
    In-Memory Caching

    Table structures cached in memory for faster responses

  • ⚡
    Optimized SQL Queries

    Efficient query generation for better response times

💼 Use Cases

📊 Business Intelligence & Analytics

Connect Excel, Power BI, Tableau, and other BI tools directly to your SQL Server without complex setup. Analysts work with live data through familiar OData interfaces.

🔗 Microservices Integration

Expose your database as a standardized REST API for integration with modern microservices architectures. Consistent OData interface across all services.

📱 Mobile & Web Applications

Build mobile and web applications that consume your database through a clean, standardized API. No need for custom backend development.

🌐 API Gateway Pattern

Use as a lightweight API gateway for legacy SQL Server databases, enabling modern API-first architectures without database migration.

🛠️ Technical Stack

Core Technologies

  • ⚙️ Framework: Nest.js (TypeScript-first Node.js framework)
  • 🗄️ Database: Microsoft SQL Server (via mssql package)
  • 📋 Protocol: OData 4.0 (OASIS standard)
  • 📚 Documentation: Swagger/OpenAPI 3.0

Architecture Features

  • 🧩 Modular, scalable architecture
  • 💉 Dependency injection for testing
  • 🔄 Middleware-based request handling
  • 🎯 Service layer separation

⚙️ Easy Configuration

Simple environment-based configuration—no code changes required:

DB_SERVER=localhost
DB_NAME=your_database
DB_USER=sa
DB_PASSWORD=your_password
ODATA_TABLES_WHITELIST=Users,Products,Orders
LOG_FORMAT=json
ENABLE_SWAGGER=true

📈 Example Usage

Query Data

GET /api/Products?$filter=Price gt 100&$orderby=Name asc&$top=10

Create Record

POST /api/Products
{
"Name": "New Product",
"Price": 99.99,
"CategoryId": 1
}

Update Record

PATCH /api/Products(1)
{
"Price": 89.99
}

Access Points

  • • API: http://localhost:3000/api
  • • Metadata: http://localhost:3000/api/$metadata
  • • Swagger: http://localhost:3000/api-docs

🎯 Key Benefits

👨‍💻

For Developers

  • • Rapid integration
  • • TypeScript support
  • • Auto-generated docs
  • • Testable architecture
💼

For Business

  • • No database changes
  • • Excel ready
  • • Open source (MIT)
  • • Fast deployment
⚙️

For IT Operations

  • • Production ready
  • • Structured logging
  • • Scalable design
  • • Maintainable code

💡 Why This Matters

For organizations with SQL Server databases, MSSQL2oData eliminates the need to build custom REST APIs for each database or table. It provides:

  • Standards Compliance: Full OData 4.0 implementation ensures compatibility with any OData client
  • Zero Configuration: Works out of the box with your existing SQL Server databases
  • Production Ready: Built with security and performance in mind from day one
  • Developer Friendly: Comprehensive documentation, Swagger UI, and TypeScript support
  • Flexible: Whitelist/blacklist, views support, and extensive configuration options
  • Open Source: Free to use, modify, and distribute under MIT license

If you need to expose SQL Server data as modern REST APIs for Excel, Power BI, or application integration, this solution provides enterprise-grade capabilities without the enterprise price tag.

🚀 Quick Start

  1. Clone the repository
  2. Install dependencies: npm install
  3. Configure your database: Set up .env file with connection settings
  4. Run the server: npm run start:dev
  5. Explore the API: Visit http://localhost:3000/api-docs

📞 Contact & Support

For questions, contributions, or support:

  • Email: vhlu@sims-service.com
  • Website: https://sims-service.com
📄

Open Source License

MSSQL2oData is released under the MIT License. Use freely in commercial and non-commercial projects.

🔗 Resources

📂

GitHub Repository

Explore the complete source code, documentation, and latest releases on GitHub.

View on GitHub
← Back to productsContact Us