Sequel

00:00:12.960 Well, hi! I'm Jeremy, the maintainer of Sequel. I'm here to talk to you about what Sequel is and how it can help you. First, what is Sequel?

00:00:20.680 Sequel is a database toolkit. It's a collection of tools that allow you to interact with the database and build solutions that solve your problems.

00:00:27.000 Sequel is not just a kitchen sink. If you put in a little effort, you can build whatever type of kitchen sink you want with it.

00:00:33.360 The toolkit versus kitchen sink approach is one thing that differentiates Sequel from other Ruby database libraries. There is another main difference.

00:00:45.440 They say a picture is worth a thousand words, but in this case, I'll settle for three: Sequel at its core is SQL in Ruby.

00:00:53.879 To understand Sequel's purpose, we need to talk about the evolution of database access in Ruby. In the beginning, Ruby had database adapters.

00:01:06.280 However, after a while, people wanted to use Ruby to interact with different kinds of databases, so they sought adapters specific to each database, such as the Ruby-Post adapter originally created by Matt himself.

00:01:12.080 These adapters allowed programmers to use Ruby to increase their productivity, but they had a few shortcomings. For one, they were database specific, meaning code written for one database would not work with another.

00:01:23.400 The first issue was with the API, and the second was that the SQL code was not abstracted. Differences in SQL syntax meant that SQL that worked on one database might not work on another.

00:01:30.720 The database-specific adapters operated at a very low level and required the programmer to write all the SQL themselves, which was often tedious. Finally, the adapters offered little opportunity for abstraction, as returned rows were array-like or hash-like objects, making it difficult to assign behavior to records.

00:01:42.640 In 2001, the D project started, giving programmers a standard database access interface. While this allowed for writing database-independent code, it still had some problems. It only abstracted the API, meaning the programmer was still responsible for writing database-independent SQL. Also, it required the programmer to write all the SQL themselves, making it both flexible yet cumbersome.

00:02:08.200 Although users could choose whether they wanted array, hash, or row objects returned, it still didn’t offer the ability to easily assign behavior to records.

00:02:14.080 Fast forward to 2004 when Rails Active Record started addressing some of these problems. Active Record provided an interface abstraction, allowing the same interface to be used across multiple databases. However, although parts of SQL creation were simplified, programmers still had to write SQL fragments.

00:02:28.840 This led to database-specific SQL being used. By abstracting some parts of creation, Active Record significantly reduced the Ruby inherent inefficiencies in previous approaches. In my opinion, the best feature of Active Record is how it allows programmers to specify the behavior of rows, enabling the rows themselves to perform actions. This made for much nicer, more object-oriented code, as opposed to the procedural code previously common.

00:02:51.599 Although Active Record made many things easier, it also had strong opinions on how things should be designed, which were not always amendable to disagreement.

00:03:06.640 In 2007, Sequel was created to solve these problems more completely. Sequel brings more database independence by abstracting not just the interface but many SQL syntax issues as well. I would say that, while a programmer doesn’t need to know every syntax detail, they should still understand SQL concepts.

00:03:26.280 You might be thinking, how often do database-dependent SQL issues pop up? If you’ve ever had to work on an application that supports multiple databases, you know it's more often than you might think. For example, consider a simple operation like concatenating strings. The SQL standard uses the double pipe operator for string concatenation, while Microsoft SQL Server uses the addition operator, and MySQL uses the concat function.

00:03:50.240 A general approach to database dependency using the library is to avoid string concatenation in the database altogether and select all the required columns, then do the concatenation in Ruby. This approach, however, can be slower and is not always feasible, since a filter may depend on the result of the string concatenation operation. Sequel abstracts the SQL syntax issues and allows for efficient and database-independent code.

00:04:08.480 Sequel's code is generally more concise than Active Record's in most cases, while still being easily readable. This gives the programmer the option to decide whether to assign behavior to records, which is optional. There are many instances where you might not want to assign behavior to records, with reporting being the most common. Sequel does have some opinions on how to do things, but it aims to make it easy to disagree.

00:04:49.880 Matt mentioned that Ruby should be like clay in a child's hands, and Sequel reflects this philosophy, aiming to be flexible so you can mold it to suit your needs.

