ludb

Ludb (Beta Release)

Ludb offers an (almost) identical api to Laravel Database.

Installation

npm install ludb

The Ludb type definitions are included in the lucontainer npm package.

You also need to install lupdo-drivers needed:

npm install lupdo-mssql lupdo-mysql lupdo-postgres lupdo-sqlite

import { DatabaseManager } from 'ludb';

Database: Getting Started

• Introduction
  • Configuration
  • Query Builder
  • Schema Builder
• Running SQL Queries
  • Using Multiple Database Connections
  • Events
  • Listening For Query Events
  • Monitoring Cumulative Query Time
• Cache
• Database Transactions
• Differences With Laravel
• Under The Hood
• Api

Introduction

Almost every modern web application interacts with a database. Ludb makes interacting with databases extremely simple across a variety of supported databases using raw SQL, a fluent query builder. Currently, Ludb provides first-party support for five databases:

• MariaDB 10.3+ (Version Policy)
• MySQL 5.7+ (Version Policy)
• PostgreSQL 10.0+ (Version Policy)
• SQLite 3.8.8+
• SQL Server 2017+ (Version Policy)

Configuration

In the configuration object, you may define all of your database connections, as well as specify which connection should be used by default.

Query Builder

Once you have configured your database connection, you may retrieve the Query Builder using the DatabaseManager connection.

import { DatabaseManager } from 'ludb';

const DB = new DatabaseManager(config);
const connection = DB.connection(connectionName);
const query = connection.table('users');
// or
const query = connection.query();

Schema Builder

Once you have configured your database connection, you may retrieve the Schema Builder using the DatabaseManager connection.

import { DatabaseManager } from 'ludb';

const DB = new DatabaseManager(config);
const connection = DB.connection(connectionName);
const Schema = connection.getSchemaBuilder();

Running SQL Queries

Once you have configured your database connection, you may run queries using the DatabaseManager connection.

import { DatabaseManager } from 'ludb';

const DB = new DatabaseManager(config);
const connection = DB.connection(connectionName);

Running A Select Query

To run a basic SELECT query, you may use the select method on the connecttion:

users = await connection.select('select * from users where active = ?', [1]);

The first argument passed to the select method is the SQL query, while the second argument is any parameter bindings that need to be bound to the query. Typically, these are the values of the where clause constraints. Parameter binding provides protection against SQL injection.

The select method will always return an array of results. Each result within the array will be an object representing a record from the database:

interface User {
    name: string;
}
users = await connection.select<User>('select * from users where active = ?', [1]);
for (const user of users) {
    console.log(user.name);
}

Selecting Scalar Values

Sometimes your database query may result in a single, scalar value. Instead of being required to retrieve the query's scalar result from a record object, Ludb allows you to retrieve this value directly using the scalar method:

burgers = await connection.scalar("select count(case when food = 'burger' then 1 end) as burgers from menu");

Using Named Bindings

Instead of using ? to represent your parameter bindings, you may execute a query using named bindings:

results = await connection.select('select * from users where id = :id', { id: 1 });

Running An Insert Statement

To execute an insert statement, you may use the insert method on the connecttion. Like select, this method accepts the SQL query as its first argument and bindings as its second argument:

await connection.insert('insert into users (id, name) values (?, ?)', [1, 'Marc']);

Running An Update Statement

The update method should be used to update existing records in the database. The number of rows affected by the statement is returned by the method:

affected = await connection.update('update users set votes = 100 where name = ?', ['Anita']);

Running A Delete Statement

The delete method should be used to delete records from the database. Like update, the number of rows affected will be returned by the method:

deleted = await connection.delete('delete from users');

Running A General Statement

Some database statements do not return any value. For these types of operations, you may use the statement method on the connecttion:

await connection.statement('drop table users');

Running An Unprepared Statement

Sometimes you may want to execute an SQL statement without binding any values. You may use the connecttion unprepared method to accomplish this:

await connection.unprepared('update users set votes = 100 where name = "Dries"');

Warning
Since unprepared statements do not bind parameters, they may be vulnerable to SQL injection. You should never allow user controlled values within an unprepared statement.

Implicit Commits

When using the connecttion statement and unprepared methods within transactions you must be careful to avoid statements that cause implicit commits. These statements will cause the database engine to indirectly commit the entire transaction, leaving Ludb unaware of the database's transaction level. An example of such a statement is creating a database table:

await connection.unprepared('create table a (col varchar(1) null)');

Please refer to the MySQL manual for a list of all statements that trigger implicit commits.

Bindings Caveat

Ludb and Lupdo can detect the right type of binded value through the Javascript type of a variable, but SqlServer Ludpo driver need to know the exact type of the database column to make an insert or an update, and in some case it can fail (for instance when a binded value is null, or when you are working with time or date).
You can bypass the problem using the TypedBinding object of Lupdo; Ludb make it super easy to implement it providing a complete set of TypedBinding through bindTo Api, an example:

await connection.insert('insert into users (id, name, nullablestring) values (?, ?)', [1, 'Marc', connection.bindTo.string(null)]);

Using Multiple Database Connections

If your application defines multiple connections in your configuration object, you may access each connection via the connection method provided by the connecttion. The connection name passed to the connection method should correspond to one of the connections listed in your configuration:

import { DatabaseManager } from 'ludb';

const DB = new DatabaseManager(config);
const connection = DB.connection('sqlite').select(/* ... */);

You may access the raw, underlying Lupdo instance of a connection using the getPdo method on a connection instance:

pdo = connection.connection('connectionName').getPdo();

Events

Ludb emit an event for each query executed, the QueryExecuted event instance expose 6 properties:

• connection: the ConnectionSession instance who generate the query
• sql: the sql executed
• bindings: the bindings of the query executed
• time: time of execution in milliseconds
• sessionTime: total time of session execution in millisecond
• inTransaction: the sql executed is in a transaction

When a query is executed in a transaction, all the query executed inside a committed transaction will generate two Event, the first one will have the property inTransaction true, the second will be emitted only after the commit will have property inTransaction false.

Ludb emit an event every time a Lupdo Statement is prepared, the StatementPrepared event instance expose 2 properties:

• connection: the ConnectionSession instance who generate the query
• statement: the Lupdo Statement

Lupdo emit 4 event when a transaction is used, every transaction event expose only the connection property.

• TransactionBeginning
• TransactionCommitted
• TransactionCommitting
• TransactionRolledBack

Listening For Query Events

If you would like to specify a closure that is invoked for each SQL query executed by your application, you may use the connecttion listen method. This method can be useful for logging queries or debugging.

import { DatabaseManager } from 'ludb';

const DB = new DatabaseManager(config);
const connection = DB.connection('sqlite').listen(query => {
    // query.sql;
    // query.bindings;
    // query.time;
});

You can also detach a listener using connecttion unlisten method:

import { DatabaseManager } from 'ludb';

const DB = new DatabaseManager(config);

const TempListener = query => {
    // query.sql;
    // query.bindings;
    // query.time;
    DB.connection('sqlite').unlisten(TempListener);
};

const connection = DB.connection('sqlite').listen(TempListener);

By Default DatabaseManager will use an EventEmitter instance to manage events. You can provide a custom instance of EventEmitter through constructor.

import { DatabaseManager } from 'ludb';
import EventEmitter from 'node:events';

const emitter = new EventEmitter();

const DB = new DatabaseManager(config, emitter);
const connection = DB.connection('sqlite').listen(query => {
    // query.sql;
    // query.bindings;
    // query.time;
});

Monitoring Cumulative Query Time

A common performance bottleneck of modern web applications is the amount of time they spend querying databases. The connecttion listen method can be helpful to make any kind of monitoring. An example of monitoring single query time execution:

DB.connection('sqlite').listen(query => {
    if (query.time > 500 && !query.inTransaction) {
        console.log('warning');
    }
});

An example of monitoring a session query time execution (all transaction queries are executed in a single session):

DB.connection('sqlite').listen(query => {
    if (query.sessionTime > 500 && !query.inTransaction) {
        console.log('warning');
    }
});

Sometimes you want to know when your application spends too much time querying the database during a single request. An example with Expressjs

import express, { Express, Request, Response, NextFunction } from 'express';
import { DatabaseManager, QueryExecuted } from 'ludb';

const DB = new DatabaseManager(config);
const app: Express = express();

const beforeMiddleware = (req: Request, res: Response, next: NextFunction) => {
    let totalTime = 0;
    let hasRun = false;
    const queryExecuted = [];
    req.referenceQueryId = 'uniqueid-for-req';
    req.queryLogListener = (event: QueryExecuted) => {
        if (event.referenceId ===  req.referenceQueryId && !hasRun && !event.inTransaction) {
            totalTime += event.time;
            queryExecuted.push(event);

            if (totalTime > 500) {
                hasRun = true;
                console.log('warning', queryExecuted);
            }
        }
    };
    DB.connection('connectionName').listen(req.queryLogListener);
    next();
};

const responseHandler = (req: Request, res: Response, next: NextFunction) => {
    // do stuff with database using reference
    // DB.connection('connectionName').reference(req.referenceQueryId).select(...)
    res.status(200).send({ response: 'ok' });
    next();
};

const afterMiddleware = (req: Request, res: Response, next: NextFunction) => {
    DB.connection('connectionName').unlisten(req.queryLogListener);
    next();
};

app.get('/', beforeMiddleware, responseHandler, afterMiddleware);

Cache

Ludb support caching queries for select operations selectOne, scalar, selectFromWriteConnection and select, here you can find more information about caching

Database Transactions

You may use the transaction method provided by the connecttion to run a set of operations within a database transaction. If an exception is thrown within the transaction closure, the transaction will automatically be rolled back and the exception is re-thrown. If the closure executes successfully, the transaction will automatically be committed. You don't need to worry about manually rolling back or committing while using the transaction method:

await connection.transaction(async session => {
    await session.update('update users set votes = 1');

    await session.delete('delete from posts');
});

Warning
Since Transaction will generate a new session you should always use the ConnectioSession provided as first parameter of callback. Query executed on default connection will do not be exectued within the transaction.

Handling Deadlocks

The transaction method accepts an optional second argument which defines the number of times a transaction should be retried when a deadlock occurs. Once these attempts have been exhausted, an exception will be thrown:

await connection.transaction(async session => {
    await session.update('update users set votes = 1');

    await session.delete('delete from posts');
}, 5);

Manually Using Transactions

If you would like to begin a transaction manually and have complete control over rollbacks and commits, you may use the beginTransaction method provided by the connecttion:

session = await connection.beginTransaction();

You can rollback the transaction via the rollBack method:

session.rollBack();

Lastly, you can commit a transaction via the commit method:

session.commit();

Warning
Since Transaction will generate a new session you should always use the ConnectioSession returned by beginTransacion. Query executed on default connection will do not be executed within the transaction.

Differences With Laravel

• The DatabaseManager instance do not proxy methods to default connection, you always need to call connection(name) method to access method of Connection.
• The DatabaseManager do not expose functionality to extend registered drivers.
• Methods whenQueryingForLongerThan and allowQueryDurationHandlersToRunAgain do not exist, Monitoring Cumulative Query Time offer a valid alternative.
• Methods getQueryLog and getRawQueryLog do not exist, logging query is used only internally for pretend method.
• Methods beginTransaction and useWriteConnectionWhenReading return a ConnectionSession you must use the session instead the original connection for the queries you want to execute them within the transaction or against the write pdo.
• Callbacks for methods transaction and pretend are called with a ConnectionSession you must use the session instead the original connection inside the callback if you want to execute the queries within the transaction or to pretend the execution.
• Query Builder return Array instead of Collection
• Connection Method selectResultSets is not supported.

