@secjs/database v1.1.5

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'

export default {
  /*
  |--------------------------------------------------------------------------
  | 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 database
const force = 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 connection
const saveOnDriver = true // default is true

// You can create the connection for postgres calling connect method
const database = await new Database()
  .connection('postgres')
  .connect(force, saveOnDriver)

// Now every time that you need postgres connection, you don't need to call connect again
const newDb = new Database().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 file
await Database.createConnections('postgres', 'mongo')

// Now you don't need to call connect method when changing your connection
database.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 instance
await database.close()

// 2 - Calling the static closeConnections method
await Database.closeConnections('postgres', 'mongo')

// 3 - Calling the static closeAllDrivers method
await Database.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.

const force = true
const saveOnDriver = false
const runtimeConfigs = { database: 'testing' }

const runtimeDb = new Database()
  .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 connection
await Database.closeConnections('postgres')

// So always remember closing this connection!
await runtimeDb.close()

Create/drop tables and databases

import { Knex } from 'knex'
import { Database, TableBuilder } from '@secjs/database'
import database from './database'

// All SQL Drivers from Database are using Knex as query builder and for Mongo NoSQL, mongoose.
await database.createTable('products', (tableBuilder: Knex.TableBuilder) => {
  tableBuilder.increments('id').primary()
  tableBuilder.string('name').nullable()
  tableBuilder.integer('quantity').nullable().defaultTo(0)
})

// Creating the product details table
await database.createTable('product_details', (tableBuilder: Knex.TableBuilder) => {
  tableBuilder.increments('id').primary()
  tableBuilder.string('detail').nullable()
  // One product has many product details
  tableBuilder.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 database
database.connection('mongo')

// With mongo connection we can't create the table. But we can set our schema
// in buildTable method
const productSchema = new Schema({
  name: String,
  quantity: Number,
})

database.buildTable({
  name: 'Product',
  collection: 'products',
  schema: productSchema
})

// Drop table products from database
await database.dropTable('products')

// You can also create databases
await database.createDatabase('testing-database')

// Then you can create a new database instance to connect to this new database
const runtimeConfigurations = { database: 'testing-database' }

const testingDatabase = await new Database(runtimeConfigurations)
  .connection('mongo')
  // Force connection and don't save the connection 
  .connect(true, false)

// Do operations using testingDatabase....

// Close the connection with testing database
await testingDatabase.close()

// You can drop databases too
await database.dropDatabase('testing-database')

Insert products

// Insert and return an array of ID
const productIds = await database
  .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 value
  const returnKey = 'name'
  const productNames = await database
    .buildTable('products')
    .insert([{ name: 'iPhone 13' }], returnKey)

  // The returnKey can be defined only in SQL Drivers because Mongo always returns the _id by default
}

const returnKeyId = 'id'
// Insert and return an array of products objects
const products = await database
  .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:
const data = await database
  // .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 _id
const product = await database.buildWhere('id', idIphone).find()

// Find many return an array of values
const products = await database.findMany()

const page = 0
const limit = 10

// Find many products paginated and return meta and links
const { data, meta, links } = await database.paginate(page, limit, '/products')

// Find many products paginated and return only the data
const productsPaginated = await database.forPage(page, limit)

Update products

const productIds = await database.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 value
  const returnKey = 'name'
  const productNames = await database
    .buildTable('products')
    .update([{ name: 'iPhone 13' }], returnKey)

  // The returnKey can be defined only in SQL Drivers because Mongo always returns the _id by default
}

const returnKeyId = 'id'
// WARN - Be carefull with update, remember to always use where
const productsUpdated = await database
  .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

const productIds = await database.insert([{ name: 'iPhone 10' }, { name: 'iPhone 11' }])

// Be carefull with delete, remember to always use where
await database
  .buildWhereIn('id', productIds)
  .delete()

Join relations

const productIds = await database.insert([{ name: 'iPhone 10' }, { name: 'iPhone 11' }])

// Creating a new detail for each product created
const promisesPending = productIds.map(productId => (database.buildTable('product_details').insert({
  detail: '64 GB',
  productId
})))

// Resolving the array of promises
await Promise.all(promisesPending)

const productsWithDetails = await database
  .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 object
console.log(productsWithDetails) // [{ id: 1, name: 'iPhone 10', quantity: 0, product_details: [{ id: 1, detail: '64 GB', productId: 1 }] }, ...]

Order By

await database.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 mode
const products = await database
  .buildTable('products')
  .buildSelect('name', 'quantity')
  .buildOrderBy('quantity', 'desc')
  .findMany()

console.log(products[0].name) // 'iPhone 8'

Group By and Having

await database.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 },
])


const products = await database
  .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 transaction
const trx: Transaction = await database.beginTransaction()

await trx
  .buildTable('products')
  .insert({ name: 'AirPods 3' })

try {
  // Do some process that could fail...

  // Commit the transaction if process does not fail
  await trx.commit()
} catch (error) {
  // Handle the error...

  // Rollback the transaction and all the operations done in the database
  await trx.rollback()
}

Pluck column values

await database
  .buildTable('products')
  .insert([{ name: 'Apple Watch Series 2' }, { name: 'Apple Watch Series 3' }])

// Pluck get the value of only one column
const productsName = await database.pluck('name')

console.log(productsName) // ['Apple Watch Series 2', 'Apple Watch Series 3']

Min & Max

await database.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 table
console.log(await database.max('quantity')) // 40
// Get the min value from column quantity in products table
console.log(await database.min('quantity')) // -40

Increment & Decrement

const ids = await database.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 quantity
await database.buildWhere('_id', ids[1]).increment('quantity', 20)
// Decrementing product quantity
await database.buildWhere('_id', ids[2]).decrement('quantity', 20)

// 10 + 20 = 30
console.log(await database.buildWhere('_id', ids[1]).find()) // { name: 'iPhone 2', quantity: 30 }

// 20 - 20 = 40
console.log(await database.buildWhere('_id', ids[2]).find()) // { name: 'iPhone 3', quantity: 0 }

Count & Count Distinct

await database
  .buildTable('products')
  .insert([
    { name: null },
    { name: 'Apple Watch Series 2' },
    { name: 'Apple Watch Series 2' },
    { name: 'Apple Watch Series 3' },
  ])

// Count all table rows
console.log(await database.count('*')) // 4

// Count only where name column exists in table products
console.log(await database.count('name')) // 3

// Count only where name column exists in table products and the values from name are distinct
console.log(await database.countDistinct('name')) // 2

Sum & Sum Distinct

await database.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 table
console.log(await database.sum('quantity')) // 100

// Sum all the values from the column quantity in products table only where quantity values are distinct
console.log(database.sumDistinct('quantity')) // 60

Avg & Avg Distinct

await database.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 products
console.log(await database.avg('quantity')) // 16.666666666666668

// Get the average of all the values from column quantity in table products where quantity values are distinct
console.log(await database.avgDistinct('quantity')) // 12

Raw queries

await database
  .buildTable('products')
  .insert({ name: 'iPhone X' })

// Using Knex
const knexRaw = await database.raw('SELECT * FROM ??;', ['products'])

console.log(knexRaw) // { command: 'SELECT', rowCount: 1, rows: [{ id: 1, name: 'iPhone X' }] }

// Using Mongoose
await database.connection('mongo').connect()

const mongooseRaw = await database.raw('db.collection(??).find().toArray()', ['products'])

console.log(mongooseRaw)  // { command: 'find()', rowCount: 1, rows: [{ id: 1, name: 'iPhone X' }] }

Get information from columns

const columnInfo = await database.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 products
database.buildTable('products')

// Clone the database query chain, this will create a new instance of the Database class
// but with the exactly same query chain.
const clonedDatabase = database.clone()

console.log(database === clonedDatabase) // false

// This insert will be done in products table because of database.buildTable
const arrayOfIds = await clonedDatabase.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 using
const { client } = await database.cloneQuery<Knex.QueryBuilder>()

const arrayOfIds = await client.insert({ name: 'AirPods 2' }, 'id')

{
  // For mongoose you can set the Schema as type
  database.connection('mongo')

  const { client, session } = await database.cloneQuery<UserSchema>()

  // If using session...
  const product = await client.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'

class CustomDriver implements DriverContract {
  private readonly _dbUrl: string

  constructor(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 file

export default {
  // default etc...

  connections: {
    myconnection: {
      driver: 'custom',
      url: Env('DATABASE_URL', ''),
    }
    // ... other connections
  }
}

Build you new driver using build static method

const name = 'custom'
const driver = CustomDriver

// Will create the driver
Database.build(name, driver)

// List all Database drivers
console.log(Database.drivers()) // ['mysql', 'mongo', 'sqlite', 'mssql', 'postgres', 'custom']

const onlyConnected = true

// List only Database drivers where there are a connection established
console.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 DriverFactory
await database.connection('myconnection').connect()

console.log(Database.drivers(true)) // ['postgres', 'mongo', 'custom']

Made with 🖤 by jlenon7 :wave: