• Stars
    star
    236
  • Rank 170,480 (Top 4 %)
  • Language
    Scala
  • License
    Apache License 2.0
  • Created almost 5 years ago
  • Updated 3 months ago

Reviews

There are no reviews yet. Be the first to send feedback to the community and the maintainers!

Repository Details

Type-safe, composable SQL for ZIO applications

ZIO SQL

ZIO SQL lets you write type-safe, type-inferred, and composable SQL queries in ordinary Scala, helping you prevent persistence bugs before they happen, and leverage your IDE to make writing SQL productive, safe, and fun.

Development CI Badge Sonatype Releases Sonatype Snapshots javadoc ZIO SQL

Introduction

  • Type-safety. ZIO SQL queries are type-safe by construction. Most classes of bugs can be detected at compile-time, shortening your feedback loop and helping you use your IDE to write correct queries.
  • Composable. All ZIO SQL components are ordinary values, which can be transformed and composed in sensible ways. This uniformity and regularity means you have a lot of power in a small package.
  • Type-inferred. ZIO SQL uses maximal variance and lower-kinded types, which means it features very good type inference. You can let Scala figure out the types required for type-safe SQL.
  • No magic. ZIO SQL does not need any macros or plug-ins to operate (everything is a value!), and it works across both Scala 2.x and Scala 3. Optionally, Scala schema can be created from database schemas.

ZIO SQL can be used as a library for modeling SQL in a type-safe ADT. In addition, ZIO SQL has a JDBC interface, which utilizes the type-safe SQL ADT for interacting with common JDBC databases.

For the JDBC module:

  • Like Slick, ZIO SQL has an emphasis on type-safe SQL construction using Scala values and methods. However, ZIO SQL utilizes reified lenses, contravariant intersection types, and in-query nullability to improve ergonomics for end-users. Unlike Slick, the intention is to use names resembling SQL instead of trying to mimic the Scala collections.
  • Like Doobie, ZIO SQL is purely functional, but ZIO SQL does compile-time query validation that catches most issues, and has rich ZIO integration, offering improved type-safety compared to monofunctor effects and minimal dependencies (depending only on ZIO).

ZIO SQL does not offer Language Integrated Queries (LINQ) or similar functionality. It is intended only as a data model for representing SQL queries and an accompanying lightweight JDBC-based executor.

Current status: Non-production release

Progress report towards 0.1

βœ”οΈ - good to go

βœ… - some more work needed

General features:

Feature Progress
Type-safe schema βœ”οΈ
Type-safe DSL βœ”οΈ
Running Reads βœ”οΈ
Running Deletes βœ”οΈ
Running Updates βœ”οΈ
Running Inserts βœ”οΈ
Transactions βœ…
Connection pool βœ…

Db-specific features:

Feature PostgreSQL SQL Server Oracle MySQL
Render Read βœ”οΈ βœ”οΈ βœ”οΈ βœ”οΈ
Render Delete βœ”οΈ βœ”οΈ βœ”οΈ βœ”οΈ
Render Update βœ”οΈ βœ”οΈ βœ”οΈ βœ”οΈ
Render Insert βœ”οΈ βœ”οΈ βœ”οΈ βœ”οΈ
Functions βœ”οΈ βœ”οΈ βœ”οΈ βœ”οΈ
Types βœ… βœ…
Operators

Installation

ZIO SQL is packaged into separate modules for different databases. Depending on which of these (currently supported) systems you're using, you will need to add one of the following dependencies:

//PostgreSQL
libraryDependencies += "dev.zio" %% "zio-sql-postgres" % "0.1.2" 

//MySQL
libraryDependencies += "dev.zio" %% "zio-sql-mysql" % "0.1.2"

//Oracle
libraryDependencies += "dev.zio" %% "zio-sql-oracle" % "0.1.2"

//SQL Server
libraryDependencies += "dev.zio" %% "zio-sql-sqlserver" % "0.1.2"

Imports and modules

Most of the needed imports will be resolved with

import zio.sql._

ZIO SQL relies heavily on path dependent types, so to use most of the features you need to be in the scope of one of the database modules:

trait MyRepositoryModule extends PostgresModule {

  // your ZIO SQL code here

}

// other available modules are MysqlModule, OracleModule and SqlServerModule

We will assume this scope in the following examples.

Table schema

In order to construct correct and type-safe queries, we need to describe tables by writing user defined data type - case class in which name of the case class represents table name, field names represent column names and field types represent column types.

Values that will represent tables in DSL are then created by calling defineTable method which takes case class type parameter. In order for defineTable to work, user need to provide implicit Schema of data type.

import zio.schema.DeriveSchema
import zio.sql.postgresql.PostgresJdbcModule
import zio.sql.table.Table._

import java.time._
import java.util.UUID

