Readme

Octo.jl

Documentation Build Status

Octo.jl is an SQL Query DSL in Julia. It also comes with a very useful tool called Repo. You could Repo.get , Repo.insert! Repo.update! Repo.delete! for many database drivers without hand-written SQL.

It's influenced by Ecto.

☕️ You can make a donation to support this project.

SQL Query DSL

julia> using Octo.Adapters.SQL julia> struct User end julia> Schema.model(User, table_name="users") | primary_key | table_name | | ------------- | ------------ | | id | users | julia> u = from(User) FromItem users julia> [SELECT * FROM u] SELECT * FROM users julia> [SELECT (u.name, u.salary) FROM u] SELECT name, salary FROM users julia> [SELECT * FROM u WHERE u.id == 2] SELECT * FROM users WHERE id = 2 julia> to_sql([SELECT * FROM u WHERE u.id == 2]) "SELECT * FROM users WHERE id = 2"

Repo

Current supported database drivers:

julia> Repo.debug_sql() LogLevelDebugSQL::RepoLogLevel = -1

julia> Repo.connect( adapter = Octo.Adapters.PostgreSQL, dbname = "postgresqltest", user = "postgres", ) Octo.Repo.Connection(false, "postgresqltest", Main.PostgreSQLLoader, PostgreSQL connection (CONNECTION_OK) with parameters: user = postgres passfile = /Users/wookyoung/.pgpass dbname = postgresqltest port = 5432 client_encoding = UTF8 options = -c DateStyle=ISO,YMD -c IntervalStyle=iso_8601 -c TimeZone=UTC application_name = LibPQ.jl sslmode = prefer sslcompression = 0 gssencmode = disable target_session_attrs = any)

julia> struct Employee end

julia> Schema.model(Employee, table_name="Employee", primary_key="ID") | primary_key | table_name | | ------------- | ------------ | | ID | Employee |

