zio/zio-sql

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.

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 .

License

License

zio-sql's Issues

Add rendering to SQL for core data types

We should add rendering to SQL for core data types, with several options:

sealed trait RenderMode
object RenderMode {
  case object Compact
  final case class Pretty(indent: Int)
}
...

// On all SQL types
def render(mode: RenderMode): String = /* calls render builder */

private[zio] def renderBuilder(builder: StringBuilder: mode: RenderMode): Unit

The use of StringBuilder is for performance reasons and can be hidden as a private implementation detail.

Note that extreme care should be taken when rendering strings into SQL, as they must be properly escaped, to avoid SQL injection attacks.

In ReadExecutor, verify a statement execution returns a ResultSet

Currently, the boolean value returned from Statement#execute in ReadExecutor is ignored. It should be checked, and if false, it means there is no result set being returned, which we should handle appropriately rather than just calling getResultSet().

Add all unary operators supported by ANSI-92

We should support the full set of unary operators, like NOT, -, IS NULL, etc., which are defined by ANSI-92 and / or supported by Postgres, MySQL, SQL Server, and Oracle.

These would be added as UnaryOp subtypes, with new methods defined in Expr.

If anything is unclear, please ask for more details!

all non-aggregated columns must be included in `groupBy` - remove `Arbitrary`?

groupBy should ensure all non-aggregated columns in the select are included in group by (but are not required to be in the same order)

I think perhaps Arbitrary should be removed as it makes the columns into AggregationDefs even though they are not aggregated.

val orderValue = (select {
    (Arbitrary(fkUserId) as "id") ++ (Arbitrary(orderId) as "id") ++ (Sum(quantity * unitPrice) as "quantity")
  } from (orders join orderDetails).on(fkOrderId === orderId)) groupBy (fkUserId/* , orderId */) //shouldn't compile without orderId being uncommented

Add full-stack test for SQL Server

We need a full-stack test for SQL Server.

We need an automated ZIO Test that, when run, will execute a simple query, such as select 1, against a live-running SQL Server, and verify the result set returned from the query.

This full-stack test will be the foundation for adding other more complicated tests.

This can follow the established pattern from PostgresModuleTest.

support VALUES as a table we can join on

SELECT a.name, v.address FROM (VALUES('101 Main St City USA', 'foo', 'bar'), ('24 Sussex Drive Ottawa Canada', 'foo2', 'bar2')) vals(address, foo, bar) CROSS JOIN A

with any kinds of joins being supported as usual. So (VALUES) looks like a subquery (SELECT ...).

Implement live layer for DeleteExecutor

Following the pattern established in ReadExecutor, this live layer should delete matching rows from a connection.

Implement live layer for UpdateExecutor

Following the pattern established by ReadExecutor, the implementation should perform the specified updates.

Add various transformation methods to Expr

Some generic folding and transformation methods will be useful to add to Expr, including:

• bottom-up transformations, with and without context and state
• top-down transformations, with and without context and state
• folding bottom-up, with state
• folding top-down, with state

Add dialect-specific extension point to Expr

Currently, Expr is closed, and subtypes of the Sql module cannot add new expressions. This is inconvenient as different dialects of SQL support different constructs.

To support extensibility, we can add a new constructor for Expr that holds a module-defined type, e.g:

type DialectSpecificExpr[Features, -Input, Output]

...

object Expr {
  ...
  sealed case class DialectSpecific[F, -A, B](dialectSpecific: DialectSpecificExpr[F, A, B]) extends Expr[F, A, B]
}

This way, a dialect that has no extension can define type DialectSpecificExpr[F, I, O] = Nothing, while others can define it to be a custom type.

Add infrastructure for testing against Oracle

We will soon need to run tests against Oracle. To prepare for this, we can modify the CircleCI continuous build to install Oracle (on supported platforms such as Windows, Linux, or Solaris), and possibly add a helper script(s) to install Oracle locally so developers who run locally tests can have a local install of the database for testing purposes.

• Have helper SBT command that can install Oracle locally on developer machines
• Modify CircleCI build to install Oracle

