Python SQLAlchemy database model for Version Control system

Question

This is my first time creating a non-trivial database and I was wondering what I could do better. As the title says this will be used a in toy version control system. Most of my choices feel "random" because I do not know what is and is not important. I also can not decide on what the tables should know about themselves. For example: Should objects belong to a commit? Is it ok to keep every object unlabeled and only have commits refer to them. And things like that. Any review would be welcome.

class Object(Base):
   __tablename__ = "objects"
   oid = Column(String, primary_key= True)
   name = Column(String)
   blob = Column(BLOB)

class Commit(Base):
   __tablename__ = "commits"
   oid = Column(String, primary_key= True)
   commit_message = Column(String)
   parent_oid = Column(String, nullable= True)

   objects = relationship("Object", secondary="commit_object_association")

   repository_id = Column(Integer, ForeignKey("repositories.id"))
   repository = relationship("Repository", back_populates="commits")

# Many to Many 
class CommitObjectAssociation(Base):
   __tablename__ = 'commit_object_association'
   commit_oid = Column(String, ForeignKey('commits.oid'), primary_key=True)
   object_oid = Column(String, ForeignKey('objects.oid'), primary_key=True)

class Branch(Base):
   __tablename__ = "branches"
   id = Column(Integer, primary_key=True, autoincrement=True)
   name = Column(String)
   
   head_commit_oid = Column(String, ForeignKey("commits.oid"))
   
   repository_id = Column(Integer, ForeignKey("repositories.id"))
   repository = relationship("Repository", back_populates="branches", foreign_keys=[repository_id])

   __table_args__ = (
      UniqueConstraint('name', 'repository_id', name='unique_branch_per_repo'),
   )


class Repository(Base):
   __tablename__ = "repositories"
   id = Column(Integer, primary_key=True, autoincrement= True)
   name = Column(String)
   head_oid = Column(String, nullable= True, default= None)
   
   current_branch_id = Column(Integer, ForeignKey("branches.id"), nullable=True)
   current_branch = relationship("Branch", uselist=False, foreign_keys=[current_branch_id])

   creator_id = Column(Integer, ForeignKey('users.id'), nullable= False)
   creator = relationship("User", back_populates="repositories")

   commits = relationship("Commit", back_populates="repository")
   branches = relationship("Branch", back_populates="repository", foreign_keys="Branch.repository_id")

   __table_args__ = (
      UniqueConstraint('name', 'creator_id', name='unique_repo_per_user'),
   )

class User(Base):
   __tablename__ = "users"

   id = Column(Integer, primary_key=True, index=True)
   name = Column(String, unique= True)
   password = Column(String)

   repositories = relationship("Repository", back_populates="creator")

Answer 1

object()

class Object(Base):

This identifier does not clash with or shadow the object() builtin. But still, you might give a moment's thought to giving this class a more descriptive name, something more project-specific, maybe vc_object or vc_obj. I do not look forward to hallway discussions or telephone calls where we diagnose the behavior of an Object which is an object.

Also the {oid, name, blob} columns are generic enough that this class really needs a docstring explaining the concept it models. As it stands, I don't understand why we'd want to represent this pair of rows:

• (1, "bob", "lorem ipsum")
• (2, "bob", "lorem ipsum")

That is, what does it mean to have distinct rows with same name? Or perhaps we wanted a UNIQUE index there?

singular

   __tablename__ = "objects"
   ...
   __tablename__ = "commits"

Reasonable people will disagree on this topic. I assert it makes more sense to talk about "the commit table". Given that it's an RDBMS table, it is clear that we intend to store more than a single row in it.

lint

   oid = Column(String, primary_key= True) ...
   parent_oid = Column(String, nullable= True)

Every now and again run your code through black *.py, please.

Or run a linter and heed its advice.

plural

class User(Base):
   __tablename__ = "users"

It appears that some projects wind up adopting the "tables are plural" rule because

1. they have a user table, and
2. sql-92 makes USER a reserved keyword, present in several commands.

Consider calling this the vc_user table instead.

credentials

   password = Column(String)

That seems bad.

Do not store plaintext passwords.

Prefer to store an argon2id hash.

next steps

Run it, and examine some SHOW CREATE TABLE output to verify you're happy with the defaults and with the choices that you've made. Pay careful attention to whether it really does make sense for a given column to hold a NULL value. When in doubt, add a NOT NULL constraint, as humans can reason about queries (including JOIN queries) on such columns more easily.

Verify that tables and indexes use a character collation order you're happy with, typically UTF8 rather than Latin1. Get it right from the start, so you don't have conflicting character sets in "old" and "new" tables which the query planner has trouble combining.

Consider using an automated tool to produce a graphical ERD.

Give a little thought to how the system will evolve. You will likely need a "test" DB environment, which routinely gets nuked with DELETE commands and/or DROP TABLE ... CASCADE commands. The order in which you do things may matter, with one DB object holding a reference on another, thereby preventing a DROP. You may want to script the details, for reproducibility.

Your "prod" DB environment will eventually hold rows that you'd be sad if you lost them. Write a brief script that dumps rows to .csv or .dump file. Verify that it works, for example by restoring "prod" into newly created "test" tables.

I don't know what your favorite "prod" database vendor would be, possibly postgres. Take advantage of the flexibility offered by the sqlalchemy connect string. Test your code against more than one backend, such as both postgres and sqlite. It has its limitations, but sqlite database files are a terrific match for rapid edit-debug cycles. A simple cp command can quickly re-establish known state from a backup DB file.

Write automated tests that interact with your tables.