Database 🛢️

Handle your application database with factories, seeders and query builder in Node.js

The intention behind this repository is to always maintain a Database class to manipulate data using any database driver (SQL and NoSQL).

Installation

To use the high potential from this package you need to install first this other packages from SecJS, it keeps as dev dependency because one day @secjs/core will install everything once.

npm install @secjs/env @secjs/utils @secjs/exceptions

Then you can install the package using:

npm install @secjs/database

Usage

Config database template

First you need to create the configuration file database in the config folder on project root path. Is extremely important to use export default in these configurations.

import{Env}from'@secjs/env'import{Path}from'@secjs/utils'exportdefault{/* |-------------------------------------------------------------------------- | Default Database Connection Name |-------------------------------------------------------------------------- | | Here you may specify which of the database connections below you wish | to use as your default connection for all database work. Of course | you may use many connections at once using the Database library. | */default: Env('DB_CONNECTION','postgres'),/* |-------------------------------------------------------------------------- | Database Connections |-------------------------------------------------------------------------- | | Here are each of the database connections setup for your application. | Of course, examples of configuring each database platform that is | supported by SecJS is shown below to make development simple. | */connections: {sqlite: {driver: 'sqlite',filename: Env('DB_FILENAME',Path.database('sqlite')),},mysql: {driver: 'mysql',url: Env('DB_URL',''),host: Env('DB_HOST','127.0.0.1'),port: Env({type: 'number',name: 'DB_PORT'},3306),database: Env('DB_DATABASE','mysql'),user: Env('DB_USERNAME','root'),password: Env('DB_PASSWORD',''),},postgres: {driver: 'postgres',url: Env('DB_URL',''),host: Env('DB_HOST','127.0.0.1'),port: Env({type: 'number',name: 'DB_PORT'},5432),database: Env('DB_DATABASE','postgres'),user: Env('DB_USERNAME','root'),password: Env('DB_PASSWORD',''),},sqlserver: {driver: 'sqlserver',url: Env('DB_URL',''),host: Env('DB_HOST','127.0.0.1'),port: Env({type: 'number',name: 'DB_PORT'},1433),database: Env('DB_DATABASE','sqlserver'),user: Env('DB_USERNAME','root'),password: Env('DB_PASSWORD',''),},mongo: {driver: 'mongo',protocol: 'mongodb',url: Env('DB_URL',''),host: Env('DB_HOST','127.0.0.1'),port: Env({type: 'number',name: 'DB_PORT'},27017),database: Env('DB_DATABASE','mongodb'),user: Env('DB_USERNAME',''),password: Env('DB_PASSWORD',''),options: {w: 'majority',replicaSet: 'rs',retryWrites: true,authSource: 'admin',useNewUrlParser: true,useUnifiedTopology: true,},},},/* |-------------------------------------------------------------------------- | Migration Repository Table |-------------------------------------------------------------------------- | | This table keeps track of all the migrations that have already run for | your application. Using this information, we can determine which of | the migrations on disk haven't actually been run in the database. | */migrations: 'migrations',}

Database

With the config/database file created you can use Database class to start handling operations in your database.

Connecting to database

import{Database}from'@secjs/database'// Database class will always use the default value set in config/database // to handle operations, in this case, postgres.// If true will force the creation of the connection for that driver // even if that driver is already connected to databaseconstforce=false// default is false// If true will save the connection inside DriverFactory and this connection// will be shared in all Database/Driver instances that use this connectionconstsaveOnDriver=true// default is true// You can create the connection for postgres calling connect methodconstdatabase=awaitnewDatabase().connection('postgres').connect(force,saveOnDriver)// Now every time that you need postgres connection, you don't need to call connect againconstnewDb=newDatabase().connection('postgres')// Database/Driver always share the same database connection for each instance!// We recommend using the static method createConnections in the application startup.// It will create the connections according to config/database fileawaitDatabase.createConnections('postgres','mongo')// Now you don't need to call connect method when changing your connectiondatabase.connection('mongo')// will use mongo driver