object Repository extends PostgresJdbcModule {
  final case class Product(id: UUID, name: String, price: BigDecimal)
  implicit val productSchema = DeriveSchema.gen[Product]

  val products = defineTableSmart[Product]
  
  final case class Order(id: UUID, productId: UUID, quantity: Int, orderDate: LocalDate)
  implicit val orderSchema = DeriveSchema.gen[Order]
  
  val orders = defineTable[Order]
}

defineTable method is overloaded with an alternative that takes table name as an input. User can also specify table name using @name annotation. Alternatively user can use defineTableSmart method which will smartly pluralize table name according to english grammar. OrderOrigin -> order_origins Foot -> feet PersonAddress -> person_addresses Field names are also converted to lowercase and snake case. productId -> product_id and so on.

Table schema decomposition

Once we have our table definition we need to decompose table into columns which we will use in queries. Using the previous example with Product and Order table

val (id, name, price) = products.columns

val (orderId, productId, quantity, date) = orders.columns

Selects

Simple select.

val allProducts = select(id, name, price).from(products)

Using where clause.

def productById(uuid: UUID) = 
  select(id, name, price).from(products).where(id === uuid)

Inner join.

val ordersWithProductNames = 
  select(orderId, name).from(products.join(orders).on(productId === id))

Left outer join.

val leftOuter = 
  select(orderId, name).from(products.leftOuter(orders).on(productId === id))

Right outer join.

val rightOuter = 
  select(orderId, name).from(products.rightOuter(orders).on(productId === id))

Using limit and offset

val limitedResults = 
  select(orderId, name)
    .from(products.join(orders)
    .on(productId === id))
    .limit(5)
    .offset(10)

Inserts

def insertProduct(uuid: UUID) =
  insertInto(products)(id, name, price)
    .values((uuid, "Zionomicon", 10.5))

Updates

def updateProduct(uuid: UUID) =
  update(products)
    .set(name, "foo")
    .set(price, price * 1.1)
    .where(id === uuid)

Deletes

def deleteProduct(uuid: UUID) =
    deleteFrom(products)
      .where(id === uuid)

Transactions

TODO: details

Printing queries

TODO: details

Running queries

TODO: details

Documentation

Learn more on the ZIO SQL homepage!

Contributing

For the general guidelines, see ZIO contributor's guide.### TL;DR Prerequisites (installed):

Technology Version
sbt 1.4.3
Docker 3.1

To set up the project follow below steps:

  1. Fork the repository.
  2. Setup the upstream (Extended instructions can be followed here).
  3. Make sure you have installed sbt and Docker.
  4. In project directory execute sbt test.
  5. Pick up an issue & you are ready to go!

Code of Conduct

See the Code of Conduct

Support

Come chat with us on Badge-Discord.

License

License

More Repositories

1

zio

ZIO β€” A type-safe, composable library for async and concurrent programming in Scala
Scala
4,077
star
2

zio-quill

Compile-time Language Integrated Queries for Scala
Scala
2,153
star
3

zio-http

A next-generation Scala framework for building scalable, correct, and efficient HTTP clients and servers
Scala
785
star
4

zio-prelude

A lightweight, distinctly Scala take on functional abstractions, with tight ZIO integration
Scala
451
star
5

zio-json

Fast, secure JSON library with tight ZIO integration.
Scala
406
star
6

zio-kafka

A Kafka client for ZIO and ZIO Streams
Scala
336
star
7

zio-actors

A high-performance, purely-functional library for building, composing, and supervising typed actors based on ZIO
Scala
268
star
8

zio-config

Easily use and document any config from anywhere in ZIO apps
Scala
231
star
9

zio-intellij

A companion IntelliJ IDEA plugin for the ZIO library ecosystem.
Scala
212
star
10

zio-protoquill

Quill for Scala 3
Scala
205
star
11

zio-keeper

A ZIO library for building distributed systems
Scala
199
star
12

zio-nio

A small, unopinionated ZIO interface to NIO.
Scala
187
star
13

zio-logging

Powerful logging for ZIO 2.0 applications, with compatibility with many logging backends out-of-the-box.
Scala
174
star
14

zio-akka-cluster

ZIO wrapper for Akka Cluster
Scala
164
star
15

interop-cats

ZIO instances for cats-effect type classes
Scala
157
star
16

zio-direct

Direct-Style Programming for ZIO
Scala
154
star
17

zio-query

Add efficient pipelining, batching, and caching to any data source
Scala
150
star
18

zio-flow

Resilient, distributed applications powered by ZIO
Scala
142
star
19

zio-schema

Compositional, type-safe schema definitions, which enable auto-derivation of codecs and migrations.
Scala
141
star
20

izumi-reflect

TypeTag without scala-reflect. Supports Scala 2 and Scala 3.
Scala
140
star
21

zio-aws

Low level ZIO interface for the full AWS
Scala
139
star
22

