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

sqlit

v2.0.0

Published

Sqlit - A library that makes database access easy and fun

Downloads

44

Readme

sqlit 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.

Installation

$ npm install sqlit

Usage

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

const Database = require('sqlit').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 sqlit 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: '*'
    }
  }
});

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();
});

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.

Command line interface

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

To dump the database schema into a json file:

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

Debugging

Using Chrome

  1. Put a debugger statement in the source code
  2. Start node:

$ node --inspect-brk ./node_modules/jest/bin/jest.js --runInBand

  1. Visit chrome://inspect