How to know current db tables state/fields?

[himat]

Hi, I'm new to clojure in general and have started using migratus.

But one thing that's been difficult for me coming from other languages like python is that with ORMs there like sqlalchemy, in addition to the migration up/down files, I can define what the current state of each of the DB tables looks like.

e.g.

class Users():
   id = Column(int, primary_key=True)
   created_at = Column(datetime)
   name = Column(str)
   ....

And then I'll have multiple migration files alongside that in a separate directory

migrations/1 // created the initial users table
migrations/2 // Added the name column
migrations/3 // Changed constraint on a column
...

With Migratus, I only appear to have the latter migration files since it's not an ORM. So I'm missing the former where I can go to just see "oh what fields are on the Users table -> I open up the models/users.py file and can see all the current fields there. With migratus, it seems like the only way would be to connect to the db with a db tool like psql and manually look at the tables and columns there. Since the only other way is manually going through possibly many individual migration files to see what changed, which is not really a good solution.

So what do people generally do here without using an ORM?

[yogthos]

Typically, you would define the fields directly in the queries, and your db schema acts the definitive model for the application. When you query the db, you'll get maps with keys representing the column names in the tables. Then you just use these directly in the application. The two popular libraries to look at are Honey SQL and Hug SQL.

[himat]

That doesn't make sense to me since you're saying you literally query your db to figure out the schema.

I'm asking how I can easily see my schema in the first place. I shouldn't need to query the db to see the schema. Again in python with sqlalchemy, I would just have a file that shows me my current db schema for each table.

[himat]

It seems to me that people just open a connection to the db here via some sql tool like dbeaver to see the table schema. I also don't like that though because why do I need to connect to the db to see the schema. But I guess there's no better solution here.

[yogthos]

Right, using a separate database tool is generally the approach for seeing the schema. Database isn't seen as being part of the application, so it makes sense to use a separate tool to interact with it.

[himat]

Database isn't seen as being part of the application I disagree with this - the database itself isn't part of the application, that's true, but the db schema is entirely what your app is based on and so it is a part of your application and without it, the application wouldn't make any sense, and it's like you're trying to code against an API that you don't even know the endpoints of.

But anyway I'm not trying to argue with you specifically haha since your tool isn't meant for this anyway, just thinking out loud here

I'm now thinking maybe it makes sense just to get another tool that dumps the db schema from the db every time a migration is run, and that db schema file(s) can be committed into my application repo.

Thank you though!

[himat]

I guess this is actually one reason in favor of an ORM since they usually do store the schema in your codebase

[yogthos]

Dumping a db schema is one option, but I find what's important to revision are the changes themselves. If you write your migrations properly then you can migrate the schema back and forth by running up/down migrations. So, I don't really see the need to store the schema itself in version control.

[himat]

Changes are not useful to me to look back at, they are a point in a past time. The only thing that matters in the present when you want to create a new feature is to know what the current db schema is so that you know which columns you can query and so that you know if might need to add a new column or not.

But yes, this can be done by connecting to the db to see the schema there as well.