00:05:11.120 Now that you know a bit about the reasons for Sequel's creation, you may wonder, why choose Sequel? You may already be using other libraries that might get the job done. There's a natural human desire to resist change and fear the unknown, but what does Sequel bring to the table and why should we consider it?

00:05:20.919 First, Sequel is simple. It is straightforward to learn and use, with internals kept as simple as possible but no simpler. Second, Sequel is flexible. As I mentioned, while it has opinions, most options are easy to override at a very granular level.

00:05:44.080 My Sequel tool allows you to choose which tools to use to solve your problems, and Sequel's design enables you to build more tools, whether specific or general, to meet your needs.

00:06:01.520 The ability to build your own tools is part of what makes Sequel powerful, and the other part is that Sequel's toolkit comes with some powerful tools built-in.

00:06:07.720 In a day and age where other Ruby ORMs look like poster children for the American obesity epidemic, Sequel was raised on the South Beach Diet. It uses less than half the memory of Active Record and starts up more than twice as fast.

00:06:35.000 Do you feel frustrated when you file a bug report and the developers just let it sit there, festering in their bug tracker, with the mindset that more is better about everything? How would you feel if you took your car to a repair shop, explained exactly what was wrong, and they told you to find three other people with the same problem before they would even consider fixing it?

00:06:56.680 Wouldn’t you prefer a quick response from someone who likely knows how to fix it, or may even fix it for you?

00:07:03.880 What about suggesting improvements? Maybe you have a great idea but aren't sure how to pitch it. With Sequel, you'll have someone who may understand your idea or, at the very least, work with you to help you achieve your goals.

00:07:22.840 If you decide that you want to implement something yourself, wouldn’t you like to read code that's easy to follow and designed specifically to be easily modifiable? Instead of code where optimization appears to be the primary design objective?

00:07:42.440 I should point out that while Sequel is not focused on performance, it remains competitive performance-wise with other options.

00:07:54.240 Finally, and perhaps most importantly, you should use Sequel for the same reason you use Ruby: because it’s more fun or at least less painful.

00:08:17.880 Now, I’m sure some of you are thinking, 'Talk is cheap; show me some code and let me judge for myself.' So let's get to some coding examples.

00:08:27.480 A good measure of the complexity of a given piece of software is the number of steps you have to take before you can start using it. Other than requiring a library, using Sequel for the first time is like embarking on a long journey that begins with just a single step.

00:08:46.480 That step is creating your database object, and there are multiple ways to do this. One common method is using the connect method.

00:08:54.480 Sequel also provides methods for various adapter types, so if you are using SQLite, you only need to call the SQLite method with the name of the database.

00:09:04.000 Once you have your database object, you can immediately use it to return results. For example, we use the count method to get the number of records.

00:09:12.280 Sequel does not force you to create models if they don’t help your application. If you aren’t using models, Sequel rows can be represented as a hash with symbol keys, and you can utilize Sequel to return any type of object you choose.

00:09:29.680 Last month, someone posted on a Sequel mailing list, mentioning they had a database with thousands of tables using the same schema. By using Active Record, they had to use metaprogramming to create thousands of model classes, one for each table. With Sequel, they could access the tables directly, making their work much easier.

00:09:50.000 I certainly do not advocate having database-dependent action, but this does illustrate that Sequel can handle degenerate cases with greater ease. The convention when using Sequel with a single database is to store that database object in a constant named DB.

00:10:08.960 The database object is mainly used to create datasets, which are also used to handle transactions instead of using SQL directly, which I'll discuss in a moment.

00:10:20.200 The only way to use transactions with Sequel is through the database object's transaction method. This method takes a block and ensures that all database interactions within the block use the same database connection.

00:10:35.760 This is necessary if we want to make changes to the database and ensure that either all changes are made or none are. As shown in the example, we want to add an accounting entry to the database and update the account balance at the same time. Since we shouldn’t insert an entry unless the account balance is updated, we utilize a database transaction to guarantee either all statements are successful or none are.

00:10:54.080 SQL loggers are useful for tracking what SQL statement Sequel is sending to the database. Because Sequel abstracts so much SQL code, you might not know what it's executing unless you add an SQL logger. You can access the array of database loggers via the loggers method and add loggers as you see fit.

