sqlpture

sqlpture (/ˈskʌlptʃə/) is a type-level SQL parser & validator, inspired by ts-sql.

import { Query } from 'sqlpture'
import { DB } from './types/DB'

const query = 'SELECT name, email, age FROM customer;'
type result = Query<typeof query, DB> // Array<{ name: string, email: string | null, age: number }>

Installation

yarn add -D sqlpture

Getting Started

⚠️ You will need TypeScript 4.1 or higher

1. Setup Database

2. Generate Type Definition for Your Relational Database

• Reccomend to use schemats to generate Table type intefaces for MySQL & Postgres
• Your DB type definition should meet such structure, type Database = { dialect: string; schema: Record<string, any> }

3. Install sqlpture

How to use in Real World ?

Check out the example repository!

https://github.com/andoshin11/sqlpture-example

There you can see...

• How I manage PostgreSQL DB
• How I do codegen TypeScript schema from actual DB
• How I call PostgreSQL query on Node.js application
• How I develop a type-safe Node.js API server

TODO

• Query Result Type
  • Querying Data
    • SELECT
      • SELECT * FROM table_name
      • SELECT select_list FROM table_name
      • SELECT DISTINCT column_name FROM table_name
      • (PostgreSQL) SELECT statement with expressions
      • LENGTH() function
      • SUM() function
      • COUNT() function
      • HAVING clause
    • Column Alias
      • SELECT column_name AS alias_name FROM table_name
      • SELECT column_name alias_name FROM table_name
      • Column Aliases that contain spaces
    • Join Tables
      • INNER JOIN multiple tables
      • field name from public table
      • field name with table alias prefix
      • SELF JOIN
      • USING
      • LEFT JOIN
      • RIGHT JOIN
      • FULL OUTER JOIN
      • CROSS JOIN
      • NATURAL JOIN
    • GROUP BY
    • UNION
      • UNION ALL
    • INTERSECT
    • EXCEPT
  • Modifying Data
    • INSERT
      • Return Data
      • Insert multiple rows
    • UPDATE
      • Return Data
    • DELETE
• Query Validator
  • SELECT
    • Field names
      • Invalid filed names from public schema
      • Invalid field names with table alias prefix
      • Invalid field names with alias
    • Join
      • Invalid Join target table
      • Invalid ON target fields
    • ORDER BY clause
      • Invalid field names
    • WHERE clause
      • Invalid field names
      • Accept Variable Expression( $)
  • INSERT
    • Insert target table
    • Insert field names
    • Return field names
    • Check values type
    • Insert multiple rows
    • Accept Variable Expression( $)
  • UPDATE
    • Return field names
    • Set field names
    • Set field values
    • Where expression validity
  • DELETE