Disconnecting from database

// There are three ways to disconect from database// WARN - Becarefull with close if you are using a shared connection, // it will close the connection for all Database/Driver instances // 1 - Calling close in the database instanceawaitdatabase.close()// 2 - Calling the static closeConnections methodawaitDatabase.closeConnections('postgres','mongo')// 3 - Calling the static closeAllDrivers methodawaitDatabase.closeAllDrivers()// Close all Drivers connections inside DriverFactory

Creating a specific connection with database subscribing configs in runtime

// As you can see above, each Driver class share the same database connection.// But maybe you need to create a specific connection with runtime configurations,// without implicating in other Driver connections.constforce=trueconstsaveOnDriver=falseconstruntimeConfigs={database: 'testing'}construntimeDb=newDatabase().connection('postgres',runtimeConfigs)// You need to set force as true and saveOnDriver as false, // this way you will force creating a new connection for postgres but// it will not implicate in other Database/Driver instances connections. // We can use connect method this way to create a specific connection// to work on..connect(force,saveOnDriver)// This connection won't be available in static method Database.closeConnections.// If calling this method it will close the main PostgresDriver connectionawaitDatabase.closeConnections('postgres')// So always remember closing this connection!awaitruntimeDb.close()

Create/drop tables and databases

import{Knex}from'knex'import{Database,TableBuilder}from'@secjs/database'importdatabasefrom'./database'// All SQL Drivers from Database are using Knex as query builder and for Mongo NoSQL, mongoose.awaitdatabase.createTable('products',(tableBuilder: Knex.TableBuilder)=>{tableBuilder.increments('id').primary()tableBuilder.string('name').nullable()tableBuilder.integer('quantity').nullable().defaultTo(0)})// Creating the product details tableawaitdatabase.createTable('product_details',(tableBuilder: Knex.TableBuilder)=>{tableBuilder.increments('id').primary()tableBuilder.string('detail').nullable()// One product has many product detailstableBuilder.integer('productId').references('id').inTable('products')})// Mongoose driver has a TableBuilder to create collections, // but it does not have all the methods from Knex table builder.// Changing the connection to mongo databasedatabase.connection('mongo')// With mongo connection we can't create the table. But we can set our schema// in buildTable methodconstproductSchema=newSchema({name: String,quantity: Number,})database.buildTable({name: 'Product',collection: 'products',schema: productSchema})// Drop table products from databaseawaitdatabase.dropTable('products')// You can also create databasesawaitdatabase.createDatabase('testing-database')// Then you can create a new database instance to connect to this new databaseconstruntimeConfigurations={database: 'testing-database'}consttestingDatabase=awaitnewDatabase(runtimeConfigurations).connection('mongo')// Force connection and don't save the connection .connect(true,false)// Do operations using testingDatabase....// Close the connection with testing databaseawaittestingDatabase.close()// You can drop databases tooawaitdatabase.dropDatabase('testing-database')

Insert products

// Insert and return an array of IDconstproductIds=awaitdatabase.buildTable('products').insert([{name: 'iPhone 10'},{name: 'iPhone 11'},{name: 'iPhone 12'}])// WARN - ONLY FOR SQL{// When Database insert the data he will always return an array of ID. // But you can change this behavior setting the returnKey valueconstreturnKey='name'constproductNames=awaitdatabase.buildTable('products').insert([{name: 'iPhone 13'}],returnKey)// The returnKey can be defined only in SQL Drivers because Mongo always returns the _id by default}constreturnKeyId='id'// Insert and return an array of products objectsconstproducts=awaitdatabase.buildTable('products')// You can set returnKey here too, this way it will insert the product // and then find the products by the returnKey value, in this case, the id// WARN - Remember, this is a SQL feature only and will not take effect in MongoDriver.insertAndGet({name: 'iPhone 13'},returnKeyId)// WARN - buildTable method needs to be called only one time before the connection is stabilished.// when you call buildTable it saves in the Driver instance the table that you are working on.// So, all the operations that I call now will use 'products' table to operate. Example:constdata=awaitdatabase// .buildTable('products') I do not need this anymore.findMany()// Will find all the data inside products table

