npm package discovery and stats viewer.

Discover Tips

  • General search

    [free text search, go nuts!]

  • Package details

    pkg:[package-name]

  • User packages

    @[username]

Sponsor

Optimize Toolset

I’ve always been into building performant and accessible sites, but lately I’ve been taking it extremely seriously. So much so that I’ve been building a tool to help me optimize and monitor the sites that I build to make sure that I’m making an attempt to offer the best experience to those who visit them. If you’re into performant, accessible and SEO friendly sites, you might like it too! You can check it out at Optimize Toolset.

About

Hi, 👋, I’m Ryan Hefner  and I built this site for me, and you! The goal of this site was to provide an easy way for me to check the stats on my npm packages, both for prioritizing issues and updates, and to give me a little kick in the pants to keep up on stuff.

As I was building it, I realized that I was actually using the tool to build the tool, and figured I might as well put this out there and hopefully others will find it to be a fast and useful way to search and browse npm packages as I have.

If you’re interested in other things I’m working on, follow me on Twitter or check out the open source projects I’ve been publishing on GitHub.

I am also working on a Twitter bot for this site to tweet the most popular, newest, random packages from npm. Please follow that account now and it will start sending out packages soon–ish.

Open Software & Tools

This site wouldn’t be possible without the immense generosity and tireless efforts from the people who make contributions to the world and share their work via open source initiatives. Thank you 🙏

© 2024 – Pkg Stats / Ryan Hefner

sqlex

v3.2.7

Published

Sqlex - A library that makes database access easy and fun

Downloads

126

Readme

sqlex is a library to help retrieve, update, import and export data from a relational database easily. Apart from standard database relations like one-to-one, many-to-one and many-to-many, it also has built-in support for hierarchical data (trees) using closure tables, including cloning a tree rooted at a specific object. When running raw SQL queries, placeholders can be used for both positional (?) and named (e.g :id) parameters, and parameter values can be either simple Javascript values or arrays.

Supported databases: MySQL, Postgres, SQLite

Installation

$ npm install sqlex

Usage

The easiest way to use sqlex is to create an instance of Database by passing in your database connection details:

const Database = require('sqlex').Database;

const db = new Database({
  dialect: 'mysql',
  connection: {
    user: 'root',
    password: 'secret',
    database: 'example',
    timezone: 'Z',
    connectionLimit: 10
  }
});

After creating the database instance, you'll need to let sqlex retrieve the schema information about your database:

await db.buildSchema();

Now the database instance is ready for use. Let's play with some data.

Making queries

Select the 2nd 10 products ordered by their names:

const db = helper.connectToDatabase(NAME);
const options = {
  where: {
    name_like: '%Apple'
  },
  orderBy: 'name',
  offset: 10,
  limit: 10
};
db.table('product').select('*', options);

This example selects all rows from the order table of your database along with the user details and items in that order:

const rows = await db.table('order').select({
  user: '*',
  orderItems: {
    fields: {
      product: '*'
    }
  }
});

Views and aggregate functions are supported:

const rows = await db.select({
 fields: [
    'oi.order.user.email as userEmail',
    'product.name',
    'count(*)',
    'avg(product.price) as price',
  ],
  from: {
    table: 'order_item oi',
    joins: [
      {
        table: 'product',
        on: `oi.product_id = product.id`,
      },
      {
        table: 'service_log sl',
        on: `sl.product_code = product.sku`,
      },
    ],
  },
  groupBy: ['oi.order.user.email', 'product.name'],
  where: { 'product.name_like': '%Apple%' },
};
expect(typeof rows[0].userEmail).toBe('string');
expect(typeof rows[0].price).toBe('number');

Updating

This example creates a new category and then renames its name:

db.table('category')
  .insert({ name: 'Ice' })
  .then(id => {
    expect(id).toBeGreaterThan(0);
    db.table('category')
      .update({ name: 'Ice Cream' }, { name: 'Ice' })
      .then(() => {
        db.table('category')
          .select('*', { where: { id } })
          .then(rows => {
            expect(rows.length).toBe(1);
            expect(rows[0].name).toBe('Ice Cream');
            done();
          });
      });
  });

This example does an upsert to the order table:

  table.upsert(
      {
        user: { connect: { email: '[email protected]' } },
        code: `test-order-${ID}`
      },
      {
        user: { create: { email: '[email protected]' } },
        code: `test-order-${ID}2`
      }
    );
  }

