Repository files navigation

Ludb offers an (almost) identical api toLaravel Database.

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';

• 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

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, afluent 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)

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

Once you have configured your database connection, you may retrieve theQuery Builder using theDatabaseManager connection.

import{DatabaseManager}from'ludb';constDB=newDatabaseManager(config);constconnection=DB.connection(connectionName);constquery=connection.table('users');// orconstquery=connection.query();

Once you have configured your database connection, you may retrieve theSchema Builder using theDatabaseManager connection.

import{DatabaseManager}from'ludb';constDB=newDatabaseManager(config);constconnection=DB.connection(connectionName);constSchema=connection.getSchemaBuilder();

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

import{DatabaseManager}from'ludb';constDB=newDatabaseManager(config);constconnection=DB.connection(connectionName);

To run a basic SELECT query, you may use theselect method on theconnecttion:

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

The first argument passed to theselect 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 thewhere clause constraints. Parameter binding provides protection against SQL injection.

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

interfaceUser{name:string;}users=awaitconnection.select<User>('select * from users where active = ?',[1]);for(constuserofusers){console.log(user.name);}

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 thescalar method:

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

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

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

To execute aninsert statement, you may use theinsert method on theconnecttion. Likeselect, this method accepts the SQL query as its first argument and bindings as its second argument:

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

Theupdate 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=awaitconnection.update('update users set votes = 100 where name = ?',['Anita']);

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

deleted=awaitconnection.delete('delete from users');

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

awaitconnection.statement('drop table users');

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

awaitconnection.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.

When using theconnecttionstatement andunprepared methods within transactions you must be careful to avoid statements that causeimplicit 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:

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

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

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 isnull, or when you are working with time or date).
You can bypass the problem using theTypedBinding object of Lupdo; Ludb make it super easy to implement it providing a complete set of TypedBinding throughbindTo Api, an example:

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

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

import{DatabaseManager}from'ludb';constDB=newDatabaseManager(config);constconnection=DB.connection('sqlite').select(/* ... */);

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

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

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

• connection: theConnectionSession 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 propertyinTransaction true, the second will be emitted only after the commit will have propertyinTransaction false.

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

• connection: theConnectionSession 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

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