Get products

// Build a where query and handle it with find, find return only one value// If using mongodb change from id to _idconstproduct=awaitdatabase.buildWhere('id',idIphone).find()// Find many return an array of valuesconstproducts=awaitdatabase.findMany()constpage=0constlimit=10// Find many products paginated and return meta and linksconst{ data, meta, links }=awaitdatabase.paginate(page,limit,'/products')// Find many products paginated and return only the dataconstproductsPaginated=awaitdatabase.forPage(page,limit)

Update products

constproductIds=awaitdatabase.insert([{name: 'iPhone 10'},{name: 'iPhone 11'}])// WARN - ONLY FOR SQL{// When Database update the data he will always return an array of ID.// But you can change this behavior setting the returnKey valueconstreturnKey='name'constproductNames=awaitdatabase.buildTable('products').update([{name: 'iPhone 13'}],returnKey)// The returnKey can be defined only in SQL Drivers because Mongo always returns the _id by default}constreturnKeyId='id'// WARN - Be carefull with update, remember to always use whereconstproductsUpdated=awaitdatabase.buildWhereIn('id',productIds)// You can set returnKey here too, this way it will update the product // and then find the products by the returnKey value, in this case, the id// WARN - Remember, this is a SQL feature only and will not take effect in MongoDriver.updateAndGet({name: 'iPhone X'},returnKeyId)// or updateAngGet('name', 'iPhone X', returnKeyId)console.log(productsUpdated)// [{ id: 1, name: 'iPhone X', quantity: 0 }, { id: 2, name: 'iPhone X', quantity: 0 }]

Delete products

constproductIds=awaitdatabase.insert([{name: 'iPhone 10'},{name: 'iPhone 11'}])// Be carefull with delete, remember to always use whereawaitdatabase.buildWhereIn('id',productIds).delete()

Join relations

constproductIds=awaitdatabase.insert([{name: 'iPhone 10'},{name: 'iPhone 11'}])// Creating a new detail for each product createdconstpromisesPending=productIds.map(productId=>(database.buildTable('product_details').insert({detail: '64 GB',
productId
})))// Resolving the array of promisesawaitPromise.all(promisesPending)constproductsWithDetails=awaitdatabase.buildTable('products').buildJoin('product_details','products.id','product_details.id','leftJoin').findMany()// If using SQL driver, the detail will come inside the main product object.//Knex does not map the relations, is just a query builder.console.log(productsWithDetails)// [{ id: 1, name: 'iPhone 10', quantity: 0, detail: '64 GB', productId: 1 }, ...]// But if using MongoDriver, mongoose will map the "relation" and bring product details inside an objectconsole.log(productsWithDetails)// [{ id: 1, name: 'iPhone 10', quantity: 0, product_details: [{ id: 1, detail: '64 GB', productId: 1 }] }, ...]

Order By

awaitdatabase.buildTable('products').insert([{name: 'iPhone 1',quantity: -40},{name: 'iPhone 2',quantity: -30},{name: 'iPhone 3',quantity: 10},{name: 'iPhone 4',quantity: 20},{name: 'iPhone 5',quantity: 30},{name: 'iPhone 6',quantity: 40},{name: 'iPhone 6',quantity: 40},{name: 'iPhone 7',quantity: 50},{name: 'iPhone 8',quantity: 60},])// Order the products by quantity value in desc modeconstproducts=awaitdatabase.buildTable('products').buildSelect('name','quantity').buildOrderBy('quantity','desc').findMany()console.log(products[0].name)// 'iPhone 8'

Group By and Having