00:11:19.000 Every time a query is executed, the details are logged at the info level to all database loggers. For example, executing the all method of an Active DataSet will log the SQL to standard output. Each database object has its own private connection pool, which is designed for high concurrency.

00:11:41.720 Sequel doesn't check out a connection from the pool until it is needed and returns it to the pool as soon as it's no longer required. The SQL connection pool never necessitates the programmer to manually clean up connections, nor does it require connection repairs to automatically clean connections.

00:12:02.640 Let’s quickly review how to perform the four basic query types in Sequel. For select queries, using the all method returns all rows. You can also use the each method to iterate over the rows as the database provides them.

00:12:33.440 This allows you to process a million records in a dataset at once, depending on your adapter. If you only want the first record, you can use the first method. Inserting rows is accomplished with the insert method, using a hash of arguments where the keys specify columns and the values specify the value for that column.

00:12:54.000 Updating rows follows a similar process, using the update method with a similar approach to insert. Keep in mind that updating affects all rows in the dataset, so if you use it on an unfiltered dataset, it will update all rows in the table. If you only want to update certain rows, you need to filter the dataset first and then proceed with the update.

00:13:12.800 Deleting rows is quite similar and done using the delete method. Like the update method, it affects all rows in the dataset unless you filter to specify which rows to delete.

00:13:34.720 Now, I need to explain an interesting little creature you see on the screen: the Sequel DataSet. It represents an SQL query or, more generally, an abstraction of objects. At any point, you can take that abstract set and turn it into a concrete set by calling all.

00:14:04.240 Data sets are usually created by calling the access operator on the database object with a symbol. My colleague with the dataset he represents won't change, but if you ask that copy to change by calling limit, it will return another copy with both limit and filter.

00:14:27.920 This is known as a functional-style API where objects return modified copies of themselves. It's beneficial because it allows you to share data sets across multiple threads without worrying that those threads will modify shared state.

00:14:51.520 Data sets have many methods that modify the query and change the SQL used. Pretty much every standard SQL clause has an associated method. For example, select changes which columns are included in each returned row.

00:15:07.640 You generally achieve the best performance by selecting only the columns you will utilize. The filter method reduces the rows to those included in the specified subset, and it’s among the most commonly used methods. The order method changes the order in which you want rows returned, whether chronological, alphabetic, or numeric.

00:15:26.880 The limit method specifies an upper limit on the number of rows returned. You can also utilize a specified offset along with the limit method to implement pagination features.

00:15:43.520 Sequel has methods for nearly everything you can do in standard SQL. It’s a very powerful language that adds levels of abstraction and gives you a simple interface to interact with it.

00:16:05.760 Earlier, I mentioned that Sequel is SQL in Ruby. So far, I haven't shown many examples, so let me rectify that. Here’s a fairly simple query. Notice how the Ruby code doesn't contain any SQL yet uses Ruby symbols for SQL columns and Ruby strings for SQL strings.

00:16:14.200 This is how most Sequel code looks. Rarely do people write SQL manually, even if Sequel supports that too. You can see how you use select to include the ID and name columns, the filter to restrict the records to those where the name is Jeremy and also include records where page is true. Finally, you order the result by name and limit it to ten records.

00:16:42.480 If you’re used to SQL, it’s fairly easy to translate this code into SQL. However, if you don’t know SQL, you might find learning SQL easier than learning SQL itself.

00:17:05.760 Here’s a slightly more complicated example that involves a join. It shows how you can select all columns from a table using the symbol multiplication operator without an argument, similar to how you would do it in SQL. It also illustrates how easy it is to join tables by specifying table names and conditions.

00:17:31.280 Notice how Sequel assumes the ID column is from the events table, and the event ID column is from the sessions table. Finally, keep in mind how the bitwise operators on symbols operate as logical operators in SQL: the ampersand represents 'and', the pipe represents 'or', and the tilde represents 'not'.

00:17:54.680 This final query uses a filter with a block. Inside the block, instance methods without arguments refer to SQL columns, while instance methods with arguments refer to SQL functions. The exclude method functions as an inverse filter that utilizes a hash of the n value to set is null condition.

