https://github.com/tilyupo/qustar

Skip to content

Navigation Menu

Toggle navigation
 
Sign in

  * Product
      +  
        Actions
        Automate any workflow
      +  
        Packages
        Host and manage packages
      +  
        Security
        Find and fix vulnerabilities
      +  
        Codespaces
        Instant dev environments
      +  
        GitHub Copilot
        Write better code with AI
      +  
        Code review
        Manage code changes
      +  
        Issues
        Plan and track work
      +  
        Discussions
        Collaborate outside of code
    Explore
      + All features
      + Documentation
      + GitHub Skills
      + Blog
  * Solutions
    By size
      + Enterprise
      + Teams
      + Startups
    By industry
      + Healthcare
      + Financial services
      + Manufacturing
    By use case
      + CI/CD & Automation
      + DevOps
      + DevSecOps
  * Resources
    Topics
      + AI
      + DevOps
      + Security
      + Software Development
      + View all
    Explore
      + Learning Pathways
      + White papers, Ebooks, Webinars
      + Customer Stories
      + Partners
  * Open Source
      +  
        GitHub Sponsors
        Fund open source developers
      +  
        The ReadME Project
        GitHub community articles
    Repositories
      + Topics
      + Trending
      + Collections
  * Enterprise
      +  
        Enterprise platform
        AI-powered developer platform
    Available add-ons
      +  
        Advanced Security
        Enterprise-grade security features
      +  
        GitHub Copilot
        Enterprise-grade AI features
      +  
        Premium Support
        Enterprise-grade 24/7 support
  * Pricing

Search or jump to...

Search code, repositories, users, issues, pull requests...

Search
[                    ]
Clear

Search syntax tips

Provide feedback

We read every piece of feedback, and take your input very seriously.

[                    ] [ ] Include my email address so I can be
contacted
Cancel Submit feedback

Saved searches

Use saved searches to filter your results more quickly

Name [                    ] 
Query [                    ]

To see all available qualifiers, see our documentation.

Cancel Create saved search
Sign in
Sign up Reseting focus
You signed in with another tab or window. Reload to refresh your
session. You signed out in another tab or window. Reload to refresh
your session. You switched accounts on another tab or window. Reload
to refresh your session. Dismiss alert
{{ message }}
tilyupo / qustar Public

  * Notifications You must be signed in to change notification
    settings
  * Fork 1
  * Star 92

Query SQL database through an array-like API

github.com/tilyupo/qustar

License

MIT license
92 stars 1 fork Branches Tags Activity
Star
Notifications You must be signed in to change notification settings

  * Code
  * Issues 1
  * Pull requests 0
  * Actions
  * Projects 0
  * Security
  * Insights

Additional navigation options

  * Code
  * Issues
  * Pull requests
  * Actions
  * Projects
  * Security
  * Insights

tilyupo/qustar

This commit does not belong to any branch on this repository, and may
belong to a fork outside of the repository.
 main
BranchesTags
  
Go to file
Code

Folders and files

      Name              Name           Last commit      Last commit
                                         message           date
Latest commit

 

History

172 Commits
 
.github/workflows .github/workflows                    
examples          examples                             
packages          packages                             
scripts           scripts                              
.editorconfig     .editorconfig                        
.eslintignore     .eslintignore                        
.eslintrc.json    .eslintrc.json                       
.gitignore        .gitignore                           
.prettierrc.cjs   .prettierrc.cjs                      
LICENSE           LICENSE                              
README.md         README.md                            
package-lock.json package-lock.json                    
package.json      package.json                         
todo.yaml         todo.yaml                            
tsconfig.json     tsconfig.json                        
vitest.config.ts  vitest.config.ts                     
View all files

Repository files navigation

  * README
  * MIT license

Qustar

 

npm version MIT license

Query SQL database through an array-like API.

Features

 

 Expressive AND high-level query builder
 TypeScript support
 SQL databases:
   PostgreSQL
   SQLite
   MySQL
   MariaDB
   SQL Server
   Oracle
 Navigation properties
 Codegen free
 Surprise free, all queries produce 100% SQL
 Raw SQL
 Migrations
 Transactions

