Make a PostgreSQL diffing tool that produces migrations
720 sats \ 4 comments \ @fiatjaf 13 Mar 2023 bitcoin
Suppose I have a file where I write my database schema, which is initialized upon:
CREATE TABLE users (
  id PRIMARY KEY,
  name TEXT
)
In the next day I decide to add a new table, so I edit that file:
CREATE TABLE users (
  id PRIMARY KEY,
  name text
);

CREATE TABLE actions (
  user_id int REFERENCES users (id),
  name text
);
If I run the tool, I should be able to get a diff with only the stuff required for bring a database created on the first day into the state it should be in the second day:
CREATE TABLE actions (
  user_id int REFERENCES users (id),
  name text
);
Now suppose on the third day I go back and make a bunch of changes:
CREATE TABLE users (
  id PRIMARY KEY,
  name text NOT NULL,
  age int NOT NULL
);

CREATE TABLE actions (
  user_id int REFERENCES users (id),
  name text NOT NULL
);
I should also get a migration from the changes above:
ALTER TABLE users ADD COLUMN age int NOT NULL;
ALTER TABLE users ALTER TABLE name ADD CONSTRAINT NOT NULL;
ALTER TABLE actions ALTER TABLE name ADD CONSTRAINT NOT NULL;
And so on and so forth (I don't know if the example above is correct).
This should work for the most common things, like adding and removing tables or columns, adding and removing constraints and indexes and foreign keys (what else? I can't remember). More complex stuff could have their migrations specified manually.
https://github.com/lelit/pglast gives you a parsed AST, so you could use that.
The only requirement is that this tool is written in a way that it is runnable without installing thousands of runtimes or dependencies or Docker stuff. Scala, Python and JavaScript are ok, Java, PHP and Ruby are not fine. Go, Rust and lower-level things are fine.
50,000 sats bounty
write
preview
fiatjaf's bounties
482 sats \ 0 replies \ @patrick1 13 Mar 2023 freebie
It sounds like you want prisma: https://www.prisma.io/
reply
10 sats \ 0 replies \ @random_ 13 Mar 2023
As for myself, I use gorm and automigrator.
See also: https://atlasgo.io/guides/orms/gorm
reply
0 sats \ 0 replies \ @Hopeful_Sat 8 Jun 2023
Disclaimer: I am not knowledgeable in this field, but asked someone to help me with it so here is his solution (might be a waste of time but I don't know):
You can use the "sqlalchemy" library in Python along with the "alembic" library, which is a database migration tool compatible with SQLAlchemy.
Here's how you can set up and use the tool:

1: Install the necessary dependencies:

bash
pip install sqlalchemy alembic

2: Create a new directory for your project and navigate to it:

bash
mkdir postgres_diff_tool && cd postgres_diff_tool

3: Initialize the Alembic migration environment:

bash
alembic init migrations

4: Update the alembic.ini file in the migrations directory to configure your database connection. Modify the [alembic] section as follows:

ini
[alembic] script_location = migrations sqlalchemy.url = postgresql://username:password@localhost:5432/database_name
Replace username, password, localhost, and database_name with your actual database credentials and connection details.

5: Create a new Python script in the project directory, for example diff_tool.py. Add the following code to the script:

python
from sqlalchemy import create_engine from alembic import command from alembic.config import Config
def generate_migrations(): config = Config("migrations/alembic.ini") command.revision(config, autogenerate=True)
def apply_migrations(): config = Config("migrations/alembic.ini") command.upgrade(config, "head")
def main(): # Update the database URL according to your setup engine = create_engine("postgresql://username:password@localhost:5432/database_name") with engine.connect() as connection: generate_migrations() apply_migrations()
if name == "main": main()

6: Customize the script as needed, including the database URL in the create_engine function.

7: Run the diffing tool:

bash
python diff_tool.py This will generate the necessary migration scripts based on the changes between the current schema and the one defined in the previous day's file. The migrations will be created in the migrations/versions directory.

8: To apply the generated migrations to the database, run the diffing tool again:

bash
python diff_tool.py
This will apply the migrations to bring the database to the desired state.
Please note that this is a basic setup using SQLAlchemy and Alembic for PostgreSQL database migrations. You can customize the tool further based on your specific requirements, such as handling additional schema changes or incorporating more complex migrations.
Remember to update the database URL and credentials in both the alembic.ini file and the diff_tool.py script according to your actual PostgreSQL database setup.
Hope it works! Or, at least, helps?
reply
0 sats \ 0 replies \ @kristapsk 14 Mar 2023
Would be useful to support not only PostgreSQL, but also MySQL.
And I don't understand why JavaScript is ok. Node.js ir heavier dependency than PHP in cli.
reply