@soapjs/soap-node-sql

This package provides SQL database integration for the SoapJS framework, supporting PostgreSQL, MySQL, and SQLite. It enables seamless interaction with SQL databases while ensuring that your data access layer is clean, efficient, and scalable.

Features

Multi-Database Support: Full support for PostgreSQL, MySQL, and SQLite with unified API
Clean Architecture Support: Follows SoapJS clean architecture patterns with full abstraction support
Type Safety: Full TypeScript support with comprehensive type definitions
Source Implementation: Full implementation of Source interface for SQL databases
Transaction Support: Full support for SQL transactions with SOAPJS transaction system
Query Builder: Advanced query building with Where conditions and QueryBuilder support
Field Mapping: Flexible field mapping between domain entities and database tables
Database Migrations: Powerful migration system for managing database schema changes with rollback support
Performance Monitoring: Optional built-in performance monitoring with metrics collection and slow query detection
Connection Pooling: Advanced connection pool configuration for optimal performance
Error Handling: Comprehensive error handling with specific SQL error types

Supported Databases

PostgreSQL

Driver: pg (node-postgres)
Expected Version: ^8.11.0
Features: Full support including JSON operations, advanced indexing, and complex queries

MySQL

Driver: mysql2
Expected Version: ^3.6.0
Features: Full support including stored procedures, views, and MySQL-specific optimizations

SQLite

Driver: better-sqlite3
Expected Version: ^9.2.0
Features: Full support for embedded database operations with file-based storage

Installation

Remember to have the appropriate database driver and @soapjs/soap installed in your project:

# For PostgreSQL
npm install @soapjs/soap-node-sql pg

# For MySQL
npm install @soapjs/soap-node-sql mysql2

# For SQLite
npm install @soapjs/soap-node-sql better-sqlite3

# Core SoapJS framework
npm install @soapjs/soap

Quick Start

1. Import the necessary classes:

import {
  SoapSQL,
  SqlSource,
  SqlConfig,
  SqlMigrationManager,
  createMigration
} from '@soapjs/soap-node-sql';
import { Where, MetaMapper, DatabaseContext, ReadRepository, ReadWriteRepository, Entity } from '@soapjs/soap';

2. Set up your database configuration:

PostgreSQL Configuration

const postgresConfig = new SqlConfig({
  type: 'postgresql',
  host: 'localhost',
  port: 5432,
  database: 'myapp',
  username: 'user',
  password: 'password',
  ssl: false,
  pool: {
    min: 2,
    max: 10,
    idleTimeoutMillis: 30000
  }
});

MySQL Configuration

const mysqlConfig = new SqlConfig({
  type: 'mysql',
  host: 'localhost',
  port: 3306,
  database: 'myapp',
  username: 'user',
  password: 'password',
  ssl: false,
  pool: {
    min: 2,
    max: 10,
    acquireTimeoutMillis: 30000
  }
});

SQLite Configuration

const sqliteConfig = new SqlConfig({
  type: 'sqlite',
  database: './myapp.db',
  mode: 'rwc',
  pool: {
    min: 1,
    max: 1
  }
});

3. Create a new `SoapSQL` driver instance:

const soapSql = await SoapSQL.create(postgresConfig); // or mysqlConfig, sqliteConfig

4. Define your entities and models:

// Entity
interface User extends Entity {
  id: string;
  name: string;
  email: string;
  age: number;
  status: 'active' | 'inactive';
  createdAt: Date;
  updatedAt: Date;
}

// Model (database table structure)
interface UserModel {
  id: string;
  name: string;
  email: string;
  age: number;
  status: string;
  created_at: string;
  updated_at: string;
}

5. Create SQL source and use with SOAPJS repositories:

// Create mapper
const userMapper = new MetaMapper(User, UserModel);

// Create source with field mappings
const userSource = new SqlSource<UserModel>(
  soapSql,
  'users',
  {
    modelFieldMappings: {
      id: { name: 'id', type: 'String' },
      name: { name: 'name', type: 'String' },
      email: { name: 'email', type: 'String' },
      age: { name: 'age', type: 'Number' },
      status: { name: 'status', type: 'String' },
      createdAt: { name: 'created_at', type: 'Date' },
      updatedAt: { name: 'updated_at', type: 'Date' }
    },
    indexes: [
      { name: 'idx_users_email', columns: ['email'], unique: true },
      { name: 'idx_users_status', columns: ['status'] },
      { name: 'idx_users_created_at', columns: ['created_at'] }
    ],
    performanceMonitoring: {
      enabled: true,
      slowQueryThreshold: 1000
    }
  }
);

