Title: Migrations with UUID and server_default fail. · Issue #491 · fastapi/sqlmodel · GitHub
Open Graph Title: Migrations with UUID and server_default fail. · Issue #491 · fastapi/sqlmodel
X Title: Migrations with UUID and server_default fail. · Issue #491 · fastapi/sqlmodel
Description: First Check I added a very descriptive title to this issue. I used the GitHub search to find a similar issue and didn't find it. I searched the SQLModel documentation, with the integrated search. I already searched in Google "How to X in...
Open Graph Description: First Check I added a very descriptive title to this issue. I used the GitHub search to find a similar issue and didn't find it. I searched the SQLModel documentation, with the integrated search. I...
X Description: First Check I added a very descriptive title to this issue. I used the GitHub search to find a similar issue and didn't find it. I searched the SQLModel documentation, with the integrated searc...
Opengraph URL: https://github.com/fastapi/sqlmodel/issues/491
X: @github
Domain: github.com
{"@context":"https://schema.org","@type":"DiscussionForumPosting","headline":"Migrations with UUID and server_default fail.","articleBody":"### First Check\n\n- [X] I added a very descriptive title to this issue.\n- [X] I used the GitHub search to find a similar issue and didn't find it.\n- [X] I searched the SQLModel documentation, with the integrated search.\n- [X] I already searched in Google \"How to X in SQLModel\" and didn't find any information.\n- [X] I already read and followed all the tutorial in the docs and didn't find an answer.\n- [X] I already checked if it is not related to SQLModel but to [Pydantic](https://github.com/samuelcolvin/pydantic).\n- [X] I already checked if it is not related to SQLModel but to [SQLAlchemy](https://github.com/sqlalchemy/sqlalchemy).\n\n### Commit to Help\n\n- [X] I commit to help with one of those options 👆\n\n### Example Code\n\n```python\nimport uuid\r\nfrom typing import Optional\r\nfrom sqlmodel import SQLModel, Field\r\nfrom sqlalchemy.sql.expression import func\r\nclass Event(SQLModel, table=True):\r\n\r\n event_uuid: Optional[uuid.UUID] = Field(\r\n primary_key=True, nullable=False, sa_column_kwargs=dict(server_default=func.gen_random_uuid())\r\n )\r\n\r\n# a PostgreSQL database with this model cannot have a migration created.\n```\n\n\n### Description\n\nModels which use `sqlmodel.sql.sqltypes.GUID` and a `server_default` cannot be upgraded by Alembic, using PostgreSQL\r\n\r\n### Problem\r\n\r\n1. create a column with UUID and server default, e.g. \r\n ```\r\n event_uuid: Optional[uuid.UUID] = Field(\r\n primary_key=True, nullable=False, sa_column_kwargs=dict(server_default=func.gen_random_uuid())\r\n )\r\n ```\r\n2. `alembic` creates a table like this:\r\n ```\r\n sa.Column(\r\n 'event_uuid', sqlmodel.sql.sqltypes.GUID(), server_default=sa.text('gen_random_uuid()'), nullable=False\r\n ),\r\n ```\r\n3. update the database. Use the database. All is fine.\r\n4. Create another migration with alembic. The analysis and comparison of the current model with the current database will result in the following error:\r\n ```\r\n sqlalchemy.exc.DataError: (psycopg2.errors.InvalidTextRepresentation) invalid input syntax for type uuid: \"gen_random_uuid()\"\r\n LINE 1: SELECT gen_random_uuid() = 'gen_random_uuid()'\r\n ```\r\n\r\n### Workaround \r\n\r\n Replacing the `Field` specification in the model with\r\n ```\r\n Field(\r\n sa_column=sa.Column(sqlalchemy.dialects.postgresql.UUID(), primary_key=True, server_default=func.gen_random_uuid()),\r\n )\r\n ```\r\n gets rid of the error. But this bypasses SqlModel's type inference with a more lengthy and explicit `sa.Column`.\r\n \r\n It would appear that sqlmodel.sql.sqltypes as a type in a `sa.Column` somehow does not behave the same as the type class from sqlalchemy.\r\n \r\n \r\n \n\n### Operating System\n\nLinux\n\n### Operating System Details\n\n_No response_\n\n### SQLModel Version\n\n0.0.6\n\n### Python Version\n\n3.10\n\n### Additional Context\n\nalembic 1.8.1\r\nsqlalchemy 1.4.35","author":{"url":"https://github.com/kristjanvalur","@type":"Person","name":"kristjanvalur"},"datePublished":"2022-11-08T14:14:06Z","url":"https://github.com/fastapi/sqlmodel/issues/491"}
| react-profiling | 0 |
| react-import-map | react,react-dom,react-dom/client,react-dom/profiling,react-is,react/compiler-runtime,react/jsx-dev-runtime,react/jsx-runtime,scheduler@b8488ee2c322 |
| route-pattern | /_view_fragments/issues/show/:user_id/:repository/:id/issue_layout(.:format) |
| route-controller | voltron_issues_fragments |
| route-action | issue_layout |
| fetch-nonce | v2:7de49cea-caea-1d10-ee4f-a42f29978319 |
| current-catalog-service-hash | 81bb79d38c15960b92d99bca9288a9108c7a47b18f2423d0f6438c5b7bcd2114 |
| request-id | E6F4:74373:4368BF:5B0F6B:6AFE20B5 |
| html-safe-nonce | 6248e83b64cf6e470860ce242d1ba50ccaa567354f247bc9f95f0642cca7d1bd |
| visitor-payload | eyJyZWZlcnJlciI6IiIsInJlcXVlc3RfaWQiOiJFNkY0Ojc0MzczOjQzNjhCRjo1QjBGNkI6NkFGRTIwQjUiLCJ2aXNpdG9yX2lkIjoiMTY2ODk0NjY0MDAxMTkyNzczMyIsInJlZ2lvbl9lZGdlIjoiaWFkIiwicmVnaW9uX3JlbmRlciI6ImlhZCJ9 |
| visitor-hmac | b45cec242dda3179842015c0d9c106e9751165c6bf7eb7b366d7e352b1eae29c |
| hovercard-subject-tag | issue:1440286340 |
| github-keyboard-shortcuts | repository,issues,copilot |
| google-site-verification | Apib7-x98H0j5cPqHWwSMm6dNU4GmODRoqxLiDzdx9I |
| octolytics-url | https://collector.github.com/github/collect |
| analytics-location | / |
| fb:app_id | 1401488693436528 |
| apple-itunes-app | app-id=1477376905, app-argument=https://github.com/_view_fragments/issues/show/fastapi/sqlmodel/491/issue_layout |
| twitter:image | https://opengraph.githubassets.com/f05f2b72310ffb3943c8ad2859b38d96b2521e58afcf934f1b2def1449a092f8/fastapi/sqlmodel/issues/491 |
| twitter:card | summary_large_image |
| og:image | https://opengraph.githubassets.com/f05f2b72310ffb3943c8ad2859b38d96b2521e58afcf934f1b2def1449a092f8/fastapi/sqlmodel/issues/491 |
| og:image:alt | First Check I added a very descriptive title to this issue. I used the GitHub search to find a similar issue and didn't find it. I searched the SQLModel documentation, with the integrated search. I... |
| og:image:width | 1200 |
| og:image:height | 600 |
| og:site_name | GitHub |
| og:type | object |
| og:author:username | kristjanvalur |
| hostname | github.com |
| expected-hostname | github.com |
| None | e973f9ec3625f3faad8cac5c2d11a214b7ffe45f3cc563b828459b290ecaeed3 |
| turbo-cache-control | no-preview |
| go-import | github.com/fastapi/sqlmodel git https://github.com/fastapi/sqlmodel.git |
| octolytics-dimension-user_id | 156354296 |
| octolytics-dimension-user_login | fastapi |
| octolytics-dimension-repository_id | 399495186 |
| octolytics-dimension-repository_nwo | fastapi/sqlmodel |
| octolytics-dimension-repository_public | true |
| octolytics-dimension-repository_is_fork | false |
| octolytics-dimension-repository_network_root_id | 399495186 |
| octolytics-dimension-repository_network_root_nwo | fastapi/sqlmodel |
| turbo-body-classes | logged-out env-production page-responsive |
| disable-turbo | false |
| browser-stats-url | https://api.github.com/_private/browser/stats |
| browser-errors-url | https://api.github.com/_private/browser/errors |
| release | 1f92c367595b9190e0be293896078033886b1913 |
| ui-target | full |
| theme-color | #1e2327 |
| color-scheme | light dark |
| react-app-name | issues |
Links:
Viewport: width=device-width