Generated columns, SQLite & Ruby on Rails
I keep creating small useless apps. One of the apps I'm currently working on is a small personal CRM or PRM.
The app has a person table with first_name and last_name columns. I want to be able to search a person by first and last name, and after doing some research I learned about generated columns.
This time I decided to stick with SQLite and SQLite supports generated columns.
Long story short, a generated column is a column that you have access to, but you cannot update directly. The content is generated by SQLite based on an expression you provide on table creation. This expression can reference other columns.
In this case I'm using the generated column to store a concatenation of strings on a column named full_name.
The table looks something like this (simplified):
This time I decided to stick with SQLite and SQLite supports generated columns.
Long story short, a generated column is a column that you have access to, but you cannot update directly. The content is generated by SQLite based on an expression you provide on table creation. This expression can reference other columns.
In this case I'm using the generated column to store a concatenation of strings on a column named full_name.
The table looks something like this (simplified):
CREATE TABLE person( first_name TEXT, last_name TEXT, full_name TEXT GENERATED ALWAYS AS (concat(first_name, ' ', last_name)) STORED );
You can omit the GENERATED ALWAYS, and you can choose between STORED or VIRTUAL. You can imagine what these keyword do.
Instead of concat you can do almost whatever you want in there, even math calculations. There is a list of limitations that you better have in mind before creating your table, like you cannot add a stored column after the table has been created (but you can add virtual columns).
I believe another way to concatenate strings is with the || operator but I couldn't find info on how to use it (ok, I didn't spend much time looking for it because concat worked just fine).
Before knowing about generated columns I used to do this on a Rails model callback.
There is some info about this in the official Rails docs. The docs are for PostgreSQL, but support for SQLite was merged to master in late 2023.
A Rails migration for creating the same person table would look like:
Instead of concat you can do almost whatever you want in there, even math calculations. There is a list of limitations that you better have in mind before creating your table, like you cannot add a stored column after the table has been created (but you can add virtual columns).
I believe another way to concatenate strings is with the || operator but I couldn't find info on how to use it (ok, I didn't spend much time looking for it because concat worked just fine).
Before knowing about generated columns I used to do this on a Rails model callback.
There is some info about this in the official Rails docs. The docs are for PostgreSQL, but support for SQLite was merged to master in late 2023.
A Rails migration for creating the same person table would look like:
create_table :people do |t| t.string :first_name t.string :last_name t.virtual :full_name, type: :string, as: "concat(first_name, ' ', last_name)", stored: true end
#ruby on rails
#sqlite