kandi background
Explore Kits

knex | query builder for PostgreSQL , MySQL , CockroachDB , SQL Server | SQL Database library

 by   knex JavaScript Version: 1.0.7 License: MIT

 by   knex JavaScript Version: 1.0.7 License: MIT

Download this library from

kandi X-RAY | knex Summary

knex is a JavaScript library typically used in Database, SQL Database, Nodejs applications. knex has no bugs, it has a Permissive License and it has medium support. However knex has 1 vulnerabilities. You can install using 'npm i knex' or download it from GitHub, npm.
A SQL query builder that is flexible, portable, and fun to use!.
Support
Support
Quality
Quality
Security
Security
License
License
Reuse
Reuse

kandi-support Support

  • knex has a medium active ecosystem.
  • It has 15613 star(s) with 1875 fork(s). There are 201 watchers for this library.
  • There were 2 major release(s) in the last 6 months.
  • There are 560 open issues and 2751 have been closed. On average issues are closed in 301 days. There are 57 open pull requests and 0 closed requests.
  • It has a neutral sentiment in the developer community.
  • The latest version of knex is 1.0.7
knex Support
Best in #SQL Database
Average in #SQL Database
knex Support
Best in #SQL Database
Average in #SQL Database

quality kandi Quality

  • knex has 0 bugs and 0 code smells.
knex Quality
Best in #SQL Database
Average in #SQL Database
knex Quality
Best in #SQL Database
Average in #SQL Database

securitySecurity

  • knex has 1 vulnerability issues reported (1 critical, 0 high, 0 medium, 0 low).
  • knex code analysis shows 0 unresolved vulnerabilities.
  • There are 0 security hotspots that need review.
knex Security
Best in #SQL Database
Average in #SQL Database
knex Security
Best in #SQL Database
Average in #SQL Database

license License

  • knex is licensed under the MIT License. This license is Permissive.
  • Permissive licenses have the least restrictions, and you can use them in most projects.
knex License
Best in #SQL Database
Average in #SQL Database
knex License
Best in #SQL Database
Average in #SQL Database

buildReuse

  • knex releases are available to install and integrate.
  • Deployable package is available in npm.
  • Installation instructions are not available. Examples and code snippets are available.
knex Reuse
Best in #SQL Database
Average in #SQL Database
knex Reuse
Best in #SQL Database
Average in #SQL Database
Top functions reviewed by kandi - BETA

kandi has reviewed knex and discovered the below as its top functions. This is intended to give you an instant insight into knex implemented functionality, and help decide if they suit your requirements.

  • Load the file
  • Monkey patch the connection
  • Initializes the knex context .
  • Adds built - in statement to builder
  • Adds properties to the knex object
  • Makes a client object .
  • Convert a date object into a string .
  • Resolves a dialect based on the specified config .
  • Initialize a batch with given data
  • Replace key bindings .

knex Key Features

transactions

connection pooling

streaming queries

both a promise and callback API

a thorough test suite

Take a look at the full documentation to get started!

Browse the list of plugins and tools built for knex

Check out our recipes wiki to search for solutions to some specific problems

In case of upgrading from an older version, see migration guide

https://github.com/Vincit/objection.js

https://github.com/mikro-orm/mikro-orm

https://bookshelfjs.org

Examples

copy iconCopydownload iconDownload
const knex = require('knex')({
  client: 'sqlite3',
  connection: {
    filename: './data.db',
  },
});

try {

  // Create a table
  await knex.schema
    .createTable('users', table => {
      table.increments('id');
      table.string('user_name');
    })
    // ...and another
    .createTable('accounts', table => {
      table.increments('id');
      table.string('account_name');
      table
        .integer('user_id')
        .unsigned()
        .references('users.id');
    })

  // Then query the table...
  const insertedRows = await knex('users').insert({ user_name: 'Tim' })

  // ...and using the insert id, insert into the other table.
  await knex('accounts').insert({ account_name: 'knex', user_id: insertedRows[0] })

  // Query both of the rows.
  const selectedRows = await knex('users')
    .join('accounts', 'users.id', 'accounts.user_id')
    .select('users.user_name as user', 'accounts.account_name as account')

  // map over the results
  const enrichedRows = selectedRows.map(row => ({ ...row, active: true }))

  // Finally, add a catch statement
} catch(e) {
  console.error(e);
};

