Proposal: Automatically reorder foreign key constraint statements

[hokaccha]

Platform

• OS: macOS
• RDBMS: PostgreSQL
• Version: 0.15.27

--export output

-- No table exists --

Input SQL

CREATE TABLE posts (
  id      bigint PRIMARY KEY,
  user_id bigint
);

ALTER TABLE ONLY public.posts ADD CONSTRAINT posts_ibfk_1 FOREIGN KEY (user_id) REFERENCES users (id);

CREATE TABLE users (
  id   bigint PRIMARY KEY,
  name text
);

Current output

ALTER TABLE is performed before create table 'public.users': 'ALTER TABLE ONLY public.posts ADD CONSTRAINT posts_ibfk_1 FOREIGN KEY (user_id) REFERENCES users (id)'

Expected output

CREATE TABLE posts (
  id      bigint,
  user_id bigint
);
CREATE TABLE users (
  id   bigint,
  name text
);
ALTER TABLE ONLY public.posts ADD CONSTRAINT posts_ibfk_1 FOREIGN KEY (user_id) REFERENCES users (id);

---

Currently, with sqldef, you need to consider the order of statements when adding foreign keys. Simply moving the addition of the foreign key below the creation of the users table would work, but having constraints related to the posts table near the posts table improves readability.

Also, when the DDL becomes large, you may want to split the file.

-- schema/posts.sql
CREATE TABLE posts (
  id      bigint PRIMARY KEY,
  user_id bigint
);

ALTER TABLE ONLY public.posts ADD CONSTRAINT posts_ibfk_1 FOREIGN KEY (user_id) REFERENCES users (id);

-- schema/users.sql
CREATE TABLE users (
  id   bigint PRIMARY KEY,
  name text
);

In this case, you need to consider the order when merging the schema, but ideally, you want to execute it without considering the order, like this:

$ cat schema/*.sql | psqldef --dry-run --host localhost --user postgres sandbox

Proposal

To solve this problem, I propose a change in the sqldef generator to place the DDL for adding foreign keys after all create table statements.

For reference, ridgepole behaves in the same way.

create_table "posts", force: :cascade do |t|
  t.bigint "user_id"
end

add_foreign_key "posts", "users", name: "posts_ibfk_1"

create_table "users", force: :cascade do |t|
  t.text   "name"
end

$ bundle exec ridgepole -c config.yml --apply --dry-run
Apply `Schemafile` (dry-run)
create_table("posts", **{}) do |t|
  t.column("user_id", :"bigint", **{})
end

create_table("users", **{}) do |t|
  t.column("name", :"text", **{})
end

add_foreign_key("posts", "users", **{:name=>"posts_ibfk_1"})

# CREATE TABLE "posts" (
# "id" bigserial primary key,
# "user_id" bigint)
# CREATE TABLE "users" (
# "id" bigserial primary key,
# "name" text)
# ALTER TABLE "posts" ADD CONSTRAINT "posts_ibfk_1"
# FOREIGN KEY (
# "user_id")
#   REFERENCES "users" ("id")

$ bundle exec ridgepole -c config.yml --apply 
Apply `Schemafile`
-- create_table("posts")
   -> 0.0039s
-- create_table("users")
   -> 0.0037s
-- add_foreign_key("posts", "users", {:name=>"posts_ibfk_1"})
   -> 0.0053s

Are there any design issues with this change in sqldef? If there are no problems, I will attempt to create a Pull Request.

[k0kubun]

To solve this problem, I propose a change in the sqldef generator to place the DDL for adding foreign keys after all create table statements.
Are there any design issues with this change in sqldef?

The design sounds good to me.

If there are no problems, I will attempt to create a Pull Request.

Looking forward to it. Since you already seem to understand the core implementation of psqldef and I trust you, I'd like to make you a co-maintainer of sqldef if you're interested. WDYT?

[hokaccha]

Since you already seem to understand the core implementation of psqldef and I trust you, I'd like to make you a co-maintainer of sqldef if you're interested. WDYT?

Thank you for considering me! I'd be happy to help out as a co-maintainer of sqldef. Let's do this!

[k0kubun]

Thank you! Invited.

[k0kubun]

FYI, pushing a git tag allows you to release a new version. I generally just release every pull request as a new version, so please feel free to do the same.

[hokaccha]

I got it, thank you!