// Create data context
const userContext = new DatabaseContext(
  userSource,
  userMapper,
  soapSql.sessions
);

// Create repositories using SOAPJS abstractions
const userReadRepo = new ReadRepository(userContext);
const userRepo = new ReadWriteRepository(userContext);

6. Using repositories with SOAPJS abstractions:

Basic CRUD Operations

// Find users with Where conditions
const where = new Where()
  .valueOf('status').isEq('active')
  .and.valueOf('age').isGte(18);

const result = await userRepo.find({ where });
if (result.isSuccess()) {
  const users = result.content;
  console.log('Found users:', users);
}

// Count users
const countResult = await userRepo.count({ where });
if (countResult.isSuccess()) {
  console.log('User count:', countresult.content);
}

// Add new user
const newUser: User = {
  id: 'user-123',
  name: 'John Doe',
  email: 'john@example.com',
  age: 30,
  status: 'active',
  createdAt: new Date(),
  updatedAt: new Date()
};

const addResult = await userRepo.add([newUser]);
if (addResult.isSuccess()) {
  console.log('User added:', addresult.content);
}

// Update user
const updateResult = await userRepo.update({
  where: new Where().valueOf('id').isEq('user-123'),
  updates: [{ name: 'Jane Doe' }],
  methods: ['updateOne']
});
if (updateResult.isSuccess()) {
  console.log('User updated:', updateresult.content);
}

// Remove user
const removeResult = await userRepo.remove({
  where: new Where().valueOf('id').isEq('user-123')
});
if (removeResult.isSuccess()) {
  console.log('User removed:', removeresult.content);
}

Advanced Queries

// Complex Where conditions
const complexWhere = new Where()
  .valueOf('status').isEq('active')
  .and.brackets(w => {
    w.valueOf('age').isGte(18)
      .and.valueOf('age').isLte(65);
  })
  .and.brackets(w => {
    w.valueOf('email').isLike('@gmail.com')
      .or.valueOf('email').isLike('@yahoo.com');
  });

const users = await userRepo.find({ 
  where: complexWhere,
  limit: 10,
  offset: 0,
  sort: { createdAt: 'desc' }
});

// Aggregation queries
const aggregationResult = await userRepo.aggregate({
  where: new Where().valueOf('status').isEq('active'),
  groupBy: ['age'],
  having: { count: { $gte: 5 } }
});

7. Transaction Support

Using SOAPJS Transaction System

import { Transaction, TransactionRunner, Result } from '@soapjs/soap';

class CreateUserTransaction extends Transaction<void> {
  constructor(
    private readonly userRepo: ReadWriteRepository<User>,
    private readonly userData: { name: string; email: string; age: number }
  ) {
    super(userRepo);
  }

  public async execute(): Promise<Result<void>> {
    const user: User = {
      id: `user-${Date.now()}`,
      name: this.userData.name,
      email: this.userData.email,
      age: this.userData.age,
      status: 'active',
      createdAt: new Date(),
      updatedAt: new Date()
    };
    
    const result = await this.userRepo.add([user]);
    
    if (result.isFailure()) {
      this.abort("Failed to create user: " + result.failure.error.message);
    }

    return Result.withSuccess();
  }
}

// Execute transaction
const runner = TransactionRunner.getInstance('default');
const transaction = new CreateUserTransaction(userRepo, {
  name: 'John Doe',
  email: 'john@example.com',
  age: 30
});

const result = await runner.run(transaction);
if (result.isSuccess()) {
  console.log('Transaction completed successfully');
} else {
  console.error('Transaction failed:', result.failure.error.message);
}

8. Database Migrations

Creating Migrations

import { SqlMigrationManager, createMigration, MigrationDefinition } from '@soapjs/soap-node-sql';

// Create a migration
const createUsersTable = createMigration({
  id: 'create-users-table',
  version: 1,
  description: 'Create users table with indexes',
  up: async (query) => {
    await query(`
      CREATE TABLE users (
        id VARCHAR(255) PRIMARY KEY,
        name VARCHAR(255) NOT NULL,
        email VARCHAR(255) UNIQUE NOT NULL,
        age INTEGER,
        status VARCHAR(50) DEFAULT 'active',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
      )
    `);

    await query('CREATE INDEX idx_users_email ON users(email)');
    await query('CREATE INDEX idx_users_status ON users(status)');
    await query('CREATE INDEX idx_users_created_at ON users(created_at)');
  },
  down: async (query) => {
    await query('DROP TABLE IF EXISTS users');
  }
});

