sequel

Sequel: The Database Toolkit for Ruby Sequel is a simple, flexible, and powerful SQL database access toolkit for Ruby. Sequel provides thread safety, connection pooling and a concise DSL for constructing SQL queries and table schemas. Sequel includes a comprehensive ORM layer for mapping records to Ruby objects and handling associated records. Sequel supports advanced database features such as prepared statements, bound variables, savepoints, two-phase commit, transaction isolation, primary/replica configurations, and database sharding. Sequel currently has adapters for ADO, Amalgalite, IBM_DB, JDBC, MySQL, Mysql2, ODBC, Oracle, PostgreSQL, SQLAnywhere, SQLite3, TinyTDS, and Trilogy. Resources Website sequel.jeremyevans.net RDoc Documentation sequel.jeremyevans.net/rdoc Source Code github.com/jeremyevans/sequel Bug tracking (GitHub Issues) github.com/jeremyevans/sequel/issues Discussion Forum (GitHub Discussions) github.com/jeremyevans/sequel/discussions Archived Discussion Forum (sequel-talk Google Group) www.mail-archive.com/sequel-talk@googlegroups.com/ If you have questions about how to use Sequel, please ask on GitHub Discussions. Only use the bug tracker to report bugs in Sequel, not to ask for help on using Sequel. To check out the source code: git clone git://github.com/jeremyevans/sequel.git Contact If you have any comments or suggestions please post to the Google group. Installation gem install sequel A Short Example require 'sequel' DB = Sequel.sqlite # memory database, requires sqlite3 DB.create_table :items do primary_key :id String :name Float :price end items = DB[:items] # Create a dataset # Populate the table items.insert(name: 'abc', price: rand * 100) items.insert(name: 'def', price: rand * 100) items.insert(name: 'ghi', price: rand * 100) # Print out the number of records puts "Item count: #{items.count}" # Print out the average price puts "The average price is: #{items.avg(:price)}" The Sequel Console Sequel includes an IRB console for quick access to databases (usually referred to as bin/sequel). You can use it like this: sequel sqlite://test.db # test.db in current directory You get an IRB session with the Sequel::Database object stored in DB. In addition to providing an IRB shell (the default behavior), bin/sequel also has support for migrating databases, dumping schema migrations, and copying databases. See the bin/sequel guide for more details. An Introduction Sequel is designed to take the hassle away from connecting to databases and manipulating them. Sequel deals with all the boring stuff like maintaining connections, formatting SQL correctly and fetching records so you can concentrate on your application. Sequel uses the concept of datasets to retrieve data. A Dataset object encapsulates an SQL query and supports chainability, letting you fetch data using a convenient Ruby DSL that is both concise and flexible. For example, the following one-liner returns the average GDP for countries in the middle east region: DB[:countries].where(region: 'Middle East').avg(:GDP) Which is equivalent to: SELECT avg(GDP) FROM countries WHERE region = 'Middle East' Since datasets retrieve records only when needed, they can be stored and later reused. Records are fetched as hashes, and are accessed using an Enumerable interface: middle_east = DB[:countries].where(region: 'Middle East') middle_east.order(:name).each{|r| puts r[:name]} Sequel also offers convenience methods for extracting data from Datasets, such as an extended map method: middle_east.map(:name) # => ['Egypt', 'Turkey', 'Israel', ...] middle_east.map([:id, :name]) # => [[1, 'Egypt'], [3, 'Turkey'], [2, 'Israel'], ...] Or getting results as a hash via as_hash, with one column as key and another as value: middle_east.as_hash(:name, :area) # => {'Israel' => 20000, 'Turkey' => 120000, ...} Getting Started Connecting to a database To connect to a database you simply provide Sequel.connect with a URL: require 'sequel' DB = Sequel.connect('sqlite://blog.db') # requires sqlite3 The connection URL can also include such stuff as the user name, password, and port: DB = Sequel.connect('postgres://user:password@host:port/database_name') # requires pg You can also specify optional parameters, such as the connection pool size, or loggers for logging SQL queries: DB = Sequel.connect("postgres://user:password@host:port/database_name", max_connections: 10, logger: Logger.new('log/db.log')) It is also possible to use a hash instead of a connection URL, but make sure to include the :adapter option in this case: DB = Sequel.connect(adapter: :postgres, user: 'user', password: 'password', host: 'host', port: port, database: 'database_name', max_connections: 10, logger: Logger.new('log/db.log')) You can specify a block to connect, which will disconnect from the database after it completes: Sequel.connect('postgres://user:password@host:port/database_name'){|db| db[:posts].delete} The DB convention Throughout Sequel’s documentation, you will see the DB constant used to refer to the Sequel::Database instance you create. This reflects the recommendation that for an app with a single Sequel::Database instance, the Sequel convention is to store the instance in the DB constant. This is just a convention, it’s not required, but it is recommended. Note that some frameworks that use Sequel may create the Sequel::Database instance for you, and you might not know how to access it. In most cases, you can access the Sequel::Database instance through Sequel::Model.db. Arbitrary SQL queries You can execute arbitrary SQL code using Database#run: DB.run("create table t (a text, b text)") DB.run("insert into t values ('a', 'b')") You can also create datasets based on raw SQL: dataset = DB['select id from items'] dataset.count # will return the number of records in the result set dataset.map(:id) # will return an array containing all values of the id