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

ts-sql-builder

v1.0.1

Published

A straightforward api for SQL query & schema generation

Downloads

13

Readme

SQL Query & Schema Builder for Node.js

A straightforward api for SQL query & schema generation.

Overview

This npm package provides a versatile SQL query & schema builder for Node.js applications. It supports the creation of SQL queries for common operations like SELECT, INSERT, UPDATE, DELETE and a schema builder api making it easy to create & interact with relational databases.

Built with Typescript, but can be used in pure JavaScript as well.

Installation

Install the package using npm:

npm install ts-sql-builder

Usage

Building Queries

Create a new instance of QueryBuilder:

import { createQueryBuilder } from 'ts-sql-builder';

const qb = createQueryBuilder();

Build a SELECT query:

const selectQuery = qb.select('*').from('logs').limit(5).build().getSql();
SELECT * FROM "logs" logs LIMIT 5

Build an INSERT query:

const insertQuery = qb
  .clear() // Clear the query builder for reuse (or create a new one)
  .insertInto('items')
  .columns('title', 'price', 'isActive')
  .values(['headset', 359.99, true], ['camera', 1999, true])
  .build()
  .format({ tabWidth: 4 }) // Format the generated query if needed
  .getSql();
INSERT INTO
    items ("title", "price", "isActive")
VALUES
    ('headset', 359.99, true),
    ('camera', 1999, true)

Build an UPDATE query:

const updateQuery = createQueryBuilder()
  .update('user')
  .set({ employed: false, profession: 'student' })
  .where('user.age <= 16')
  .build()
  .format()
  .getSql();
UPDATE user
SET
  "employed" = false,
  "profession" = 'student'
WHERE
  user.age <= 16

Perform a join operation:

const userWithPosts = createQueryBuilder()
  .select('user.*')
  .addSelect({ 'JSON_AGG(post.*)': 'posts' })
  .from('user')
  .innerJoin({
    name: 'post',
    condition: 'user.id = post."userId"',
  })
  .groupBy('user.id')
  .build()
  .format({ tabWidth: 4 })
  .getSql();
SELECT
    user.*,
    JSON_AGG(post.*) AS posts
FROM
    "user" user
    INNER JOIN "post" post ON (user.id = post."userId")
GROUP BY
    user.id

And more usage features like sub-queries, a handful of operations (IN, ALL, ANY, CONCAT, AND, OR), complex joins, sorting, you name it..

Schema Generation

Important note:

  • For using schema builder api, you have to enable experimental support for decorators:
    • using command line: tsc --experimentalDecorators
    • or using compiler options inside tsconfig.json:
      {
        "compilerOptions": {
          "experimentalDecorators": true
        }
      }

Api usage:

import {
  Column,
  ForeignKey,
  Index,
  PrimaryKey,
  Table,
  buildSchema,
  tableSchema,
} from 'ts-sql-builder';

@Table()
export class Address {
  @PrimaryKey()
  @Column({ type: 'SERIAL' })
  id!: number;

  @Column({ type: 'VARCHAR', unique: true })
  rawAddress!: string;

  @Column({ type: 'VARCHAR' })
  city!: string;

  @Column({ type: 'VARCHAR' })
  street!: string;

  @Column({ type: 'INTEGER' })
  zip!: number;
}

@Index({ name: 'idx_user_email', columns: ['email'], unique: true })
@Index({ name: 'idx_user_username', columns: ['username'], unique: true })
@Table('users')
class User {
  @PrimaryKey()
  @Column({ type: 'SERIAL' })
  id!: number;

  @Column({ type: 'VARCHAR(255)', nullable: false })
  name!: string;

  @Column({ type: 'VARCHAR(65)', nullable: false, unique: true })
  username!: string;

  @Column({ type: 'INTEGER', check: 'age >= 18' })
  age!: number;

  @Column({ type: 'VARCHAR(255)', unique: true })
  email!: string;

  @Column({
    name: 'created_at',
    type: 'TIMESTAMP',
    default: () => 'CURRENT_TIMESTAMP',
  })
  createdAt!: Date;

  @Column({ type: 'BOOLEAN', default: true })
  activated!: boolean;

  @ForeignKey({ reference: 'address(id)', onDelete: 'NO ACTION' })
  @Column({ type: 'INTEGER' })
  addressId!: number;
}

To generate schemas in strings:

const addressSchema = tableSchema(Address);
const userSchema = tableSchema(User);
console.log(addressSchema);
console.log(userSchema);

Generates:

CREATE TABLE address (
  id SERIAL,
  rawAddress VARCHAR UNIQUE,
  city VARCHAR,
  street VARCHAR,
  zip INTEGER,
  PRIMARY KEY (id)
);

CREATE TABLE users (
  id SERIAL,
  name VARCHAR(255) NOT NULL,
  username VARCHAR(65) NOT NULL UNIQUE,
  age INTEGER CHECK (age >= 18),
  email VARCHAR(255) UNIQUE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  activated BOOLEAN DEFAULT true,
  addressId INTEGER,
  PRIMARY KEY (id),
  FOREIGN KEY (addressId) REFERENCES address (id) ON DELETE NO ACTION
);

CREATE UNIQUE INDEX idx_user_username ON users (username);

CREATE UNIQUE INDEX idx_user_email ON users (email);

To generate each table schema in a separate file:

// simply call buildSchema and provide the base directory:
buildSchema({ dirname: './db/tables/' });

Generates these files:

./db
└── tables
    ├── address.schema.sql
    └── users.schema.sql

To generate the whole database schema in a single file:

// call buildSchema with the path:
buildSchema({ path: './db/schema/db.sql' });

Generates a single file:

./db
└── schema
    └── db.sql

For detailed usage examples and API documentation, refer to the full documentation.

License

This package is licensed under the MIT License.