const addUserProfileField = createMigration({
  id: 'add-user-profile-field',
  version: 2,
  description: 'Add profile field to users table',
  up: async (query) => {
    await query('ALTER TABLE users ADD COLUMN profile JSON');
  },
  down: async (query) => {
    await query('ALTER TABLE users DROP COLUMN profile');
  }
});

Running Migrations

// Initialize migration manager
const migrationManager = new SqlMigrationManager(soapSql, {
  tableName: 'migrations',
  autoRun: false,
  validateBeforeRun: true
});

// Register migrations
migrationManager.register(createUsersTable);
migrationManager.register(addUserProfileField);

// Run migrations
const result = await migrationManager.migrate();
console.log('Migration result:', result);

// Check migration status
const status = await migrationManager.getMigrationStatus();
console.log('Migration status:', status);

// Rollback last migration
const rollbackResult = await migrationManager.rollback();
console.log('Rollback result:', rollbackResult);

Performance Monitoring

The package includes optional built-in performance monitoring capabilities to help you track and optimize your SQL operations.

Basic Usage

// Create source with performance monitoring
const userSource = new SqlSource<UserModel>(
  soapSql,
  'users',
  {
    modelFieldMappings: {
      // ... field mappings
    },
    performanceMonitoring: {
      enabled: true,
      slowQueryThreshold: 1000, // 1 second
      maxMetrics: 1000,
      metricsCollector: (metrics) => {
        // Custom metrics collector
        console.log('Performance metric:', metrics);
      }
    }
  }
);

// Use the source normally - performance is automatically monitored
const users = await userSource.find();
const count = await userSource.count();

// Get performance metrics
const metrics = userSource.getPerformanceMetrics();
const summary = userSource.getPerformanceSummary();
const slowQueries = userSource.getSlowQueries();

console.log('Performance Summary:', summary);
console.log('Slow Queries:', slowQueries);

Database-Specific Features

PostgreSQL Features

// JSON operations
const jsonQuery = new Where()
  .valueOf('profile').jsonExtract('$.preferences.theme').isEq('dark');

// Full-text search
const searchQuery = new Where()
  .valueOf('content').fullTextSearch('search term');

// Array operations
const arrayQuery = new Where()
  .valueOf('tags').arrayContains(['important', 'urgent']);

MySQL Features

// MySQL-specific functions
const mysqlQuery = new Where()
  .valueOf('name').isLike('John%')
  .and.valueOf('created_at').isGte('2023-01-01');

// JSON operations (MySQL 5.7+)
const jsonQuery = new Where()
  .valueOf('profile').jsonExtract('$.preferences.theme').isEq('dark');

SQLite Features

// SQLite-specific functions
const sqliteQuery = new Where()
  .valueOf('name').isLike('John%')
  .and.valueOf('created_at').isGte('2023-01-01');

// JSON operations (SQLite 3.38+)
const jsonQuery = new Where()
  .valueOf('profile').jsonExtract('$.preferences.theme').isEq('dark');

Extended Operators

The package supports advanced operators for specific database features:

JSON Operations

// Extract values from JSON fields
const jsonQuery = {
  profile: { 
    jsonExtract: { 
      path: '$.preferences.theme', 
      value: 'dark' 
    } 
  }
};

// PostgreSQL: profile->>'$.preferences.theme' = ?
// MySQL: JSON_EXTRACT(profile, '$.preferences.theme') = ?
// SQLite: json_extract(profile, '$.preferences.theme') = ?

Full-Text Search

// Full-text search across different databases
const searchQuery = {
  content: { 
    fullTextSearch: { 
      value: 'search term' 
    } 
  }
};

// PostgreSQL: to_tsvector('english', content) @@ plainto_tsquery('english', ?)
// MySQL: MATCH(content) AGAINST(? IN BOOLEAN MODE)
// SQLite: content MATCH ?

Array Operations

// Check if array contains specific values
const arrayQuery = {
  tags: { 
    arrayContains: { 
      values: ['important', 'urgent'] 
    } 
  }
};

// PostgreSQL: tags @> ?
// MySQL: JSON_CONTAINS(tags, ?)
// SQLite: json_extract(tags, '$') = ?

Text Search

// Simple text search with wildcards
const textQuery = {
  name: { 
    textSearch: { 
      value: 'John' 
    } 
  }
};

// All databases: name LIKE '%John%'

API Reference

Core Classes

SoapSQL: Main SQL driver class for managing connections and sessions
SqlSource: SQL data source implementation implementing Source interface
SqlQueryFactory: SQL query factory for building complex queries
SqlWhereParser: Parser for converting Where conditions to SQL WHERE clauses
SqlFieldResolver: Field mapping and transformation between entities and database records
SqlDatabaseSession: SQL session implementation for transactions
SqlSessionManager: Session management for SQL connections
SqlMigrationManager: Migration system for database schema changes
SqlPerformanceMonitor: Performance monitoring implementation

Configuration Classes

SqlConfig: SQL configuration with connection pool settings
MigrationConfig: Configuration for database migrations
PerformanceConfig: Configuration for performance monitoring

Interfaces

MigrationDefinition: Interface for database migrations
MigrationStatus: Status information for migrations
MigrationResult: Result of migration operations
SqlPerformanceMetrics: Performance metrics for operations
SqlPerformanceSummary: Summary statistics for performance monitoring

Extended Operators

jsonExtract: Extract values from JSON fields using JSON path expressions
jsonPath: PostgreSQL-specific JSON path operations
fullTextSearch: Database-specific full-text search capabilities
arrayContains: Check if arrays contain specific values
textSearch: Simple text search with wildcards

Error Handling

The package provides comprehensive error handling with specific SQL error types:

import { SqlError, DuplicateKeyError, ValidationError } from '@soapjs/soap-node-sql';

try {
  const result = await userRepo.add([user]);
  if (result.isSuccess()) {
    console.log('User added successfully');
  } else {
    const error = result.failure.error;
    
    if (error instanceof DuplicateKeyError) {
      console.error('Duplicate key error:', error.message);
    } else if (error instanceof ValidationError) {
      console.error('Validation error:', error.message);
    } else if (error instanceof SqlError) {
      console.error('SQL error:', error.message);
    }
  }
} catch (error) {
  console.error('Unexpected error:', error);
}

Testing

Unit Tests

Run unit tests (mocked SQL):

npm run test:unit

Integration Tests

Integration tests use Testcontainers to automatically start and manage database containers for testing.

Prerequisites

Docker: Ensure Docker is running on your system
Testcontainers: Automatically manages database containers
No manual setup required: Everything is handled automatically

Running Integration Tests

# Run only integration tests (requires Docker)
npm run test:integration

# Run all tests (unit + integration)
npm test

Integration Test Coverage

Integration tests cover:

SqlSource Operations: CRUD, queries, aggregations, transactions
Performance Monitoring: Metrics collection, slow query detection
Migration System: Migration execution, rollback, status tracking
Error Handling: Duplicate keys, validation errors
Database-Specific Features: PostgreSQL JSON, MySQL functions, SQLite features

Performance Optimization

Indexing Strategy

// Create indexes for optimal performance
const userSource = new SqlSource(soapSql, 'users', {
  indexes: [
    // Primary index on email (unique)
    { name: 'idx_users_email', columns: ['email'], unique: true },
    
    // Compound index for queries
    { name: 'idx_users_status_created', columns: ['status', 'created_at'] },
    
    // Partial index for active users
    { name: 'idx_users_active', columns: ['email'], where: 'status = "active"' }
  ]
});

Query Optimization

// Use projection to limit returned fields
const users = await userRepo.find({
  projection: { name: 1, email: 1, id: 0 }
});

// Use limit and offset for pagination
const paginatedUsers = await userRepo.find({
  limit: 10,
  offset: 20
});

// Use sort for ordered results
const sortedUsers = await userRepo.find({
  sort: { createdAt: 'desc' }
});

Security Best Practices

Authentication and Authorization

// Use environment variables for sensitive data
const config = new SqlConfig({
  type: 'postgresql',
  host: process.env.DB_HOST || 'localhost',
  port: parseInt(process.env.DB_PORT || '5432'),
  database: process.env.DB_NAME || 'myapp',
  username: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  ssl: process.env.DB_SSL === 'true'
});

SQL Injection Prevention

The package automatically handles SQL injection prevention through parameterized queries:

// Safe - uses parameterized queries
const where = new Where().valueOf('email').isEq('john@example.com');
const users = await userRepo.find({ where });

// Safe - automatic escaping
const updateResult = await userRepo.update({
  where: new Where().valueOf('id').isEq('user-123'),
  updates: [{ name: 'John Doe' }]
});

Troubleshooting

Common Issues

Connection Issues

// Check connection status
const isConnected = await soapSql.isConnected();
console.log('Connected:', isConnected);

Performance Issues

// Enable performance monitoring
const source = new SqlSource(soapSql, 'users', {
  performanceMonitoring: {
    enabled: true,
    slowQueryThreshold: 1000
  }
});