julia> Repo.execute([DROP TABLE IF EXISTS Employee]) [ Info: DROP TABLE IF EXISTS Employee

julia> Repo.execute(Raw(""" CREATE TABLE Employee ( ID SERIAL, Name VARCHAR(255), Salary FLOAT(8), PRIMARY KEY (ID) )""")) ┌ Info: CREATE TABLE Employee ( │ ID SERIAL, │ Name VARCHAR(255), │ Salary FLOAT(8), │ PRIMARY KEY (ID) └ )

julia> Repo.insert!(Employee, [ (Name="Jeremy", Salary=10000.50), (Name="Cloris", Salary=20000.50), (Name="John", Salary=30000.50), (Name="Hyunden", Salary=40000.50), (Name="Justin", Salary=50000.50), (Name="Tom", Salary=60000.50), ]) [ Info: INSERT INTO Employee (Name, Salary) VALUES ($1, $2) RETURNING ID (Name = "Jeremy", Salary = 10000.5), (Name = "Cloris", Salary = 20000.5), (Name = "John", Salary = 30000.5), (Name = "Hyunden", Salary = 40000.5), (Name = "Justin", Salary = 50000.5), (Name = "Tom", Salary = 60000.5) | id | num_affected_rows | | ---- | ------------------- | | 6 | 6 |

julia> Repo.get(Employee, 2) [ Info: SELECT * FROM Employee WHERE ID = 2 | id | name | salary | | ---- | -------- | --------- | | 2 | Cloris | 20000.5 | 1 row.

julia> Repo.get(Employee, 2:5) [ Info: SELECT * FROM Employee WHERE ID BETWEEN 2 AND 5 | id | name | salary | | ---- | --------- | --------- | | 2 | Cloris | 20000.5 | | 3 | John | 30000.5 | | 4 | Hyunden | 40000.5 | | 5 | Justin | 50000.5 | 4 rows.

julia> Repo.get(Employee, (Name="Jeremy",)) [ Info: SELECT * FROM Employee WHERE Name = 'Jeremy' | id | name | salary | | ---- | -------- | --------- | | 1 | Jeremy | 10000.5 | 1 row.

julia> Repo.query(Employee) [ Info: SELECT * FROM Employee | id | name | salary | | ---- | --------- | --------- | | 1 | Jeremy | 10000.5 | | 2 | Cloris | 20000.5 | | 3 | John | 30000.5 | | 4 | Hyunden | 40000.5 | | 5 | Justin | 50000.5 | | 6 | Tom | 60000.5 | 6 rows.

julia> Repo.insert!(Employee, (Name="Jessica", Salary=70000.50)) [ Info: INSERT INTO Employee (Name, Salary) VALUES ($1, $2) RETURNING ID (Name = "Jessica", Salary = 70000.5) | id | num_affected_rows | | ---- | ------------------- | | 7 | 1 |

julia> Repo.update!(Employee, (ID=2, Salary=85000)) [ Info: UPDATE Employee SET Salary = $1 WHERE ID = 2 85000 | num_affected_rows | | ------------------- | | 1 |

julia> Repo.delete!(Employee, (ID=3,)) [ Info: DELETE FROM Employee WHERE ID = 3 | num_affected_rows | | ------------------- | | 1 |

julia> Repo.delete!(Employee, 3:5) [ Info: DELETE FROM Employee WHERE ID BETWEEN 3 AND 5 | num_affected_rows | | ------------------- | | 2 |

julia> em = from(Employee) FromItem Employee

julia> Repo.query(em) [ Info: SELECT * FROM Employee | id | name | salary | | ---- | --------- | --------- | | 1 | Jeremy | 10000.5 | | 6 | Tom | 60000.5 | | 7 | Jessica | 70000.5 | | 2 | Cloris | 85000.0 | 4 rows.

julia> Repo.query([SELECT * FROM em WHERE em.Name == "Cloris"]) [ Info: SELECT * FROM Employee WHERE Name = 'Cloris' | id | name | salary | | ---- | -------- | --------- | | 2 | Cloris | 85000.0 | 1 row.

julia> Repo.query(em, (Name="Cloris",)) [ Info: SELECT * FROM Employee WHERE Name = 'Cloris' | id | name | salary | | ---- | -------- | --------- | | 2 | Cloris | 85000.0 | 1 row.

julia> ❓ = Octo.PlaceHolder PlaceHolder

julia> Repo.query([SELECT * FROM em WHERE em.Name == ❓], ["Cloris"]) [ Info: SELECT * FROM Employee WHERE Name = $1 "Cloris" | id | name | salary | | ---- | -------- | --------- | | 2 | Cloris | 85000.0 | 1 row.

### Subqueries

julia> sub = from([SELECT * FROM em WHERE em.Salary > 30000], :sub) SubQuery (SELECT * FROM Employee WHERE Salary > 30000) AS sub

julia> Repo.query(sub) [ Info: SELECT * FROM Employee WHERE Salary > 30000 | id | name | salary | | ---- | --------- | --------- | | 6 | Tom | 60000.5 | | 7 | Jessica | 70000.5 | | 2 | Cloris | 85000.0 | 3 rows.

julia> Repo.query([SELECT sub.Name FROM sub]) [ Info: SELECT sub.Name FROM (SELECT * FROM Employee WHERE Salary > 30000) AS sub | name | | --------- | | Tom | | Jessica | | Cloris | 3 rows.

## Colored SQL statements ![colored_sql_statements.png](https://raw.github.com/wookay/Octo.jl/master/docs/images/colored_sql_statements.png) * See the CI logs [https://travis-ci.org/wookay/Octo.jl/jobs/388090148#L691](https://travis-ci.org/wookay/Octo.jl/jobs/388090148#L691). ## Requirements You need [Julia](https://julialang.org/downloads/). `julia>` type `]` key

(v1.0) pkg> add Octo

(v1.0) pkg> add LibPQ # for PostgreSQL (depends on LibPQ.jl 1.1, 1.2) (v1.0) pkg> add SQLite # for SQLite (depends on SQLite.jl 1.0) (v1.0) pkg> add MySQL # for MySQL (depends on MySQL.jl 1.0, 1.1) (v1.0) pkg> add ODBC # for ODBC (depends on ODBC.jl 1.0) (v1.0) pkg> add JDBC # for JDBC (depends on JDBC.jl ≥ 0.5.0)