aminalaee/sqladmin

SQLAlchemy Admin for FastAPI and Starlette

---

SQLAlchemy Admin for Starlette/FastAPI

SQLAdmin is a flexible Admin interface for SQLAlchemy models.

Main features include:

• SQLAlchemy sync/async engines
• Starlette integration
• FastAPI integration
• WTForms form building
• SQLModel support
• UI using Tabler

---

Documentation: https://aminalaee.dev/sqladmin

Source Code: https://github.com/aminalaee/sqladmin

Online Demo: Demo

---

Installation

Install using pip:

$ pip install sqladmin

This will install the full version of sqladmin with optional dependencies:

$ pip install "sqladmin[full]"

---

Screenshots

Quickstart

Let's define an example SQLAlchemy model:

from sqlalchemy import Column, Integer, String, create_engine
from sqlalchemy.orm import declarative_base


Base = declarative_base()
engine = create_engine(
    "sqlite:///example.db",
    connect_args={"check_same_thread": False},
)


class User(Base):
    __tablename__ = "users"

    id = Column(Integer, primary_key=True)
    name = Column(String)


Base.metadata.create_all(engine)  # Create tables

If you want to use SQLAdmin with FastAPI:

from fastapi import FastAPI
from sqladmin import Admin, ModelView


app = FastAPI()
admin = Admin(app, engine)


class UserAdmin(ModelView, model=User):
    column_list = [User.id, User.name]


admin.add_view(UserAdmin)

Or if you want to use SQLAdmin with Starlette:

from sqladmin import Admin, ModelView
from starlette.applications import Starlette


app = Starlette()
admin = Admin(app, engine)


class UserAdmin(ModelView, model=User):
    column_list = [User.id, User.name]


admin.add_view(UserAdmin)

Now visiting /admin on your browser you can see the SQLAdmin interface.

Related projects and inspirations

• Flask-Admin Admin interface for Flask supporting different database backends and ORMs. This project has inspired SQLAdmin extensively and most of the features and configurations are implemented the same.
• FastAPI-Admin Admin interface for FastAPI which works with TortoiseORM.
• Dashboard Admin interface for ASGI frameworks which works with the orm package.

sqladmin's Issues

Lazy support

Checklist

• There are no similar issues or pull requests for this yet.

Is your feature related to a problem? Please describe.

Hello. In my project I have a lot relationships with lazy="dynamic". But sqladmin don't supports it

Describe the solution you would like.

I would like to see setting in config like load_lazys . If if is True load all relationships

Describe alternatives you considered

No response

Additional context

No response

Any way to manage in batch?

Checklist

• There are no similar issues or pull requests for this yet.

Is your feature related to a problem? Please describe.

I want to be ablet to add new users in batch, but I didn't find such a feature

Describe the solution you would like.

No response

Describe alternatives you considered

No response

Additional context

No response

Datetime field can't choose from panel

Checklist

• The bug is reproducible against the latest release or master.
• There are no similar issues or pull requests to fix it yet.

Describe the bug

