@baqx/sql2mongo NPM

SQL2Mongo

Write SQL-like syntax to query MongoDB. This package allows you to use familiar SQL syntax while working with MongoDB Atlas or any MongoDB instance.

Features

🚀 Write MongoDB queries using familiar SQL syntax
🔄 Full CRUD operations support (SELECT, INSERT, UPDATE, DELETE)
🔍 Advanced querying capabilities:
- Complex WHERE conditions (AND, OR, IN, NOT IN, LIKE, etc.)
- JOINs (INNER, LEFT, RIGHT)
- GROUP BY with aggregations (COUNT, SUM, AVG, MIN, MAX)
- HAVING clause for filtered aggregations
- ORDER BY with multi-column support
- LIMIT and OFFSET pagination
🛡️ Schema validation and type checking
🔗 Foreign key constraint support
📦 Automatic index management
⚡ Works with MongoDB Atlas
🔒 Transaction support for multi-query operations

Installation

npm install @baqx/sql2mongo

Usage

import { SQL2Mongo } from "@baqx/sql2mongo";

// Initialize with MongoDB Atlas
const sql2mongo = new SQL2Mongo({
  uri: "mongodb+srv://username:password@cluster0.mongodb.net/",
  dbName: "mydb",
  options: {
    retryWrites: true,
    w: "majority",
  },
});

// Connect to the database
await sql2mongo.connect();

// Execute queries
try {
  // Simple SELECT
  const users = await sql2mongo.query("SELECT * FROM users WHERE age >= 21");

  // Complex SELECT with JOIN and GROUP BY
  const orderStats = await sql2mongo.query(`
    SELECT 
      c.category_name,
      COUNT(*) as order_count,
      SUM(o.total_amount) as total_sales,
      AVG(o.total_amount) as avg_order_value
    FROM orders o
    JOIN categories c ON o.category_id = c.id
    WHERE o.order_date >= '2025-01-01'
    GROUP BY c.category_name
    HAVING total_sales > 1000
    ORDER BY total_sales DESC
    LIMIT 10
  `);

  // INSERT with multiple values
  const insertResult = await sql2mongo.query(`
    INSERT INTO products (name, price, category_id) 
    VALUES 
      ('Product 1', 29.99, 1),
      ('Product 2', 39.99, 2)
  `);

  // UPDATE with complex conditions
  const updateResult = await sql2mongo.query(`
    UPDATE users 
    SET 
      status = 'premium',
      points = points + 100,
      last_updated = CURRENT_TIMESTAMP
    WHERE 
      subscription_type IN ('yearly', 'lifetime')
      AND (points >= 1000 OR referral_count > 5)
  `);

  // DELETE with subquery
  const deleteResult = await sql2mongo.query(`
    DELETE FROM inactive_users 
    WHERE last_login < '2024-01-01' 
    AND user_id NOT IN (SELECT user_id FROM premium_subscribers)
  `);

  // Transaction example
  const results = await sql2mongo.transaction([
    "UPDATE accounts SET balance = balance - 100 WHERE id = 1",
    "UPDATE accounts SET balance = balance + 100 WHERE id = 2",
    "INSERT INTO transactions (from_id, to_id, amount) VALUES (1, 2, 100)",
  ]);
} finally {
  // Always close the connection when done
  await sql2mongo.close();
}

// Run SQL queries
try {
  // SELECT query
  const users = await sql2mongo.query(
    "SELECT name, age FROM users WHERE age > 20 ORDER BY age DESC LIMIT 5"
  );
  console.log(users);

  // INSERT query
  const insert = await sql2mongo.query(
    'INSERT INTO users (name, age) VALUES ("John", 25)'
  );
  console.log(insert);

  // CREATE TABLE query
  const create = await sql2mongo.query(`
    CREATE TABLE products (
      id INT PRIMARY KEY,
      name VARCHAR(255),
      category_id INT,
      FOREIGN KEY (category_id) REFERENCES categories(id)
    )
  `);
  console.log(create);
} finally {
  // Always close the connection when done
  await sql2mongo.close();
}