Quick start

 

To start using qustar with PostgreSQL (the list of all supported data
sources is available below) run the following command:

npm install qustar qustar-pg pg

Here an example usage of qustar:

import {PgConnector} from 'qustar-pg';
import {Q} from 'qustar';

// specify a schema
const users = Q.table({
  name: 'users',
  schema: {
    // generated is not required during insert
    id: Q.i32().generated(), // 32 bit integer
    firstName: Q.string(), // any text
    lastName: Q.string(),
    age: Q.i32().null(), // nullable integer
  },
});

// compose a query
const query = users
  .orderByDesc(user => user.createdAt)
  // map will be translated into 100% SQL, as every other operation
  .map(user => ({
    name: user.firstName.concat(' ', user.lastName),
    age: user.age,
  }))
  .limit(3);

// connect to your database
const connector = new PgConnector('postgresql://qustar:passwd@localhost:5432');

// run the query
console.log('users:', await query.fetch(connector));

Output:

{ age: 54, name: 'Linus Torvalds' }
{ age: 29, name: 'Clark Kent' }
{ age: 18, name: 'John Smith' }

The query above will be translated to:

SELECT
  "s1"."age",
  concat("s1"."firstName", ' ', "s1"."lastName") AS "name"
FROM
  users AS "s1"
ORDER BY
  ("s1"."createdAt") DESC
LIMIT
  3

Insert/update/delete:

// insert
await users.insert({firstName: 'New', lastName: 'User'}).execute(connector);

// update
await users
  .filter(user => user.id.eq(42))
  .update(user => ({age: user.age.add(1)}))
  .execute(connector);

// delete
await users.delete(user => user.id.eq(42)).execute(connector);

Supported database drivers

 

To execute query against a database you need a connector. There are
many ready to use connectors that wrap existing NodeJS drivers:

  * PostgreSQL
      + qustar-pg
  * SQLite
      + qustar-better-sqlite3 (recommended)
      + qustar-sqlite3
  * MySQL
      + qustar-mysql2
  * MariaDB
      + qustar-mysql2

If you implemented your own connector, let me know and I will add it
to the list above!

Usage

 

Any query starts from a table or a raw sql. We will talk more about
raw queries later, for now the basic usage looks like this:

import {Q} from 'qustar';

const users = Q.table({
  name: 'users',
  schema: {
    id: Q.i32(),
    age: Q.i32().null(),
    // ...
  },
});

In qustar you compose a query by calling query methods like .filter
or .map:

const young = users.filter(user => user.age.lt(18));
const youngIds = young.map(user => user.id);

// or

const ids = users.filter(user => user.age.lt(18)).map(user => user.id);

Queries are immutable, so you can reuse them safely.

For methods like .filter or .map you pass a callback which returns an
expression. Expression represents a condition or operation you wish
to do. Expressions are build using methods like .add or .eq:

// for arrays you would write: users.filter(x => x.age + 1 === x.height - 5)
const a = users.filter(user => user.age.add(1).eq(user.height.sub(5)));

// you can also use Q.eq to achieve the same
import {Q} from 'qustar';

