0.0.9 • Published 3 years ago
sqlpture v0.0.9
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
:warning: You will need TypeScript 4.1 or higher
Setup Database
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> }
- 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()
functionSUM()
functionCOUNT()
functionHAVING
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
- Querying Data
- 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(
$
)
- Field names
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