zio-cli

Rapidly build powerful command-line applications powered by ZIO
Scala
130
star
23

zio-redis

A ZIO-based redis client
Scala
123
star
24

zio-telemetry

ZIO-powered OpenTelemetry library
Scala
112
star
25

zio-petclinic

An idiomatic pet clinic application written with ZIO.
Scala
103
star
26

zio-cache

A ZIO native cache with a simple and compositional interface
Scala
85
star
27

zio-zmx

Monitoring, Metrics and Diagnostics for ZIO
Scala
85
star
28

zio-shield

Enforce best coding practices with ZIO
Scala
82
star
29

zio-jdbc

A small, idiomatic ZIO interface to JDBC.
Scala
82
star
30

zio-sqs

ZIO-powered client for AWS SQS
Scala
82
star
31

zio-openai

Scala
66
star
32

zio-process

A simple ZIO library for interacting with external processes and command-line programs
Scala
64
star
33

zio-quickstarts

A minimal quickstart ZIO application for writing a RESTful Web Service
Scala
62
star
34

zio-analytics

Distributed stream processing using ZIO
Scala
61
star
35

zio-optics

Easily modify parts of larger data structures
Scala
57
star
36

zio-metrics-legacy

⛔️ DEPRECATED
Scala
56
star
37

interop-reactive-streams

Interoperability Layer Between ZIO and Reactive Streams
Scala
47
star
38

zio-s3

An S3 client for ZIO
Scala
44
star
39

zio-lambda

AWS Lambda Runtime built with ZIO
Scala
38
star
40

zio-dynamodb

Simple, type-safe, and efficient access to DynamoDB
Scala
37
star
41

zio-amqp

ZIO-based AMQP client for Scala
Scala
33
star
42

zio-macros

Macros to scrap ZIO boilerplate
Scala
32
star
43

zio-rocksdb

A ZIO-based interface to RocksDB.
Scala
32
star
44

zio-connect

Sources, Sinks and Pipelines for channeling data
Scala
32
star
45

zio-crypto

Fast, secure cryptographic primitives in a ZIO & ZIO Streams friendly package.
Scala
30
star
46

zio-metrics-connectors

Monitoring, Metrics and Diagnostics for ZIO
Scala
30
star
47

zio-ftp

A simple, idiomatic (S)FTP client for ZIO
Scala
29
star
48

zio-parser

Scala
28
star
49

zio-mock

Scala
27
star
50

zio-constraintless

An advanced library for building DSLs that allows defering the existence of type class instances until interpretation.
Scala
26
star
51

zio-codec

High-performance codecs for ZIO applications
Scala
23
star
52

zio-project-seed.g8

giter8 template used to start new ZIO projects for the ZIO organization
Scala
21
star
53

zio-webhooks

A microlibrary for reliable and persistent webhook delivery
Scala
20
star
54

zio-profiling

Scala
19
star
55

zio-gcp

A ZIO-based interface to Google Cloud API
Scala
18
star
56

zio-insight-ui

TypeScript
17
star
57

interop-java

Scala
17
star
58

caliban-deriving

Full-featured, robust deriving for Caliban.
Scala
16
star
59

zio-test-intellij

An optional ZIO Test runner support module for the ZIO IntelliJ plugin
Shell
16
star
60

zio-insight

Toolset for ZIO developers
Scala
15
star
61

zio-delegate

Scala
14
star
62

zio-wasm

WASM AST and syntax based on zio-parser.
Scala
14
star
63

interop-twitter

Scala
13
star
64

zio-sbt

SBT Plugins For ZIO Projects
Scala
12
star
65

zio-deriving

Scala
11
star
66

zio-memberlist

Cluster membership and failure detection
Scala
11
star
67

interop-monix

Scala
8
star
68

zio-spark

A simple, type-safe ZIO interface to Spark
Scala
8
star
69

interop-scalaz

Scala
7
star
70

interop-guava

Scala
6
star
71

zio-concurrent

Concurrency utilities for ZIO.
JavaScript
6
star
72

zio-morphir

Scala
5
star
73

zio-uring

Scala
5
star
74

interop-future

Scala
5
star
75

zio-quickstart-graphql-webservice

Quickstart for Writing GraphQL Servers
Scala
4
star
76

zio-bson

BSON library with tight ZIO integration
Scala
4
star
77

zio-cron

ZIO Cron
3
star
78

zio-simple-seed.g8

Scala
3
star
79

zio-meta

Scala
2
star
80

zio-direct-intellij

Scala
2
star
81

zio-insight-server

Scala
2
star
82

zio-quickstart-hello-world

A minimal working example of ZIO Application
Scala
2
star
83

zio-distributed

The future home of ZIO Distributed!
Scala
1
star
84

zio-docs

Experimenting with a new way of maintaining dev.zio
JavaScript
1
star