herbsjs / herbs2knex

Create repositories using Knex to retrieve and store Entities

Website

This website is built using Docusaurus 2, a modern static website generator.

Installation

yarn install

Local Development

yarn start

This command starts a local development server and open up a browser window. Most changes are reflected live without having to restart the server.

Build

yarn build

This command generates static content into the build directory and can be served using any static contents hosting service.

Deployment

GIT_USER=<Your GitHub username> USE_SSH=true yarn deploy

If you are using GitHub pages for hosting, this command is a convenient way to build the website and push to the gh-pages branch.

herbs2knex's Issues

Mocks in the unit test suite do not work when you use more than one method in the same test.

Describe the bug
Mocks in the unit test suite do not work when you use more than one method in the same test.
Example: If you want to test in a method the limit with orderBy or where with offset, won't work.

To Reproduce
Steps to reproduce the behavior:

1. Go to find suite test, and let's check the should return entities with column order by method:

   herbs2knex/test/queries/find.js

   Line 81 in c2ed159

   it('should return entities with collumn order by', async () => {

2. If you add a limit clause into this method, like this:

//when
const ret = await itemRepo.find({ limit: 1,  orderBy: 'stringTest' })

3. Then the test will break showing about the query.orderBy is not a function

Expected behavior
I expect that multiple causes in the test runner cases work's fine together

Additional context
I believe that the problem is because how the methods works overriding the knex(mock) instance to join multiple conditions in the same query.

    if (options.limit > 0) query = query.limit(options.limit)
    if (options.offset > 0) query = query.offset(options.offset)

See the find method in the image:

When the query var, receives a new query instance, the order methods that was inside the knex instance, no longer exists

Implementation of FindWithPages

Hi!

I'm implementing findWithPages using knex builder. I will open Pull Request!

/** 
  *
  * Find data using pagination
  * 
  * @param {type}   object.limit Limit items to list
  * @param {type}   object.offset List items offset
  * @param {type}   object.orderBy Order by query
  *
  * @return {type} List of entities
  */
  async findWithPage(options = {
    orderBy: [],
    offset: 1,
    limit: 10
  }) {

    options.orderBy = options.orderBy || []
    options.offset = options.offset || 1
    options.limit = options.limit || 10

    const tableFields = this.dataMapper.tableFields()

    if (!options.orderBy || typeof options.orderBy === "object" && !Object.keys(options.orderBy)) throw "order by is invalid"

    let query = this.runner
      .select(tableFields)

    if (options.offset > 0) query = query.offset(options.offset)
    if (options.limit > 0) query = query.limit(options.limit)
    if (!isEmpty(options.orderBy)) query = query.orderBy(options.orderBy)

    const entities = []
    const ret = await query

    for (const row of ret) {
      if (row === undefined) continue
      entities.push(this.dataMapper.toEntity(row))
    }

    let count = await this.runner
      .count('* as count')
      .first()

    return {
      total: count,
      per_page: options.limit,
      to: offset + entities.length,
      last_page: Math.ceil(count / options.limit),
      current_page: page,
      from: options.offset,
      data: entities
    }
  }

Update use case

Create update use case to modify existing data in a table.

Insert - Entity with 1:1 relationships with other entity

Given:

const User =
        entity('User', {
          id: id(Number),
          description: field(String),
        })

const Customer =
        entity('Customer', {
          id: id(Number),
          description: field(String),
          user: field(User)   // <---- relationship 1:1
        })

class CustomerRepository extends Repository {
    constructor(injection) {
        super({
            entity: Customer,
            table: "customer",
            knex: connection,
            foreignKeys: [{ userId: String }], // <---- relationship 1:1 on DB
        })
    }
}

when:

const customer = Customer.fromJSON({ id: 1, description: "C1", user: { id: 2 } })
const repo = new CustomerRepository()
const ret = await repo.insert(customer)

it should insert not only the fields for customer table (id and description) but also user_id

The library should have first method

Is your feature request related to a problem? Please describe.
The library should have a first method to return the top one element of a query

Implementation of findAll

Hi!

I think findAll should be like findBy, but changing to return all records, correct?

I'm implementing this solution for getAll():

async findAll() {

    const tableFields = this.dataMapper.tableFields()    

    const ret = await this.runner
      .select(tableFields);      

    const entities = []

    for (const row of ret) {
      if (row === undefined) continue
      entities.push(this.dataMapper.toEntity(row))
    }

    return entities
  }

Be able to change conventions used by the Repository class methods

Is your feature request related to a problem? Please describe.
Herbs2knex uses Snake case as convention for naming database table fields. But not every project uses this convention by default. In the project I'm working we use Camel Case in database table fields. Herbs2knex should be able to change conventions used by the Repository class methods.

I believe that snake case and camel case would be enough at the time.

Describe the solution you'd like

const { Repository } = require('@herbsjs/herbs2knex')
const connection = require('connection')  // Knex initialize instance
const { ProductItem } = require('../domain/entities/productItem')

class YourRepository extends Repository {
    constructor() {
        super({
            entity: ProductItem,
            schema: 'main',
            table: 'product_items',
            ids: ['id'],
            foreignKeys: [{ customerId: String }],
            knex: connection,
            //  Configure the convention
            convention: 'snakeCase' // options available: snakeCase and camelCase 
        })
    }
}

node-sqlite3 does not currently support RETURNING clause

Describe the bug
The method insert used inside herbs2knex has the returning clause wich is not supported by sqllite3. So calling the insert method the inserted id not returns

Expected behavior
The id of the inserted objects should return

I'm using this config:

 client: 'sqlite3',
    connection: { filename: "file:memDb1?mode=memory&cache=shared" },
    useNullAsDefault: true,
    migrations: {
      directory: './src/infra/db/migrations',
      tableName: 'knex_migrations'
    }

Need to fix the exportation

When I was trying to import the project as a lib to use, I saw this problem. The main file doesn't exist.

Automatically identify pk and fks into repository layer

Following the issue herbsjs/herbs-cli#26

I would like to have an automatic PK and FK identification for my herbs2knex repositories

e.g:

USERS_TABLE	PRODUCTS_TABLE	SELES_TABLE
id name email 12 italo [email protected] 13 jhow [email protected] 14 david [email protected]	id name price 3 mouse 50 4 monitor 200 5 keyboard 40	id userId productId 34 12 3 35 12 5 36 13 3

Today we have to explicity inform what is my PK and my FK with ids and foreignKeys keys

entity("Sale", {
    id: field(Number),
    product: field(Product),
    user: field(User), 
})
// ----------------------------------------
class SaleRepository extends Repository {    
   constructor() {       
       super({            
         entity: Sale,            
         table: 'SALES_TABLE',            
         ids: ['id'], 
         foreignKeys: [{ userId: Number, productId: Number }], 
         knex: connection        
       })   
     }
}

Instead write foreignKeys: [{ userId: Number, productId: Number }] and ids: ['id'] I would like the herbs2knex identify my FKs and Pks automagically.

My sugestion

• When we don't specify our ID/PK the herbs2knex will look for id property into father entity(Sale)
• When we don't specify our FKs the herbs2knex will look each father's property and for each entity will look for an id property

class SaleRepository extends Repository {    
   constructor() {       
       super({            
         entity: Sale,            
         table: 'SALES_TABLE', 
         knex: connection        
       })   
     }

If we need to have complex fields like :

entity("User", {
    id: field(Number),
    [...]
    address: field(Address)
})

we can check if this have an ID property, if doesn't we just don't process it as an FK.
it makes match with herbsjs/gotu#46

future possibilities

When we identify a entity that have relations inside, we can make "lazy load/insert" into repositories too, example:

entity("Sale", {
    id: field(Number),
    product: field(Product),
    user: field(User), 
})
// ---------------------------------
const entity = saleRepository.FindByID(123)
/*
id: 123
product: {
    id: 23,
    name: 'mouse',
    price: 123.23
},
user: {
   id: 155,
   name: 'italo',
   email: '[email protected]'
}
*/

"Cannot read properties of undefined" error when entity has functions

Describe the bug
When an entity has functions, a TypeError: Cannot read properties of undefined (reading 'isId') occurs.

I verified that this occurs in the getEntityIds function, as the entity fields are obtained through entity.prototype.meta.schema, but before filtering the id fields, you must go through this list again to filter the functions, as functions do not have the options field, resulting in an attempt to read the property of an undefined.

To Reproduce
Create a project with Herbs2Knex, whose entity has functions, then try to run the project, making insertions and searches in the database.

Expected behavior
The behavior is that this exception is not thrown.

Screenshots

Additional context
I believe this can be resolved by adding a .filter((field) => typeof field != 'function') in the idFields constant.

Implementation of a query builder to improve the power

I believe that we need to implement a powerful query builder to increase the possibilities, like the knex.

A tool like this provides a bridge to any database, without increase too much the time expended in the adaptations...

In addition to other facilities, like Seeds and Migrations...

Some ideas

I made this code, thinking about how to start a query builder or something, this code is an implementation of a pagination, it works, but to insert WHERE, ORDER or any other statement this code would be extremely complex.

Then I worked on a research to understand how Knex and other ORMs do that magic. I saw just the tip of the iceberg, but I think I understand how is the logic. The implementation is hard because of the amount of details.

Anyway I think we can do something simple, to cover pagination, order and these statements that don't involve logic like Where and Join, in this moment. But preparing to implement these in a second moment.

 async findAll({ pagination }) {

        const dataMapper = this.dataMapper;
        const tableIDs = dataMapper.getTableIDs();
        const tableFields = dataMapper.getTableFields();
        const countResult = await this.count();
        const totalPages = Math.ceil(countResult / pagination.pageSize);
        const pagePosition = pagination.pageSelected * pagination.pageSize;

        const sql = \`SELECT ${tableFields.join(", ")} FROM ${
          this.tableQualifiedName
        } ORDER BY ${tableIDs[0]} LIMIT $1 OFFSET $2
        ;`;

        const ret = await this.query(sql, [pagination.pageSize, pagePosition]);
        const entities = [];
        for (const row of ret.rows) {
          if (row === undefined) continue;
          this.dataMapper.load(row);
          entities.push(this.entity.fromJSON(this.dataMapper));
        }

        return { entities, totalPages };
}

The library should have last method

Is your feature request related to a problem? Please describe.
The library should have a last method to return the top one element of a descending order query

ID field: use ID metadata from entity

With the upcoming release of the "ID field" feature (herbsjs/gotu#46), it would be possible to read the entity's metadata and, as a convention, use those IDs that today need to be explicitly defined.

class ItemRepository extends Repository {
    constructor() {
        super({
            entity: Item,
            table: 'aTable',
            ids: ['id'],              // <---- not necessary
            knex: connection
        })
    }

Explicitly defining IDs would still be possible, but not mandatory for entities with IDs.

herbs2knex dont works for singleton

I noted when I use the same instance for multiple actions on database, some queries just get in bug after first database operation.

Here I wrote an demo that I did an insert, findbyid and update but the update method throw an exception

TypeError: Cannot read property 'id' of undefined
at Object.get [as id] (/Users/italojs/dev/herbjs/herbs2knex/xp/node_modules/herbs2knex/src/dataMapper.js:121:48)
at Function.fromJSON (/Users/italojs/dev/herbjs/herbs2knex/xp/node_modules/gotu/src/baseEntity.js:119:96)
at DataMapper.toEntity (/Users/italojs/dev/herbjs/herbs2knex/xp/node_modules/herbs2knex/src/dataMapper.js:19:28)
at UserRepository.update (/Users/italojs/dev/herbjs/herbs2knex/xp/node_modules/herbs2knex/src/repository.js:164:28)
at processTicksAndRejections (internal/process/task_queues.js:93:5)
at async main (/Users/italojs/dev/herbjs/herbs2knex/xp/index.js:48:21)

I believe it can be an bug.
To solve it, basically we can

1. Removes the singleton runner into Repository
2. Creates a new Knex Builder for each query like:

[...]
  async insert(entityInstance) {
   [...]
    const ret = await this.knex(this.tableQualifiedName) // here
      .returning(fields)
      .insert(payload)
    [...]
  }

  async update(entityInstance) {
    [...]
    const ret = await this.knex(this.tableQualifiedName) // here
      .where(tableIDs[0], entityInstance[tableIDs[0]])
      .returning(fields)
      .update(payload)

    [...]
  }

[...]

Implement find last

Hello everyone!

We need to implement find last in herbs2knex repository, to find the last element for a query.

Describe the solution you'd like

Implement find last method with filters bellow:

• orderBy
• where

Describe alternatives you've considered

async last (
   options = {
     orderBy: null,
     where: null
   }
 ) {
   options.orderBy = options.orderBy || null
   options.where = options.where || null

   const tableFields = this.dataMapper.tableFields()

   let query = this.runner().first(['timestamp', ...tableFields])

   if (!Array.isArray(options.orderBy)) options.orderBy = [options.orderBy]
   options.orderBy = [...options.orderBy, ['timestamp', 'desc']]

   return this.#executeFindQuery(query, options)
 }

Additional context

This new method uses first method to get data from data base and return the last element using timestamp to control the order of the given records.

Herbs2knex should connect into mysql

Is your feature request related to a problem? Please describe.
Herbs2knex should connect into mysql

Describe the solution you'd like
MySQL connection layer