// Check slow queries
const slowQueries = source.getSlowQueries();
console.log('Slow queries:', slowQueries);

Migration Issues

// Check migration status
const status = await migrationManager.getMigrationStatus();
console.log('Migration status:', status);

// Rollback if needed
const rollbackResult = await migrationManager.rollback();
console.log('Rollback result:', rollbackResult);

Migration Guide

From Previous Versions

Version 0.2.x to 0.3.x

Performance Monitoring: New optional feature - no breaking changes
Connection Pool: Enhanced configuration - backward compatible
Migrations: New feature - no impact on existing code

Breaking Changes

None in version 0.3.x

Contributing

We welcome contributions! Please follow these steps:

Fork the repository
Create a feature branch: git checkout -b feature/amazing-feature
Make your changes:
- Follow TypeScript best practices
- Add comprehensive tests
- Update documentation
- Ensure all tests pass
Commit your changes: git commit -m 'Add amazing feature'
Push to the branch: git push origin feature/amazing-feature
Submit a pull request

Development Setup

# Clone the repository
git clone https://github.com/soapjs/soap-node-sql.git
cd soap-node-sql

# Install dependencies
npm install

# Run tests
npm run test:unit

# Build the project
npm run build

# Check code coverage
npm run test:unit -- --coverage

License

This project is licensed under the MIT License - see the LICENSE file for details.

Support

Documentation: https://docs.soapjs.com
Issues: GitHub Issues
Discussions: GitHub Discussions
Email: radoslaw.kamysz@gmail.com

Expected Package Versions

Core Dependencies

{
  "@soapjs/soap": "^0.3.0",
  "typescript": "^5.0.0"
}

Database Drivers

{
  "pg": "^8.11.0",
  "mysql2": "^3.6.0", 
  "better-sqlite3": "^9.2.0"
}

Development Dependencies

{
  "@types/pg": "^8.10.0",
  "@types/better-sqlite3": "^7.6.0",
  "jest": "^29.0.0",
  "testcontainers": "^10.0.0"
}

Version Compatibility Matrix

Package Version	@soapjs/soap	pg	mysql2	better-sqlite3	Node.js
0.3.x	^0.3.0	^8.11.0	^3.6.0	^9.2.0	>=16.0.0
0.2.x	^0.2.0	^8.10.0	^3.5.0	^9.1.0	>=14.0.0
0.1.x	^0.1.0	^8.9.0	^3.4.0	^9.0.0	>=12.0.0

Name		Name	Last commit message	Last commit date
Latest commit History 6 Commits
src		src
.eslintrc.js		.eslintrc.js
.gitignore		.gitignore
LICENSE		LICENSE
README.md		README.md
jest.config.integration.json		jest.config.integration.json
jest.config.unit.json		jest.config.unit.json
package-lock.json		package-lock.json
package.json		package.json
test.env.example		test.env.example
tsconfig.build.json		tsconfig.build.json
tsconfig.json		tsconfig.json

License

soapjs/soap-node-sql

Folders and files

Latest commit

History

Repository files navigation

@soapjs/soap-node-sql

Features

Supported Databases

PostgreSQL

MySQL

SQLite

Installation

Quick Start

1. Import the necessary classes:

2. Set up your database configuration:

PostgreSQL Configuration

MySQL Configuration

SQLite Configuration

3. Create a new SoapSQL driver instance:

4. Define your entities and models:

5. Create SQL source and use with SOAPJS repositories:

6. Using repositories with SOAPJS abstractions:

Basic CRUD Operations

Advanced Queries

7. Transaction Support

Using SOAPJS Transaction System

8. Database Migrations

Creating Migrations

Running Migrations

Performance Monitoring

Basic Usage

Database-Specific Features

PostgreSQL Features

MySQL Features

SQLite Features

Extended Operators

JSON Operations

Full-Text Search

Array Operations

Text Search

API Reference

Core Classes

Configuration Classes

Interfaces

Extended Operators

Error Handling

Testing

Unit Tests

Integration Tests

Prerequisites

Running Integration Tests

Integration Test Coverage

Performance Optimization

Indexing Strategy

Query Optimization

Security Best Practices

Authentication and Authorization

SQL Injection Prevention

Troubleshooting

Common Issues

Migration Guide

From Previous Versions

Version 0.2.x to 0.3.x

Breaking Changes

Contributing

Development Setup

License

Support

Expected Package Versions

Core Dependencies

Database Drivers

Development Dependencies

Version Compatibility Matrix

About

Resources

License

Uh oh!

Stars

Watchers

3. Create a new `SoapSQL` driver instance:

Packages