00:18:03.680 Using the exclude method instead changes it to 'is not null'. Furthermore, you can reference existing columns when setting new values, which means the new value of a price can depend on the existing price. This is powerful because it allows you to update prices for all filtered records simultaneously, instead of retrieving all filtered records, determining what the new price should be, and updating each of them individually.

00:18:29.920 Whenever possible, you should attempt to update multiple records in a single query unless you have a compelling reason not to. After examining these examples, you might be wondering about the magic Sequel uses to support its DSL. The answer is that it’s not that complicated.

00:18:50.240 Sequel adds some methods to symbols and other core classes. Ruby isn’t defined in these methods, but they create objects Sequel understands. For instance, the numeric operator and symbol return instances of Sequel SQL numeric expressions. Here, I am returning a numeric expression with the multiplication operator and arguments.

00:19:16.160 Numeric expressions also have the mathematical operators defined, which return other numeric expressions, allowing you to create complex queries. Numeric expressions also have inequality methods defined, yielding boolean expressions.

00:19:34.160 Sequel has a basic understanding of the differences between numeric types and boolean types at an SQL level. If it knows an object is boolean in SQL, you can use the bitwise operators in place of logical operators, which will yield other boolean expression instances. If it recognizes that an object is numeric, it won't let you use the mathematical operators since they don’t operate on boolean in SQL.

00:19:51.760 While you’re writing complex SQL queries in Ruby, Sequel is building a simple abstract syntax tree, which it compiles when generating the SQL code. The display shown here reflects a slightly simplified abstract syntax tree for the particular object involved.

00:20:15.040 Understanding SQL at an object level instead of at a string level allows for powerful introspection capabilities. When the instance for SQL manifests this knowledge, it comes into play when inverting existing conditions.

00:20:39.000 Other database libraries that only understand SQL at a string level might only stick a 'not' in front of the conditions. In contrast, Sequel can intelligently apply the inversion operator to the abstract syntax tree, changing the 'and' to 'or', 'not coupon' to 'coupon', and 'greater than or equal to' to 'less than'.

00:21:05.040 The final result is cleaner-looking SQL that's easier to comprehend. A row is not allowed if its price, including tax, is less than 25 or if a coupon was used.

00:21:23.840 However, to demonstrate that the inversion operator works, we can invert the object twice, returning to the original SQL we began with. You may have noticed that Sequel code is quite concise.

00:21:40.560 What Sequel does to maintain concise code is it allows you to use a single symbol to contain both a table and column by separating them with a double underscore. Furthermore, you can utilize a single symbol to contain both column and alias by separating them with a triple underscore.

00:22:01.920 Additionally, you can combine the two approaches by using both double and triple underscores in the same symbol. If you want to execute custom SQL, you can do so by calling the array access operator on the database object.

00:22:30.000 This capability, along with the array access operator on the database object and intuitive method names, is one reason that Sequel code is often more concise than code written with other database libraries.

00:22:47.760 Thus far, we have only discussed core SQL, but Sequel is actually split into two parts: SQL core and SQL model. SQL model is just an object-relational mapper on top of Sequel, where model classes are backed by core datasets, allowing you to leverage all the power of core SQL while using models.

00:23:06.160 The basics of SQL model are similar to those of other ORMs. One notable feature of this model is its powerful and flexible associations. Staying true to the toolkit approach, Sequel only supports the three most common association types, yet it allows you to build your own custom associations and even supports eager loading of custom associations.

00:23:21.360 One example of an association type that doesn’t use eager loading is the 'has many through' association. This association can easily be established using Sequel's toolkit. The key is the dataset option, which permits you to specify the dataset to use for the association.

00:23:42.640 In this scenario, each firm has many clients, each client has many invoices. To obtain the invoices for a firm, you load all invoices for each client of that firm's current dataset.

00:23:57.680 The eager loading method loads the clients for each firm so that each client object for each invoice is cached and returns the invoices. In Active Record, you cannot create custom associations but need to write all of that by hand, which can be quite painful.

00:24:08.800 However, Sequel gives you that ability using the eager loading association option. This option is a proc that takes three arguments: the key hash and an array of current objects, as well as the dependent associations to eagerly load.