I used SQLAdmin in my personal project(That's awesome 🥳), But the datetime field need to enter manually:

Maybe choose from panel is better:

Steps to reproduce the bug

No response

Expected behavior

No response

Actual behavior

No response

Debugging material

No response

Environment

os: mac
python: 3.9
sqladmin: lastest

Additional context

No response

Support for additional icons

Checklist

• There are no similar issues or pull requests for this yet.

Is your feature related to a problem? Please describe.

No response

Describe the solution you would like.

eg how FastAPI-Admin can specify tabler icons https://tablericons.com/

Describe alternatives you considered

No response

Additional context

No response

Cleaning up pagination

Right now the pagination only shows the current page plus previous and next.

It would be great to always have 7 pages shown (if applicable) and show previous and next pages.

Support for registering custom converters

Checklist

• There are no similar issues or pull requests for this yet.

Is your feature related to a problem? Please describe.

There doesn't seem to be an obvious way to register converter functions with @converts or subclass ModelConverter.

This might also be a bug where ModelConverterBase.get_converter is unable to recognize TypeDecorator types that extend a type that already has a converter.

Describe the solution you would like.

Possibly utilizing a global registry for @converts.

Describe alternatives you considered

No response

Additional context

Encountered while trying to create a ModelAdmin for a SQLModel (related to #57)

Exception: Could not find field converter for column name (<class 'sqlmodel.sql.sqltypes.AutoString'>). where AutoString extends String

EDIT:
Got it to work by setting the sa_column= on the SQLModel field:

class MyModel(SQLModel):
    # name: str = Field(..., index=True) # broken
    name: str = Field(..., sa_column=Column(String(length=512))) # works

I believe the feature request still has value

BooleanField should not be `required`

Checklist

• The bug is reproducible against the latest release or master.
• There are no similar issues or pull requests to fix it yet.

Describe the bug

The form adds the required attribute to its checkbox input when the corresponding Boolean column is not nullable, but in this situation, the browser never accepts a false value due to its configured validation.

(It says "Please check this box if you want to proceed." in Japanese)

Steps to reproduce the bug

from sqlalchemy import Boolean, Column, ForeignKey, Integer, create_engine
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()
engine = create_engine(...)


class User(Base):
    __tablename__ = "users"

    id = Column(Integer, primary_key=True)
    is_active = Column(Boolean, nullable=False, default=False)


Base.metadata.create_all(engine)

from fastapi import FastAPI
from sqladmin import Admin, ModelAdmin

app = FastAPI()
admin = Admin(app, engine)


class UserAdmin(ModelAdmin, model=User):
    column_list = [User.id, User.is_active]


admin.register_model(UserAdmin)

Expected behavior

No response

Actual behavior

No response

Debugging material

No response

Environment

• Ubuntu 20.04.4 LTS
• Python 3.9.12
• SQLAdmin 0.1.9

Additional context

No response

View Template: AttributeError: 'NoneType' object has no attribute 'id'

Checklist

• The bug is reproducible against the latest release or master.
• There are no similar issues or pull requests to fix it yet.

Describe the bug

I'm getting a 500 error when accessing details.

Steps to reproduce the bug

1. Select the model in /admin/.
2. Click view on any item.

Expected behavior

Load the view template.

Actual behavior

500 error.
Exception.

Debugging material

INFO: 127.0.0.1:63769 - "GET /file/details/c09f63a4-7093-45f6-8498-17bbcd7e40e6 HTTP/1.1" 500 Internal Server Error
ERROR: Exception in ASGI application
Traceback (most recent call last):
File "/Users/theber/code/college-toolkit/.venv/lib/python3.9/site-packages/uvicorn/protocols/http/httptools_impl.py", line 372, in run_asgi
result = await app(self.scope, self.receive, self.send)
File "/Users/theber/code/college-toolkit/.venv/lib/python3.9/site-packages/uvicorn/middleware/proxy_headers.py", line 75, in call
return await self.app(scope, receive, send)
File "/Users/theber/code/college-toolkit/.venv/lib/python3.9/site-packages/fastapi/applications.py", line 269, in call
await super().call(scope, receive, send)
File "/Users/theber/code/college-toolkit/.venv/lib/python3.9/site-packages/starlette/applications.py", line 124, in call
await self.middleware_stack(scope, receive, send)
File "/Users/theber/code/college-toolkit/.venv/lib/python3.9/site-packages/starlette/middleware/errors.py", line 184, in call
raise exc
File "/Users/theber/code/college-toolkit/.venv/lib/python3.9/site-packages/starlette/middleware/errors.py", line 162, in call
await self.app(scope, receive, _send)
File "/Users/theber/code/college-toolkit/.venv/lib/python3.9/site-packages/starlette/exceptions.py", line 93, in call
raise exc
File "/Users/theber/code/college-toolkit/.venv/lib/python3.9/site-packages/starlette/exceptions.py", line 82, in call
await self.app(scope, receive, sender)
File "/Users/theber/code/college-toolkit/.venv/lib/python3.9/site-packages/fastapi/middleware/asyncexitstack.py", line 21, in call
raise e
File "/Users/theber/code/college-toolkit/.venv/lib/python3.9/site-packages/fastapi/middleware/asyncexitstack.py", line 18, in call
await self.app(scope, receive, send)
File "/Users/theber/code/college-toolkit/.venv/lib/python3.9/site-packages/starlette/routing.py", line 670, in call
await route.handle(scope, receive, send)
File "/Users/theber/code/college-toolkit/.venv/lib/python3.9/site-packages/starlette/routing.py", line 418, in handle
await self.app(scope, receive, send)
File "/Users/theber/code/college-toolkit/.venv/lib/python3.9/site-packages/starlette/applications.py", line 124, in call
await self.middleware_stack(scope, receive, send)
File "/Users/theber/code/college-toolkit/.venv/lib/python3.9/site-packages/starlette/middleware/errors.py", line 184, in call
raise exc
File "/Users/theber/code/college-toolkit/.venv/lib/python3.9/site-packages/starlette/middleware/errors.py", line 162, in call
await self.app(scope, receive, _send)
File "/Users/theber/code/college-toolkit/.venv/lib/python3.9/site-packages/starlette/exceptions.py", line 93, in call
raise exc
File "/Users/theber/code/college-toolkit/.venv/lib/python3.9/site-packages/starlette/exceptions.py", line 82, in call
await self.app(scope, receive, sender)
File "/Users/theber/code/college-toolkit/.venv/lib/python3.9/site-packages/starlette/routing.py", line 670, in call
await route.handle(scope, receive, send)
File "/Users/theber/code/college-toolkit/.venv/lib/python3.9/site-packages/starlette/routing.py", line 266, in handle
await self.app(scope, receive, send)
File "/Users/theber/code/college-toolkit/.venv/lib/python3.9/site-packages/starlette/routing.py", line 65, in app
response = await func(request)
File "/Users/theber/code/college-toolkit/.venv/lib/python3.9/site-packages/sqladmin/application.py", line 286, in details
return self.templates.TemplateResponse(model_admin.details_template, context)
File "/Users/theber/code/college-toolkit/.venv/lib/python3.9/site-packages/starlette/templating.py", line 98, in TemplateResponse
return _TemplateResponse(
File "/Users/theber/code/college-toolkit/.venv/lib/python3.9/site-packages/starlette/templating.py", line 37, in init
content = template.render(context)
File "/Users/theber/code/college-toolkit/.venv/lib/python3.9/site-packages/jinja2/environment.py", line 1301, in render
self.environment.handle_exception()
File "/Users/theber/code/college-toolkit/.venv/lib/python3.9/site-packages/jinja2/environment.py", line 936, in handle_exception
raise rewrite_traceback_stack(source=source)
File "/Users/theber/code/college-toolkit/.venv/lib/python3.9/site-packages/sqladmin/templates/details.html", line 1, in top-level template code
{% extends "layout.html" %}
File "/Users/theber/code/college-toolkit/.venv/lib/python3.9/site-packages/sqladmin/templates/layout.html", line 1, in top-level template code
{% extends "base.html" %}
File "/Users/theber/code/college-toolkit/.venv/lib/python3.9/site-packages/sqladmin/templates/base.html", line 15, in top-level template code
{% block body %}
File "/Users/theber/code/college-toolkit/.venv/lib/python3.9/site-packages/sqladmin/templates/layout.html", line 49, in block 'body'
{% block content %} {% endblock %}
File "/Users/theber/code/college-toolkit/.venv/lib/python3.9/site-packages/sqladmin/templates/details.html", line 29, in block 'content'
{{ model_admin.get_detail_value(model, attr) }}
File "/Users/theber/code/college-toolkit/.venv/lib/python3.9/site-packages/sqladmin/models.py", line 592, in _url_for_details_with_attr
pk = getattr(target, attr.mapper.primary_key[0].name)
AttributeError: 'NoneType' object has no attribute 'id'

Environment

MacOSX, Python3.9, 0.1.9

Additional context

This is my models file:

# https://gist.github.com/gmolveau/7caeeefe637679005a7bb9ae1b5e421e
class GUID(TypeDecorator):
    """Platform-independent GUID type.
    Uses PostgreSQL's UUID type, otherwise uses
    CHAR(32), storing as stringified hex values.
    """

    impl = CHAR
    cache_ok = True

    def load_dialect_impl(self, dialect):
        if dialect.name == "postgresql":
            return dialect.type_descriptor(UUID())
        else:
            return dialect.type_descriptor(CHAR(32))

    def process_bind_param(self, value, dialect):
        if value is None:
            return value
        elif dialect.name == "postgresql":
            return str(value)
        else:
            if not isinstance(value, uuid.UUID):
                return "%.32x" % uuid.UUID(value).int
            else:
                # hexstring
                return "%.32x" % value.int

    def _uuid_value(self, value):
        if value is None:
            return value
        else:
            if not isinstance(value, uuid.UUID):
                value = uuid.UUID(value)
            return value

    def process_result_value(self, value, dialect):
        return self._uuid_value(value)

    def sort_key_function(self, value):
        return self._uuid_value(value)


class ModelBase(Base):
    __abstract__ = True

    id = Column(GUID(), primary_key=True, default=lambda: str(uuid.uuid4()))


class File(ModelBase):
    __tablename__ = "files"

    name = Column(String(300), index=True)
    file_id = Column(String(256), unique=True)
    toolkit_id = Column(GUID(), ForeignKey("toolkits.id"), index=True)

    toolkit = relationship("Toolkit", back_populates="files")

[Bug] Docs are rendering docstrings from `wtforms.Form` and not `form_base_class`.

Checklist

• The bug is reproducible against the latest release or master.
• There are no similar issues or pull requests to fix it yet.

Describe the bug

This is not intended:

Steps to reproduce the bug

Deploy the docs.

Expected behavior

The docstring should be from the file sqladmin/models.py:

Actual behavior

No response

Debugging material

No response

Environment

Github Actions

Additional context

No response

There is no way to customize object_list for form_args.

Checklist

• There are no similar issues or pull requests for this yet.

Is your feature related to a problem? Please describe.

I want to set up a custom objects_list for the QuerySelectField field, but I can't because this property is overridden in the ModelConverter.convert method.

Describe the solution you would like.

No response

Describe alternatives you considered

No response

Additional context

No response

Errors with Postgres UUID primary key

Checklist

• The bug is reproducible against the latest release or master.
• There are no similar issues or pull requests to fix it yet.

Describe the bug

The call self.pk_column.type.python_type fails when the pk is a Postgres UUID field with a NotImplementedError. Maybe this call can be replaced with something along the lines of

from sqlalchemy.dialects.postgresql import UUID

...

    def coerce_to_pk_value(self, value):

        if isinstance(self.pk_column.type, UUID):
            return str(value)

        return self.pk_column.type.python_type(value)

...

            stmt = select(self.model).where(self.pk_column == self.coerce_to_pk_value(value))

Alternatively, if it's not desirable importing specific dialects such as Postgres, maybe we could pass a custom value coercion function to each admin model?

Steps to reproduce the bug

No response

Expected behavior

No response

Actual behavior

No response

Debugging material

No response

Environment

Python 3.10

Additional context

No response

Setting up Github Actions for other databases

Place an "edit" button on the detail page

Checklist

• There are no similar issues or pull requests for this yet.

Is your feature related to a problem? Please describe.

It is useful if the detail page has an edit button.

Describe the solution you would like.

I think the edit button for each record in the list view can be removed if the detail page has it. The small icon in the list is a bit tiny and prone to misclick.

Describe alternatives you considered

No response

Additional context

No response

Expose Starlette options

Checklist

• There are no similar issues or pull requests for this yet.

Is your feature related to a problem? Please describe.

It'd be great to expose the middleware and debug parameters to pass to the Starlette admin app. This would allow, for example, to create a middleware that checks for an "admin" session cookie and redirect the user in case the cookie doesn't exist. The debug option is useful when developing.

Describe the solution you would like.

No response

Describe alternatives you considered

No response

Additional context

No response

Now the json field is only for viewing and printing. Automatic generation is needed.

Checklist

• There are no similar issues or pull requests for this yet.

Is your feature related to a problem? Please describe.

There are:
class JSONField(fields.TextAreaField):
Necessary:
class JSONField(fields.FileField):

Describe the solution you would like.

In the web forms I have configured https://accent-starlette.github.io/starlette-files/handling_files/
file = sa.Column(FileType.as_mutable(sa.JSON), nullable=True)
views.py
The result is excellent
In the admin area of the coma

Describe alternatives you considered

Alternative solution: I have not found.

Additional context

Now the json field is only for viewing and printing. Automatic generation is needed.

Make `get_attr_value` accessible from formatter

Checklist

• There are no similar issues or pull requests for this yet.

Is your feature related to a problem? Please describe.

I'm writing custom formatters:

class AddressAdmin(ModelAdmin, model=Address):
    ...
    _formatters = {
        Address.user: lambda m, a: formatters.format_user(m.user, a),
    }
    column_formatters = _formatters
    column_formatters_detail = _formatters

but accessing its attribute like m.user everytime in the format function is a bit verbose and error prone. I'd like to use get_attr_value method implemented in ModelAdmin.

Describe the solution you would like.

No response

Describe alternatives you considered

No response

Additional context

No response

Simple Edit page

Right now we have List/Create/Detail/Delete.

Next one will be a simple Edit page.

`ModelConverter` fails to handle one-to-one relationship

Checklist

• The bug is reproducible against the latest release or master.
• There are no similar issues or pull requests to fix it yet.

Describe the bug

get_converter() in ModelConverterBase selects QuerySelectMultipleField instead of QuerySelectField for a column with ONETOMANY direction even if its uselist is False (i.e. the column is defined as one-to-one relationship).

If a scalar is desired where normally a list would be present, such as a bi-directional one-to-one relationship, set relationship.uselist to False.

Because of that, the following code tries to iterate over the non-iterable column data and fails with Internal Server Error in edit pages:

Steps to reproduce the bug

from sqlalchemy import Column, ForeignKey, Integer, create_engine
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()
engine = create_engine(...)


class User(Base):
    __tablename__ = "user"

    id = Column(Integer, primary_key=True)
    address: "Address" = relationship(
        "Address",
        back_populates="user",
        uselist=False,  # indicates it is one-to-one relationship
    )


class Address(Base):
    __tablename__ = "address"

    id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey("user.id"), unique=True, nullable=False)
    user: User = relationship("User", back_populates="address")


Base.metadata.create_all(engine)

from fastapi import FastAPI
from sqladmin import Admin, ModelAdmin

app = FastAPI()
admin = Admin(app, engine)


class UserAdmin(ModelAdmin, model=User):
    column_list = [User.id, User.address]


admin.register_model(UserAdmin)

Expected behavior

No response

Actual behavior

No response

Debugging material

Traceback (most recent call last):
  ...
  File "/home/myproject/.venv/lib/python3.9/site-packages/sqladmin/templates/edit.html", line 18, in block 'content'
    {{ field() }}
  File "/home/myproject/.venv/lib/python3.9/site-packages/wtforms/fields/core.py", line 172, in __call__
    return self.meta.render_field(self, kwargs)
  File "/home/myproject/.venv/lib/python3.9/site-packages/wtforms/meta.py", line 64, in render_field
    return field.widget(field, **render_kw)
  File "/home/myproject/.venv/lib/python3.9/site-packages/wtforms/widgets/core.py", line 351, in __call__
    for val, label, selected in field.iter_choices():
  File "/home/myproject/.venv/lib/python3.9/site-packages/sqladmin/fields.py", line 411, in iter_choices
    primary_keys = [str(inspect(m).identity[0]) for m in self.data]
TypeError: 'Address' object is not iterable

Environment

• Ubuntu 20.04.4 LTS
• Python 3.9.12
• SQLAdmin 0.1.9

Additional context

Here is a workaround for this issue:

class UserAdmin(ModelAdmin, model=User):
    ...
    form_overrides = {
        "address": QuerySelectField,  # workaround for aminalaee/sqladmin#180
    }

Multiple PK columns support

Discussed in #59

^{Originally posted by javtau February 19, 2022}
Hi, this is so great,
Are you thinking on implement the multiple pk support for foreign primary keys like flask Admin?

Ability to choose format of datetimes in list/table view

Checklist

• There are no similar issues or pull requests for this yet.

Is your feature related to a problem? Please describe.

Currently the values for datetime columns seem to be rendered as ISO-8601 with millisecond precision which is unnecessary (eg 2022-04-04 22:43:30.027151)

Describe the solution you would like.

It'd be great if we had a way to pass some sort of custom formatter, ideally a callable that takes the DB value and returns the value that should be passed to the renderer.

Describe alternatives you considered

No response

Additional context

No response

get_converter() fails with AttributeError: '...' object has no attribute '__name__'

Checklist

• The bug is reproducible against the latest release or master.
• There are no similar issues or pull requests to fix it yet.

Describe the bug

get_converter() in ModelConverterBase expects that impl class level attribute in column types inheriting TypeDecorator has TypeEngine type, but it also accepts its instantiated value:

If the class level impl is not a callable (the unusual case), it will be assigned to the same instance attribute ‘as-is’, ignoring those arguments passed to the constructor.

In that case, get_converter() will raise AttributeError with a message '...' object has no attribute '__name__'.

Steps to reproduce the bug

By implementing the following code, http://[host]/admin/user/edit/1 will return Internal Server Error.

sqlalchemy_utc produces a custom UtcDateTime type whose impl attribute has DateTime(timezone=True) instance instead of DateTime class.

from sqlalchemy import Column, Integer, create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy_utc import UtcDateTime, utcnow

Base = declarative_base()
engine = create_engine(...)


class User(Base):
    __tablename__ = "user"

    id = Column(Integer, primary_key=True)
    created_at = Column(UtcDateTime, default=utcnow())


Base.metadata.create_all(engine)

from fastapi import FastAPI
from sqladmin import Admin, ModelAdmin

app = FastAPI()
admin = Admin(app, engine)


class UserAdmin(ModelAdmin, model=User):
    column_list = [User.id, User.created_at]


admin.register_model(UserAdmin)

Expected behavior

No response

Actual behavior

No response

Debugging material

Traceback (most recent call last):
  ...
  File "/home/myproject/.venv/lib/python3.9/site-packages/sqladmin/application.py", line 312, in create
    Form = await model_admin.scaffold_form()
  File "/home/myproject/.venv/lib/python3.9/site-packages/sqladmin/models.py", line 834, in scaffold_form
    return await get_model_form(
  File "/home/myproject/.venv/lib/python3.9/site-packages/sqladmin/forms.py", line 415, in get_model_form
    field = await converter.convert(
  File "/home/myproject/.venv/lib/python3.9/site-packages/sqladmin/forms.py", line 232, in convert
    converter = self.get_converter(model=model, prop=prop)
  File "/home/myproject/.venv/lib/python3.9/site-packages/sqladmin/forms.py", line 113, in get_converter
    if col_type.impl.__name__ in self._converters:  # type: ignore
AttributeError: 'DateTime' object has no attribute '__name__'

Environment

• Ubuntu 20.04.4 LTS
• Python 3.9.12
• SQLAdmin 0.1.9

Additional context

No response

Be able to hook into the fastAPI subapp

Checklist

• There are no similar issues or pull requests for this yet.

Is your feature related to a problem? Please describe.

Hello, great work ! :)

I would like to be able to add my own url paths below the root endpoint of the admin app.
For example I would like to be able to do a POST on the homepage (url "/")

Something that would look like this

@router.post("")
async def post_homepage(request: Request):
    logger.warning("post !!")
    return templates.TemplateResponse("index.html", {"request": request})

From what I understand it is not possible to access or modify the sub app
Neither is it possible to mount another sub application on the same endpoint (because routes would conflict)

It would be great if we could use the default routes but also add some and override existing routes

Describe the solution you would like.

from backoffice import homepage

admin = Admin(app, create_engine(Settings().db_uri), base_url="/backoffice")

admin.include_router(homepage.router)

routes could be added before the existing routes to have priority

Describe alternatives you considered

No response

Additional context

No response

Implementing `page_size` option in list page

In the list page we have a select field: Show [10,25, 50, 100] like that.

We need to change list pagination size by switching the size field.

This needs to send query like /admin/users/list?page_size=X

Add some screenshots to the documentation

Checklist

• There are no similar issues or pull requests for this yet.

Is your feature related to a problem? Please describe.

I would really like to see some screenshots in the documentation similar to Fastapi Admin

This will help you better understand what the admin panel is like without downloading and importing the library.

Describe the solution you would like.

No response

Describe alternatives you considered

No response

Additional context

No response

Add a way to configure default `sort` and `sortBy`

Checklist

• There are no similar issues or pull requests for this yet.

Is your feature related to a problem? Please describe.

It is useful if we can configure default sort and sortBy in ModelAdmin (e.g. sortBy="created_at" and sort="desc" for log tables).

Describe the solution you would like.

class Log(Base):
    __tablename__ = "logs"

    id = Column(BigInteger, primary_key=True)
    created_at = Column(DateTime)


class LogAdmin(ModelAdmin, model=Log):
    sortBy = "created_at"
    sort = "desc"

Describe alternatives you considered

I think it is useful if there is a way to specify custom Statement directly (like get_queryset in django-admin) because it also adds functionality to optimize the query:

class LogAdmin(ModelAdmin, model=Log):
    def get_statement(self, request):
        ...

Note that a pain point of Django's implementation is that get_queryset is shared both in SELECT and INSERT/UPDATE/DELETE. We need a cleaner way to do it.

Additional context

No response

Import not working

Hi.

I installed it with pip but i cant import "from sqladmin import Admin, ModelAdmin"... its not working.

Any help?

Thanks

Switch internal icons to FontAwesome6

Checklist

• There are no similar issues or pull requests for this yet.

Is your feature related to a problem? Please describe.

No response

Describe the solution you would like.

No response

Describe alternatives you considered

No response

Additional context

No response

`column_formatters` doesn't work with one-to-many relation

Checklist

• The bug is reproducible against the latest release or master.
• There are no similar issues or pull requests to fix it yet.

Describe the bug

The details page fails to render the list value formatted by column_formatters and column_formatters_detail because the following template tries to pass it to _url_for_details() instead of the original model value.

sqladmin/sqladmin/templates/details.html

Lines 24 to 26 in 83d8d25

	{% for value in model_admin.get_detail_value(model, attr) %}
	<a href="{{ model_admin._url_for_details(value) }}">({{ value }})</a>
	{% endfor %}

Steps to reproduce the bug

from typing import List

from sqlalchemy import Column, ForeignKey, Integer, create_engine
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()
engine = create_engine(...)


class User(Base):
    __tablename__ = "users"

    id = Column(Integer, primary_key=True)
    addresses: List["Address"] = relationship("Address", back_populates="user")


class Address(Base):
    __tablename__ = "addresses"

    id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey("users.id"), nullable=False)
    user: User = relationship("User", back_populates="addresses")


Base.metadata.create_all(engine)

from fastapi import FastAPI
from sqladmin import Admin, ModelAdmin

app = FastAPI()
admin = Admin(app, engine)


class UserAdmin(ModelAdmin, model=User):
    column_list = [User.id, User.address]
    column_formatters = {
        User.addresses: lambda m, a: [a.id for a in m.addresses],
    }


admin.register_model(UserAdmin)

Expected behavior

No response

Actual behavior

No response

Debugging material

  File "/myproject/.venv/lib/python3.9/site-packages/sqladmin/templates/details.html", line 25, in block 'content'
    <a href="{{ model_admin._url_for_details(value) }}">({{ value }})</a>
  File "/myproject/.venv/lib/python3.9/site-packages/sqladmin/models.py", line 567, in _url_for_details
    pk = getattr(obj, inspect(obj).mapper.primary_key[0].name)
  File "/myproject/.venv/lib/python3.9/site-packages/sqlalchemy/inspection.py", line 71, in inspect
    raise exc.NoInspectionAvailable(
sqlalchemy.exc.NoInspectionAvailable: No inspection system is available for object of type <class 'str'>

Environment

• Ubuntu 20.04.4 LTS
• Python 3.9.12
• SQLAdmin 0.1.9

Additional context

No response

No support for SQLModel relationships

Checklist

• The bug is reproducible against the latest release or master.
• There are no similar issues or pull requests to fix it yet.

Describe the bug

SQLModel does not store relationships data directly in attributes, so trying to use them like vanilla SQLAlchemy attributes like that

class ModelA(SQLModel, table=True):
    id: uuid.UUID = Field(default_factory=uuid.uuid4, primary_key=True, nullable=False)
    

class ModelB(SQLModel, table=True):
    id: uuid.UUID = Field(default_factory=uuid.uuid4, primary_key=True, nullable=False)
    model_a: Optional[ModelA] = Relationship(sa_relationship=relationship("ModelA"))


class ModelBAdmin(ModelAdmin, model=ModelB):
    column_list = [ModelB.model_a]

results in error:

AttributeError: type object 'ModelB' has no attribute 'model_a'

Trying to use attribute used by SQLModel does not work either:

class ModelBAdmin(ModelAdmin, model=ModelB):
    column_list = [ModelB.__sqlmodel_relationships__["model_a"].sa_relationship]

error:

 File "/usr/local/lib/python3.9/site-packages/sqladmin/models.py", line 590, in get_model_attr
assert isinstance(attr, (str, InstrumentedAttribute))
 AssertionError

since what is checked in this assertion is sqlalchemy.orm.RelationshipProperty

I tried to work around that making some patch:

from typing import Union

from sqladmin.models import ModelAdmin
from sqlalchemy.orm import InstrumentedAttribute, RelationshipProperty, ColumnProperty


class ModelAdminWithSQLModelRelationships(ModelAdmin):

    def get_model_attr(
        self, attr: Union[str, InstrumentedAttribute, RelationshipProperty]
    ) -> Union[ColumnProperty, RelationshipProperty]:
        if isinstance(attr, RelationshipProperty):
            return attr

        return super().get_model_attr(attr)

But it didn't work either

File "/usr/local/lib/python3.9/site-packages/sqlalchemy/util/langhelpers.py", line 1214, in _fallback_getattr
  raise AttributeError(key)
AttributeError: key

so I've added some utitlity function to workaround that:

def get_sqlmodel_relationship(model, name):
    rel = model.__sqlmodel_relationships__[name].sa_relationship
    rel.key = name
    return rel


class ModelBAdmin(ModelAdminWithSQLModelRelationships, model=ModelB):
    column_list = [get_sqlmodel_relationship(ModelB, "model_a")]

At this point app could complete startup and I could access admin panel, but when I tried to access list of ModelB objects exception was raised:

sqlalchemy.exc.ArgumentError: Can't find property named "model_a" on mapped class ModelB->modelb in this Query.

so, at this point I gave up, it seems that it would need much more monkeypatching than I anticipated

Steps to reproduce the bug

No response

Expected behavior

Possibility to use SQLModel Relationship in admin panel just like in vanilla SQLAlchemy

Actual behavior

No response

Debugging material

No response

Environment

OS: Manjaro
Python: 3.9.7
SQLAlchemy: 1.4.36

Additional context

No response

Exception: Could not find field converter for column id (<class 'sqlmodel.sql.sqltypes.GUID'>)

Discussed in #155

^{Originally posted by Anton-Karpenko May 26, 2022}
Hey, I am using sqlmodel to create models. I use the UUID type for the id columns.

class RandomModel(SQLModel, table=True):
    id: uuid.UUID = Field(primary_key=True, index=True, nullable=False, default_factory=uuid.uuid4)

I added sqladmin to my project and I would like to create an instance within the admin panel. I cannot open create page because of an error.
Exception: Could not find field converter for column id (<class 'sqlmodel.sql.sqltypes.GUID'>)
Can I apply a custom converter to it?

Enable SQLAlchemy V2 features

Need to check SQLAlchemy V2 migration steps. As far as I can see we're using SQLAlchemy 1.4 features, It should be ready, but needs checking and fixing.

Add support for Ormar orm

Checklist

• There are no similar issues or pull requests for this yet.

Is your feature related to a problem? Please describe.

No response

Describe the solution you would like.

Hello !

First, thanks for this amazing package, this community really needs this.

I tried to use sqladmin with Ormar https://github.com/collerek/ormar/ which is basically another ORM built on top of SQLalchemy. And it seems that sqladmin has troubles with fields, even though under the hood it's SQLachemy, I think you have the same problem while implemtenting SQLModel.

So I figured out maybe if we implement for SQLmodel, we shouldn't be that far from being to implement another ORM.

Traceback (most recent call last):
  File "/.cache/pypoetry/virtualenvs/api-5UH-8tMH-py3.10/lib/python3.10/site-packages/sqladmin/models.py", line 57, in __new__
    mapper = inspect(model)
  File "ache/pypoetry/virtualenvs/-5UH-8tMH-py3.10/lib/python3.10/site-packages/sqlalchemy/inspection.py", line 71, in inspect
    raise exc.NoInspectionAvailable(
sqlalchemy.exc.NoInspectionAvailable: No inspection system is available for object of type <class 'ormar.models.metaclass.ModelMetaclass'>

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/pegazor_api/__main__.py", line 95, in <module>
    class UserAdmin(ModelAdmin, model=User):
  File "/pypoetry/virtualenvs/i-5UH-8tMH-py3.10/lib/python3.10/site-packages/sqladmin/models.py", line 59, in __new__
    raise InvalidModelError(
sqladmin.exceptions.InvalidModelError: Class User is not a SQLAlchemy model.

Thank you :)

Describe alternatives you considered

No response

Additional context

No response

Accept JSON type

Checklist

• There are no similar issues or pull requests for this yet.

Is your feature related to a problem? Please describe.

I want to be able to show JSON column in detail page.

Could not find field converter for column questions (<class 'sqlalchemy.dialects.postgresql.json.JSON'>).

Describe the solution you would like.

No response

Describe alternatives you considered

No response

Additional context

No response

Form generation with enum has an bug

Checklist

• The bug is reproducible against the latest release or master.
• There are no similar issues or pull requests to fix it yet.

Describe the bug

I have model with enum. When I open edit view, none of the options in the html have the selected attribute.

Steps to reproduce the bug

class CompanyPermissionEnum(enum.Enum):
    project_create = "project_create"
    project_read = "project_read"
    project_update = "project_update"
    
    
class CompanyUserPermission(Base):
    ...
    permission = Column(
        Enum(CompanyPermissionEnum, name="company_permission"),
        nullable=False,
    )

Expected behavior

No response

Actual behavior

No response

Debugging material

No response

Environment

• python 3.9
• sqlalchemy 1.4.31

Additional context

according to https://stackoverflow.com/a/51858425 I have fixed like this

def coerce_for_enum(enum):
    def coerce(name):
        if isinstance(name, enum):
            return name
        try:
            return enum[name]
        except KeyError:
            raise ValueError(name)

    return coerce


class CustomModelConverter(ModelConverter):
    @converts("Enum")
    def conv_Enum(
        self, column: Column, field_args: dict, **kwargs: Any
    ) -> Field:
        field_args["choices"] = [(e, e) for e in column.type.enums]
        return SelectField(
            **field_args,
            coerce=coerce_for_enum(column.type.enum_class),
        )

Many to many field setup error

Checklist

• The bug is reproducible against the latest release or master.
• There are no similar issues or pull requests to fix it yet.

Describe the bug

I am trying to update m2m field in form but i am getting error "sqlalchemy.exc.InvalidRequestError: Can't attach instance another instance with key is already present in this session"

Steps to reproduce the bug

No response

Expected behavior

No response

Actual behavior

No response

Debugging material

No response

Environment

Macos , python 3.9

Additional context

No response

Support for .with_variant() types

Checklist

• There are no similar issues or pull requests for this yet.

Is your feature related to a problem? Please describe.

When using BigIntegers in sqlalchemy together with "with_variant", e.g. Column(BigInteger().with_variant(Integer, "sqlite")) the error "Could not find field converter for column id (<class 'sqlalchemy.sql.type_api.Variant'>)" is thrown.

Describe the solution you would like.

No response

Describe alternatives you considered

Using form_overrides seems to work fine as workaround however native support would be much appreciated :)

Additional context

No response

Boolean field checkbox doesn't work

Discussed in #86

^{Originally posted by maxiaojunwolf March 15, 2022}
here is my ORM Field:

    picture = Column(Boolean, default=False)

Link to relationship

Checklist

• There are no similar issues or pull requests for this yet.

Is your feature related to a problem? Please describe.

Add auto link in relationship. As example: https://python-sqladmin.herokuapp.com/admin/address/details/1 to get user I need to go to table users, find user(no search). Please make user_id a link to users

Describe the solution you would like.

user_id is a link to users

Describe alternatives you considered

No response

Additional context

No response

Add "form_columns" concept

Checklist

• There are no similar issues or pull requests for this yet.

Is your feature related to a problem? Please describe.

I am trying to emulate all the functionality of a Flask-Admin panel I made using SQLAdmin. The Flask website I'm attempting to emulate is open source and available here. It is able to be deployed in "demo" mode to Heroku so anyone can run it. (set FLASK_ENV=demo as you deploy it).

So far, I am running into an issue with complicated lazy relationships. Take the following SQLModel as an example:

This is a SQLModel with lots of complicated relationships at the bottom, but these relationships do not need to be manually defined in the admin panel. In the case of latest_prediction it's resolved via max(timestamp). In the case of all_predictions, it is a many-to-one relationship that is resolved through an associative entity (named Reach).

In Flask-Admin, I would work around this by explicitly defining form_columns and excluding these complicated fields. And for Flask-Admin, this is what I get for my Boathouse entity type:

But due to lack of a form_columns, I cannot make this happen in SQLAdmin. It is forcing me to include these columns, which means I effectively cannot use both SQLAdmin and these complicated fields at the same time:

Describe the solution you would like.

Flask-Admin has a concept called form_columns for its ModelView class. The form_columns attribute controls which columns that WTForms creates fields for.

This is a good catchall solution for many problems because it lets users avoid any issues relating to fields that do not need to be assigned as part of the initialization of an object (e.g. optional fields, fields with defaults, complicated relationships, and more).

Flask-Admin implements form_columns here, primarily: https://github.com/flask-admin/flask-admin/blob/7cff9c742d44d42a8d3495c73a6d71381c796396/flask_admin/contrib/sqla/form.py#L94

---

Right now, the function sqladmin.forms.get_model_form can handle inclusion or exclusion of fields. However, this functionality is not used. So the function would need to be passed a form_columns, or similar, from the ModelAdmin object.

This should not be too hard to implement-- just add form_columns to the ModelAdmin class (form_columns is adherent to the Flask-Admin API and is thus preferrable, albeit another naming such as form_column_list would be acceptable if deviations are allowed), and have that be passed through the scaffold_form() method.

Describe alternatives you considered

No response

Additional context

No response

TypeError: Boolean value of this clause is not defined

Checklist

• The bug is reproducible against the latest release or master.
• There are no similar issues or pull requests to fix it yet.

Describe the bug

Traceback during open 'Create' page (url for example): http://127.0.0.1:8000/admin/user/create

File "/Users/iuliia_volkova2/Library/Caches/pypoetry/virtualenvs/admin-panel-service-Ji2bEy0B-py3.7/lib/python3.7/site-packages/starlette/templating.py", line 37, in init
content = template.render(context)
File "/Users/iuliia_volkova2/Library/Caches/pypoetry/virtualenvs/admin-panel-service-Ji2bEy0B-py3.7/lib/python3.7/site-packages/jinja2/environment.py", line 1301, in render
self.environment.handle_exception()
File "/Users/iuliia_volkova2/Library/Caches/pypoetry/virtualenvs/admin-panel-service-Ji2bEy0B-py3.7/lib/python3.7/site-packages/jinja2/environment.py", line 936, in handle_exception
raise rewrite_traceback_stack(source=source)
File "/Users/iuliia_volkova2/Library/Caches/pypoetry/virtualenvs/admin-panel-service-Ji2bEy0B-py3.7/lib/python3.7/site-packages/sqladmin/templates/create.html", line 1, in top-level template code
{% extends "layout.html" %}
File "/Users/iuliia_volkova2/Library/Caches/pypoetry/virtualenvs/admin-panel-service-Ji2bEy0B-py3.7/lib/python3.7/site-packages/sqladmin/templates/layout.html", line 1, in top-level template code
{% extends "base.html" %}
File "/Users/iuliia_volkova2/Library/Caches/pypoetry/virtualenvs/admin-panel-service-Ji2bEy0B-py3.7/lib/python3.7/site-packages/sqladmin/templates/base.html", line 15, in top-level template code
{% block body %}
File "/Users/iuliia_volkova2/Library/Caches/pypoetry/virtualenvs/admin-panel-service-Ji2bEy0B-py3.7/lib/python3.7/site-packages/sqladmin/templates/layout.html", line 49, in block 'body'
{% block content %} {% endblock %}
File "/Users/iuliia_volkova2/Library/Caches/pypoetry/virtualenvs/admin-panel-service-Ji2bEy0B-py3.7/lib/python3.7/site-packages/sqladmin/templates/create.html", line 18, in block 'content'
{{ field() }}
File "/Users/iuliia_volkova2/Library/Caches/pypoetry/virtualenvs/admin-panel-service-Ji2bEy0B-py3.7/lib/python3.7/site-packages/wtforms/fields/core.py", line 172, in call
return self.meta.render_field(self, kwargs)
File "/Users/iuliia_volkova2/Library/Caches/pypoetry/virtualenvs/admin-panel-service-Ji2bEy0B-py3.7/lib/python3.7/site-packages/wtforms/meta.py", line 64, in render_field
return field.widget(field, **render_kw)
File "/Users/iuliia_volkova2/Library/Caches/pypoetry/virtualenvs/admin-panel-service-Ji2bEy0B-py3.7/lib/python3.7/site-packages/wtforms/widgets/core.py", line 174, in call
kwargs["value"] = field._value()
File "/Users/iuliia_volkova2/Library/Caches/pypoetry/virtualenvs/admin-panel-service-Ji2bEy0B-py3.7/lib/python3.7/site-packages/wtforms/fields/datetime.py", line 36, in _value
return self.data and self.data.strftime(self.format[0]) or ""
File "/Users/iuliia_volkova2/Library/Caches/pypoetry/virtualenvs/admin-panel-service-Ji2bEy0B-py3.7/lib/python3.7/site-packages/sqlalchemy/sql/elements.py", line 582, in bool
raise TypeError("Boolean value of this clause is not defined")
TypeError: Boolean value of this clause is not defined

Steps to reproduce the bug

I think something in model cause in issue - I send the sample

Also we use async SQLAlchemy engine. I will be glad if you will do some input where to dig or what can be a reason of the problem.

Expected behavior

Create form open correctly without issues.

Actual behavior

Impossible to open Create form

Debugging material

No response

Environment

SQLAdmin == 0.1.9
SQLAlchemy == 1.4.37

Additional context

No response

Offset query issue while using MSSQL

Error while query the Model :
sqlalchemy.exc.CompileError: MSSQL requires an order_by when using an OFFSET or a non-simple LIMIT clause

Why?
MSSQL requires an order_by when using an offset.

Setup:

• fastapi
• MSSQL Server 2019
• PyODBC and SqlAlchemy

Original code:

sqladmin/sqladmin/models.py

Line 261 in 2205de1

query = select(cls.model).limit(page_size).offset((page - 1) * page_size)

My change:
https://github.com/bigg01/sqladmin/blob/main/sqladmin/models.py#L262

#  sqlalchemy.exc.CompileError: MSSQL requires an order_by when using an OFFSET or a non-simple LIMIT clause
        query = select(cls.model).order_by("id").limit(page_size).offset((page - 1) * page_size)

I think in general it would make sense to add an option for ordering ?
What do you think ?

Regards

Show the form fields in the order of `only`

Checklist

• There are no similar issues or pull requests for this yet.

Is your feature related to a problem? Please describe.

get_model_form() receives only parameter in Sequence but ignores its order to build form fields.

Describe the solution you would like.

attributes should align to the order of only if specified like:

    attributes = []
    names = only or mapper.attrs.keys()
    for name in names:
        if exclude and name in exclude:
            continue
        attributes.append((name, mapper.attrs[name]))

Describe alternatives you considered

No response

Additional context

No response

Asynchronous loading altertive to `QuerySelectField`

Checklist

• There are no similar issues or pull requests for this yet.

Is your feature related to a problem? Please describe.

I need a QuerySelectField alternative that can fetch a limited number of items on demand like autocomplete_fields in Django admin, instead of loading entire records of the related (possibly very huge) table when rendering the page.

Describe the solution you would like.

No response

Describe alternatives you considered

No response

Additional context

No response

SQLModel support

Discussed in #56

^{Originally posted by JonasKs February 16, 2022}
Hi! This looks awesome, I’m definitely going to test it out!

Since this support SQLAlchemy, will it also support SQLModel? 😊

As suggested by @JonasKs, I did test SQLAdmin with SQLModel but it needs a few tweaks to make it work.
I'll get to it after we have the minimum functionality like #24 done.

Used Sqlalchemy_utils types

Checklist

• There are no similar issues or pull requests for this yet.

Is your feature related to a problem? Please describe.

I want to be able to use model types provided by sqlalchemy_utils such as EmailType, IPAddressType, and UUIDType.

Describe the solution you would like.

I would like the admin page to work without crashing when they are used (just treating them all as strings would work fine for me).

Describe alternatives you considered

I could convert them all to strings in my model, but that would take away a lot of functionality.

Additional context

No response

Readonly column support

Checklist

• There are no similar issues or pull requests for this yet.

Is your feature related to a problem? Please describe.

It is useful if ModelAdmin has a setting like form_readonly_columns that specifies columns the users can not edit.

Describe the solution you would like.

No response

Describe alternatives you considered

No response

Additional context

The author of wtforms recommends just remove the "read-only" field from the input before storing them to the DB.

sort doesn't work after adding custom labels.

Checklist

• The bug is reproducible against the latest release or master.
• There are no similar issues or pull requests to fix it yet.

Describe the bug

Thank you for this awesome repo. It saved me a lot of time.
I have faced a weird bug.
Here goes my model.
class UserAdmin(ModelAdmin, model=UserModel.User):
column_list = [UserModel.User.id, UserModel.User.last_name, UserModel.User.first_name, UserModel.User.email]
column_sortable_list = [UserModel.User.last_name,UserModel.User.first_name,UserModel.User.email]
column_labels = dict(id='ID',last_name='Last Name', first_name='First Name',email='Email Address')

It worked perfectly until I added custom_labels. Now when ever I click on the table header, it returns with an error saying "Model User has no attribute 'Email address' "

This should sort using original field name, not with the label.

Steps to reproduce the bug

No response

Expected behavior

No response

Actual behavior

No response

Debugging material

No response

Environment

• OS: ubuntu 20.04
• python 3.7.10
• fastapi 0.63
• sqladmin 0.1.7
• SQLAlchemy 1.4.32

Additional context

No response

adding Authentication

UUIDType in sqlalchemy_utils support

Checklist

• There are no similar issues or pull requests for this yet.

Is your feature related to a problem? Please describe.

There is a UUID converter for sqlalchemy.dialects.postgresql.base.UUID but sqlalchemy_utils.types.uuid.UUIDType is missing.

Describe the solution you would like.

Add sqlalchemy_utils.types.uuid.UUIDType to the @converts annotation for conv_PgUuid.

Describe alternatives you considered

No response

Additional context

No response

Demo page crashing

Checklist

• The bug is reproducible against the latest release or master.
• There are no similar issues or pull requests to fix it yet.

Describe the bug

https://python-sqladmin.herokuapp.com/admin/ shows generic Server error heroku page.

Steps to reproduce the bug

No response

Expected behavior

No response

Actual behavior

No response

Debugging material

No response

Environment

• web

Additional context

No response