Features

Write MongoDB queries using familiar SQL syntax
Supports SELECT queries with:
- WHERE clauses
- ORDER BY
- LIMIT
Supports INSERT queries with multiple values
Supports CREATE TABLE with:
- Primary keys
- Foreign keys
- Unique constraints
Automatically creates appropriate MongoDB indexes
Works with MongoDB Atlas
Full type checking and schema validation

Configuration

The SQL2Mongo constructor accepts the following options:

{
  uri: string,       // MongoDB connection string (required)
  dbName: string,    // Database name (default: "sql2mongo_test")
  options: object    // MongoDB client options (optional)
}

Supported SQL Syntax

SELECT

SELECT [DISTINCT] column1 [AS alias1], column2 [AS alias2],
       aggregate_function(column3), ...
FROM table1
[JOIN table2 ON condition]
[LEFT JOIN table3 ON condition]
[WHERE conditions]
[GROUP BY columns]
[HAVING aggregate_conditions]
[ORDER BY column1 [ASC|DESC], column2 [ASC|DESC]]
[LIMIT count [OFFSET offset]]

Supported features:

All basic comparison operators: =, !=, >, <, >=, <=
Logical operators: AND, OR, NOT
Pattern matching: LIKE, NOT LIKE (with % wildcards)
Range testing: BETWEEN, NOT BETWEEN
List testing: IN, NOT IN
Null testing: IS NULL, IS NOT NULL
Aggregation functions: COUNT, SUM, AVG, MIN, MAX
JOINs: INNER JOIN, LEFT JOIN, RIGHT JOIN
Subqueries in WHERE clause
Complex GROUP BY with HAVING clause

INSERT

INSERT INTO collection
  (column1, column2, ...)
VALUES
  (value1, value2, ...),
  (value3, value4, ...),
  ...

Features:

Single or multiple row insertion
Type validation against schema
Foreign key constraint checking
Auto-incrementing IDs
Default value handling

UPDATE

UPDATE collection
SET
  column1 = value1,
  column2 = column2 + value2,
  column3 = NULL
WHERE conditions

Features:

Single field updates
Arithmetic operations (+, -, *, /)
Multiple field updates
Complex WHERE conditions
NULL value handling
Foreign key constraint validation

DELETE

DELETE FROM collection
WHERE conditions

Features:

Single or bulk deletion
Complex WHERE conditions
Foreign key constraint checking
Subquery support in WHERE clause

CREATE TABLE

CREATE TABLE collection (
  column1 TYPE [PRIMARY KEY] [AUTO_INCREMENT],
  column2 TYPE [NOT NULL] [UNIQUE] [DEFAULT value],
  column3 TYPE [REFERENCES other_collection(column)],
  [INDEX index_name (column1, column2)],
  [UNIQUE INDEX unique_index_name (column3)]
)

Supported features:

Data types: INT, VARCHAR(n), TEXT, BOOLEAN, TIMESTAMP, DECIMAL
Constraints: PRIMARY KEY, UNIQUE, NOT NULL, DEFAULT
Foreign key constraints with referential integrity
Automatic index creation
Compound indexes
Schema validation

Advanced Features

Transactions

await sql2mongo.transaction(["query1", "query2", "query3"]);

Schema Management

// Get collection schema
const schema = await sql2mongo.getSchema("collection_name");

// List all collections
const collections = await sql2mongo.listCollections();

Error Handling

try {
  await sql2mongo.query("...");
} catch (error) {
  if (error.message.includes("syntax error")) {
    // Handle SQL syntax errors
  } else if (error.message.includes("foreign key")) {
    // Handle constraint violations
  }
}

License

MIT

mongodb sql parser query orm javascript

mongodb node-sql-parser

5 months ago

5 months ago

5 months ago

5 months ago