const b = users.map(user => Q.eq(user.age.add(1), user.height.sub(5));

We can't use native operators like + or === because JavaScript
doesn't support operator overloading. You can find full list of
supported expression operations here.

Now lets talk about queries and expressions.

Query

 

.filter(condition)

 

const adults = users
  // users with age >= 18
  .filter(user => /* any expression */ user.age.gte(18));

.map(mapper)

 

const userIds = users.map(user => user.id);

const user = users
  // you can map to an object
  .map(user => ({id: user.id, name: user.name}));

const userInfo = users
  // you can map to nested objects
  .map(user => ({
    id: user.id,
    info: {
      adult: user.age.gte(18),
      nameLength: user.name.length(),
    },
  }));

.orderByDesc(selector), .orderByAsc(selector)

 

const users = users
  // order by age in ascending order
  .orderByAsc(user => user.age)
  // then order by name in descending order
  .thenByDesc(user => user.name);

.drop(count), Query.limit(count)

 

const users = users
  .orderByAsc(user => user.id)
  // skip first ten users
  .drop(10)
  // then take only five
  .limit(5);

.slice(start, end)

 

You can also use .slice method to achieve the same:

const users = users
  // start = 10, end = 15
  .slice(10, 15);

.{inner,left,right}Join(options)

 

Qustar supports .innerJoin, .leftJoin, .rightJoin and .fullJoin:

const bobPosts = posts
  .innerJoin({
    right: users,
    condition: (post, user) => post.authorId.eq(user.id),
    select: (post, author) => ({
      text: post.text,
      author: author.name,
    }),
  })
  .filter(({author}) => author.like('bob%'));

.unique()

 

You can select distinct rows using .unique method:

const names = users.map(user => user.name).unique();

.groupBy(options)

 

const stats = users.groupBy({
  by: user => user.age,
  select: user => ({
    age: user.age,
    count: Expr.count(1),
    averageTax: user.salary.mul(user.taxRate).mean(),
  }),
});

.union(query)

 

const studentNames = students.map(student => student.name);
const teacherNames = teachers.map(teacher => teacher.name);

const uniqueNames = studentNames.union(teacherNames);

.unionAll(query)

 

const studentNames = students.map(student => student.name);
const teacherNames = teachers.map(teacher => teacher.name);

const peopleCount = studentNames.unionAll(teacherNames).count();

.concat(query)

 

const studentNames = students.map(student => student.name);
const teacherNames = teachers.map(teacher => teacher.name);

// concat preserves original ordering
const allNames = studentNames.concat(teacherNames);

.intersect(query)

 

const studentNames = students.map(student => student.name);
const teacherNames = teachers.map(teacher => teacher.name);

const studentAndTeacherNames = studentNames.intersect(teacherNames);

.except(query)

 

const studentNames = students.map(student => student.name);
const teacherNames = teachers.map(teacher => teacher.name);

const studentOnlyNames = studentNames.except(teacherNames);

.flatMap(mapper)

 

const postsWithAuthor = users.flatMap(user =>
  posts
    .filter(post => post.authorId.eq(user.id))
    .map(post => ({text: post.text, author: user.name}))
);

.includes(value)

 

const userExists = users.map(user => user.id).includes(42);

Schema

 

The list of supported column types:

  * boolean: true or false
  * i8: 8 bit integer
  * i16: 16 bit integer
  * i32: 32 bit integer
  * i64: 64 bit integer
  * f32: 32 bit floating point number
  * f64: 64 bit floating point number
  * string: variable length string

Raw sql

 

You can use raw SQL like so:

import {Q, sql} from 'qustar';

const users = Q.rawQuery({
  sql: sql`SELECT * from users`,
  // we must specify schema so qustar knows how to compose a query
  schema: {
    id: Q.i32(),
    age: Q.i32().null(),
  },
})
  .filter(user => user.age.lte(25))
  .map(user => user.id);

You can also use aliases in a nested query like so:

const postIds = users.flatMap(user =>
  Q.rawQuery({
    sql: sql`
      SELECT
        id
      FROM
        posts p
      WHERE p.authorId = ${user.id}'
    })`,
    schema: {
      id: Q.i32(),
    },
  });
);

You can use Q.rawExpr for raw SQL in a part of an operation:

const halfIds = users.map(user => ({
  halfId: Q.rawExpr({sql: sql`CAST(${user.id} as REAL) / 2`, schema: Q.f32()}),
  name: user.name,
}));

The query above will be translated to:

SELECT
  "s1"."name",
  (CAST(("s1"."id") as REAL) / 2) AS "halfId"
FROM
  users AS "s1"

License

 

MIT License, see LICENSE.

About

Query SQL database through an array-like API

github.com/tilyupo/qustar

Topics

typescript sql query-builder

Resources

Readme

License

MIT license
Activity

Stars

92 stars

Watchers

1 watching

Forks

1 fork
Report repository

Releases

No releases published

Languages

  * TypeScript 100.0%

Footer

 (c) 2024 GitHub, Inc.

Footer navigation

  * Terms
  * Privacy
  * Security
  * Status
  * Docs
  * Contact
  * Manage cookies
  * Do not share my personal information

You can't perform that action at this time.