00:24:32.480 The key hash serves as an optimization; it’s a hash of hashes with keys as columns (like ID) and values as sub-hashes. These sub-hashes contain keys which hold the values of that column (like one or two) alongside values which are arrays of instances that have the associated values for that column.

00:24:54.840 In the above example, firm one has ID 1, and firm two has ID 2. Since the association depends on the firm's primary key, we care about that specific sub-hash, which we assign to a local variable called `id_map`.

00:25:11.920 For each firm, when we load the data we are interested in, we first set the cached invoices association to the `id_map` and then retrieve all invoices for all clients of all firms in the dataset using the keys of the `id_map`.

00:25:30.480 For each invoice, we associate back to the related firm using the values of the `id_map` based on the invoices' client firm ID and add it to the existing invoices array.

00:25:50.720 Once you've processed all the invoices, each firm will have all related invoices stored in the association cache, allowing the invoices method to return without triggering any additional database queries.

00:26:02.480 It's worth noting there’s nothing inherently specific about this approach. Creating a generic plugin that supports any 'has many' association is feasible and perhaps not that complex. There's already a plugin out there implementing simple polymorphic associations using techniques I explained earlier.

00:26:29.680 In addition to the advanced features not shared by other Ruby ORMs, Sequel supports most common ORM features including hooks, validations, association callbacks, and extensions. It also supports two separate eager loading implementations: one of which utilizes joins while the other loads each association based on specific queries.

00:26:47.920 Sequel offers a toolkit that gives programmers a choice regarding which eager loading implementation to use, rather than leaving it up to guesswork. As Aaron mentioned, ambiguity is evil.

00:27:11.760 Just like SQL core, the SQL model is incredibly flexible and built entirely out of plugins. The most basic model functionality is a plugin, and the association implementation is also one. Sequel ships with seven other optional plugins that provide support for functionalities like caching, single table inheritance, and serialization.

00:27:37.440 These plugins can modify any aspect of Sequel's model functionality. They can override any class instance or dataset method and call super to maintain the default behavior.

00:27:55.440 Though I don’t have much time left, I felt it was important to mention some additional advantages that Sequel brings to the table. Sequel currently supports thirteen database adapters, while Active Record officially only supports four adapters and Ruby only five.

00:28:13.920 The only database that the others support but which Sequel does not is SQLite 2, a database that was too old to have been widely used when Sequel was originally developed.

00:28:31.360 One reason Sequel supports so many adapters is that they are easy to write: only five methods are required. The simplest adapter is only about 50 lines long.

00:28:55.680 Sequel also has a unique feature called data graphing, which I'll illustrate by first explaining the problem it addresses: joining tables. Since Sequel returns rows as hashes, if multiple tables have the same column names, you need to manually add prefixes to prevent clobbering.

00:29:17.920 For example, both attendees and events tables each contain ID and name columns. However, when you join them and retrieve results, the columns from the events table can clobber those from the attendees table.

00:29:39.760 Graphing resolves this issue by returning rows as a hash with table name symbol keys and sub-hashes as values, where the sub-hashes have the column names as keys and their respective values.

00:30:00.640 It accomplishes this by automatically adding prefixes for you and segmenting the hash results into sub-hashes before returning them. This allows you to more easily navigate database relationships at a row level.

00:30:18.960 Furthermore, Sequel supports creating and altering tables as well as most other forms of schema modifications, which can be utilized both inside and outside of migrations. Additionally, Sequel supports both bound variables and prepared statements with native support for four separate adapters and emulated support for all others.

00:30:41.440 It also supports database stored procedures, along with MySQL and JDBC adapters, and manages both master database configurations and slave configurations.

00:31:06.080 Finally, as I mentioned earlier, there’s a natural human reaction to resist change. Suppose you have an existing Active Record infrastructure that would be tedious to change. How would you like to use Sequel's powerful filtering and easy DSL while maintaining all your current Active Record behavior?

00:31:29.360 You can achieve this in a single line of Sequel! I have elaborated it into multiple lines here, but essentially, you just need to add a proc to the dataset that alters the hash from symbol keys to string keys and invokes the private methods in Active Record using that hash.

00:31:45.120 That’s all it takes! Your dataset will continue to return Active Record instances. That concludes the slideshow portion of my talk.