This ticket will be complete when a small ZIO Test is added into a oracle project that verifies a local Oracle database can be connected to. This will be enough functionality to begin developing test suites against Oracle.

Note: Oracle cannot be installed on Mac outside a VM.

Implement proper rendering for Oracle

This rendering should be able to take one of the core data types (Read, Update, Delete, Insert) and render to a string that constitutes a valid SQL query for the database, taking care to escape strings properly and otherwise be robust to injection attacks.

Support selecting columns using existing column names

Currently we need to specify the column names using as, but some columns already have names defined e.g. for source columns. which were specified when we defined the table. These names should be used as default when "as" is not specified. Perhaps we need a new "Named" Feature?

object Example {
  new Sql { self =>
    import self.ColumnSet._

    val userTable = (uuid("user_id") ++ localDate("dob") ++ string("first_name") ++ string("last_name")).table("users")

    val userId :*: dob :*: fName :*: lName :*: _ = userTable.columns

    //val basicSelect = select { fName ++ lName } from userTable //todo this should compile using column names defined in the table

    // fName and lName already have column names, shouldn't have to do this
    val basicSelectWithAliases = (select {
      (fName as "first_name") ++ (lName as "last_name")
    } from userTable)
  }
}

`in` should support sub queries that return a single column

select * from a where a.id in (select id from b where something) is a fairly common pattern in sql which zio-sql doesn't currently support.

    val deleteFromWithSubquery = deleteFrom(orders).where(fkUserId in {
      select(userId as "id") from users where (fName === "Fred") //this should work
    })

type mismatch;
 found   : this.Read.Select[this.Features.Source,this.users.TableType,this.SelectionSet.Cons[this.users.TableType,java.util.UUID,this.SelectionSet.Empty]]
    (which expands to)  this.Read.Select[this.Features.Source,this.users.TableType,this.SelectionSet.Cons[this.users.TableType,java.util.UUID,this.SelectionSet.Empty.type]]
 required: this.Read[java.util.UUID] 

need to be able to delete from joined tables

split from #37

All major dialects provide some way to delete from a joined table but the table to be deleted from needs to be explicitly provided. We could specify a "zio-sql" way of doing this (with dialect specific implementations) e.g. I'd suggest

delete(orders).from((orders join users).on(fkUserId === userId)).where(fName === "Fred")

or we could just disallow this and let the dialect specific modules introduce the concept of deleting from a join in a manner similar to the dialect of choice (e.g. using for postgres)

Update the ZIO SQL project with the latest ZIO gitter8 template

This project is using an older template and should be updated to work with the latest gitter8 template, available here:

https://github.com/zio/zio-project-seed.g8

Add all binary operators supported by ANSI-92

We should support the full set of binary operators, like *, +, etc., which are defined by ANSI-92 and / or supported by Postgres, MySQL, SQL Server, and Oracle.

These would be added as BinaryOp subtypes, with new methods defined in Expr like the methods for *, and &&.

If anything is unclear, please ask for more details!

ExecuteBuilder in ReadExecutor only supports arities 1-2

Support for arities 1-20 should be easy to add by following the pattern established in ExecuteBuilder.

Please request clarification if anything is unclear.

Add full-stack test for Postgres

We need a full-stack test for Postgres.

We need an automated ZIO Test that, when run, will execute a simple query, such as select 1, against a live-running Postgres, and verify the result set returned from the query.

This full-stack test will be the foundation for adding other more complicated tests.

should not need to include columns from all joined tables in select

    val users = (int("usr_id") ++ localDate("dob") ++ string("first_name") ++ string("last_name")).table("users")
    val userId :*: dob :*: fName :*: lName :*: _ = users.columns
    val orders = (int("order_id") ++ int("usr_id") ++ localDate("order_date")).table("orders")
    val orderId :*: fkUserId :*: orderDate :*: _ = orders.columns
    val orderDetails = (int("order_id") ++ int("product_id") ++ double("quantity") ++ double("unit_price")).table("order_details")
    val fkOrderId :*: fkProductId :*: quantity :*: unitPrice :*: _ = orderDetails.columns
  /* equivalent sql
    select users.usr_id, first_name, last_name, sum(quantity * unit_price) as "total_spend"
    from users
        left join orders on users.usr_id = orders.usr_id
        left join order_details on orders.order_id = order_details.order_id
    group by users.usr_id, first_name, last_name */
