Skip to content

andoshin11/sqlpture

 
 

Folders and files

NameName
Last commit message
Last commit date

Latest commit

 

History

57 Commits
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

Repository files navigation

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 }>

DEMO

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> }
  1. 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

About

Type Level SQL interpreter & validator.

Resources

License

Stars

Watchers

Forks

Packages

No packages published

Languages

  • TypeScript 100.0%