Linqjs Sql

(WIP) not production ready

A JS AST to SQL conversion library

Will be compatible with Typeorm entity in future (SQL only)

Usage

Where

import { DbContext, DbSet } from '../src';

class Blog {
  title: string;
  content: string
}

export class DataContext extends DbContext {
  public blogs = new DbSet<Blog>(Blog);
}

// you can pass parameter to expression
const body = {
  keyword: 'abc',
}

const context = new DataContext();
const query = context.blogs
  .where(blog => blog.title === 'This Title' && blog.content === body.keyword, { body });
// SELECT * FROM `Blog` WHERE `blog`.`title` = 'This Title' AND `blog`.`content` = 'abc'
console.log(query.toString());

Order

import { DbContext, DbSet } from '../src';

class Blog {
  title: string;
  content: string
}

export class DataContext extends DbContext {
  public blogs = new DbSet<Blog>(Blog);
}

const context = new DataContext();
const query = context.blogs
  .orderBy(blog => blog.title)
  .thenBy(blog => blog.content);
// SELECT * FROM `Blog`  ORDER BY `blog`.`title` ASC,`blog`.`content` ASC
console.log(query.toString());

const body = {
  someValue: 1234,
}
const query2 = context.blogs
  .orderByDescending(blog => body.someValue, { body })
  .thenByDescending(blog => body.someValue * 999, { body })
  .thenBy(blog => true);
// SELECT * FROM `Blog`  ORDER BY 1234 DESC,1234 * 999 DESC,true ASC
console.log(query2.toString());

Join

TODO

Include

TODO

Sub-query

TODO

Currently support subquery but not completed yet on this part

import { DbContext, DbSet } from '../src';

class User {
  id: number;
  name: string;

  blog: Blog;
}

class Blog {
  title: string;
  content: string;

  user: User;
}

export class DataContext extends DbContext {
  public blogs = new DbSet<Blog>(Blog);
  public users = new DbSet<User>(User);
}

// TODO for now require workaround for typescript
const blog = {
  userId: undefined,
};

const context = new DataContext();
const username = context.users
  .where(user => user.id === blog.userId)
  .select(user => ({
    name: user.name,
  }))
  .take(1);
const query = context.blogs
  .select(blog => ({
    title: blog.title,
    content: blog.content,
    username,
  }), { username });
/*
SELECT `Blog`.`title`   AS `title`,
       `Blog`.`content` AS `content`,
       (SELECT `User`.`name` AS `name`
        FROM   `User`
        WHERE  `User`.`id` = `Blog`.`userId`
        LIMIT  1)       AS `username`
FROM   `Blog`
*/
console.log(query.toString());

Take Skip

import { DbContext, DbSet } from '../src';

class Blog {
  title: string;
  content: string
}

export class DataContext extends DbContext {
  public blogs = new DbSet<Blog>(Blog);
}

const context = new DataContext();

const query = context.blogs.take(10).skip(99);
// SELECT * FROM `Blog`    LIMIT 99, 10
console.log(query.toString());

Distinct

const context = new DataContext();
const query = context.blogs.distinct();

Test

npm run benchmark

TODO

• aggregate

• param binding (currnetly no escape on paramter)

• unit test

• expresssion missing

• ()

• IS NULL

• allow execute the query

• extension for expression query