Advanced Usage
Master advanced MatsushibaDB techniques for complex applications, performance optimization, and enterprise-grade implementations.Advanced Query Patterns
Complex Joins and Subqueries
Copy
-- Advanced join patterns
SELECT
u.id,
u.username,
u.email,
COUNT(p.id) as post_count,
COUNT(c.id) as comment_count,
MAX(p.created_at) as last_post_date
FROM users u
LEFT JOIN posts p ON u.id = p.author_id AND p.published = 1
LEFT JOIN comments c ON u.id = c.user_id
WHERE u.status = 'active'
GROUP BY u.id, u.username, u.email
HAVING COUNT(p.id) > 0
ORDER BY post_count DESC, last_post_date DESC;
-- Correlated subqueries
SELECT
p.title,
p.created_at,
(SELECT COUNT(*) FROM comments WHERE post_id = p.id) as comment_count,
(SELECT username FROM users WHERE id = p.author_id) as author_name
FROM posts p
WHERE p.published = 1
AND p.created_at > (
SELECT AVG(created_at)
FROM posts
WHERE author_id = p.author_id
);
-- Window functions
SELECT
u.username,
p.title,
p.created_at,
ROW_NUMBER() OVER (PARTITION BY p.author_id ORDER BY p.created_at DESC) as post_rank,
RANK() OVER (ORDER BY p.created_at DESC) as global_rank,
LAG(p.title, 1) OVER (PARTITION BY p.author_id ORDER BY p.created_at) as previous_post
FROM posts p
JOIN users u ON p.author_id = u.id
WHERE p.published = 1;
Advanced Data Manipulation
Copy
// Advanced data operations
class AdvancedDataOperations {
constructor(db) {
this.db = db;
}
// Bulk upsert operation
async bulkUpsert(tableName, data, conflictColumns) {
try {
this.db.run('BEGIN TRANSACTION');
const columns = Object.keys(data[0]);
const placeholders = columns.map(() => '?').join(', ');
const updateClause = columns
.filter(col => !conflictColumns.includes(col))
.map(col => `${col} = excluded.${col}`)
.join(', ');
const sql = `
INSERT INTO ${tableName} (${columns.join(', ')})
VALUES (${placeholders})
ON CONFLICT (${conflictColumns.join(', ')})
DO UPDATE SET ${updateClause}
`;
const stmt = this.db.prepare(sql);
for (const row of data) {
stmt.run(Object.values(row));
}
stmt.finalize();
this.db.run('COMMIT');
return { success: true, count: data.length };
} catch (error) {
this.db.run('ROLLBACK');
throw error;
}
}
// Hierarchical data operations
async getHierarchicalData(tableName, parentColumn = 'parent_id', idColumn = 'id') {
try {
// Get all data
const allData = this.db.all(`SELECT * FROM ${tableName} ORDER BY ${idColumn}`);
// Build hierarchy
const hierarchy = this.buildHierarchy(allData, parentColumn, idColumn);
return hierarchy;
} catch (error) {
throw error;
}
}
buildHierarchy(data, parentColumn, idColumn, parentId = null, level = 0) {
const result = [];
for (const item of data) {
if (item[parentColumn] === parentId) {
const children = this.buildHierarchy(data, parentColumn, idColumn, item[idColumn], level + 1);
result.push({
...item,
level: level,
children: children
});
}
}
return result;
}
// Data aggregation with multiple levels
async getAggregatedData(tableName, groupColumns, aggregateFunctions) {
try {
const groupClause = groupColumns.join(', ');
const aggregateClause = aggregateFunctions.map(func =>
`${func.function}(${func.column}) as ${func.alias}`
).join(', ');
const sql = `
SELECT
${groupClause},
${aggregateClause}
FROM ${tableName}
GROUP BY ${groupClause}
ORDER BY ${groupClause}
`;
return this.db.all(sql);
} catch (error) {
throw error;
}
}
}
// Usage
const advancedOps = new AdvancedDataOperations(db);
// Bulk upsert
const userData = [
{ id: 1, username: 'john', email: '[email protected]', status: 'active' },
{ id: 2, username: 'jane', email: '[email protected]', status: 'active' }
];
await advancedOps.bulkUpsert('users', userData, ['id']);
// Hierarchical data
const categories = await advancedOps.getHierarchicalData('categories');
// Aggregated data
const stats = await advancedOps.getAggregatedData('posts', ['author_id', 'published'], [
{ function: 'COUNT', column: 'id', alias: 'post_count' },
{ function: 'AVG', column: 'views', alias: 'avg_views' },
{ function: 'MAX', column: 'created_at', alias: 'last_post' }
]);
Performance Optimization
Query Optimization Techniques
Copy
// Advanced query optimizer
class AdvancedQueryOptimizer {
constructor(db) {
this.db = db;
this.queryCache = new Map();
this.performanceMetrics = new Map();
}
// Optimized pagination with cursor-based approach
async getPaginatedData(tableName, cursor, limit, orderBy = 'id') {
try {
const whereClause = cursor ? `WHERE ${orderBy} < ?` : '';
const params = cursor ? [cursor, limit] : [limit];
const sql = `
SELECT * FROM ${tableName}
${whereClause}
ORDER BY ${orderBy} DESC
LIMIT ?
`;
const results = this.db.all(sql, params);
// Get next cursor
const nextCursor = results.length > 0 ? results[results.length - 1][orderBy] : null;
return {
data: results,
nextCursor: nextCursor,
hasMore: results.length === limit
};
} catch (error) {
throw error;
}
}
// Batch processing for large datasets
async processLargeDataset(sql, params, batchSize = 1000, processor) {
try {
let offset = 0;
let totalProcessed = 0;
while (true) {
const batchSql = `${sql} LIMIT ? OFFSET ?`;
const batchParams = [...params, batchSize, offset];
const batch = this.db.all(batchSql, batchParams);
if (batch.length === 0) {
break;
}
await processor(batch);
totalProcessed += batch.length;
offset += batchSize;
// Force garbage collection if available
if (global.gc) {
global.gc();
}
}
return { totalProcessed };
} catch (error) {
throw error;
}
}
// Query result caching
async getCachedQuery(sql, params, ttl = 300000) {
const cacheKey = `${sql}:${JSON.stringify(params)}`;
const cached = this.queryCache.get(cacheKey);
if (cached && Date.now() - cached.timestamp < ttl) {
return cached.data;
}
const result = this.db.all(sql, params);
this.queryCache.set(cacheKey, {
data: result,
timestamp: Date.now()
});
return result;
}
// Performance monitoring
monitorQuery(sql, params, operation) {
const startTime = Date.now();
try {
const result = operation();
const executionTime = Date.now() - startTime;
// Store performance metrics
const key = `${sql}:${JSON.stringify(params)}`;
const metrics = this.performanceMetrics.get(key) || {
count: 0,
totalTime: 0,
averageTime: 0,
minTime: Infinity,
maxTime: 0
};
metrics.count++;
metrics.totalTime += executionTime;
metrics.averageTime = metrics.totalTime / metrics.count;
metrics.minTime = Math.min(metrics.minTime, executionTime);
metrics.maxTime = Math.max(metrics.maxTime, executionTime);
this.performanceMetrics.set(key, metrics);
return result;
} catch (error) {
throw error;
}
}
// Get performance report
getPerformanceReport() {
const report = [];
for (const [query, metrics] of this.performanceMetrics) {
report.push({
query: query,
...metrics,
efficiency: metrics.averageTime < 100 ? 'good' : metrics.averageTime < 1000 ? 'fair' : 'poor'
});
}
return report.sort((a, b) => b.averageTime - a.averageTime);
}
}
// Usage
const optimizer = new AdvancedQueryOptimizer(db);
// Cursor-based pagination
const page1 = await optimizer.getPaginatedData('users', null, 10, 'created_at');
const page2 = await optimizer.getPaginatedData('users', page1.nextCursor, 10, 'created_at');
// Batch processing
await optimizer.processLargeDataset(
'SELECT * FROM large_table WHERE status = ?',
['active'],
1000,
async (batch) => {
console.log(`Processing ${batch.length} records`);
// Process batch
}
);
// Cached queries
const users = await optimizer.getCachedQuery('SELECT * FROM users WHERE active = ?', [1]);
Advanced Security
Row-Level Security
Copy
// Row-level security implementation
class RowLevelSecurity {
constructor(db) {
this.db = db;
this.securityPolicies = new Map();
}
// Define security policy
definePolicy(tableName, policyName, policyFunction) {
if (!this.securityPolicies.has(tableName)) {
this.securityPolicies.set(tableName, new Map());
}
this.securityPolicies.get(tableName).set(policyName, policyFunction);
}
// Apply security filter
applySecurityFilter(tableName, userId, baseQuery) {
const policies = this.securityPolicies.get(tableName);
if (!policies) {
return baseQuery;
}
let securityConditions = [];
for (const [policyName, policyFunction] of policies) {
const condition = policyFunction(userId);
if (condition) {
securityConditions.push(condition);
}
}
if (securityConditions.length === 0) {
return baseQuery;
}
const whereClause = securityConditions.join(' AND ');
return `${baseQuery} WHERE ${whereClause}`;
}
// Secure query execution
async secureQuery(sql, params, userId) {
try {
// Extract table name from query (simplified)
const tableMatch = sql.match(/FROM\s+(\w+)/i);
if (tableMatch) {
const tableName = tableMatch[1];
const securedSql = this.applySecurityFilter(tableName, userId, sql);
return this.db.all(securedSql, params);
}
return this.db.all(sql, params);
} catch (error) {
throw error;
}
}
}
// Usage
const rls = new RowLevelSecurity(db);
// Define policies
rls.definePolicy('posts', 'user_posts', (userId) => {
return `author_id = ${userId} OR published = 1`;
});
rls.definePolicy('users', 'user_profile', (userId) => {
return `id = ${userId} OR status = 'public'`;
});
// Secure queries
const userPosts = await rls.secureQuery(
'SELECT * FROM posts ORDER BY created_at DESC',
[],
userId
);
Best Practices
Use Advanced Query Patterns
Implement complex joins, subqueries, and window functions for sophisticated data analysis.
Optimize for Performance
Use cursor-based pagination, batch processing, and query caching for large datasets.
Advanced usage patterns require careful planning and testing. Always profile your queries, implement proper error handling, and consider the long-term maintainability of your solutions.