Octo.jl
https://github.com/wookay/Octo.jl
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:
- PostgreSQL (via LibPQ.jl)
- SQLite (via SQLite.jl)
- MySQL (via MySQL.jl)
- DuckDB (via duckdb/tools/juliapkg)
julia> using Octo.Adapters.PostgreSQL
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
Requirements
You need Julia.
julia> type ] key
(v1.8) pkg> add Octo(v1.8) pkg> add LibPQ # for PostgreSQL (depends on LibPQ.jl 1.6, 1.7)
(v1.8) pkg> add SQLite # for SQLite (depends on SQLite.jl 1.6)
(v1.8) pkg> add MySQL # for MySQL (depends on MySQL.jl 1.1, 1.4)
(v1.8) pkg> add DuckDB # for DuckDB (depends on DuckDB.jl 0.4, 0.8)