New to Sqitch

[Tony Burns]

Hello all,

I'm just getting started with Sqitch and I'm really excited about the prospect of using it to replace ActiveRecord migrations for a project I'm working on. One thing I'm trying to wrap my head around is how things get organized for very iterative schema development. For example:

Let's say I create a new Project model in the application code, and create a 'projects' change in the Sqitch project, with a boilerplate CREATE TABLE that just has id, name, created_at, and updated_at

Now I want to add display_name to the projects table, do I create a 'projects_display_name' change? And then as I subsequently add columns, do I just create more changes with single ALTER TABLE statements as I need them?

If that's the way to do it, great! Just want to want to make sure I'm not missing anything and also if there's a sane way to name the changes for a workflow like this.

Thanks!

Tony

[David E. Wheeler]

On May 19, 2014, at 11:57 PM, Tony Burns <tabo...@gmail.com> wrote:

> Now I want to add display_name to the projects table, do I create a 'projects_display_name' change? And then as I subsequently add columns, do I just create more changes with single ALTER TABLE statements as I need them?

Not IMO. Unless you have made a release and tagged your Sqitch plan, I recommend just changing the CREATE TABLE change deploy script directly. Then just rebase onto the change before it.

sqitch rebase --onto mychange^

That ^ will revert to the change before "mychange", then re-deploy "mychange" and any changes that come after it.

> If that's the way to do it, great! Just want to want to make sure I'm not missing anything and also if there's a sane way to name the changes for a workflow like this.

You can do it that way, but getting away from that aspect of AR migrations was one of my primary design goals for Sqitch. The model for AR migrations in my (admittedly dated) experience was that, once you committed and pushed a migration, it was permanent. With Sqitch, the idea is that you can modify change scripts as much as you like --- until you ship and tag the release. *then* it’s locked in stone and further changes use use ALTER TABLE (again, per release; feel free to modify that change up till the next release). This allows a much more iterative development process without leaving the detritus of that process in a bajillion migration scripts.

Best,

David

[François Beausoleil]

Le 2014-05-20 à 01:18, David E. Wheeler <da...@justatheory.com> a écrit :

> On May 19, 2014, at 11:57 PM, Tony Burns <tabo...@gmail.com> wrote:
> You can do it that way, but getting away from that aspect of AR migrations was one of my primary design goals for Sqitch. The model for AR migrations in my (admittedly dated) experience was that, once you committed and pushed a migration, it was permanent. With Sqitch, the idea is that you can modify change scripts as much as you like --- until you ship and tag the release. *then* it’s locked in stone and further changes use use ALTER TABLE (again, per release; feel free to modify that change up till the next release). This allows a much more iterative development process without leaving the detritus of that process in a bajillion migration scripts.

That’s fine, but once you hit production, you must use ALTER TABLE change your schema, or else your production tables will be dropped, right?

Cheers,
François

[Tony Burns]

Thanks for the reply, David! That actually makes things seem so much clearer to me, and I believe makes Sqitch perfect for the OSS project I'm working on. I'm trying to find some more resources on pgTAP now. Do you have any pointers on where I can find some good pointers on unit testing databases in general with something like that?

[David E. Wheeler]

On May 20, 2014, at 6:58 AM, François Beausoleil <francois....@gmail.com> wrote:

> That's fine, but once you hit production, you must use ALTER TABLE change your schema, or else your production tables will be dropped, right?

Mais oui!

Best,

David

[David E. Wheeler]

On May 20, 2014, at 7:55 PM, Tony Burns <tabo...@gmail.com> wrote:

> Thanks for the reply, David! That actually makes things seem so much clearer to me, and I believe makes Sqitch perfect for the OSS project I'm working on. I'm trying to find some more resources on pgTAP now. Do you have any pointers on where I can find some good pointers on unit testing databases in general with something like that?

I assume you’ve hit http://pgtap.org/? I’ve also made some presentations:

* Agile Database Development 2ed (Git, Sqitch, pgTAP)
https://speakerdeck.com/theory/agile-database-development-2ed

* Test Driven Database Development (pgTAP)
http://www.slideshare.net/justatheory/test-drivern-database-development

* Unit Test Your Database! (A manifesto)
http://www.slideshare.net/justatheory/unit-test-your-database

Best,

David

[Tony Burns]

I’ve actually gone through all of those except for the last one! One thing I’m left wondering with pgTAP right now is whether it’s good practice to use each matcher that might be appropriate for a given column for it’s test, e.g. https://gist.github.com/tabolario/28e3d47195dad38d01b6. I certainly see how I would end up with all of them if I TDD each part of a column/table definition incrementally (i.e. failing test for col_not_null, add NOT NULL to column, repeat).

All in all though I’m very happy that I found Sqitch, because up until now I’ve been more of an ActiveRecord programmer vs. a SQL programmer. I’m excited that this is going to force me to start writing more SQL!

[David E. Wheeler]

On May 21, 2014, at 12:52 PM, Tony Burns <tabo...@gmail.com> wrote:

> I’ve actually gone through all of those except for the last one! One thing I’m left wondering with pgTAP right now is whether it’s good practice to use each matcher that might be appropriate for a given column for it’s test, e.g. https://gist.github.com/tabolario/28e3d47195dad38d01b6. I certainly see how I would end up with all of them if I TDD each part of a column/table definition incrementally (i.e. failing test for col_not_null, add NOT NULL to column, repeat).

That’s how I do it. Though if you’re using xUnit functions, you don’t have to RETURN QUERY everything. You can just RETURN NEXT the functions directly:

RETURN NEXT has_column('users', 'id');
RETURN NEXT col_type_is('users', 'id', 'uuid');
RETURN NEXT col_is_pk('users', 'id');
RETURN NEXT col_default_is('users', 'id', 'uuid_generate_v4()');

If you like grouping things, you can also use collect_tap():

RETURN NEXT collect_tap(
has_column('users', 'id'),
col_type_is('users', 'id', 'uuid'),
col_is_pk('users', 'id'),
col_default_is('users', 'id', 'uuid_generate_v4()')
);

> All in all though I’m very happy that I found Sqitch, because up until now I’ve been more of an ActiveRecord programmer vs. a SQL programmer. I’m excited that this is going to force me to start writing more SQL!

Excellent, happy to hear it.

Best,

David

[Tony Burns]

Ah, thank you! That way to do grouping is exactly how I’d want to do it so I can group by column/relation.