This example creates a category and populates it with some products:

const data = {
  name: 'Vegetable',
  parent: {
    connect: {
      id: 1
    }
  },
  categories: {
    create: [
      {
        name: 'Cucumber'
      },
      {
        name: 'Tomato'
      }
    ],
    connect: [
      { parent: { id: 2 }, name: 'Apple' },
      { parent: { id: 2 }, name: 'Banana' }
    ]
  }
};

await table.create(data);

This example creates a category tree:

  const root = await table.create({
    name: 'All',
    parent: null
  });

  const fruit = await table.create({
    name: 'Fruit',
    parent: { connect: { id: root.id } }
  });

  const apple = await table.create({
    name: 'Apple',
    parent: { connect: { id: fruit.id } }
  });

  await table.create({
    name: 'Fuji',
    parent: { connect: { id: apple.id } }
  });

  await table.create({
    name: 'Gala',
    parent: { connect: { id: apple.id } }
  });

Bulk loading/updating

You don't have to wait for an object to be persisted to the database before referencing it. Objects can even have cyclic references before they are persisted.

This example create a user and an order which reference each other (note the user.status = order line is for demo purposes only):

const user = db.table('user').append();
user.email = 'random';
const order = db.table('order').append({ code: 'random' });
order.user = user;
user.status = order;

db.flush().then(async () => {
  const user = await db.table('user').get({ email: 'random' });
  const order = await db.table('order').get({ code: 'random' });
  expect(user.status).toBe(order.id);
  done();
});

Creating testing records

sqlex provides an extremely way to create table records for testing purposes and to delete the records afterwards. Here is an example to create an order with 2 items:

 const order = await db.table('order').mock({
      orderItems: [
        {
          product: {
            price: 0,
          },
          quantity: 100,
        },
        {
          quantity: 200,
        },
      ],
    });
// making tests...
// ...
await db.cleanup();

You only need to specify fields that you are goint to test against, and sqlex will automatically populate other fields that cannot be null. When you are done with the test records, db.cleanup() will delete them from the database so that they don't affect other tests.

Importing and exporting

This example populates the category table with a list of objects. Properties other than name, parent_name, and parent_parent are saved to a table called category_attribute table using a key/value fashion:

const table = db.table('category');

const config = {
  category: {
    name: 'name',
    parent_name: 'parent.name',
    parent_parent: 'parent.parent',
    '*': 'categoryAttributes[name, value]'
  }
};

const data = [
  {
    categoryName: 'Example B1',
    parent_name: 'Example B1 Parent',
    parent_parent: null,
    colour: 'Red',
    weight: '100kg'
  }
];

await table.load(data, config.category);

This example extracts data from a number of tables and returns a flat list of objects:

const config = {
  name: 'name',
  parent_name: 'parent.name',
  product_name: 'products.name',
  product_price: 'products.price',
  '*': 'categoryAttributes[name, value]'
};

const docs = await db.table('category').xselect(config);

More examples can be found in the test folder.

Making raw SQL queries with placeholders

A database object has a query method for raw SQL queries. Positional parameters to the query can be provided using placeholder ?:

await db.query("select * from user where email = ? and status = ?", email, status);

Sqlex also supports named parameters when parameter values are stored in an object:

db.query("select * from user where email = :email and status = :status", users);

Parameter values can be simple Javascript values or arrays:

db.query("update user set status = 0 where id in (?)", [1, 2, 3]);

// or:
db.query("update user set status = 0 where id in (:id)", {id: [1, 2, 3]});

Command line interface

sqlex comes with a command line tool that does a few useful things.

To dump the database schema into a json file:

node_modules/sqlex/bin/sqlex.js --dialect postgres -u user1 -p secret1 mydb

Development

Testing

# Test for SQLite
$ DB_TYPE=sqlite3 npm run test

# Test for Postgres
$ DB_TYPE=postgres DB_USER=postgres npm run test

# Test for MySQL
$ DB_TYPE=mysql DB_USER=root DB_PASS=secret npm run test

# Test for generic driver
$ DB_TYPE='generic' SQLEX_DRIVER='/path/to/sqlex.node' npm run test