onyx-sql
Onyx::SQL
An MIT-licensed SQL ORM for Crystal.
Supporters β€οΈ
Thanks to all my patrons, I can continue working on beautiful Open Source Software! π
Alexander Maslov, Lauri Jutila
You can become a patron too in exchange of prioritized support and other perks
About π
Onyx::SQL is an SQL ORM for the Crystal Language. It features handy schema definition DSL and powerful type-safe query builder. It preserves composition and has a decent API documentation.
It is a part of Onyx Framework, but it is not strictly tied to it. You absolutely can use this ORM with a web framework other than Onyx::HTTP and Onyx::REST.
It implements the crystal-db API, which makes it usable with any SQL database! It has been successfully tested with the following DBs:
- [x] SQLite3
- [x] PostgreSQL
- [ ] MySQL (coming soon)
This ORM, as all other Onyx components, targets to be easily understandble for newcomers, but be able to grow with a developers's knowledge. Fundamentally, it relies on extremely powerful Crystal annotations, but they may be tedious for daily tasks, that why they're hidden by default under the convenient schema DSL. See the examples below.
Installation π₯
Add this to your application's shard.yml
:
dependencies:
onyx-sql:
github: onyxframework/sql
version: ~> 0.6.2
This shard follows Semantic Versioning v2.0.0, so check releases and change the version
accordingly. Please visit github.com/crystal-lang/shards to know more about Crystal shards.
You'd also need to add a database dependency conforming the crystal-db interface. For example, pg:
dependencies:
onyx-sql:
github: onyxframework/sql
version: ~> 0.6.0
pg:
github: will/crystal-pg
version: ~> 0.15.0
Usage π»
The API docs are hosted at https://api.onyxframework.org/sql, and they're pretty comprehensive. Don't hesistate to read them all after you're done with this section!
It's a good idea to get yourself familiar with the Crystal DB docs before moving on.
101 π
As any other ORM, Onyx::SQL allows to define models which will be mapped to SQL tables. Assuming that you have the following table in a PostgreSQL database:
CREATE TABLE users (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL
);
Note: Onyx::SQL does not provide any tools for migrations. Check migrate.cr for a production-ready solution.
Then in your code you would do:
require "pg"
require "onyx-sql"
class User
include Onyx::SQL::Model
schema users do
pkey id : Int32
type name : String
end
end
db = DB.open("postgresql://postgres:postgres@localhost:5432/my_db")
user = User.new(name: "John")
rs = db.query(*user.insert.returning(User).build(true))
users = User.from_rs(rs)
user = users.first
pp user.id # => 1
Congratulations, you've successfully inserted a brand new User instance! :tada:
Note:
.build(true)
is required instead of simple.build
because PostgreSQL has different syntax for query arguments ($n
instead of?
).
Querying
rs = db.query("SELECT * FROM users WHERE id = $1", 1)
user = User.from_rs(rs).first
pp user # => <User @id=1 @name="John">
Updating
You can easily update models with the Changeset
concept:
changeset = user.changeset
changeset.update(name: "Jake")
db.exec(*user.update(changeset).build(true))
Deletion
Deleting from DB is simple as well:
db.exec(*user.delete.build(true))
Repository
Onyx::SQL has the Onyx::SQL::Repository
class, which effectively wraps the database connection with logging, automatically builds queries and more:
repo = Onyx::SQL::Repository.new(db)
user = User.new(name: "Archer")
user = repo.query(user.insert.returning(User)).first
# [postgresql] INSERT INTO users (name) VALUES (?)
# 1.234ms
Query
Onyx::SQL::Query
is a powerful type-safe SQL query builder with almost all SQL methods implemented:
query = User.select(:name).where(id: 2)
pp query # => <Onyx::SQL::Query(User) ...>
pp query.build # => {"SELECT users.name FROM users WHERE id = ?", {2}}
Query
is just an object which could be expanded into a pair of SQL string a query params. You can then use it however you want:
sql, params = query.build(true)
rs = db.query(sql, params)
# Or shorter
rs = db.query(*query.build(true))
# Or with repository
user = repo.query(query).first
References
Onyx::SQL has a native support for model references, both direct and foreign ones.
CREATE TABLE posts (
id SERIAL PRIMARY KEY,
author_id INT NOT NULL REFERENCES users (id),
content TEXT NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
class User
include Onyx::SQL::Model
schema users do
pkey id : Int32
type name : String
type authored_posts : Array(Post), foreign_key: "author_id"
end
end
class Post
include Onyx::SQL::Model
schema posts do
pkey id : Int32
type content : String
type author : User, key: "author_id"
end
end
user = User.new(id: 2, name: "Archer")
post = Post.new(content: "Classic", author: user)
repo.exec(post.insert)
# [postgresql] INSERT INTO posts (content, author_id) VALUES (?, ?)
# The actual DB arguments are "Classic" and 2
Thanks to the Query
builder, it is possible to build powerful type-safe joins in no time:
posts = repo.query(Post
.select(:id, :content)
.join(author: true) do |q|
q.select(:id, :name)
q.where(name: "Archer")
end)
pp posts.first # <Post @id=1 @content="Classic" @author=<User @id=2 @name="Archer">>
Macros
Onyx top-level macros allow to define top-level repository methods:
require "onyx/env"
require "onyx/sql"
Onyx.query # Singleton Onyx::SQL::Repository.query call
Onyx.exec # ditto
Onyx.scalar # ditto
Next steps
That's all for this README! Jump to the API docs at https://api.onyxframework.org/sql or explore the Crystal World application built with Onyx, which is greatly documented as well!
Direct API links:
Community πͺ
There are multiple places to talk about this particular shard and about other ones as well:
- Onyx::SQL Gitter chat
- Onyx Framework Gitter community
- Vlad Faust Gitter community
- Onyx Framework Twitter
- Onyx Framework Telegram channel
Support β€οΈ
This shard is maintained by me, Vlad Faust, a passionate developer with years of programming and product experience. I love creating Open-Source and I want to be able to work full-time on Open-Source projects.
I will do my best to answer your questions in the free communication channels above, but if you want prioritized support, then please consider becoming my patron. Your issues will be labeled with your patronage status, and if you have a sponsor tier, then you and your team be able to communicate with me in private or semi-private channels such as e-mail and Twist. There are other perks to consider, so please, don't hesistate to check my Patreon page:
You could also help me a lot if you leave a star to this GitHub repository and spread the world about Crystal and Onyx! π£
Contributing
- Fork it ( https://github.com/onyxframework/http/fork )
- Create your feature branch (git checkout -b my-new-feature)
- Commit your changes (git commit -am 'feat: some feature') using Angular style commits
- Push to the branch (git push origin my-new-feature)
- Create a new Pull Request
Contributors
- Vlad Faust - creator and maintainer
Licensing
This software is licensed under MIT License.