val orderValues = (select {
    (Arbitrary(userId) as "usr_id") ++
      (Arbitrary(fName) as "first_name") ++
      (Arbitrary(lName) as "last_name") ++
      //(Arbitrary(orderId) as "order_id") ++ //should compile without this
      (Sum(quantity * unitPrice) as "total_spend")
  }
    from {
      users
        .join(orders)
        .on(userId === fkUserId)
        .leftOuter(orderDetails)
        .on(orderId == fkOrderId)
    })
    .groupBy(userId, fName , lName)

fails to compile with error:

type mismatch;
 found   : zio.sql.ShopSchema.Table{type TableType = zio.sql.ShopSchema.Users.users.TableType with zio.sql.ShopSchema.Orders.orders.TableType with zio.sql.ShopSchema.OrderDetails.orderDetails.TableType}
 required: zio.sql.ShopSchema.Table.Aux[zio.sql.ShopSchema.Users.users.TableType with zio.sql.ShopSchema.OrderDetails.orderDetails.TableType]
    (which expands to)  zio.sql.ShopSchema.Table{type TableType = zio.sql.ShopSchema.Users.users.TableType with zio.sql.ShopSchema.OrderDetails.orderDetails.TableType}

because the select statement is only using columns from users and orderDetails. it compiles if you uncomment Arbitrary(orderId)

Create UPDATE query examples

We need a few examples of UPDATE queries under different schemas, both to validate the design and flesh out edge cases, as well as to show people how to use the data type to model UPDATE queries.

Create SELECT join query examples

We need a few examples of SELECT join queries under different schemas, both to validate the design and flesh out edge cases, as well as to show people how to use the data type to model SELECT join queries.

Add full-stack test for Oracle

Depends on #148

We need a full-stack test for Oracle.

We need an automated ZIO Test that, when run, will execute a simple query, such as select 1, against a live-running Oracle, and verify the result set returned from the query.

This full-stack test will be the foundation for adding other more complicated tests.

This can follow the established pattern from PostgresModuleTest.

Pull out SQL rendering into dialect-specific modules

SQL rendering will be dialect-specific. We could have generic rendering but that should be only for debugging / diagnostics.

Now that we have separate modules for Postgres, SQL Server, Oracle, and MySQL, we should create traits for each of these dialects, and implement SQL rendering functionality there.

Implement proper SQL rendering for MySQL

This rendering should be able to take one of the core data types (Read, Update, Delete, Insert) and render to a string that constitutes a valid SQL query for the database, taking care to escape strings properly and otherwise be robust to injection attacks.

Capture all decoding errors

Currently only the "first" decoding error is captured in unsafeExtractRow. But by extending DecodingError with some sort of DecodingError.Both that can hold two errors, we could capture two decoding errors, and thus validate every column separately. This would allow us returning more than one error which could be useful when diagnosing codec errors.

Create simple SELECT query examples

We need a few examples of SELECT query under different schemas, both to validate the design and flesh out edge cases, as well as to show people how to use the data type to model SELECT queries.

Add infrastructure for testing against Postgres

We will soon need to run tests against Postgres. To prepare for this, we can modify the CircleCI continuous build to install Postgres, and possibly add a helper script(s) to install Postgres locally so developers who run locally tests can have a local install of the database for testing purposes.

• Have helper SBT command that can install Postgres locally on developer machines
• Modify CircleCI build to install Postgres

This ticket will be complete when a small ZIO Test is added into a postgres project that verifies a local Postgres database can be connected to. This will be enough functionality to begin developing test suites against Postgres.

Split up giant Sql.scala file into multiple files

Currently, Sql.scala defines a module trait Sql, in which everything is placed. This means all data types and functions are located in a single file.

To make maintenance easier and improve organization, we should split this file into many files. However, because we are using the module pattern, this means we must not split just the file, but also split the trait Sql module into multiple submodules, which will depend on each other (initially using trait XYZ extends ZYX, probably).