awaitdatabase.buildTable('products').insert([{name: 'iPhone 1',quantity: -40},{name: 'iPhone 2',quantity: -30},{name: 'iPhone 3',quantity: 10},{name: 'iPhone 4',quantity: 20},{name: 'iPhone 5',quantity: 30},{name: 'iPhone 6',quantity: 40},{name: 'iPhone 6',quantity: 40},{name: 'iPhone 7',quantity: 50},{name: 'iPhone 8',quantity: 60},])constproducts=awaitdatabase.buildTable('products').buildSelect('name','quantity')// Group the products by name and quantity.buildGroupBy('name','quantity')// Then regroup then only where quantity is under 40.buildHaving('quantity','<=',40).findMany()console.log(products[0].name)// 'iPhone 3'

Database Transactions

import{Transaction}from'@secjs/database'// Start the transactionconsttrx: Transaction=awaitdatabase.beginTransaction()awaittrx.buildTable('products').insert({name: 'AirPods 3'})try{// Do some process that could fail...// Commit the transaction if process does not failawaittrx.commit()}catch(error){// Handle the error...// Rollback the transaction and all the operations done in the databaseawaittrx.rollback()}

Pluck column values

awaitdatabase.buildTable('products').insert([{name: 'Apple Watch Series 2'},{name: 'Apple Watch Series 3'}])// Pluck get the value of only one columnconstproductsName=awaitdatabase.pluck('name')console.log(productsName)// ['Apple Watch Series 2', 'Apple Watch Series 3']

Min & Max

awaitdatabase.buildTable('products').insert([{name: 'iPhone 1',quantity: -40},{name: 'iPhone 2',quantity: 10},{name: 'iPhone 3',quantity: 20},{name: 'iPhone 4',quantity: 30},{name: 'iPhone 5',quantity: 40},])// Get the max value from column quantity in products tableconsole.log(awaitdatabase.max('quantity'))// 40// Get the min value from column quantity in products tableconsole.log(awaitdatabase.min('quantity'))// -40

Increment & Decrement

constids=awaitdatabase.buildTable('products').insert([{name: 'iPhone 1',quantity: -40},{name: 'iPhone 2',quantity: 10},{name: 'iPhone 3',quantity: 20},{name: 'iPhone 4',quantity: 30},{name: 'iPhone 5',quantity: 40},{name: 'iPhone 6',quantity: 40},])// Incrementing product quantityawaitdatabase.buildWhere('_id',ids[1]).increment('quantity',20)// Decrementing product quantityawaitdatabase.buildWhere('_id',ids[2]).decrement('quantity',20)// 10 + 20 = 30console.log(awaitdatabase.buildWhere('_id',ids[1]).find())// { name: 'iPhone 2', quantity: 30 }// 20 - 20 = 40console.log(awaitdatabase.buildWhere('_id',ids[2]).find())// { name: 'iPhone 3', quantity: 0 }

Count & Count Distinct

awaitdatabase.buildTable('products').insert([{name: null},{name: 'Apple Watch Series 2'},{name: 'Apple Watch Series 2'},{name: 'Apple Watch Series 3'},])// Count all table rowsconsole.log(awaitdatabase.count('*'))// 4// Count only where name column exists in table productsconsole.log(awaitdatabase.count('name'))// 3// Count only where name column exists in table products and the values from name are distinctconsole.log(awaitdatabase.countDistinct('name'))// 2

Sum & Sum Distinct

awaitdatabase.buildTable('products').insert([{name: 'iPhone 1',quantity: -40},{name: 'iPhone 2',quantity: 10},{name: 'iPhone 3',quantity: 20},{name: 'iPhone 4',quantity: 30},{name: 'iPhone 5',quantity: 40},{name: 'iPhone 6',quantity: 40},])// Sum all the values from the column quantity in products tableconsole.log(awaitdatabase.sum('quantity'))// 100// Sum all the values from the column quantity in products table only where quantity values are distinctconsole.log(database.sumDistinct('quantity'))// 60

Avg & Avg Distinct