Under The Hood

Ludb use Lupdo an abstraction layer used for accessing databases.
When the nodejs application start and a connection is required from DatabaseManager only the first time Ludb generate the pdo connection and it store internally the pdo required for the specific connection.
EveryTime a method that require a builder is invoked within the connection by the user, a new ConnectionSession will be initialized and provided to the builder.
The ConnectionSession expose almost all the api exposed by the original Connection and is completly "hidden" for the user the switch between sessions and connection.
Ludb will require a connection from the pool only when a method of ConnectionSession require to comunicate with the database, everytime the request is completed the connection will be released to the pool, and the ConnectionSession is burned.
For this reason when methods transaction, beginTransaction, pretend and useWriteConnectionWhenReading are called Ludb return the ConnectionSession to the user and the user must use the session provided to execute next queries.

Ludb will generate 1 or 2 pdo for Query Builder (it depends on write/read configuration) and 1 pdo for Schema Builder.
The Schema Builder Pdo force the Lupdo pool to have only 1 connection, this is necessary to ensure the proper functioning of the exposed Api (temporary tables, for instance, are only visible for the connection that generated them).

Examples

An example of transaction method:

import { DatabaseManager } from 'ludb';

const DB = new DatabaseManager(config);
const connection = DB.connection(connectionName);

await connection.transaction(async session => {
    await session.update('update users set votes = 1');

    await session.delete('delete from posts');
});

const users = await connection.table('users').get();

An example of beginTransaction method:

import { DatabaseManager } from 'ludb';

const DB = new DatabaseManager(config);
const connection = DB.connection(connectionName);
const session = await connection.beginTransaction();
try {
    await session.update('update users set votes = 1');
    await session.delete('delete from posts');
    await session.commit();
} catch (error) {
    await session.rollBack();
}

const users = connection.table('users').get();

An example of pretend method:

import { DatabaseManager } from 'ludb';

const DB = new DatabaseManager(config);
const connection = DB.connection(connectionName);
const queries = await connection.pretend(async session => {
    await session.table('users').get();
    await session.table('posts').get();
});
console.log(queries);
const users = connection.table('users').get();

An example of useWriteConnectionWhenReading method:

import { DatabaseManager } from 'ludb';

const DB = new DatabaseManager(config);
const connection = DB.connection(connectionName);
await connection.table('users').where('id', 10).update({ name: 'Claudio' });
const session = connection.useWriteConnectionWhenReading();
const userFromWrite = session.table('users').find(10);
const userFromRead = connection.table('users').find(10);

An example of temporary with Schema:

import { DatabaseManager } from 'ludb';
import * as crypto from 'crypto';

const DB = new DatabaseManager(config);
const connection = DB.connection(connectionName);
const Schema = connection.getSchemaBuilder();

await Schema.table('orders', table => {
    table.string('hash_id').index();
});

await Schema.create('temp_mappings', table => {
    table.temporary();
    table.integer('id').primary();
    table.string('hash_id');
});

const connection = Schema.getConnection();

// insert mappings in 10K chunks
await connection.table('orders').chunkById(1000, async orders => {
    const values = orders
        .map(order => {
            const hash = crypto.createHash('sha1').update(order.id).digest('hex');
            /* Prepare the value string for the SQL statement */
            return `(${order.id}, '${hash}')`;
        })
        .join(',');

    await connection.insert(connection.raw(`INSERT INTO temp_mappings(id, reference) VALUES ${values}`));
});

await connection
    .table('orders')
    .join('temp_mappings', 'temp_mappgings.id', 'orders.id')
    .update({ hash_id: connection.raw('temp_mappings.hash_id') });