Looking at the way the file is organized now, it looks like we could pull out the following modules:

• trait NewtypesModule / newtypes.scala, which would contain things like ColumnName, TableName, FunctionName, etc. Simple type aliases or case class wrappers, which have no dependencies and just wrap other types.
• trait TypeTagModule / typetag.scala, which would contain TypeTag and companion object, with the extension alias. Possibly, it could contain IsNumeric and other
• trait FeaturesModule / features.scala which would contain Features and associated stuff
• trait ExprModule / expr.scala (extends NewtypeModule, TypeTagModule), which would contain Expr, together with FunctionDef and AggregationDef and their helpers
• trait TableModule / table.scala (extends ExprModule), which could contain ColumnSchema, ColumnSet, Column, JoinType, Table.
• trait SelectModule / select.scala, which contains all Select related classes and helpers.
• trait DeleteModule / delete.scala, which contains all Delete related classes and helpers.
• trait UpdateModule / update.scala, which contains all Update related classes and helpers.

It would be nice to find a way to pull out the FunctionDef and AggregationDef listings into their own separate files / modules, without circular dependencies, as these will contain long lists of very simple definitions. That will require a bit of work.

Add AggregationDef for aggregations supported by ANSI-92

We should support the full set of standard aggregations, such as COUNT, SUM, etc, which are defined by ANSI-92 and / or supported by Postgres, MySQL, SQL Server, and Oracle.

These would be added as AggregationDef values, in the companion object of AggregationDef.

If anything is unclear, please ask for more details!

Add HAVING

Add support for having https://www.w3schools.com/sql/sql_having.asp

In most cases when I use group by there is need to add extra conditions using having clause.

Add infrastructure for testing against SQL Server

We will soon need to run tests against SQL Server. To prepare for this, we can modify the CircleCI continuous build to install SQL Server, and possibly add a helper script(s) to install SQL Server locally so developers who run locally tests can have a local install of the database for testing purposes.

• Have helper SBT command that can install SQL Server locally on developer machines
• Modify CircleCI build to install SQL Server

This ticket will be complete when a small ZIO Test is added into a sqlserver project that verifies a local SQL Server database can be connected to. This will be enough functionality to begin developing test suites against SQL Server.

Note: Looks like this will require docker.

force `deleteFrom` argument to be a source table

users may only delete from a source table, not a joined table.

val basicDelete = deleteFrom(users).where(fName === "Fred") //valid
val joinDelete = deleteFrom((orders join users).on(fkUserId === userId)).where(fName === "Fred") //not valid but compiles

All major dialects provide some way to delete from a joined table but the table to be deleted from needs to be explicitly provided. We could specify a "zio-sql" way of doing this (with dialect specific implementations) e.g. I'd suggest

delete(orders).from((orders join users).on(fkUserId === userId)).where(fName === "Fred")

or we could just disallow this and let the dialect specific modules introduce the concept of deleting from a join in a manner similar to the dialect of choice (e.g. using for postgres)

@jdegoes do you have any preference here?

Implement partial success

Currently, when a SELECT is executed, if any row contains a null and the user's data model cannot accommodate it, then the whole query will fail. It would be more useful to allow configurability of this behavior: for analytical / data transformation purposes, it's possible the user would wish to ignore null rows.

If stream had an element-level error combinator, that could achieve the same functionality (TODO: push there?).

Create DELETE query examples

We need a few examples of DELETE queries under different schemas, both to validate the design and flesh out edge cases, as well as to show people how to use the data type to model DELETE queries.

Create aggregation SELECT queries

We need a few examples of SELECT aggregation queries under different schemas, both to validate the design and flesh out edge cases, as well as to show people how to use the data type to model SELECT aggregation queries.

support common table expressions (CTEs) for Selects

I believe they are fairly standard for modify DML statements as well. This minimally looks like this syntax WITH...

Add full-stack test for MySQL

Depends on #149

We need a full-stack test for MySQL.

We need an automated ZIO Test that, when run, will execute a simple query, such as select 1, against a live-running MySQL, and verify the result set returned from the query.

This full-stack test will be the foundation for adding other more complicated tests.