import{DatabaseManager}from'ludb';constDB=newDatabaseManager(config);constconnection=DB.connection('sqlite').listen(query=>{// query.sql;// query.bindings;// query.time;});

You can also detach a listener usingconnecttionunlisten method:

import{DatabaseManager}from'ludb';constDB=newDatabaseManager(config);constTempListener=query=>{// query.sql;// query.bindings;// query.time;DB.connection('sqlite').unlisten(TempListener);};constconnection=DB.connection('sqlite').listen(TempListener);

By DefaultDatabaseManager will use anEventEmitter instance to manage events. You can provide a custom instance of EventEmitter through constructor.

import{DatabaseManager}from'ludb';importEventEmitterfrom'node:events';constemitter=newEventEmitter();constDB=newDatabaseManager(config,emitter);constconnection=DB.connection('sqlite').listen(query=>{// query.sql;// query.bindings;// query.time;});

A common performance bottleneck of modern web applications is the amount of time they spend querying databases.Theconnecttion 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

importexpress,{Express,Request,Response,NextFunction}from'express';import{DatabaseManager,QueryExecuted}from'ludb';constDB=newDatabaseManager(config);constapp:Express=express();constbeforeMiddleware=(req:Request,res:Response,next:NextFunction)=>{lettotalTime=0;lethasRun=false;constqueryExecuted=[];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();};constresponseHandler=(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();};constafterMiddleware=(req:Request,res:Response,next:NextFunction)=>{DB.connection('connectionName').unlisten(req.queryLogListener);next();};app.get('/',beforeMiddleware,responseHandler,afterMiddleware);

Ludb support caching queries for select operationsselectOne,scalar,selectFromWriteConnection andselect,here you can find more information about caching

You may use thetransaction method provided by theconnecttion 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 thetransaction method:

awaitconnection.transaction(asyncsession=>{awaitsession.update('update users set votes = 1');awaitsession.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.

Thetransaction 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:

awaitconnection.transaction(asyncsession=>{awaitsession.update('update users set votes = 1');awaitsession.delete('delete from posts');},5);

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

session=awaitconnection.beginTransaction();

You can rollback the transaction via therollBack method:

session.rollBack();

Lastly, you can commit a transaction via thecommit method:

session.commit();

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

• TheDatabaseManager instance do not proxy methods to default connection, you always need to callconnection(name) method to access method ofConnection.
• TheDatabaseManager do not expose functionality to extend registered drivers.
• MethodswhenQueryingForLongerThan andallowQueryDurationHandlersToRunAgain do not exist,Monitoring Cumulative Query Time offer a valid alternative.
• MethodsgetQueryLog andgetRawQueryLog do not exist, logging query is used only internally forpretend method.
• MethodsbeginTransaction anduseWriteConnectionWhenReading return aConnectionSession 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 methodstransaction andpretend are called with aConnectionSession 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 returnArray instead ofCollection
• Connection MethodselectResultSets is not supported.

Ludb useLupdo an abstraction layer used for accessing databases.
When the nodejs application start and a connection is required fromDatabaseManager 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 newConnectionSession will be initialized and provided to the builder.
TheConnectionSession expose almost all the api exposed by the originalConnection 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 ofConnectionSession require to comunicate with the database, everytime the request is completed the connection will be released to the pool, and theConnectionSession is burned.
For this reason when methodstransaction,beginTransaction,pretend anduseWriteConnectionWhenReading are called Ludb return theConnectionSession to the user and the user must use the session provided to execute next queries.

Ludb will generate 1 or 2 pdo forQuery Builder (it depends on write/read configuration) and 1 pdo forSchema 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).

An example oftransaction method:

import{DatabaseManager}from'ludb';constDB=newDatabaseManager(config);constconnection=DB.connection(connectionName);awaitconnection.transaction(asyncsession=>{awaitsession.update('update users set votes = 1');awaitsession.delete('delete from posts');});constusers=awaitconnection.table('users').get();

An example ofbeginTransaction method:

import{DatabaseManager}from'ludb';constDB=newDatabaseManager(config);constconnection=DB.connection(connectionName);constsession=awaitconnection.beginTransaction();try{awaitsession.update('update users set votes = 1');awaitsession.delete('delete from posts');awaitsession.commit();}catch(error){awaitsession.rollBack();}constusers=connection.table('users').get();

An example ofpretend method:

import{DatabaseManager}from'ludb';constDB=newDatabaseManager(config);constconnection=DB.connection(connectionName);constqueries=awaitconnection.pretend(asyncsession=>{awaitsession.table('users').get();awaitsession.table('posts').get();});console.log(queries);constusers=connection.table('users').get();

An example ofuseWriteConnectionWhenReading method:

import{DatabaseManager}from'ludb';constDB=newDatabaseManager(config);constconnection=DB.connection(connectionName);awaitconnection.table('users').where('id',10).update({name:'Claudio'});constsession=connection.useWriteConnectionWhenReading();constuserFromWrite=session.table('users').find(10);constuserFromRead=connection.table('users').find(10);

An example oftemporary with Schema:

import{DatabaseManager}from'ludb';import*ascryptofrom'crypto';constDB=newDatabaseManager(config);constconnection=DB.connection(connectionName);constSchema=connection.getSchemaBuilder();awaitSchema.table('orders',table=>{table.string('hash_id').index();});awaitSchema.create('temp_mappings',table=>{table.temporary();table.integer('id').primary();table.string('hash_id');});constconnection=Schema.getConnection();// insert mappings in 10K chunksawaitconnection.table('orders').chunkById(1000,asyncorders=>{constvalues=orders.map(order=>{consthash=crypto.createHash('sha1').update(order.id).digest('hex');/* Prepare the value string for the SQL statement */return`(${order.id}, '${hash}')`;}).join(',');awaitconnection.insert(connection.raw(`INSERT INTO temp_mappings(id, reference) VALUES${values}`));});awaitconnection.table('orders').join('temp_mappings','temp_mappgings.id','orders.id').update({hash_id:connection.raw('temp_mappings.hash_id')});