Fork me on GitHub
#sql
<
2021-06-08
>
NoahTheDuke14:06:07

are there any fixture/test data generation libraries like factory_bot (https://github.com/thoughtbot/factory_bot/)? (maybe I should ask this in #testing, but the main domain would be interaction with sql)

lukasz15:06:15

since you're not dealing with an ORM, factory bot approach is an overkill - you can use something like faker: https://github.com/paraseba/faker and generate data sets for inserting via jdbc

seancorfield15:06:20

We often write Specs for our tables and use those to generate random, conforming data for tests.

seancorfield15:06:54

The nice thing about that approach is that the Specs serve double-duty: you can use them to validate data before inserting into the DB and you can also use them to drive generative testing, either through generative data production or directly to test exercise code that operates on what could come out of the database.

NoahTheDuke16:06:14

well, the benefit of factory_bot isn’t so much that it interfaces with the ORM (tho that’s helpful), but that you can define the structure of the data and generate “random” versions while also over-riding specific values. i am particularly fond of the “trait” system that lets you bundle a set of values to a name, so for instance, if an admin user needs to have their “role” set to “admin” and they need to be added to the “admin” table, in the :admin trait you can do that and then just call fb.create(:user, :admin) and it’ll set the role and insert into the admin table

NoahTheDuke16:06:45

Spec and faker work when you only want to get a random conforming object, but it gets harder/more annoying when you want to construct more elaborate set-up data.

NoahTheDuke16:06:22

one method would be to write a wrapper function that does that kind of conversion/set up for you, but at that point you’re re-implementing the factory_bot-style logic without the reusability of a factory_bot-style library

practicalli-john16:06:05

SQL migrations script question for postgresql I'm writing an SQL migration script to update the names of 10 different people in a table. I know the unique id's of each person and their new name. Is there a better approach than just using a migration script with 10 separate update statements ? The project uses ragtime for the subscriptions and .up.sql scripts in resources/migrations. The project doesn't merit anything fancy as it will be replace at some point.

indy20:06:13

update table t
set name = old.name
from (select id, name from (values (1, 'new-name-1'), (2, 'new-name-2')) as old(id, name)) old
where old.id = t.id;

dpsutton16:06:05

a CTE combining id, old name and new name, then update set new name where id = id and name = old_name

practicalli-john18:06:49

Sounds intriguing, I'll take a look. Thanks

indy20:06:26

That is not a schema migration. Are the same names in prod, (staging?) and local dbs? If not, a one time update should suffice?