# MiniSql ## Installation Add this line to your application's Gemfile: ```ruby gem 'mini_sql' ``` And then execute: $ bundle Or install it yourself as: $ gem install mini_sql ## Usage MiniSql is a very simple, safe and fast SQL executor and mapper for PG and Sqlite. ```ruby pg_conn = PG.connect(db_name: 'my_db') conn = MiniSql::Connection.get(pg_conn) puts conn.exec('update table set column = 1 where id in (1,2)') # returns 2 if 2 rows changed conn.query("select 1 id, 'bob' name").each do |user| puts user.name # bob puts user.id # 1 end p conn.query_single('select 1 union select 2') # [1,2] p conn.query_hash('select 1 as a, 2 as b union select 3, 4') # [{"a" => 1, "b"=> 1},{"a" => 3, "b" => 4} ``` ## The query builder You can use the simple query builder interface to compose queries. ```ruby builder = conn.build("select * from topics /*where*/ /*limit*/") builder.where('created_at > ?', Time.now - 400) if look_for_something builder.where("title = :title", title: 'something') end builder.limit(20) builder.query.each do |t| puts t.id puts t.title end ``` The builder allows for `order_by`, `where`, `select`, `set`, `limit`, `join`, `left_join` and `offset`. ## Is it fast? Yes, it is very fast. See benchmarks in [the bench directory](https://github.com/discourse/mini_sql/tree/master/bench). As a rule it will outperform similar naive PG code while remaining safe. ```ruby pg_conn = PG.connect(db_name: 'my_db') # this is slower, and less safe result = pg_conn.async_exec('select * from table') result.each do |r| name = r['name'] end # ideally you want to remember to run r.clear here # this is faster and safer conn = MiniSql::Connection.get(pg_conn) r = conn.query('select * from table') r.each do |row| name = row.name end ``` ## Safety In PG gem version 1.0 and below you should be careful to clear results. If you do not you risk memory bloat. See: [Sam's blog post](https://samsaffron.com/archive/2018/06/13/ruby-x27-s-external-malloc-problem). MiniSql is careful to always clear results as soon as possible. ## Timestamp decoding MiniSql's default type mapper prefers treating `timestamp without time zone` columns as utc. This is done to ensure widest amount of compatability and is a departure from the default in the PG 1.0 gem. If you wish to amend behavior feel free to pass in a custom type_map. ## I want more features! MiniSql is designed to be very minimal. Even though the query builder and type materializer give you a lot of mileage, it is not intended to be a fully fledged ORM. If you are looking for an ORM I recommend investigating ActiveRecord or Sequel which provide significantly more features. ## Development To install this gem onto your local machine, run `bundle exec rake install`. To release a new version, update the version number in `version.rb`, and then run `bundle exec rake release`, which will create a git tag for the version, push git commits and tags, and push the `.gem` file to [rubygems.org](https://rubygems.org). ## Contributing Bug reports and pull requests are welcome on GitHub at https://github.com/discourse/mini_sql. This project is intended to be a safe, welcoming space for collaboration, and contributors are expected to adhere to the [Contributor Covenant](http://contributor-covenant.org) code of conduct. ## License The gem is available as open source under the terms of the [MIT License](https://opensource.org/licenses/MIT). ## Code of Conduct Everyone interacting in the MiniSql project’s codebases, issue trackers, chat rooms and mailing lists is expected to follow the [code of conduct](https://github.com/discourse/mini_sql/blob/master/CODE_OF_CONDUCT.md).