TypeScript example

copy iconCopydownload iconDownload
import { Knex, knex } from 'knex'

interface User {
  id: number;
  age: number;
  name: string;
  active: boolean;
  departmentId: number;
}

const config: Knex.Config = {
  client: 'sqlite3',
  connection: {
    filename: './data.db',
  },
};

const knexInstance = knex(config);

try {
  const users = await knex<User>('users').select('id', 'age');
} catch (err) {
  // error handling
}

Usage as ESM module

copy iconCopydownload iconDownload
import { knex } from 'knex/knex.mjs'

422 error trying to save json data to the database

copy iconCopydownload iconDownload
await existing_post.save({
    ...
    attachments: JSON.stringify(post.acf.attachments),
)};
const acf = await knex('posts').select('acf').first();
const attachment = JSON.parse(acf.attachment;
-----------------------
await existing_post.save({
    ...
    attachments: JSON.stringify(post.acf.attachments),
)};
const acf = await knex('posts').select('acf').first();
const attachment = JSON.parse(acf.attachment;

How to mock Knex using jest

copy iconCopydownload iconDownload
import Knex from 'knex';

const knex = Knex({ client: 'mysql' });

export function main() {
  const query = knex('table_name')
    .where({
      title: 'xxx-yyy',
      lang: 'eng',
    })
    .select()
    .orderBy('date', 'desc')
    .toSQL()
    .toNative();
}
import Knex from 'knex';

jest.mock('knex');

describe('68717941', () => {
  test('should pass', () => {
    const querybuilder = {
      where: jest.fn().mockReturnThis(),
      select: jest.fn().mockReturnThis(),
      orderBy: jest.fn().mockReturnThis(),
      toSQL: jest.fn().mockReturnThis(),
      toNative: jest.fn(),
    };
    const mKnex = jest.fn().mockReturnValue(querybuilder);
    Knex.mockReturnValue(mKnex);
    const { main } = require('./');
    main();
    expect(Knex).toBeCalledWith({ client: 'mysql' });
    expect(mKnex).toBeCalledWith('table_name');
    expect(querybuilder.where).toBeCalledWith({ title: 'xxx-yyy', lang: 'eng' });
    expect(querybuilder.select).toBeCalledTimes(1);
    expect(querybuilder.orderBy).toBeCalledWith('date', 'desc');
    expect(querybuilder.toSQL).toBeCalledTimes(1);
    expect(querybuilder.toNative).toBeCalledTimes(1);
  });
});
 PASS  examples/68717941/index.test.js (8.844 s)
  68717941
    ✓ should pass (7464 ms)

----------|---------|----------|---------|---------|-------------------
File      | % Stmts | % Branch | % Funcs | % Lines | Uncovered Line #s 
----------|---------|----------|---------|---------|-------------------
All files |     100 |      100 |     100 |     100 |                   
 index.js |     100 |      100 |     100 |     100 |                   
----------|---------|----------|---------|---------|-------------------
Test Suites: 1 passed, 1 total
Tests:       1 passed, 1 total
Snapshots:   0 total
Time:        9.439 s
-----------------------
import Knex from 'knex';

const knex = Knex({ client: 'mysql' });

export function main() {
  const query = knex('table_name')
    .where({
      title: 'xxx-yyy',
      lang: 'eng',
    })
    .select()
    .orderBy('date', 'desc')
    .toSQL()
    .toNative();
}
import Knex from 'knex';

jest.mock('knex');

describe('68717941', () => {
  test('should pass', () => {
    const querybuilder = {
      where: jest.fn().mockReturnThis(),
      select: jest.fn().mockReturnThis(),
      orderBy: jest.fn().mockReturnThis(),
      toSQL: jest.fn().mockReturnThis(),
      toNative: jest.fn(),
    };
    const mKnex = jest.fn().mockReturnValue(querybuilder);
    Knex.mockReturnValue(mKnex);
    const { main } = require('./');
    main();
    expect(Knex).toBeCalledWith({ client: 'mysql' });
    expect(mKnex).toBeCalledWith('table_name');
    expect(querybuilder.where).toBeCalledWith({ title: 'xxx-yyy', lang: 'eng' });
    expect(querybuilder.select).toBeCalledTimes(1);
    expect(querybuilder.orderBy).toBeCalledWith('date', 'desc');
    expect(querybuilder.toSQL).toBeCalledTimes(1);
    expect(querybuilder.toNative).toBeCalledTimes(1);
  });
});
 PASS  examples/68717941/index.test.js (8.844 s)
  68717941
    ✓ should pass (7464 ms)

----------|---------|----------|---------|---------|-------------------
File      | % Stmts | % Branch | % Funcs | % Lines | Uncovered Line #s 
----------|---------|----------|---------|---------|-------------------
All files |     100 |      100 |     100 |     100 |                   
 index.js |     100 |      100 |     100 |     100 |                   
----------|---------|----------|---------|---------|-------------------
Test Suites: 1 passed, 1 total
Tests:       1 passed, 1 total
Snapshots:   0 total
Time:        9.439 s
-----------------------
import Knex from 'knex';

const knex = Knex({ client: 'mysql' });

export function main() {
  const query = knex('table_name')
    .where({
      title: 'xxx-yyy',
      lang: 'eng',
    })
    .select()
    .orderBy('date', 'desc')
    .toSQL()
    .toNative();
}
import Knex from 'knex';

jest.mock('knex');

describe('68717941', () => {
  test('should pass', () => {
    const querybuilder = {
      where: jest.fn().mockReturnThis(),
      select: jest.fn().mockReturnThis(),
      orderBy: jest.fn().mockReturnThis(),
      toSQL: jest.fn().mockReturnThis(),
      toNative: jest.fn(),
    };
    const mKnex = jest.fn().mockReturnValue(querybuilder);
    Knex.mockReturnValue(mKnex);
    const { main } = require('./');
    main();
    expect(Knex).toBeCalledWith({ client: 'mysql' });
    expect(mKnex).toBeCalledWith('table_name');
    expect(querybuilder.where).toBeCalledWith({ title: 'xxx-yyy', lang: 'eng' });
    expect(querybuilder.select).toBeCalledTimes(1);
    expect(querybuilder.orderBy).toBeCalledWith('date', 'desc');
    expect(querybuilder.toSQL).toBeCalledTimes(1);
    expect(querybuilder.toNative).toBeCalledTimes(1);
  });
});
 PASS  examples/68717941/index.test.js (8.844 s)
  68717941
    ✓ should pass (7464 ms)

----------|---------|----------|---------|---------|-------------------
File      | % Stmts | % Branch | % Funcs | % Lines | Uncovered Line #s 
----------|---------|----------|---------|---------|-------------------
All files |     100 |      100 |     100 |     100 |                   
 index.js |     100 |      100 |     100 |     100 |                   
----------|---------|----------|---------|---------|-------------------
Test Suites: 1 passed, 1 total
Tests:       1 passed, 1 total
Snapshots:   0 total
Time:        9.439 s
-----------------------
// my-cool-controller.ts
import { db } from '../common/db-setup';

export async function addUser(user: User): Promise<{ id }> {
  const [insertId] = await db.insert(user).into('users');

  return { id: insertId };
}
// my-cool-controller.spec.ts
import { expect } from '@jest/globals';
import knex, { Knex } from 'knex';
import { getTracker, MockClient } from 'knex-mock-client';
import faker from 'faker';

jest.mock('../common/db-setup', () => {
  return {
    db: knex({ client: MockClient })
  };
});

describe('my-cool-controller tests', () => {
  let tracker: Tracker;

  beforeAll(() => {
    tracker = getTracker();
  });

  afterEach(() => {
    tracker.reset();
  });

  it('should add new user', async () => {
    const insertId = faker.datatype.number();
    tracker.on.insert('users').response([insertId]);
    
    const newUser = { name: 'foo bar', email: 'test@test.com' };
    const data = await addUser(newUser);

    expect(data.id).toEqual(insertId);

    const insertHistory = tracker.history.insert;

    expect(insertHistory).toHaveLength(1);
    expect(insertHistory[0].method).toEqual('insert');
    expect(insertHistory[0].bindings).toEqual([newUser.name, newUser.email]);
  });
});
-----------------------
// my-cool-controller.ts
import { db } from '../common/db-setup';

export async function addUser(user: User): Promise<{ id }> {
  const [insertId] = await db.insert(user).into('users');

  return { id: insertId };
}
// my-cool-controller.spec.ts
import { expect } from '@jest/globals';
import knex, { Knex } from 'knex';
import { getTracker, MockClient } from 'knex-mock-client';
import faker from 'faker';

jest.mock('../common/db-setup', () => {
  return {
    db: knex({ client: MockClient })
  };
});

describe('my-cool-controller tests', () => {
  let tracker: Tracker;

  beforeAll(() => {
    tracker = getTracker();
  });

  afterEach(() => {
    tracker.reset();
  });

  it('should add new user', async () => {
    const insertId = faker.datatype.number();
    tracker.on.insert('users').response([insertId]);
    
    const newUser = { name: 'foo bar', email: 'test@test.com' };
    const data = await addUser(newUser);

    expect(data.id).toEqual(insertId);

    const insertHistory = tracker.history.insert;

    expect(insertHistory).toHaveLength(1);
    expect(insertHistory[0].method).toEqual('insert');
    expect(insertHistory[0].bindings).toEqual([newUser.name, newUser.email]);
  });
});

KnexTimeoutError: Knex: Timeout acquiring a connection

copy iconCopydownload iconDownload
knex.client.pool.propagateCreateError = false; 
-----------------------
cloud_sql_proxy -instances=<INSTANCE_CONNECTION_NAME>=tcp:5432
-----------------------
"express": "^4.16.2",
"knex": "^0.14.2",
"objection": "^2.1.3",
"pg": "^8.0.3",

knex Sort by JSON field values

copy iconCopydownload iconDownload
knex.table('YourTableName').orderByRaw(`data->"$.city_name" desc`);

How can I insert information from a referenced table into another table?

copy iconCopydownload iconDownload
knex("forms").returning("id").insert([
  { title: "Form 1 title", order: "1"} // id = gen_random_uuid();
  // form2, form3...
]}
  .then(function(rows) {
    for (let i = 0; i < rows.length; i++) {
      knex("questions").insert([
        {
          form_id: rows[i].id,
          title: "Question 1",
          order: "1"
        }
      ])
    }
  });

My bcrypt.js hash returns undefined while all other inputs are fine

copy iconCopydownload iconDownload
  const { email, name, hash } = req.body;
  let salt = bcrypt.genSaltSync(10);
  let hashedPassword = bcrypt.hashSync(hash, salt);
  
  knex
    .transaction((trx) => {
      trx
        .insert({
          hash: hashedPassword,
          email: email,
        })

Error trying to run seeds: Knex: Timeout acquiring a connection. The pool is probably full

copy iconCopydownload iconDownload
const rows = [];
for (let i =  0; i < 10000; i++) {
  rows.push({ name : `foo  ${i}` });
}
await Promise.all(rows.map(data => knex('user').insert(data)));

Precedence and parentheses In Join. How can I write below SQL query in objection ORM?

copy iconCopydownload iconDownload
getUnblockedUsersPosts: (builder) => {
            builder.leftJoin('blocks as b', function(){
              this.on(function(){
                this.on(function(){
                  this.on('b.blocked_id', '=', 'posts.post_author')
                  .andOn('b.blocker_id', '=', parseInt(user.id))
                })
                this.orOn(function(){
                  this.on('b.blocker_id', '=', 'posts.post_author')
                  .andOn('b.blocked_id', '=', parseInt(user.id))
                })
              })
              this.andOn('b.is_deleted', knex.raw('false'))
            }).where('b.id', 'is', null)      
          }

ObjectionJs fetch entity with many to many relationship

copy iconCopydownload iconDownload
await User
.query()
.withGraphFetched('[authorities]')
.findById(id)
.throwIfNotFound({message: "User not found."})
.then(async user=> { ...})

esbuild + knex: Cannot find module '../../dialects/mysql/index.js'

copy iconCopydownload iconDownload
import Knex from 'knex';
import KnexMysql from 'knex/lib/dialects/mysql';

const knex = Knex({
  client: KnexMysql,
  ...
});

Community Discussions

Trending Discussions on knex
  • Knex : universal way to get the last inserted id
  • 422 error trying to save json data to the database
  • How to mock Knex using jest
  • KnexTimeoutError: Knex: Timeout acquiring a connection
  • Relational database created/stored on client
  • knex Sort by JSON field values
  • Error handler middleware doesn't handle the errors
  • How can I insert information from a referenced table into another table?
  • My bcrypt.js hash returns undefined while all other inputs are fine
  • Error trying to run seeds: Knex: Timeout acquiring a connection. The pool is probably full
Trending Discussions on knex

QUESTION

Knex : universal way to get the last inserted id

Asked 2022-Apr-17 at 21:19

I'm using Knex, because I'm working on an application that I would like to use with multiple database servers, currently Sqlite3, Postgres and MySQL.

I'm realizing that this might be more difficult that I expected.

On MySQL, it appears that this syntax will return an array with an id:

knex('table').insert({ field: 'value'}, 'id');

On postgres I need something like this:

knex('table').insert({ field: 'value'}, 'id').returning(['id']);

In each case, the structure they return is different. The latter doesn't break MySQL, but on SQlite it will throw a fatal error.

The concept of 'insert a record, get an id' seems to exist everywhere though. What am I missing in Knex that lets me write this once and use everywhere?

ANSWER

Answered 2022-Apr-17 at 21:19

Way back in 2007, I implemented the database access class for a PHP framework. It was to support MySQL, PostgreSQL, SQLite, Microsoft SQL Server, Oracle, and IBM DB2.

When it came time to support auto-incremented columns, I discovered that all of these implement that feature differently. Some have SERIAL, some have AUTO-INCREMENT (or AUTOINCREMENT), some have SEQUENCE, some have GENERATED, some support multiple solutions.

The solution was to not try to write one implementation that worked with all of them. I wrote classes using the Adapter Pattern, one for each brand of SQL database, so I could implement each adapter class tailored to the features supported by the respective database. The adapter satisfied an interface that I defined in my framework, to allow the primary key column to be defined and the last inserted id to be fetched in a consistent manner. But the internal implementation varied.

This was the only sane way to develop that code, in my opinion. When it comes to variations of SQL implementations, it's a fallacy that one can develop "portable" code that works on multiple brands.

Source https://stackoverflow.com/questions/71904883

Community Discussions, Code Snippets contain sources that include Stack Exchange Network

Vulnerabilities

No vulnerabilities reported

Install knex

You can install using 'npm i knex' or download it from GitHub, npm.

Support

For any new features, suggestions and bugs create an issue on GitHub. If you have any questions check and ask questions on community page Stack Overflow .

DOWNLOAD this Library from

Find, review, and download reusable Libraries, Code Snippets, Cloud APIs from
over 430 million Knowledge Items
Find more libraries
Reuse Solution Kits and Libraries Curated by Popular Use Cases

Save this library and start creating your kit

Explore Related Topics

Share this Page

share link
Consider Popular SQL Database Libraries
Compare SQL Database Libraries with Highest Support
Compare SQL Database Libraries with Highest Quality
Compare SQL Database Libraries with Highest Security
Compare SQL Database Libraries with Permissive License
Find, review, and download reusable Libraries, Code Snippets, Cloud APIs from
over 430 million Knowledge Items
Find more libraries
Reuse Solution Kits and Libraries Curated by Popular Use Cases

Save this library and start creating your kit

  • © 2022 Open Weaver Inc.