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

1$ npm install sql-next

Quick start

An example of finding an item:

1import { Client, IConfig } from 'sql-next';
2
3const config: IConfig = {
4  host: 'example.com',
5  user: 'username',
6  password: '123',
7  port: 8080,
8};
9
10interface Item {
11  _id: number;
12  header: string;
13  body: string;
14}
15
16async function init() {
17  const client = new Client();
18
19  await client.connect(config);
20
21  console.log('Connected!');
22
23  const db = client.db('name');
24  const table = db.table<Item>('tests');
25
26  const item = await table.findOne({ _id: 2 });
27
28  console.log(item);
29}
30
31init();

Output:

1{

2  _id: 2,

3  header: 'Hello world!',

4  body: 'Lorem ipsum...',
5}

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.
  

  1import { Client } from 'sql-next';
  2
  3const client = new Client();
  4
  5try {
  6  await client.connect({
  7    host: 'example.com',
  8    user: 'username',
  9    password: '123',
  10    port: 8080,
  11  });
  12
  13  console.log('Connected!');
  14} catch (error) {
  15  console.log('Failed to connect!', error);
  16}

• Client.close()
  Closes connection.
  

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

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

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

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

  1const client = new Client();
  2
  3client.switchUser({
  4  user: 'seconduser',
  5  password: 'password',
  6});

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

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

Class Database

Methods

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

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

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

  1import { Client } from 'sql-next';
  2
  3const client = new Client();
  4const db = client.db('test');
  5const table = db.table('news');
  6
  7const news = await table.find();
  8
  9console.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.
  

  1const table = db.table('news');
  2
  3const 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.
  

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

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

  1const table = db.table('news');
  2
  3const count = await table.count();
  4
  5console.log(count); // 141

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

  1const table = db.table('news');
  2
  3const [first, second] = await table.insert([
  4  { title: 'Hello world!' },
  5  { title: 'Lorem ipsum' },
  6]);
  7
  8console.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.
  

  1const table = db.table('news');
  2
  3const data = await table.insertOne({ title: 'Cooking tips' });
  4
  5console.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.
  

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

Properties

• Table.name

Interface IConfig

1interface IConfig {
2  user?: string;
3  password?: string;
4  port?: number;
5  ssl?: ISSLConfig;
6  charset?: string;
7  insecureAuth?: boolean;
8  socketPath?: string;
9  debug?: boolean | string[];
10  bigNumberStrings?: boolean;
11  connectTimeout?: number;
12  dateStrings?: boolean | ('TIMESTAMP' | 'DATETIME' | 'DATE')[];
13  host?: string;
14  localAddress?: string;
15  supportBigNumbers?: boolean;
16  timeout?: number;
17  timezone?: number;
18  trace?: boolean;
19}

Interface ISSLConfig

1import { SecureContextOptions } from 'tls';
2
3export type ISSLConfig =
4  | string
5  | (SecureContextOptions & {
6      rejectUnauthorized?: boolean;
7    });

Interface IQueryFilter

1export type IQueryFilter<T> = {
2  [P in keyof T]?: Partial<T[P]> | RegExp;
3} &
4  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

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

Interface IQueryOptions

1export interface IQueryOptions {
2  limit?: number;
3  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:

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

And what if we want _categoryId to be 1.

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

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.

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