migrator/migrations/
m20241025030906_users.rs

1use sqlx::{Acquire, PgConnection, Postgres};
2use sqlx_migrator::Operation;
3use sqlx_migrator::error::Error;
4use sqlx_migrator::migration;
5use sqlx_migrator::vec_box;
6
7pub(crate) struct Users;
8migration!(
9    Postgres,
10    Users,
11    "backend",
12    "20241025030906_users",
13    vec_box![],
14    vec_box![MigrationOperation]
15);
16
17struct MigrationOperation;
18#[async_trait::async_trait]
19impl Operation<Postgres> for MigrationOperation {
20    async fn up(&self, conn: &mut PgConnection) -> Result<(), Error> {
21        let mut tx = conn.begin().await?;
22
23        sqlx::query(
24            "
25            CREATE TABLE users (
26                id TEXT PRIMARY KEY,
27                created TIMESTAMPTZ NOT NULL,
28                signed_in TIMESTAMPTZ NOT NULL,
29                username TEXT UNIQUE,
30                display_name TEXT
31            );
32            ",
33        )
34        .execute(&mut *tx)
35        .await?;
36
37        sqlx::query(
38            "
39            CREATE TYPE permission_level AS ENUM ('read', 'write', 'maintain', 'own');
40            ",
41        )
42        .execute(&mut *tx)
43        .await?;
44
45        sqlx::query(
46            "
47            CREATE TABLE permissions (
48                subject TEXT REFERENCES users (id),
49                object UUID NOT NULL REFERENCES refs (id),
50                level permission_level NOT NULL,
51                CONSTRAINT permissions_is_relation UNIQUE (subject, object)
52            );
53            ",
54        )
55        .execute(&mut *tx)
56        .await?;
57
58        sqlx::query(
59            "
60            CREATE INDEX ON permissions (object);
61            ",
62        )
63        .execute(&mut *tx)
64        .await?;
65
66        tx.commit().await?;
67        Ok(())
68    }
69
70    async fn down(&self, conn: &mut PgConnection) -> Result<(), Error> {
71        let mut tx = conn.begin().await?;
72
73        sqlx::query(
74            "
75            DROP TABLE permissions, users;
76            ",
77        )
78        .execute(&mut *tx)
79        .await?;
80
81        sqlx::query(
82            "
83            DROP TYPE permission_level;
84            ",
85        )
86        .execute(&mut *tx)
87        .await?;
88
89        tx.commit().await?;
90        Ok(())
91    }
92}
migrator/migrations/m20241025030906_users.rs

migrator/migrations/
m20241025030906_users.rs
