oxide_sql_core/migrations/dialect/
postgres.rs

1//! PostgreSQL dialect for migrations.
2
3use super::MigrationDialect;
4use crate::ast::DataType;
5use crate::migrations::column_builder::{ColumnDefinition, DefaultValue};
6use crate::migrations::operation::{
7    AlterColumnChange, AlterColumnOp, DropIndexOp, RenameColumnOp, RenameTableOp,
8};
9use crate::schema::RustTypeMapping;
10
11/// PostgreSQL dialect for migration SQL generation.
12#[derive(Debug, Clone, Copy, Default)]
13pub struct PostgresDialect;
14
15impl PostgresDialect {
16    /// Creates a new PostgreSQL dialect.
17    #[must_use]
18    pub const fn new() -> Self {
19        Self
20    }
21}
22
23impl MigrationDialect for PostgresDialect {
24    fn name(&self) -> &'static str {
25        "postgresql"
26    }
27
28    fn map_data_type(&self, dt: &DataType) -> String {
29        match dt {
30            DataType::Smallint => "SMALLINT".to_string(),
31            DataType::Integer => "INTEGER".to_string(),
32            DataType::Bigint => "BIGINT".to_string(),
33            DataType::Real => "REAL".to_string(),
34            DataType::Double => "DOUBLE PRECISION".to_string(),
35            DataType::Decimal { precision, scale } => match (precision, scale) {
36                (Some(p), Some(s)) => format!("DECIMAL({p}, {s})"),
37                (Some(p), None) => format!("DECIMAL({p})"),
38                _ => "DECIMAL".to_string(),
39            },
40            DataType::Numeric { precision, scale } => match (precision, scale) {
41                (Some(p), Some(s)) => format!("NUMERIC({p}, {s})"),
42                (Some(p), None) => format!("NUMERIC({p})"),
43                _ => "NUMERIC".to_string(),
44            },
45            DataType::Char(len) => match len {
46                Some(n) => format!("CHAR({n})"),
47                None => "CHAR".to_string(),
48            },
49            DataType::Varchar(len) => match len {
50                Some(n) => format!("VARCHAR({n})"),
51                None => "VARCHAR".to_string(),
52            },
53            DataType::Text => "TEXT".to_string(),
54            DataType::Blob => "BYTEA".to_string(), // PostgreSQL uses BYTEA
55            DataType::Binary(len) => match len {
56                Some(n) => format!("BIT({n})"),
57                None => "BYTEA".to_string(),
58            },
59            DataType::Varbinary(len) => match len {
60                Some(n) => format!("VARBIT({n})"),
61                None => "BYTEA".to_string(),
62            },
63            DataType::Date => "DATE".to_string(),
64            DataType::Time => "TIME".to_string(),
65            DataType::Timestamp => "TIMESTAMP".to_string(),
66            DataType::Datetime => "TIMESTAMP".to_string(), // PostgreSQL uses TIMESTAMP
67            DataType::Boolean => "BOOLEAN".to_string(),
68            DataType::Custom(name) => name.clone(),
69        }
70    }
71
72    fn autoincrement_keyword(&self) -> String {
73        // PostgreSQL uses SERIAL types instead of AUTOINCREMENT keyword
74        // However, when PRIMARY KEY is specified with BIGINT, we don't change the type
75        // The application should use SERIAL/BIGSERIAL types directly
76        String::new()
77    }
78
79    fn column_definition(&self, col: &ColumnDefinition) -> String {
80        // PostgreSQL uses SERIAL/BIGSERIAL for auto-increment
81        let data_type = if col.autoincrement && col.primary_key {
82            match col.data_type {
83                DataType::Integer | DataType::Smallint => "SERIAL".to_string(),
84                DataType::Bigint => "BIGSERIAL".to_string(),
85                _ => self.map_data_type(&col.data_type),
86            }
87        } else {
88            self.map_data_type(&col.data_type)
89        };
90
91        let mut sql = format!("{} {}", self.quote_identifier(&col.name), data_type);
92
93        if col.primary_key {
94            sql.push_str(" PRIMARY KEY");
95        } else {
96            if !col.nullable {
97                sql.push_str(" NOT NULL");
98            }
99            if col.unique {
100                sql.push_str(" UNIQUE");
101            }
102        }
103
104        if let Some(ref default) = col.default {
105            sql.push_str(" DEFAULT ");
106            sql.push_str(&self.render_default(default));
107        }
108
109        if let Some(ref fk) = col.references {
110            sql.push_str(" REFERENCES ");
111            sql.push_str(&self.quote_identifier(&fk.table));
112            sql.push_str(" (");
113            sql.push_str(&self.quote_identifier(&fk.column));
114            sql.push(')');
115            if let Some(action) = fk.on_delete {
116                sql.push_str(" ON DELETE ");
117                sql.push_str(action.as_sql());
118            }
119            if let Some(action) = fk.on_update {
120                sql.push_str(" ON UPDATE ");
121                sql.push_str(action.as_sql());
122            }
123        }
124
125        if let Some(ref check) = col.check {
126            sql.push_str(&format!(" CHECK ({})", check));
127        }
128
129        if let Some(ref collation) = col.collation {
130            sql.push_str(&format!(" COLLATE \"{}\"", collation));
131        }
132
133        sql
134    }
135
136    fn render_default(&self, default: &DefaultValue) -> String {
137        match default {
138            DefaultValue::Boolean(b) => {
139                if *b {
140                    "TRUE".to_string()
141                } else {
142                    "FALSE".to_string()
143                }
144            }
145            _ => default.to_sql(),
146        }
147    }
148
149    fn rename_table(&self, op: &RenameTableOp) -> String {
150        format!(
151            "ALTER TABLE {} RENAME TO {}",
152            self.quote_identifier(&op.old_name),
153            self.quote_identifier(&op.new_name)
154        )
155    }
156
157    fn rename_column(&self, op: &RenameColumnOp) -> String {
158        format!(
159            "ALTER TABLE {} RENAME COLUMN {} TO {}",
160            self.quote_identifier(&op.table),
161            self.quote_identifier(&op.old_name),
162            self.quote_identifier(&op.new_name)
163        )
164    }
165
166    fn alter_column(&self, op: &AlterColumnOp) -> String {
167        let table = self.quote_identifier(&op.table);
168        let column = self.quote_identifier(&op.column);
169
170        match &op.change {
171            AlterColumnChange::SetDataType(dt) => {
172                format!(
173                    "ALTER TABLE {} ALTER COLUMN {} TYPE {}",
174                    table,
175                    column,
176                    self.map_data_type(dt)
177                )
178            }
179            AlterColumnChange::SetNullable(nullable) => {
180                if *nullable {
181                    format!(
182                        "ALTER TABLE {} ALTER COLUMN {} DROP NOT NULL",
183                        table, column
184                    )
185                } else {
186                    format!("ALTER TABLE {} ALTER COLUMN {} SET NOT NULL", table, column)
187                }
188            }
189            AlterColumnChange::SetDefault(default) => {
190                format!(
191                    "ALTER TABLE {} ALTER COLUMN {} SET DEFAULT {}",
192                    table,
193                    column,
194                    self.render_default(default)
195                )
196            }
197            AlterColumnChange::DropDefault => {
198                format!("ALTER TABLE {} ALTER COLUMN {} DROP DEFAULT", table, column)
199            }
200            AlterColumnChange::SetUnique(true) => {
201                format!("ALTER TABLE {} ADD UNIQUE ({})", table, column)
202            }
203            AlterColumnChange::SetUnique(false) => {
204                format!(
205                    "ALTER TABLE {} DROP CONSTRAINT \"{}_key\"",
206                    table, op.column
207                )
208            }
209            AlterColumnChange::SetAutoincrement(_) => {
210                format!(
211                    "-- PostgreSQL cannot ALTER autoincrement \
212                     for {}.{}; table recreation required",
213                    op.table, op.column
214                )
215            }
216        }
217    }
218
219    fn drop_index(&self, op: &DropIndexOp) -> String {
220        let mut sql = String::from("DROP INDEX ");
221        if op.if_exists {
222            sql.push_str("IF EXISTS ");
223        }
224        sql.push_str(&self.quote_identifier(&op.name));
225        sql
226    }
227
228    fn drop_foreign_key(&self, op: &super::super::operation::DropForeignKeyOp) -> String {
229        format!(
230            "ALTER TABLE {} DROP CONSTRAINT {}",
231            self.quote_identifier(&op.table),
232            self.quote_identifier(&op.name)
233        )
234    }
235}
236
237impl RustTypeMapping for PostgresDialect {
238    fn map_type(&self, rust_type: &str) -> DataType {
239        match rust_type {
240            "bool" => DataType::Boolean,
241            "i8" | "i16" | "u8" | "u16" => DataType::Smallint,
242            "i32" | "u32" => DataType::Integer,
243            "i64" | "u64" | "i128" | "u128" | "isize" | "usize" => DataType::Bigint,
244            "f32" => DataType::Real,
245            "f64" => DataType::Double,
246            "String" => DataType::Varchar(Some(255)),
247            "Vec<u8>" => DataType::Blob,
248            s if s.contains("DateTime") => DataType::Timestamp,
249            s if s.contains("NaiveDate") => DataType::Date,
250            _ => DataType::Text,
251        }
252    }
253}
254
255#[cfg(test)]
256mod tests {
257    use super::*;
258    use crate::migrations::column_builder::{bigint, varchar};
259    use crate::migrations::table_builder::CreateTableBuilder;
260
261    #[test]
262    fn test_postgres_data_types() {
263        let dialect = PostgresDialect::new();
264        assert_eq!(dialect.map_data_type(&DataType::Integer), "INTEGER");
265        assert_eq!(dialect.map_data_type(&DataType::Bigint), "BIGINT");
266        assert_eq!(dialect.map_data_type(&DataType::Text), "TEXT");
267        assert_eq!(
268            dialect.map_data_type(&DataType::Varchar(Some(255))),
269            "VARCHAR(255)"
270        );
271        assert_eq!(dialect.map_data_type(&DataType::Blob), "BYTEA");
272        assert_eq!(dialect.map_data_type(&DataType::Boolean), "BOOLEAN");
273        assert_eq!(dialect.map_data_type(&DataType::Timestamp), "TIMESTAMP");
274        assert_eq!(
275            dialect.map_data_type(&DataType::Decimal {
276                precision: Some(10),
277                scale: Some(2)
278            }),
279            "DECIMAL(10, 2)"
280        );
281    }
282
283    #[test]
284    fn test_create_table_with_serial() {
285        let dialect = PostgresDialect::new();
286        let op = CreateTableBuilder::new()
287            .name("users")
288            .column(bigint("id").primary_key().autoincrement().build())
289            .column(varchar("username", 255).not_null().unique().build())
290            .build();
291
292        let sql = dialect.create_table(&op);
293        assert!(sql.contains("CREATE TABLE \"users\""));
294        assert!(sql.contains("\"id\" BIGSERIAL PRIMARY KEY"));
295        assert!(sql.contains("\"username\" VARCHAR(255) NOT NULL UNIQUE"));
296    }
297
298    #[test]
299    fn test_alter_column_sql() {
300        let dialect = PostgresDialect::new();
301
302        // Set NOT NULL
303        let op = AlterColumnOp {
304            table: "users".to_string(),
305            column: "email".to_string(),
306            change: AlterColumnChange::SetNullable(false),
307        };
308        assert_eq!(
309            dialect.alter_column(&op),
310            "ALTER TABLE \"users\" ALTER COLUMN \"email\" SET NOT NULL"
311        );
312
313        // Drop NOT NULL
314        let op = AlterColumnOp {
315            table: "users".to_string(),
316            column: "email".to_string(),
317            change: AlterColumnChange::SetNullable(true),
318        };
319        assert_eq!(
320            dialect.alter_column(&op),
321            "ALTER TABLE \"users\" ALTER COLUMN \"email\" DROP NOT NULL"
322        );
323
324        // Change type
325        let op = AlterColumnOp {
326            table: "users".to_string(),
327            column: "age".to_string(),
328            change: AlterColumnChange::SetDataType(DataType::Bigint),
329        };
330        assert_eq!(
331            dialect.alter_column(&op),
332            "ALTER TABLE \"users\" ALTER COLUMN \"age\" TYPE BIGINT"
333        );
334    }
335
336    #[test]
337    fn test_drop_foreign_key() {
338        let dialect = PostgresDialect::new();
339        let op = super::super::super::operation::DropForeignKeyOp {
340            table: "invoices".to_string(),
341            name: "fk_invoices_user".to_string(),
342        };
343        assert_eq!(
344            dialect.drop_foreign_key(&op),
345            "ALTER TABLE \"invoices\" DROP CONSTRAINT \"fk_invoices_user\""
346        );
347    }
348}
oxide_sql_core/migrations/dialect/postgres.rs

oxide_sql_core/migrations/dialect/
postgres.rs