00:32:04.680 Now, my reading time is up, and I'm going to conduct some live coding to showcase some Sequel features. Hopefully, I’ll demonstrate some modifications. As you can see, I’m presenting on Windows, which makes me either brave or foolish. Let’s assume I'm brave and doing this to show that if you're one of the unfortunate souls confined to Windows, you can still achieve your goals with Sequel.

00:32:14.560 This is a really old machine, so I apologize for the delays. I'm opening Office 3 on this machine, and it takes a long time to load.

00:32:20.520 Hold on just a moment. Does anyone have any questions while I get this ready?

00:32:37.680 I have a question: You mentioned it in the last slide a little bit. What’s your vision of the future of Sequel in relation to its coexistence with Active Record?

00:32:56.360 Personally, I mostly use Sequel. I aim to demonstrate how powerful SQL is in terms of flexibility, but using a row proc with a Sequel dataset, you can return any type of object you want.

00:33:15.520 I primarily find Active Record to be the most common ORM, but if you want to return other types of models—or basically any object you want—you can alter the hash Sequel produces. In most cases, you just need to return a different type of object using that data.

00:33:34.040 Does that answer your question? I was just curious if you had a specific vision for it, like whether you want this to be part of the next Rails release, or if you’ve talked to them about it.

00:33:49.400 I have not yet discussed that. I assume anyone who likes Sequel would want to use Sequel model as well. I believe Sequel model is quite similar to Active Record in most instances.

00:34:06.280 One thing Active Record has that Sequel does not is a more powerful schema support when it comes to working with existing schemas and creating migrations to establish your schema. Sequel doesn't currently offer that functionality.

00:34:30.720 However, other than that, I believe Sequel is a better choice. But if you have a lot of existing Active Record infrastructure that you cannot change or don't want to change, you can still use Sequel and return Active Record instances while retaining all of the instance-level behavior.

00:34:49.840 One of the main draws of Sequel is its powerful filtering functionality, which is incredibly easy to use and flexible. Many people may want to keep that while maintaining Active Record’s behaviors.

00:35:10.480 Is there another question? Go ahead.

00:35:24.080 Someone mentioned how Sequel looks amazing, supporting many database adapters and functionalities. It seems you would be using it all the time; in what context did you develop this, and how do you manage compatibility?

00:35:49.640 I actually was not the original developer of Sequel; I took it over after using it for about a month. The original developer was Sharon Rosner, who decided to give up programming altogether. After he left, I submitted a patch to version 1.3 shortly after he made the decision.

00:36:09.440 I was one of two developers that responded to the call for someone to take it over, and the other person didn’t have enough time. I didn’t work much with Sequel’s codebase before I became the main maintainer.

00:36:25.720 If you look at my Mountain list talk from last month, it dives into a lot of technical details, and you may find it quite code-heavy. It necessitates pausing every few seconds to read through my slides, but that’s how you get the full picture of how I approach it.

00:36:46.880 How do I test Sequel? Well, when I started, Sequel had a fairly extensive test suite and good code coverage, which was extremely helpful for refining the library without breaking anything.

00:37:07.560 Moreover, I added an integration test suite since the original tests didn’t utilize a database, which is unusual for a database library. By building tests around actual database interactions, I could cover some edge case bugs.

00:37:28.640 The main aspect that contributes to maintaining a high code quality in Sequel is the extensive unit tests. Unlike many other Ruby-based libraries, Sequel's master branch is regularly more stable than the latest release, largely due to rigorous test procedures.

00:37:52.440 Every patch that goes into Sequel is pushed via GitHub, allowing it to be rigorously tested against the complete test framework I use for releases. It’s quite stable for production use.

00:38:06.000 Do you have any additional questions? Please go ahead.

00:38:29.320 How do you handle exceptions? Does Sequel propagate database-specific errors to the user? In most adapters, it will catch database-specific errors and return them as SQL errors.

00:38:56.320 You can write your application code to capture SQL errors easily; for example, PostgreSQL and SQLite adapters automatically convert errors, allowing you to utilize one exception class to address all database-related problems.

00:39:09.680 Not all adapters support this behavior, but the most common ones—SQLite, MySQL, and PostgreSQL—do, ensuring a unified error handling strategy.