awaitdatabase.buildTable('products').insert([{name: 'iPhone 1',quantity: -40},{name: 'iPhone 2',quantity: 10},{name: 'iPhone 3',quantity: 20},{name: 'iPhone 4',quantity: 30},{name: 'iPhone 5',quantity: 40},{name: 'iPhone 6',quantity: 40},])// Get the average of all the values from column quantity in table productsconsole.log(awaitdatabase.avg('quantity'))// 16.666666666666668// Get the average of all the values from column quantity in table products where quantity values are distinctconsole.log(awaitdatabase.avgDistinct('quantity'))// 12

Raw queries

awaitdatabase.buildTable('products').insert({name: 'iPhone X'})// Using KnexconstknexRaw=awaitdatabase.raw('SELECT * FROM ??;',['products'])console.log(knexRaw)// { command: 'SELECT', rowCount: 1, rows: [{ id: 1, name: 'iPhone X' }] }// Using Mongooseawaitdatabase.connection('mongo').connect()constmongooseRaw=awaitdatabase.raw('db.collection(??).find().toArray()',['products'])console.log(mongooseRaw)// { command: 'find()', rowCount: 1, rows: [{ id: 1, name: 'iPhone X' }] }

Get information from columns

constcolumnInfo=awaitdatabase.buildTable('products').columnInfo('name')console.log(columnInfo)// { defaultValue: null, type: 'character varying', maxLength: 255, nullable: true }// WARN Under MongoDriver// columnInfo method in mongo just return a fake object

Clone Database Query Chain

// Set the table productsdatabase.buildTable('products')// Clone the database query chain, this will create a new instance of the Database class// but with the exactly same query chain.constclonedDatabase=database.clone()console.log(database===clonedDatabase)// false// This insert will be done in products table because of database.buildTableconstarrayOfIds=awaitclonedDatabase.insert({name: 'AirPods 2'})

Clone client instance (Knex, Mongoose, etc)

import{Knex}from'knex'// This method will give you an instance of the client // depending the driver that you are usingconst{ client }=awaitdatabase.cloneQuery<Knex.QueryBuilder>()constarrayOfIds=awaitclient.insert({name: 'AirPods 2'},'id'){// For mongoose you can set the Schema as typedatabase.connection('mongo')const{ client, session }=awaitdatabase.cloneQuery<UserSchema>()// If using session...constproduct=awaitclient.insertOne({name: 'AirPods 2'},{ session })}

Extending connections and drivers

Nowadays, @secjs/database has only MongoDriver, MySqlDriver, PostgresDriver, SqliteDriver and SqlServerDriver support, but you can extend the drivers for Database class if you implement DriverContract interface

import{DriverContract}from'@secjs/database'classCustomDriverimplementsDriverContract{privatereadonly_dbUrl: stringconstructor(connection: string){this._dbUrl=Config.get(`database.connections.${connection}.url`)}// all the methods implemented from DriverContract...}

Constructor is extremely important in your CustomDriver class, it's the constructor that will use the values from config/database connections to manipulate your CustomDriver using connection method from database. So if you are building a CustomDriver, and you want to use it, you can create a new connection inside config/database connections or change the driver from an existing connection.

// extending connections// config/database fileexportdefault{// default etc...connections: {myconnection: {driver: 'custom',url: Env('DATABASE_URL',''),}// ... other connections}}

Build you new driver using build static method

constname='custom'constdriver=CustomDriver// Will create the driverDatabase.build(name,driver)// List all Database driversconsole.log(Database.drivers())// ['mysql', 'mongo', 'sqlite', 'mssql', 'postgres', 'custom']constonlyConnected=true// List only Database drivers where there are a connection establishedconsole.log(Database.drivers(onlyConnected))// ['postgres', 'mongo']

Now, if you have implemented your connection in config/database, you can use him inside Database

// Will use CustomDriver to handle the database operations and // save the shared connection in DriverFactoryawaitdatabase.connection('myconnection').connect()console.log(Database.drivers(true))// ['postgres', 'mongo', 'custom']

---

Made with 🖤 by jlenon7 👋