@megaorm/builder v1.0.0
MegaORM Builder
This package provides a flexible query builder for MegaORM, designed to simplify the process of constructing and executing SQL queries. With a clean, simple API.
Table of Contents
- Installation
- How to Use MegaBuilder
- SELECT Queries
- Building Queries
- Logging and Debugging
- Where Clause
- Condition Builder Methods
- Joining Tables
- Grouping and Ordering
- Distinct, Limit and Offset
- Union and UnionAll
- Pagination and Count
- INSERT Queries
- UPDATE Queries
- DELETE Queries
- Raw Queries
- Setter and Getter
- Query Classes
Installation
To install this package, run the following command:
npm install @megaorm/builder
How to Use MegaBuilder
- Import
MegaBuilder
andMegaConfig
const { MegaBuilder } = require('@megaorm/builder');
const { MegaConfig } = require('@megaorm/cli');
You should be familiar with @megaorm/cli, @megaorm/pool, and @megaorm/cluster.
- Create a New Builder Instance
const app = async () => {
// Load your configuration
const config = await MegaConfig.load();
// Request a connection
const connection = await config.cluster.request(config.default);
// Create a builder instance with the connection
const builder = new MegaBuilder(connection);
// Fetch all users
const users = await builder.select().from('users').exec();
console.log(users);
// Release the connection back when done
connection.release();
// Ensure not to reuse the connection after release...
};
app(); // Execute the app
You must provide a
MegaPoolConnection
instance for executing queries.MegaConnection
instances are not supported. Learn the difference here.
SELECT Queries
The select()
method returns a Select
instance to construct and execute your SELECT
queries:
const { Select } = require('@megaorm/builder');
console.log(builder.select() instanceof Select); // true
Building Queries
The build()
method is available on all query instances (Select
, Update
, Delete
, Insert
) to construct your SQL string.
builder.insert().build();
// Throws an error because you haven't defined the table.
To select data from a table, use the from(table)
method:
builder.select().from('users').build();
// Returns: SELECT * FROM users;
By default, all columns (*
) are selected. Use the col
method to specify columns:
builder.select().col('id', 'email').from('users').build();
// Returns: SELECT id, email FROM users;
You can also use aliases:
builder.select().col('email AS mail').from('users').build();
// Returns: SELECT email AS mail FROM users;
Logging and Debugging
You can log and retrieve your query and values using the log
and get
properties:
const query = builder
.select()
.from('profiles')
.where((col) => col('city').equal('Tokyo'));
query.log.query(); // Outputs: SELECT * FROM profiles WHERE city = ?;
query.log.values(); // Outputs: ['Tokyo'];
query.get.query(); // Returns: SELECT * FROM profiles WHERE city = ?;
query.get.values(); // Returns: ['Tokyo'];
These features are available for
Select
,Update
,Delete
, andInsert
queries.
Where Clause
The where(condition)
method lets you specify conditions for your query:
builder
.select()
.from('users')
.where((col) => col('id').equal(1))
.build();
// Returns: SELECT * FROM users WHERE id = ?;
The where(condition)
method uses a condition builder function. This function receives:
col
: A column selector function to specify the column and build conditions.con
: A condition builder instance.
Condition Builder Methods
equal(values)
: Compares the column with a specified value for equality.
builder
.select()
.from('profiles')
.where((col) => col('age').equal(30))
.build();
// Returns: SELECT FROM profiles WHERE age = ?;
lessThan(value)
: Compares the column with a specified value to check if it's less than the value.
builder
.select()
.from('products')
.where((col) => col('price').lessThan(100))
.build();
// Returns: SELECT FROM products WHERE price < ?;
lessThanOrEqual(value)
: Compares the column with a specified value to check if it's less than or equal to the value.
builder
.select()
.from('orders')
.where((col) => col('order_date').lessThanOrEqual('2023-01-01'))
.build();
// Returns: SELECT FROM orders WHERE order_date <= ?;
greaterThan(value)
: Compares the column with a specified value to check if it's greater than the value.
builder
.select()
.from('employees')
.where((col) => col('salary').greaterThan(50000))
.build();
// Returns: SELECT FROM employees WHERE salary > ?;
greaterThanOrEqual(value)
: Compares the column with a specified value to check if it's greater than or equal to the value.
builder
.select()
.from('profiles')
.where((col) => col('age').greaterThanOrEqual(18))
.build();
// Returns: SELECT FROM profiles WHERE age >= ?;
between(start, end)
: Compares the column with a specified range to check if the value is between the start
and end
values.
builder
.select()
.from('users')
.where((col) => col('created_at').between('2023-01-01', '2023-12-31'))
.build();
// Returns: SELECT FROM users WHERE created_at BETWEEN ? AND ?;
in(values)
: Checks if the column's value is included in a list of specified values.
builder
.select()
.from('profiles')
.where((col) => col('status').in('active', 'pending'))
.build();
// Returns: SELECT FROM profiles WHERE status IN (?, ?);
inSubquery(subquery)
: Checks if the column's value is in the result set of a subquery.
// Select customer ids from orders
const subquery = (select) => select.col('customer_id').from('orders');
// Select customers with at least one order
builder
.select()
.from('customers')
.where((col) => col('id').inSubquery(subquery))
.build();
// Returns:
// SELECT FROM customers
// WHERE id IN (SELECT customer_id FROM orders);
like(value)
: Compares the column to a pattern using the LIKE
operator.
builder
.select()
.from('profiles')
.where((col) => col('name').like('%john%'))
.build();
// Returns: SELECT FROM profiles WHERE name LIKE ?;
%
means 0 or more characters.
isNull()
: Checks if the column's value is NULL
.
builder
.select()
.from('profiles')
.where((col) => col('gender').isNull())
.build();
// Returns: SELECT FROM profiles WHERE gender IS NULL;
not()
: Negates the current condition.
builder
.select()
.from('profiles')
.where((col) => col('name').not().like('%john%'))
.build();
// Returns: SELECT FROM profiles WHERE name NOT LIKE ?;
not()
must be called before the condition builder method
builder
.select()
.from('profiles')
.where((col) => col('name').like('%john%').not()) // No affect
.build();
// Returns: SELECT FROM profiles WHERE name LIKE ?;
You can use
not()
with all condition builder methods
and()
: Combines conditions with a logical AND.
builder
.select()
.from('profiles')
.where((col) => col('age').equal(20))
.and()
.where((col) => col('city').equal('NY'))
.build();
// Returns: SELECT * FROM profiles WHERE age = ? AND city = ?;
You can chain and()
directly.
builder
.select()
.from('profiles')
.where((col) => col('age').equal(20).and().col('city').equal('NY'))
.build();
// Returns: SELECT * FROM profiles WHERE age = ? AND city = ?;
or()
: Combines conditions with a logical OR.
builder
.select()
.from('profiles')
.where((col) => col('status').equal('inactive'))
.or()
.where((col) => col('status').equal('banned'))
.build();
// Returns: SELECT * FROM profiles WHERE status = ? OR status = ?;
You can chain or()
directly.
builder
.select()
.from('profiles')
.where((col) =>
col('status').equal('inactive').or().col('status').equal('banned')
)
.build();
// Returns: SELECT * FROM profiles WHERE status = ? OR status = ?;
If the column is the same, call col
only once.
builder
.select()
.from('profiles')
.where((col) => col('status').equal('inactive').or().equal('banned'))
.build();
// Returns: SELECT * FROM profiles WHERE status = ? OR status = ?;
open()
and close()
: Adds explicit parentheses to group conditions.
builder
.select()
.from('profiles')
.where((col) => col('city').equal('NY'))
.and()
.open() // Start grouping with parentheses
.where((col) => col('status').equal('inactive'))
.or()
.where((col) => col('status').equal('banned'))
.close() // End grouping with parentheses
.build();
// Returns:
// SELECT * FROM profiles
// WHERE city = ? AND (status = ? OR status = ?);
Nested parentheses are supported as well.
builder
.select()
.from('profiles')
.where((col) => col('city').equal('NY'))
.and()
.open() // Start outer parentheses
.open() // Start inner parentheses
.where((col) => col('status').equal('inactive'))
.or()
.where((col) => col('status').equal('banned'))
.close() // Close inner parentheses
.close() // Close outer parentheses
.build();
// Returns:
// SELECT * FROM profiles
// WHERE city = ? AND ((status = ? OR status = ?));
paren()
: Toggles parentheses, simplifying their usage.
builder
.select()
.from('profiles')
.where((col) => col('city').equal('NY'))
.and()
.paren() // Opens parentheses (or closes if already open)
.where((col) => col('status').equal('inactive'))
.or()
.where((col) => col('status').equal('banned'))
.paren() // Closes parentheses (or opens if already closed)
.build();
// Returns:
// SELECT * FROM profiles
// WHERE city = ? AND (status = ? OR status = ?);
paren()
does not support nested parentheses. Useopen()
andclose()
for that.
raw(condition, values)
: Adds a raw SQL condition string to the condition stack.
builder
.select()
.from('profiles')
.where((col) => col('status').equal('active').and().raw('age = ?', 18))
.build();
// Returns: SELECT * FROM profiles WHERE status = ? AND age = ?;
build()
: Builds and returns the final condition string.
builder
.select()
.from('products')
.where((col) => {
const condition = col('price').between(20, 50).build();
console.log(condition); // Outputs: price BETWEEN ? AND ?
})
.build();
// Returns: SELECT FROM products WHERE proce BETWEEN ? AND ?;
The build methods ensures your query is valid.
builder
.select()
.from('products')
.where(() => {}) // Empty condition
.build();
// Throws: Invalid syntax: Condition cannot be empty.
builder
.select()
.from('products')
.where((col, con) => con.and()) // Invalid condition
.build();
// Throws: Invalid syntax: Condition cannot start with an operator.
exists(subquery)
: Checks if any rows exist based on the result of a subquery.
// Import ref
const { ref } = require('@megaorm/builder');
// Use `ref(column)` to ensure column references are not replaced with `?`
const subquery = (select) =>
select
.from('products')
.where((col) => col('products.supplier_id').equal(ref('suppliers.id')))
.and()
.where((col) => col('products.price').lessThan(20));
// Select suppliers with at least one product
builder
.select()
.from('suppliers')
.where((col, con) => con.exists(subquery));
// Returns:
// SELECT * FROM suppliers WHERE EXISTS (
// SELECT * FROM products
// WHERE products.supplier_id = suppliers.id AND products.price < ?
// );
// You can also use `raw(condition)` if you like
const subquery = (select) =>
select
.from('products')
.where((col, con) => con.raw('products.supplier_id = suppliers.id'))
.and()
.where((col) => col('products.price').lessThan(20));
builder
.select()
.from('suppliers')
.where((col, con) => con.exists(subquery));
// Returns:
// SELECT * FROM suppliers WHERE EXISTS (
// SELECT * FROM products
// WHERE products.supplier_id = suppliers.id AND products.price < ?
// );
all(operator, subquery)
: Checks if the column value satisfies a condition when compared to all results from a subquery.
// Import the Greater Than Operator
const { MORE } = require('@megaorm/builder');
// Build a subquery to fetch the order_amounts of the first customer
const subquery = (select) =>
select
.col('order_amount')
.from('orders')
.where((col) => col('customer_id').equal(1));
// Fetch all orders with a higher order_amount
// than all orders placed by the first customer
builder
.select()
.from('orders')
.where((col) => col('order_amount').all(MORE, subquery))
.build();
// Returns:
// SELECT * FROM orders
// WHERE order_amount > ALL (
// SELECT order_amount FROM orders WHERE customer_id = ?
// );
You can use these operators as well:
EQUAL
,NOT_EQUAL
,LESS
,LESS_OR_EQUAL
,MORE_OR_EQUAL
.
any(operator, subquery)
: Checks if the column value satisfies a condition when compared to any result from a subquery.
// Import the Greater Than Operator
const { MORE } = require('@megaorm/builder');
// Build a subquery to fetch the order_amounts of the first customer
const subquery = (select) =>
select
.col('order_amount')
.from('orders')
.where((col) => col('customer_id').equal(1));
// Fetch all orders with a higher order_amount
// than any orders placed by the first customer
builder
.select()
.from('orders')
.where((col) => col('order_amount').any(MORE, subquery))
.build();
// Returns:
// SELECT * FROM orders
// WHERE order_amount > ANY (
// SELECT order_amount FROM orders WHERE customer_id = ?
// );
You can use these operators as well:
EQUAL
,NOT_EQUAL
,LESS
,LESS_OR_EQUAL
,MORE_OR_EQUAL
.
inDate(date)
: Compares the date portion of a DATETIME
, TIMESTAMP
, or DATE
column with a specified date value.
builder
.select()
.from('products')
.where((col) => col('created_at').inDate('2023-05-01'))
.build();
// Returns: SELECT * FROM products WHERE DATE(created_at) = ?;
inTime(time)
: Compares the time portion of a DATETIME
, TIMESTAMP
, or TIME
column with a specified time value.
builder
.select()
.from('products')
.where((col) => col('created_at').inTime('15:30:00'))
.build();
// Returns: SELECT * FROM products WHERE TIME(created_at) = ?;
inYear(year)
: Compares the year portion of a DATETIME
, TIMESTAMP
, or DATE
column with a specified year value.
builder
.select()
.from('products')
.where((col) => col('created_at').inYear('2023'))
.build();
// Returns: SELECT * FROM products WHERE YEAR(created_at) = ?;
inMonth(month)
: Compares the month portion of a DATETIME
, TIMESTAMP
, or DATE
column with a specified month value.
builder
.select()
.from('products')
.where((col) => col('created_at').inMonth(3)) // 1 - 12
.build();
// Returns: SELECT * FROM products WHERE MONTH(created_at) = ?;
inDay(day)
: Compares the day portion of a DATETIME
, TIMESTAMP
, or DATE
column with a specified day value.
builder
.select()
.from('products')
.where((col) => col('created_at').inDay(20)) // 1 - 31
.build();
// Returns: SELECT * FROM products WHERE DAY(created_at) = ?;
inHour(hour)
: Compares the hour portion of a DATETIME
, TIMESTAMP
, or TIME
column with a specified hour value.
builder
.select()
.from('products')
.where((col) => col('created_at').inHour(16)) // 0 - 23
.build();
// Returns: SELECT * FROM products WHERE HOUR(created_at) = ?;
inMinute(minute)
: Compares the minute portion of a DATETIME
, TIMESTAMP
, or TIME
column with a specified minute value.
builder
.select()
.from('products')
.where((col) => col('created_at').inMinute(30)) // 0 - 59
.build();
// Returns: SELECT * FROM products WHERE MINUTE(created_at) = ?;
inSecond(second)
: Compares the second portion of a DATETIME
, TIMESTAMP
, or TIME
column with a specified second value.
builder
.select()
.from('products')
.where((col) => col('created_at').inSecond(45)) // 0 - 59
.build();
// Returns: SELECT * FROM products WHERE SECOND(created_at) = ?;
The extract function changes based on your driver.
Joining Tables
join(table, condition)
: Adds an INNER JOIN
clause to the query.
// Import ref
const { ref } = require('@megaorm/builder');
// Use `ref(column)` to ensure column references are not replaced with `?`
builder
.select()
.from('users')
.join('orders', (col) =>
col('users.id')
.equal(ref('orders.user_id'))
.and()
.col('orders.status')
.equal('pending')
)
.build();
// Returns:
// SELECT * FROM users
// INNER JOIN orders ON users.id = orders.user_id AND orders.status = ?;
You can join as many tables as you like.
builder
.select()
.col('users.*', 'profiles.name', 'orders.status')
.from('users')
.join('profiles', (col) => col('users.id').equal(ref('profiles.user_id')));
.join('orders', (col) => col('users.id').equal(ref('orders.user_id')))
.build();
// Returns:
// SELECT users.*, profiles.name, orders.status FROM users
// INNER JOIN profiles ON users.id = profiles.user_id
// INNER JOIN orders ON user.id = orders.user_id;
leftJoin(table, condition)
: Adds a LEFT JOIN
clause to the query.
// Import ref
const { ref } = require('@megaorm/builder');
// Fetch all users and their orders
builder
.select()
.from('users')
.leftJoin('orders', (col) => col('users.id').equal(ref('orders.user_id')))
.build();
// Returns:
// SELECT * FROM users LEFT JOIN orders ON users.id = orders.user_id;
rightJoin(table, condition)
: Adds a LEFT JOIN
clause to the query.
// Import ref
const { ref } = require('@megaorm/builder');
// Fetch all orders and their users
builder
.select()
.from('users')
.rightJoin('orders', (col) => col('users.id').equal(ref('orders.user_id')))
.build();
// Returns:
// SELECT * FROM users RIGHT JOIN orders ON users.id = orders.user_id;
Grouping and Ordering
groupBy(columns)
: Adds a GROUP BY
clause to the query.
// Group products by category and price
builder.select().from('products').groupBy('category', 'price').build();
// Returns: SELECT * FROM products GROUP BY category, price;
orderBy(column, type)
: Adds an ORDER BY
clause to the query.
// Import Order types
const { DESC, ASC } = require('@megaorm/builder');
// Order products by price in descending order
new Select(connection).from('products').orderBy('price', DESC).build();
// Returns: SELECT * FROM products ORDER BY price DESC;
// Order products by price in ascending order
new Select(connection).from('products').orderBy('price', ASC).build();
// Returns: SELECT * FROM products ORDER BY price ASC;
type
is optional, andASC
is the default order type.
having(condition)
: Adds a HAVING
clause to the query, typically used after a GROUP BY
. This allows you to filter the grouped results based on aggregate functions (like SUM()
, COUNT()
, etc.) which can't be filtered using the WHERE
clause.
// Get products with total sales greater than 1000
new Select(connection)
.from('sales') // The sales table stores product_id, price
.groupBy('product_id')
.having((col) => col('SUM(price)').greaterThan(1000))
.build();
// Returns:
// SELECT * FROM sales GROUP BY product_id HAVING SUM(price) > ?;
Use
having()
aftergroupBy()
when you need to apply conditions on aggregated results, such as filtering groups based on their summed, counted, or averaged values.
In summary:
groupBy()
organizes your data into groups.orderBy()
arranges those groups in a specific order.having()
applies filters to those grouped and ordered results, typically with aggregate functions.
Distinct, Limit and Offset
distinct()
: ensuring the results are unique. This eliminates duplicate rows from the result set.
// Select distinct product names
builder.select().distinct().col('name').from('products').build();
// Returns: SELECT DISTINCT name FROM products;
// Select distinct countries
builder.select().distinct().col('country').from('profiles').build();
// Returns: SELECT DISTINCT country FROM profiles;
limit(number)
: limits the number of rows to return.
offset(number)
: skip a certain number of rows and start from a specific point.
// Get the first 10 users
builder.select().from('users').limit(10).build();
// Returns: SELECT * FROM users LIMIT 10;
// Get 5 products, starting from the 6th
builder
.select()
.from('products')
.offset(5) // Start from the 6th product
.limit(5) // Limit to 5 products
.build();
// Returns: SELECT * FROM products OFFSET 5 LIMIT 5;
Union and UnionAll
union(subquery)
: Adds a UNION
clause to the query, combining the current query’s results with the results of a subquery.
// Get users with a gold membership
// and combine them with VIP membership users
builder
.select()
.from('users')
.where((col) => col('membership').equal('gold'))
.union((select) => {
select.from('users').where((col) => col('membership').equal('vip'));
})
.build();
// Returns:
// SELECT * FROM users WHERE membership = ?
// UNION
// SELECT * FROM users WHERE membership = ?;
union()
ensures that any duplicate rows are removed in the final result. It is useful when you want to merge results from multiple queries but avoid repetition.
unionAll(subquery)
: Adds a UNION ALL
clause to the query, combining the current query’s results with the results of a subquery. Unlike UNION
, UNION ALL
keeps all rows, including duplicates, in the combined result set.
// Get users with a gold membership
// and combine them with VIP membership users
builder
.select()
.from('users') // First query: Get gold members
.where((col) => col('membership').equal('gold'))
.unionAll((select) => {
select // Second query: Get VIP members
.from('users')
.where((col) => col('membership').equal('vip'));
})
.build();
// Returns:
// SELECT * FROM users WHERE membership = ?
// UNION ALL
// SELECT * FROM users WHERE membership = ?;
unionAll()
does not remove duplicates, allowing all results to be returned as they are. It is used when you want to merge results from multiple queries and keep all entries, including repetitions.
Pagination and Count
count()
: returns the total number of rows that match the current query conditions. This is particularly useful when you need to know the total number of items that meet specific filters or criteria, such as how many blog posts are published or how many products belong to a certain category.
// Get the total number of products in the "Laptops" category
builder
.select()
.from('products')
.where((col) => col('category').equal('Laptops'))
.count()
.then((count) => console.log(count));
// Outputs: The total number of laptops available
paginate()
: breaks down large result sets into smaller, paginated chunks. This is useful when you want to display results in a paginated format, such as showing a subset of products or blog posts per page.
// Get the first page of products in the "Laptops" category
// 10 products per page
builder
.select()
.from('products')
.where((col) => col('category').equal('Laptops'))
.paginate(1, 10)
.then((pagination) => console.log(pagination));
// Example output:
// {
// result: [{ id: 1, name: 'Laptop A' }, ...], // Products
// page: {
// current: 1, // Current page number
// prev: undefined, // No previous page on the first page
// next: 2, // Next page is page 2
// items: 10, // 10 products per page
// },
// total: {
// pages: 5, // Total of 5 pages
// items: 50, // Total of 50 laptops available
// }
// }
- If
next
isundefined
, it indicates the user is on the last page, so you can hide theNext
button. - If
prev
isundefined
, it indicates the user is on the first page, so you can hide theBack
button. - Use
total.pages
andtotal.items
to show total pages and items to users. - You can also use
paginate
to implement infinite scrolling in your app, loading more items as the user scrolls down.
INSERT Queries
The builder.insert()
method allows you to create and execute INSERT
queries.
- Insert a single row into your table.
const id = await builder
.insert()
.into('users')
.row({ email: 'example@gmail.com', password: '123' })
.build();
// Returns:
// INSERT INTO users (email, password) VALUES (?, ?);
- Insert multiple rows into your table.
await builder
.insert()
.into('users')
.row([
{ email: 'example1@gmail.com', password: '123' },
{ email: 'example2@gmail.com', password: '123' },
])
.build();
// Returns:
// INSERT INTO users (email, password) VALUES (?, ?), (?, ?);
returning(...columns)
: Specify columns to return after inserting data (PostgreSQL
only).
// Insert one user and return the ID
const id = await builder
.insert()
.into('users')
.row({ email: 'example@gmail.com', password: '123' })
.returning('id')
.exec();
console.log(id); // { id: 1 }
// Insert multiple users and return their IDs
const ids = await builder
.insert()
.into('users')
.row([
{ email: 'example1@gmail.com', password: '123' },
{ email: 'example2@gmail.com', password: '123' },
])
.returning('id')
.exec();
console.log(ids); // [{ id: 1 }, { id: 2 }]
Notes
- The
row
object represents the data you want to insert.- Keys are the column names like:
email
,password
. - Values are the data to insert like:
'example@gmail.com'
,123
.
- Keys are the column names like:
- You can insert the following types:
Strings
like:'example@gmail.com'
Numbers
like:18
,20.15
Null
for nullable columns
- The
exec()
method returns the primary key value forMySQL
andSQLite
drivers when inserting a single row. - The
exec()
method returnsundefined
forMySQL
andSQLite
drivers when inserting multiple rows.
UPDATE Queries
The builder.update()
method allows you to create and execute UPDATE
queries.
builder
.update()
.table('orders')
.set({ status: 'shipped' })
.paren()
.where((col) => col('status').equal('pending'))
.and()
.where((col) => col('order_date').lessThan('2024-12-01'))
.paren()
.build();
// Returns:
// UPDATE orders SET status = ?
// WHERE (status = ? AND order_date < ?);
Set columns to NULL
builder
.update()
.table('profiles')
.set({ gender: 'male', city: null, bio: null })
.where((col) => col('user_id').equal(100))
.build();
// Returns:
// UPDATE profiles
// SET gender = ?, city = NULL, bio = NULL
// WHERE user_id = ?;
Notes
- Use
table(name)
to specify theUPDATE
table. - Use
set(row)
to specify yourUPDATE
columns and values. - Use
where(condition)
to build yourUPDATE
condition. - Use
build()
to build and access yourUPDATE
query. - Use
exec()
to execute yourUPDATE
query.- This method always resolves with
undefined
inUPDATE
queries.
- This method always resolves with
DELETE Queries
The builder.delete()
method allows you to create and execute DELETE
queries.
builder
.delete()
.from('profiles')
.where((col) => col('user_id').equal(100))
.build();
// Returns: DELETE FROM profiles WHERE user_id = ?;
Notes
- Use
from(table)
to specify theDELETE
table. - Use
where(condition)
to build yourDELETE
condition. - Use
build()
to build and access yourDELETE
query. - Use
exec()
to execute yourDELETE
query.- This method always resolves with
undefined
inDELETE
queries.
- This method always resolves with
Raw Queries
The builder.raw(sql, ...values)
method allows you to execute raw queries.
// Select user by ID
await builder.raw('SELECT * FROM users WHERE id = ?;', [1]);
// Delete user by ID
await builder.raw('DELETE FROM users WHERE id = ?;', [1]);
// Update user's email
await builder.raw('UPDATE users SET email = ? WHERE id = ?;', [
'updated@gmail.com',
1,
]);
// Insert a new user
await builder.raw('INSERT INTO users (email, password) VALUES (?, ?);', [
'second@gmail.com',
'123',
]);
Use
?
as a placeholder for values in your query. Pass the corresponding values as arguments after the SQL string to prevent SQL injection.
Setter and Getter
builder.set
andbuilder.get
allows you set and get the connection your builder instance is using and that's very helpfull asspecially when you decide to build and execute queries in multiple databases
const { MegaBuilder } = require('@megaorm/builder');
const { MegaConfig } = require('@megaorm/cli');
// Imagine you have two pools in your cluster: 'asia' and 'africa'
// You want to SELECT users from both the Asian and African pools
const app = async () => {
// Load your configuration
const config = await MegaConfig.load();
// Request a connection from `asia`
const con1 = await config.cluster.request('asia');
// Create a builder instance with `con1`
const builder = new MegaBuilder(con1);
// Fetch all users
console.log(await builder.select().from('users').exec());
// Release the `con1` back when done
con1.release();
// Now request another connection from `africa`
const con2 = await config.cluster.request('africa');
// Update the builder connection
builder.set.connection(con2);
// Fetch all users
console.log(await builder.select().from('users').exec());
// Release the `con2` back when done
con2.release();
};
app(); // Execute the app
Query Classes
In MegaORM, we have four query classes: Select
, Update
, Delete
, and Insert
. You can use them to build and execute SELECT
, UPDATE
, DELETE
, and INSERT
queries.
All query classes extend the base class Query
. That's why you will see methods in common:
exec()
: executes the query.build()
: builds and returns the query string.raw(query, values)
: executes a raw query.reset()
: resets the query instance state.log
: logs the query string and values.get
: retrieves the query string and values.
// Import Select, Update, Delete, and Insert
const { Select } = require('@megaorm/builder');
const { Update } = require('@megaorm/builder');
const { Delete } = require('@megaorm/builder');
const { Insert } = require('@megaorm/builder');
// Import MegaConfig
const { MegaConfig } = require('@megaorm/cli');
// Load config
const config = await MegaConfig.load();
// Request a connection
const connection = await config.cluster.request(config.default);
// Create instances
const _select = new Select(connection);
const _update = new Update(connection);
const _delete = new Delete(connection);
const _insert = new Insert(connection);
// Build & Execute SELECT query
const user = await _select
.from('users')
.where((col) => col('email').equal('example@gmail.com'))
.exec();
// Log the result
console.log(user); // [ { id: 1, email: 'example@gmail.com', ... } ]
// Reset your query instance
_select.reset();
// Now you can build and execute another one
const profiles = await _select
.from('profiles')
.where((col) => col('user_id').equal(user.id))
.exec();
// Log the result
console.log(profiles); // [ { id: 1, user_id: 1, name: 'john', ... } ]
// Build & Execute DELETE query
await _delete
.from('profiles')
.where((col) => col('user_id').equal(1))
.exec();
await _delete
.reset() // Reset
.from('users')
.where((col) => col('id').equal(1))
.exec();
// Build & Execute UPDATE query
await _update
.table('profiles')
.set({ bio: 'MegaORM is More Than Just ORM' })
.where((col) => col('user_id').equal(22))
.exec();
// Build & Execute INSERT query
await _insert
.into('users')
.row({ email: 'megaorm@gmail.com', password: '123' })
.exec();
// Release the connection back when done!
connection.release();
// You should not use the connection or queries from this point on...
I recommend using the builder because it simplifies your workflow. With the builder, you create one instance and can execute
SELECT
,UPDATE
,DELETE
,INSERT
, and raw queries using the same connection.In contrast, if you use the
Select
,Update
,Delete
, orInsert
classes directly, you must create a new instance every time you want to execute a query.
10 months ago