sql-next

SQL-next is a wrapper around node-mysql, which provides MongoDB like queries and promise-based api.

Features

• JSON queries
• Protection from sql-injection
• Selectors (not yet done)
• Promise based api

Checkout roadmap to see what's coming.

Installing

$ npm install sql-next

Quick start

An example of finding an item:

import { Client, IConfig } from 'sql-next';
 
const config: IConfig = {
  host: 'example.com',
  user: 'username',
  password: '123',
  port: 8080,
};
 
interface Item {
  _id: number;
  header: string;
  body: string;
}
 
async function init() {
  const client = new Client();
 
  await client.connect(config);
 
  console.log('Connected!');
 
  const db = client.db('name');
  const table = db.table<Item>('tests');
 
  const item = await table.findOne({ _id: 2 });
 
  console.log(item);
}
 
init();

Output:

{
  _id: 2,
  header: 'Hello world!',
  body: 'Lorem ipsum...',
}

API

Class Client:

• Client.connect
• Client.close
• Client.db
• Client.switchUser
• Client.query

Class Database:

• Database.tables
• Database.table

Class Table:

• Table.find
• Table.findOne
• Table.count
• Table.insertOne
• Table.insert
• Table.update

Interfaces:

• IConfig
• ISSLConfig
• IQueryFilter
• IQuerySelector
• IQueryOptions

Other

• Advanced filtering

Class Client

Methods

• Client.connect(config: string | IConfig)
  Connects to mysql server.
  

  import { Client } from 'sql-next';
   
  const client = new Client();
   
  try {
    await client.connect({
      host: 'example.com',
      user: 'username',
      password: '123',
      port: 8080,
    });
   
    console.log('Connected!');
  } catch (error) {
    console.log('Failed to connect!', error);
  }

• Client.close()
  Closes connection.
  

  await client.close();
   
  console.log('Closed!');

• Client.db(name: string): Database
  Returns a new database instance that shares the same connection with Client.
  

  const client = new Client();
  const db = client.db('users');

• Client.switchUser(config: ConnectionOptions)
  Reconnects with new user credentials.
  

  const client = new Client();
   
  client.switchUser({
    user: 'seconduser',
    password: 'password',
  });

• Client.query<T>(sql: string): Promise<T>
  Performs a raw query globally.
  

  const client = new Client();
  const news = await client.query('SELECT * from `test`.`news`');

Class Database

Methods

• Database.tables(): Promise<string[]>
  Returns a list of tables in a database.
  

  import { Client } from 'sql-next';
   
  const client = new Client();
  const db = client.db('test');
  const tables = await db.tables();
   
  console.log(tables); // ['users', 'news', ...]

• Database.table<T>(name: string): Table<T>
  Returns a new table instance that shares the same connection with Client.
  

  import { Client } from 'sql-next';
   
  const client = new Client();
  const db = client.db('test');
  const table = db.table('news');
   
  const news = await table.find();
   
  console.log(news); // [{_id: 1, title: 'lorem ipsum'}, ...]

Properties

• Database.name

Class Table<T>

Methods

• Table.find(filter?: IQueryFilter<T>, options?: IQueryOptions): Promise<T[]>
  Fetches multiple items from a table. You can also set an offset or a limit, by setting options. See todo for advanced filtering.
  

  const table = db.table('news');
   
  const news = await table.find({ _authorId: 2 }, { offset: 2, limit: 10 });

• Table.findOne(filter?: IQueryFilter<T>): Promise<T[]>
  Returns a single item from a table. See todo for advanced filtering.
  

  const table = db.table('news');
   
  const item = await table.findOne({ _id: 11 });

• Table.count(filter?: IQueryFilter<T>): Promise<number>
  Counts items in a table.
  

  const table = db.table('news');
   
  const count = await table.count();
   
  console.log(count); // 141

• Table.insert(items: T[]): Promise<T[]>
  Inserts multiple items to a table and returns each of them with replaced _id property.
  

  const table = db.table('news');
   
  const [first, second] = await table.insert([
    { title: 'Hello world!' },
    { title: 'Lorem ipsum' },
  ]);
   
  console.log(first._id, second._id); // 1, 2

• Table.insertOne(items: T): Promise<T>
  Inserts a single item with replaced _id property, coresponding to added record.
  

  const table = db.table('news');
   
  const data = await table.insertOne({ title: 'Cooking tips' });
   
  console.log(data); // { _id: 3, title: 'Cooking tips' }

• Table.update(filter: IQueryFilter<T>, update: IUpdateItem<T>): Promise<T>
  Updates every items matching filter and replaces their fields with update.
  

  table.update({ _id: 1 }, { content: 'Hello world!' });

Properties

• Table.name

Interface IConfig

interface IConfig {
  user?: string;
  password?: string;
  port?: number;
  ssl?: ISSLConfig;
  charset?: string;
  insecureAuth?: boolean;
  socketPath?: string;
  debug?: boolean | string[];
  bigNumberStrings?: boolean;
  connectTimeout?: number;
  dateStrings?: boolean | ('TIMESTAMP' | 'DATETIME' | 'DATE')[];
  host?: string;
  localAddress?: string;
  supportBigNumbers?: boolean;
  timeout?: number;
  timezone?: number;
  trace?: boolean;
}

Interface ISSLConfig

import { SecureContextOptions } from 'tls';
 
export type ISSLConfig = 
  | string
  | (SecureContextOptions & {
      rejectUnauthorized?: boolean;
    });

Interface IQueryFilter

export type IQueryFilter<T> = {
  [P in keyof T]?: Partial<T[P]> | RegExp;
} &
  IQuerySelector<T>;

It means that for a type you pass, it will make every key optional and property as original or a regex expression. Also it will include selectors like $or.

Interface IQuerySelector

export interface IQuerySelector<T> {
  $or?: IQueryFilter<T>[];

Interface IQueryOptions

export interface IQueryOptions {
  limit?: number;
  offset?: number;

Other

Advanced filtering

Let's say we want to find a group of items with _authorId field equals to 2.

We can do it like this:

const table = db.table('news');
 
table.find({ _authorId: 2 });

And what if we want _categoryId to be 1.

table.find({
  _authorId: 2,
  _categoryId: 1,
});

You can see, that combining properties together works as AND selector.

There are other selectors as well.

$or

This will search for the items with _authorId = 2 and _categoryId = 1 or 2.

table.find({
  _authorId: 2,
  $or: [{ _categoryId: 1 }, { _categoryId: 2 }],
});