yoga-sql-types

Type-safe SQL query builder and typed query support for PureScript SQL database bindings.

Overview

This package provides:

• Type-level parameter tracking for SQL queries
• Type-safe query builders
• Result set parsing with type classes
• Table schema definitions
• Support for both generic SQL (? placeholders) and PostgreSQL ($N placeholders)

Installation

spago install yoga-sql-types

Usage

Basic Typed Queries

import Yoga.SQL.Types as SQL

-- Define a typed query with parameters
getUserQuery :: SQL.SQLQuery ( id :: Int )
getUserQuery = SQL.sql $
  "SELECT * FROM users WHERE id = " ^ SQL.int @"id"

-- Extract parameters in the correct order
params :: { id :: Int } -> Array SQL.SQLParameter
params = SQL.argsFor getUserQuery

PostgreSQL-Style Parameters

import Yoga.SQL.PostgresTypes as PGSQL

-- PostgreSQL uses $1, $2, etc. for parameters
getUserQuery :: PGSQL.SQLQuery ( id :: Int, name :: String )
getUserQuery = PGSQL.sql $
  "SELECT * FROM users WHERE id = " ^ PGSQL.int @"id" ^
  " AND name = " ^ PGSQL.str @"name"

-- Parameters are extracted in the correct order
params :: { id :: Int, name :: String } -> Array PGSQL.SQLParameter
params = PGSQL.argsFor getUserQuery

Type-Safe Result Parsing

import Yoga.SQL.Types (class FromResultArray, fromResultArray)

type User = { id :: Int, name :: String, email :: String }

-- Parse results from a query that returns (Int, String, String)
parseUser :: Array SQL.SQLResult -> Either String User
parseUser = fromResultArray \id name email -> { id, name, email }

Table Schema Definition

import Yoga.SQL.Types as SQL

usersTable :: SQL.Table
  ( id :: SQL.SQLColumn
  , name :: SQL.SQLColumn
  , email :: SQL.SQLColumn
  )
usersTable = SQL.table (SQL.TableName "users")
  { id: SQL.SQLColumn SQL.IntegerColumn [ SQL.PrimaryKey, SQL.NotNull ]
  , name: SQL.SQLColumn SQL.TextColumn [ SQL.NotNull ]
  , email: SQL.SQLColumn SQL.TextColumn [ SQL.Unique, SQL.NotNull ]
  }

-- Generate CREATE TABLE statement
createStatement :: SQL.CreateTableStatement
createStatement = SQL.createTable usersTable

Type Classes

ToSQLParam

Convert PureScript values to SQL parameters:

class ToSQLParam a where
  toSQLParam :: a -> SQLParameter

Instances provided for:

• Int, String, Number, Boolean
• Array a, NonEmptyArray a
• Maybe a
• Any Newtype wrapper

SQLFromForeign

Parse SQL results into PureScript values:

class SQLFromForeign a where
  fromSQLResultImpl :: Foreign -> ExceptT (NonEmptyList ForeignError) Identity a

FromResultArray

Parse multiple columns from a result row:

class FromResultArray fn a | fn -> a where
  fromResultArray :: fn -> Array SQLResult -> Either String a

Query Builder API

Building Queries

• sql - Create a query from a builder
• arg @type @name - Add a typed parameter
• int @name - Add an Int parameter
• str @name - Add a String parameter
• bool @name - Add a Boolean parameter
• num @name - Add a Number parameter
• nonArg - Add a literal SQL string
• ^ - Compose builders (infixl 8)

Example

searchQuery :: SQL.SQLQuery ( minAge :: Int, maxAge :: Int, city :: String )
searchQuery = SQL.sql $
  "SELECT * FROM users WHERE age >= " ^ SQL.int @"minAge" ^
  " AND age <= " ^ SQL.int @"maxAge" ^
  " AND city = " ^ SQL.str @"city"

Differences Between Generic and PostgreSQL Types

Generic SQL (Yoga.SQL.Types)

• Uses ? for parameter placeholders
• Parameters extracted in order they appear
• Compatible with SQLite, MySQL, and other databases

PostgreSQL (Yoga.SQL.PostgresTypes)

• Uses $1, $2, etc. for parameter placeholders
• Parameters numbered automatically
• Optimised for PostgreSQL's parameter format

Integration with Database Bindings

This package is used by:

• yoga-postgres - PostgreSQL bindings
• yoga-scylladb - ScyllaDB/Cassandra bindings
• yoga-sqlite - SQLite bindings
• yoga-bun-sqlite - Bun SQLite bindings
• yoga-node-sqlite - Node SQLite bindings

Related Packages

• yoga-postgres - PostgreSQL client with typed queries
• yoga-scylladb - ScyllaDB client with typed queries
• yoga-sqlite - SQLite client with typed queries
• yoga-json - JSON library used internally

License

MIT