This can follow the established pattern from PostgresModuleTest.

Introduce ZIO SQL "driver" interface

The ZIO SQL driver interface will have:

• select
• deleteFrom
• update
• insert

and, possibly in the future, DDL.

These will return ZIO effects or ZIO streams, as appropriate.

This is expected to be a lightweight project at the top-level, on which JDBC / etc. projects will depend to expose CRUD functionality atop the core SQL query data model.

Add Scaladoc for various data types and type classes

Feel free to pick one and try to document, asking help for any questions you have about usage or purpose!

• TypeTag
• IsNumeric
• ColumnSchema
• ColumnSet
• FunctionName
• Column
• JoinType
• Table
• select
• deleteFrom
• update
• SelectBuilder
• DeleteBuilder
• Delete
• Update
• Set
• Read
• Ordering
• Selection
• ColumnSelection
• SelectionSet
• BinaryOp
• RelationalOp
• Expr
• Features
• AggregationDef
• FunctionDef

Fix syntax for `in` with literal set

syntax for in isn't very fun for a literal set

deleteFrom(users) where (fName in Read.lit("Fred", "Terrance"))

it'd be much better if we could have something like

deleteFrom(users) where (fName in ("Fred", "Terrance"))

Support INSERT queries

There is no support for the INSERT clause.

Make a real connection pool

Currently the ConnectionPool.live layer doesn't actually make a connection pool. It should be changed to establish n connections with the database, determined by configuration information. Then it should keep track of which ones are in use, and 'lend' them to requestors.

This could be done in many ways: with STM (TMap[Connection, Boolean]), a Queue of connections, etc.

The important part is that connections should be returned to the pool when no longer needed, and the layer itself should shut down all connections as part of the layer's cleanup.

Add FunctionDef for functions in ANSI-92

We should support the full set of standard functions, which are defined by ANSI-92 and / or supported by Postgres, MySQL, SQL Server, and Oracle.

These would be added as FunctionDef values, in the companion object of FunctionDef.

If anything is unclear, please ask for more details!

Make project multi-module

We need the following modules:

• core, which contains the core data types
• driver, which contains the ZIO definitions of database operations, independent of a backend
• jdbc, which contains a lowest-common denominator JDBC "driver" for the ZIO definitions, which assumes ANSI-92 compliance
• postgres, which contains a Postgres dialect and driver
• mysql, which contains a Postgres dialect and JDBC-based driver
• sqlserver, which contains a SQL Server dialect and JDBC-based driver
• oracle, which contains an Oracle dialect and JDBC-based driver
• spark, which contains a Spark SQL dialect and Spark-based driver
• cassandra, which contains a Cassandra SQL dialect and Cassandra-based driver
• test, which contains a test "driver" for the ZIO definitions

We can probably start with just core, driver, jdbc, and test.

This ticket involves only creating the SBT project structure and directory structure, not implementing any modules.

Support Prepared Statements

Parameterized queries can offer higher performance.

Implement proper SQL rendering for SQL Server

This rendering should be able to take one of the core data types (Read, Update, Delete, Insert) and render to a string that constitutes a valid SQL query for the database, taking care to escape strings properly and otherwise be robust to injection attacks.

Add infrastructure for testing against Mysql

We will soon need to run tests against MySQL. To prepare for this, we can modify the CircleCI continuous build to install MySQL, and possibly add a helper script(s) to install MySQL locally so developers who run locally tests can have a local install of the database for testing purposes.

• Have helper SBT command that can install MySQL locally on developer machines
• Modify CircleCI build to install MySQL

This ticket will be complete when a small ZIO Test is added into a mysql project that verifies a local MySQL database can be connected to. This will be enough functionality to begin developing test suites against MySQL.

Implement proper SQL rendering for Postgres

This rendering should be able to take one of the core data types (Read, Update, Delete, Insert) and render to a string that constitutes a valid SQL query for the database, taking care to escape strings properly and otherwise be robust to injection attacks.

extractColumn is too general

The extractColumn helper in ReadExecutor can handle column indices, as well as column names. But we never utilize column names when translating from the ResultSet. So we can delete the column name functionality to simplify the fcode.