title: How to validate automatically String/Unicode columns maximum length when specified on declaration?
body: <p>SQLAlchemy allows to specify a length when declaring <code>String</code> columns:</p>
  
  <pre class="lang-py prettyprint-override"><code>foo = Column(String(10))
  </code></pre>
  
  <p>as in SQL:</p>
  
  <pre class="lang-sql prettyprint-override"><code>foo VARCHAR(10)
  </code></pre>
  
  <p>I know this length value is used by some DBMS to allocate memory when creating a row in a table. But some DBMS (like SQLite) do not take care of it and accept this syntax only for compatibility with SQL standard. But some DBMS (like MySQL) require it to be specified.</p>
  
  <p>Personally, I like to specify a maximum length for some text data because it helps to design UIs as you know the area needed to display it.</p>
  
  <p>Moreover, I think it will make my application behavior more consistent across the different DBMS.</p>
  
  <p>So, I want to validate the value of String/Unicode columns on insertion by <strong>checking its length against the declared one</strong> (when length as been declared).</p>
  
  <h2>CheckConstraint</h2>
  
  <p>The first solution is to use a <a href="https://docs.sqlalchemy.org/en/13/core/constraints.html#check-constraint" rel="nofollow noreferrer">check constraint</a>:</p>
  
  <pre class="lang-py prettyprint-override"><code>from sqlalchemy import CheckConstraint, Column, Integer, String, create_engine
  from sqlalchemy.exc import IntegrityError
  from sqlalchemy.ext.declarative import declarative_base
  from sqlalchemy.orm import sessionmaker
  
  engine = create_engine("sqlite:///:memory:", echo=True)
  Base = declarative_base(bind=engine)
  Session = sessionmaker(bind=engine)
  
  
  class Foo(Base):
      __tablename__ = "Foo"
  
      id = Column(Integer, primary_key=True)
      bar = Column(String(10), CheckConstraint("LENGTH(bar) &lt; 10"))
  
  
  Base.metadata.create_all()
  
  if __name__ == "__main__":
      session = Session()
      session.add(Foo(bar="a" * 20))
  
      try:
          session.commit()
      except IntegrityError as e:
          print(f"Failed with: {e.orig}")
  </code></pre>
  
  <p>It works but the SQL <strong>constraint expression is not generated by SQLAlchemy</strong>. So it may require some custom generation if a DBMS needs a different syntax.</p>
  
  <h2>Validator</h2>
  
  <p>I also tried to use an SQLAlchemy <a href="https://docs.sqlalchemy.org/en/13/orm/mapped_attributes.html#simple-validators" rel="nofollow noreferrer">validator</a>:</p>
  
  <pre class="lang-py prettyprint-override"><code>class Foo(Base):
      __tablename__ = "Foo"
  
      id = Column(Integer, primary_key=True)
      bar = Column(String(10))
  
      @validates("bar")
      def check_bar_length(self, key, value):
          column_type = getattr(type(self), key).expression.type
          max_length = column_type.length
  
          if len(value) &gt; max_length:
              raise ValueError(
                  f"Value '{value}' for column '{key}' "
                  f"exceed maximum length of '{max_length}'"
              )
  
          return value
  </code></pre>
  
  <pre class="lang-py prettyprint-override"><code>try:
      Foo(bar="a" * 20)
  except ValueError as e:
      print(f"Failed with: {e}")
  </code></pre>
  
  <p>Now, the maximum length is inferred from the declared one.</p>
  
  <p>The check is done on entity creation, not on commit. I don't know if it can be a problem.</p>
  
  <h2>Custom type</h2>
  
  <p><strong>Both solutions shown above need to apply the validation on each column</strong>. I'm looking for a solution to perform automatically the check on String/Unicode columns having a declared length.</p>
  
  <p>Using a <a href="https://docs.sqlalchemy.org/en/13/core/custom_types.html#augmenting-existing-types" rel="nofollow noreferrer">custom type</a> might be the solution. But it looks like a ugly hack as custom types are not made for data validation but for data transformation.</p>
  
  <p>So, do you think about another solution, maybe an SQLAlchemy feature I don't know about, which will help me to add the check automatically to all <code>String</code> columns where a <code>length</code> is specified ?</p>