SQL Query DSL

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

Repo

julia>using Octo.Adapters.PostgreSQLjulia> Repo.debug_sql()LogLevelDebugSQL::RepoLogLevel=-1julia> 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 Employeeendjulia> 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 Employeejulia> Repo.execute(Raw("""           CREATE TABLE Employee (               ID SERIAL,               Name VARCHAR(255),               Salary FLOAT(8),               PRIMARY KEY (ID)           )"""))┌ Info: CREATE TABLE Employee (│     ID SERIAL,│     NameVARCHAR(255),│     SalaryFLOAT(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 BETWEEN2 AND5|   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=285000|   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 BETWEEN3 AND5|   num_affected_rows||-------------------||2|julia> em=from(Employee)FromItem Employeejulia> 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.PlaceHolderPlaceHolderjulia> 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 subjulia> 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

(v1.11) pkg> add Octo

(v1.11) pkg> add LibPQ# for PostgreSQL (depends on LibPQ.jl 1.18)(v1.11) pkg> add SQLite# for SQLite     (depends on SQLite.jl 1.6)(v1.11) pkg> add MySQL# for MySQL      (depends on MySQL.jl 1.4)(v1.11) pkg> add DuckDB# for DuckDB     (depends on DuckDB.jl 1.2)