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.0

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:

• Onyx::SQL::Model
• Onyx::SQL::Query
• Onyx::SQL::Repository

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

1. Fork it ( https://github.com/onyxframework/http/fork )
2. Create your feature branch (git checkout -b my-new-feature)
3. Commit your changes (git commit -am 'feat: some feature') using Angular style commits
4. Push to the branch (git push origin my-new-feature)
5. Create a new Pull Request

Contributors

• Vlad Faust - creator and maintainer

Licensing

This software is licensed under MIT License.