oxide_sql_core/migrations/dialect/
duckdb.rs

1//! DuckDB 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, CreateTableOp, DropIndexOp, RenameColumnOp, RenameTableOp,
8};
9
10/// DuckDB dialect for migration SQL generation.
11///
12/// DuckDB does not support `AUTOINCREMENT` or `SERIAL`/`BIGSERIAL`.
13/// Instead, auto-increment is implemented via `CREATE SEQUENCE` +
14/// `DEFAULT nextval('seq_<table>_<column>')`.  The [`create_table`]
15/// override emits the sequence DDL automatically for every column
16/// marked with `autoincrement`.
17#[derive(Debug, Clone, Copy, Default)]
18pub struct DuckDbDialect;
19
20impl DuckDbDialect {
21    /// Creates a new DuckDB dialect.
22    #[must_use]
23    pub const fn new() -> Self {
24        Self
25    }
26
27    /// Generates a column definition with sequence-backed default for
28    /// autoincrement columns, using the given table name to build the
29    /// sequence name.
30    fn column_def_with_table(&self, col: &ColumnDefinition, table: &str) -> String {
31        let data_type = self.map_data_type(&col.data_type);
32        let mut sql = format!("{} {}", self.quote_identifier(&col.name), data_type);
33
34        if col.primary_key {
35            sql.push_str(" PRIMARY KEY");
36        } else {
37            if !col.nullable {
38                sql.push_str(" NOT NULL");
39            }
40            if col.unique {
41                sql.push_str(" UNIQUE");
42            }
43        }
44
45        if col.autoincrement && col.default.is_none() {
46            sql.push_str(&format!(" DEFAULT nextval('seq_{}_{}')", table, col.name,));
47        } else if let Some(ref default) = col.default {
48            sql.push_str(" DEFAULT ");
49            sql.push_str(&self.render_default(default));
50        }
51
52        if let Some(ref fk) = col.references {
53            sql.push_str(" REFERENCES ");
54            sql.push_str(&self.quote_identifier(&fk.table));
55            sql.push_str(" (");
56            sql.push_str(&self.quote_identifier(&fk.column));
57            sql.push(')');
58            if let Some(action) = fk.on_delete {
59                sql.push_str(" ON DELETE ");
60                sql.push_str(action.as_sql());
61            }
62            if let Some(action) = fk.on_update {
63                sql.push_str(" ON UPDATE ");
64                sql.push_str(action.as_sql());
65            }
66        }
67
68        if let Some(ref check) = col.check {
69            sql.push_str(&format!(" CHECK ({})", check));
70        }
71
72        if let Some(ref collation) = col.collation {
73            sql.push_str(&format!(" COLLATE \"{}\"", collation));
74        }
75
76        sql
77    }
78}
79
80impl MigrationDialect for DuckDbDialect {
81    fn name(&self) -> &'static str {
82        "duckdb"
83    }
84
85    fn map_data_type(&self, dt: &DataType) -> String {
86        match dt {
87            DataType::Smallint => "SMALLINT".to_string(),
88            DataType::Integer => "INTEGER".to_string(),
89            DataType::Bigint => "BIGINT".to_string(),
90            DataType::Real => "REAL".to_string(),
91            DataType::Double => "DOUBLE".to_string(),
92            DataType::Decimal { precision, scale } => match (precision, scale) {
93                (Some(p), Some(s)) => format!("DECIMAL({p}, {s})"),
94                (Some(p), None) => format!("DECIMAL({p})"),
95                _ => "DECIMAL".to_string(),
96            },
97            DataType::Numeric { precision, scale } => match (precision, scale) {
98                (Some(p), Some(s)) => format!("NUMERIC({p}, {s})"),
99                (Some(p), None) => format!("NUMERIC({p})"),
100                _ => "NUMERIC".to_string(),
101            },
102            DataType::Char(len) => match len {
103                Some(n) => format!("CHAR({n})"),
104                None => "CHAR".to_string(),
105            },
106            DataType::Varchar(len) => match len {
107                Some(n) => format!("VARCHAR({n})"),
108                None => "VARCHAR".to_string(),
109            },
110            DataType::Text => "TEXT".to_string(),
111            DataType::Blob => "BLOB".to_string(),
112            DataType::Binary(len) => match len {
113                Some(n) => format!("BLOB({n})"),
114                None => "BLOB".to_string(),
115            },
116            DataType::Varbinary(len) => match len {
117                Some(n) => format!("BLOB({n})"),
118                None => "BLOB".to_string(),
119            },
120            DataType::Date => "DATE".to_string(),
121            DataType::Time => "TIME".to_string(),
122            DataType::Timestamp => "TIMESTAMP".to_string(),
123            DataType::Datetime => "TIMESTAMP".to_string(),
124            DataType::Boolean => "BOOLEAN".to_string(),
125            DataType::Custom(name) => name.clone(),
126        }
127    }
128
129    fn autoincrement_keyword(&self) -> String {
130        // DuckDB uses CREATE SEQUENCE + DEFAULT nextval() instead.
131        String::new()
132    }
133
134    fn create_table(&self, op: &CreateTableOp) -> String {
135        // Emit CREATE SEQUENCE for every autoincrement column.
136        let mut sql = String::new();
137        for col in &op.columns {
138            if col.autoincrement {
139                sql.push_str(&format!(
140                    "CREATE SEQUENCE IF NOT EXISTS \
141                     \"seq_{table}_{col}\" START 1;\n",
142                    table = op.name,
143                    col = col.name,
144                ));
145            }
146        }
147
148        sql.push_str("CREATE TABLE ");
149        if op.if_not_exists {
150            sql.push_str("IF NOT EXISTS ");
151        }
152        sql.push_str(&self.quote_identifier(&op.name));
153        sql.push_str(" (\n");
154
155        let column_defs: Vec<String> = op
156            .columns
157            .iter()
158            .map(|c| format!("    {}", self.column_def_with_table(c, &op.name)))
159            .collect();
160        sql.push_str(&column_defs.join(",\n"));
161
162        if !op.constraints.is_empty() {
163            sql.push_str(",\n");
164            let constraint_defs: Vec<String> = op
165                .constraints
166                .iter()
167                .map(|c| format!("    {}", self.table_constraint(c)))
168                .collect();
169            sql.push_str(&constraint_defs.join(",\n"));
170        }
171
172        sql.push_str("\n)");
173        sql
174    }
175
176    fn render_default(&self, default: &DefaultValue) -> String {
177        match default {
178            DefaultValue::Boolean(b) => {
179                if *b {
180                    "TRUE".to_string()
181                } else {
182                    "FALSE".to_string()
183                }
184            }
185            _ => default.to_sql(),
186        }
187    }
188
189    fn rename_table(&self, op: &RenameTableOp) -> String {
190        format!(
191            "ALTER TABLE {} RENAME TO {}",
192            self.quote_identifier(&op.old_name),
193            self.quote_identifier(&op.new_name)
194        )
195    }
196
197    fn rename_column(&self, op: &RenameColumnOp) -> String {
198        format!(
199            "ALTER TABLE {} RENAME COLUMN {} TO {}",
200            self.quote_identifier(&op.table),
201            self.quote_identifier(&op.old_name),
202            self.quote_identifier(&op.new_name)
203        )
204    }
205
206    fn alter_column(&self, op: &AlterColumnOp) -> String {
207        let table = self.quote_identifier(&op.table);
208        let column = self.quote_identifier(&op.column);
209
210        match &op.change {
211            AlterColumnChange::SetDataType(dt) => {
212                format!(
213                    "ALTER TABLE {} ALTER COLUMN {} SET DATA TYPE {}",
214                    table,
215                    column,
216                    self.map_data_type(dt)
217                )
218            }
219            AlterColumnChange::SetNullable(nullable) => {
220                if *nullable {
221                    format!(
222                        "ALTER TABLE {} ALTER COLUMN {} DROP NOT NULL",
223                        table, column
224                    )
225                } else {
226                    format!("ALTER TABLE {} ALTER COLUMN {} SET NOT NULL", table, column)
227                }
228            }
229            AlterColumnChange::SetDefault(default) => {
230                format!(
231                    "ALTER TABLE {} ALTER COLUMN {} SET DEFAULT {}",
232                    table,
233                    column,
234                    self.render_default(default)
235                )
236            }
237            AlterColumnChange::DropDefault => {
238                format!("ALTER TABLE {} ALTER COLUMN {} DROP DEFAULT", table, column)
239            }
240        }
241    }
242
243    fn drop_index(&self, op: &DropIndexOp) -> String {
244        let mut sql = String::from("DROP INDEX ");
245        if op.if_exists {
246            sql.push_str("IF EXISTS ");
247        }
248        sql.push_str(&self.quote_identifier(&op.name));
249        sql
250    }
251
252    fn drop_foreign_key(&self, op: &super::super::operation::DropForeignKeyOp) -> String {
253        format!(
254            "ALTER TABLE {} DROP CONSTRAINT {}",
255            self.quote_identifier(&op.table),
256            self.quote_identifier(&op.name)
257        )
258    }
259}
260
261#[cfg(test)]
262mod tests {
263    use super::*;
264    use crate::migrations::column_builder::{integer, varchar};
265    use crate::migrations::operation::{DropTableOp, Operation, RenameColumnOp, RenameTableOp};
266    use crate::migrations::table_builder::CreateTableBuilder;
267
268    #[test]
269    fn test_duckdb_data_types() {
270        let d = DuckDbDialect::new();
271        assert_eq!(d.map_data_type(&DataType::Integer), "INTEGER");
272        assert_eq!(d.map_data_type(&DataType::Bigint), "BIGINT");
273        assert_eq!(d.map_data_type(&DataType::Text), "TEXT");
274        assert_eq!(
275            d.map_data_type(&DataType::Varchar(Some(255))),
276            "VARCHAR(255)"
277        );
278        assert_eq!(d.map_data_type(&DataType::Blob), "BLOB");
279        assert_eq!(d.map_data_type(&DataType::Boolean), "BOOLEAN");
280        assert_eq!(d.map_data_type(&DataType::Timestamp), "TIMESTAMP");
281        assert_eq!(d.map_data_type(&DataType::Double), "DOUBLE");
282        assert_eq!(d.map_data_type(&DataType::Real), "REAL");
283        assert_eq!(d.map_data_type(&DataType::Date), "DATE");
284        assert_eq!(d.map_data_type(&DataType::Time), "TIME");
285        assert_eq!(
286            d.map_data_type(&DataType::Decimal {
287                precision: Some(10),
288                scale: Some(2)
289            }),
290            "DECIMAL(10, 2)"
291        );
292    }
293
294    #[test]
295    fn test_create_table_basic() {
296        let d = DuckDbDialect::new();
297        let op = CreateTableBuilder::new()
298            .name("users")
299            .column(varchar("username", 255).not_null().unique().build())
300            .build();
301
302        let sql = d.create_table(&op);
303        assert_eq!(
304            sql,
305            "CREATE TABLE \"users\" (\n\
306             \x20   \"username\" VARCHAR(255) NOT NULL UNIQUE\n\
307             )"
308        );
309    }
310
311    #[test]
312    fn test_create_table_if_not_exists() {
313        let d = DuckDbDialect::new();
314        let op = CreateTableBuilder::new()
315            .if_not_exists()
316            .name("users")
317            .column(varchar("username", 255).not_null().build())
318            .build();
319
320        let sql = d.create_table(&op);
321        assert!(sql.contains("CREATE TABLE IF NOT EXISTS \"users\""));
322    }
323
324    #[test]
325    fn test_autoincrement_generates_sequence() {
326        let d = DuckDbDialect::new();
327        let op = CreateTableBuilder::new()
328            .name("users")
329            .column(integer("id").primary_key().autoincrement().build())
330            .column(varchar("username", 255).not_null().unique().build())
331            .build();
332
333        let sql = d.create_table(&op);
334
335        assert!(
336            sql.contains(
337                "CREATE SEQUENCE IF NOT EXISTS \
338                 \"seq_users_id\" START 1;"
339            ),
340            "Missing sequence DDL in:\n{sql}"
341        );
342        assert!(
343            sql.contains("DEFAULT nextval('seq_users_id')"),
344            "Missing nextval default in:\n{sql}"
345        );
346        assert!(
347            !sql.contains("AUTOINCREMENT"),
348            "Should not contain AUTOINCREMENT keyword"
349        );
350    }
351
352    #[test]
353    fn test_varchar_unique_not_null() {
354        let d = DuckDbDialect::new();
355        let op = CreateTableBuilder::new()
356            .name("items")
357            .column(varchar("domain", 255).not_null().unique().build())
358            .build();
359
360        let sql = d.create_table(&op);
361        assert!(
362            sql.contains("\"domain\" VARCHAR(255) NOT NULL UNIQUE"),
363            "Expected NOT NULL UNIQUE in:\n{sql}"
364        );
365    }
366
367    #[test]
368    fn test_drop_table() {
369        let d = DuckDbDialect::new();
370
371        let op = DropTableOp {
372            name: "users".to_string(),
373            if_exists: false,
374            cascade: false,
375        };
376        assert_eq!(d.drop_table(&op), "DROP TABLE \"users\"");
377
378        let op = DropTableOp {
379            name: "users".to_string(),
380            if_exists: true,
381            cascade: true,
382        };
383        assert_eq!(d.drop_table(&op), "DROP TABLE IF EXISTS \"users\" CASCADE");
384    }
385
386    #[test]
387    fn test_rename_table() {
388        let d = DuckDbDialect::new();
389        let op = RenameTableOp {
390            old_name: "old_users".to_string(),
391            new_name: "users".to_string(),
392        };
393        assert_eq!(
394            d.rename_table(&op),
395            "ALTER TABLE \"old_users\" RENAME TO \"users\""
396        );
397    }
398
399    #[test]
400    fn test_add_column() {
401        let d = DuckDbDialect::new();
402        let op = Operation::add_column("users", varchar("email", 255).not_null().build());
403        if let Operation::AddColumn(ref add_op) = op {
404            let sql = d.add_column(add_op);
405            assert_eq!(
406                sql,
407                "ALTER TABLE \"users\" ADD COLUMN \
408                 \"email\" VARCHAR(255) NOT NULL"
409            );
410        }
411    }
412
413    #[test]
414    fn test_drop_column() {
415        let d = DuckDbDialect::new();
416        let op = Operation::drop_column("users", "email");
417        if let Operation::DropColumn(ref drop_op) = op {
418            let sql = d.drop_column(drop_op);
419            assert_eq!(sql, "ALTER TABLE \"users\" DROP COLUMN \"email\"");
420        }
421    }
422
423    #[test]
424    fn test_rename_column() {
425        let d = DuckDbDialect::new();
426        let op = RenameColumnOp {
427            table: "users".to_string(),
428            old_name: "name".to_string(),
429            new_name: "full_name".to_string(),
430        };
431        assert_eq!(
432            d.rename_column(&op),
433            "ALTER TABLE \"users\" RENAME COLUMN \
434             \"name\" TO \"full_name\""
435        );
436    }
437
438    #[test]
439    fn test_alter_column_set_data_type() {
440        let d = DuckDbDialect::new();
441        let op = AlterColumnOp {
442            table: "users".to_string(),
443            column: "age".to_string(),
444            change: AlterColumnChange::SetDataType(DataType::Bigint),
445        };
446        assert_eq!(
447            d.alter_column(&op),
448            "ALTER TABLE \"users\" ALTER COLUMN \"age\" \
449             SET DATA TYPE BIGINT"
450        );
451    }
452
453    #[test]
454    fn test_alter_column_set_not_null() {
455        let d = DuckDbDialect::new();
456        let op = AlterColumnOp {
457            table: "users".to_string(),
458            column: "email".to_string(),
459            change: AlterColumnChange::SetNullable(false),
460        };
461        assert_eq!(
462            d.alter_column(&op),
463            "ALTER TABLE \"users\" ALTER COLUMN \"email\" SET NOT NULL"
464        );
465    }
466
467    #[test]
468    fn test_alter_column_drop_not_null() {
469        let d = DuckDbDialect::new();
470        let op = AlterColumnOp {
471            table: "users".to_string(),
472            column: "email".to_string(),
473            change: AlterColumnChange::SetNullable(true),
474        };
475        assert_eq!(
476            d.alter_column(&op),
477            "ALTER TABLE \"users\" ALTER COLUMN \"email\" DROP NOT NULL"
478        );
479    }
480
481    #[test]
482    fn test_alter_column_set_default() {
483        let d = DuckDbDialect::new();
484        let op = AlterColumnOp {
485            table: "users".to_string(),
486            column: "active".to_string(),
487            change: AlterColumnChange::SetDefault(DefaultValue::Boolean(true)),
488        };
489        assert_eq!(
490            d.alter_column(&op),
491            "ALTER TABLE \"users\" ALTER COLUMN \"active\" \
492             SET DEFAULT TRUE"
493        );
494    }
495
496    #[test]
497    fn test_alter_column_drop_default() {
498        let d = DuckDbDialect::new();
499        let op = AlterColumnOp {
500            table: "users".to_string(),
501            column: "active".to_string(),
502            change: AlterColumnChange::DropDefault,
503        };
504        assert_eq!(
505            d.alter_column(&op),
506            "ALTER TABLE \"users\" ALTER COLUMN \"active\" DROP DEFAULT"
507        );
508    }
509
510    #[test]
511    fn test_create_index() {
512        let d = DuckDbDialect::new();
513        let op = crate::migrations::operation::CreateIndexOp {
514            name: "idx_users_email".to_string(),
515            table: "users".to_string(),
516            columns: vec!["email".to_string()],
517            unique: true,
518            index_type: crate::migrations::operation::IndexType::BTree,
519            if_not_exists: true,
520            condition: None,
521        };
522        assert_eq!(
523            d.create_index(&op),
524            "CREATE UNIQUE INDEX IF NOT EXISTS \"idx_users_email\" \
525             ON \"users\" (\"email\")"
526        );
527    }
528
529    #[test]
530    fn test_drop_index() {
531        let d = DuckDbDialect::new();
532
533        let op = crate::migrations::operation::DropIndexOp {
534            name: "idx_users_email".to_string(),
535            table: None,
536            if_exists: false,
537        };
538        assert_eq!(d.drop_index(&op), "DROP INDEX \"idx_users_email\"");
539
540        let op = crate::migrations::operation::DropIndexOp {
541            name: "idx_users_email".to_string(),
542            table: None,
543            if_exists: true,
544        };
545        assert_eq!(
546            d.drop_index(&op),
547            "DROP INDEX IF EXISTS \"idx_users_email\""
548        );
549    }
550
551    #[test]
552    fn test_drop_foreign_key() {
553        let d = DuckDbDialect::new();
554        let op = crate::migrations::operation::DropForeignKeyOp {
555            table: "invoices".to_string(),
556            name: "fk_invoices_user".to_string(),
557        };
558        assert_eq!(
559            d.drop_foreign_key(&op),
560            "ALTER TABLE \"invoices\" DROP CONSTRAINT \
561             \"fk_invoices_user\""
562        );
563    }
564
565    #[test]
566    fn test_consumer_scenario_two_tables_with_sequences() {
567        let d = DuckDbDialect::new();
568
569        let ops: Vec<Operation> = vec![
570            CreateTableBuilder::new()
571                .if_not_exists()
572                .name("excluded_domains")
573                .column(integer("id").primary_key().autoincrement().build())
574                .column(varchar("domain", 255).not_null().unique().build())
575                .build()
576                .into(),
577            CreateTableBuilder::new()
578                .if_not_exists()
579                .name("excluded_ips")
580                .column(integer("id").primary_key().autoincrement().build())
581                .column(varchar("cidr", 255).not_null().unique().build())
582                .build()
583                .into(),
584        ];
585
586        let sqls: Vec<String> = ops.iter().map(|op| d.generate_sql(op)).collect();
587
588        // First table
589        assert!(
590            sqls[0].contains(
591                "CREATE SEQUENCE IF NOT EXISTS \
592                 \"seq_excluded_domains_id\" START 1;"
593            ),
594            "Missing sequence for excluded_domains:\n{}",
595            sqls[0]
596        );
597        assert!(
598            sqls[0].contains("CREATE TABLE IF NOT EXISTS \"excluded_domains\""),
599            "Missing CREATE TABLE:\n{}",
600            sqls[0]
601        );
602        assert!(
603            sqls[0].contains("DEFAULT nextval('seq_excluded_domains_id')"),
604            "Missing nextval default:\n{}",
605            sqls[0]
606        );
607        assert!(
608            sqls[0].contains("\"domain\" VARCHAR(255) NOT NULL UNIQUE"),
609            "Missing domain column:\n{}",
610            sqls[0]
611        );
612
613        // Second table
614        assert!(
615            sqls[1].contains(
616                "CREATE SEQUENCE IF NOT EXISTS \
617                 \"seq_excluded_ips_id\" START 1;"
618            ),
619            "Missing sequence for excluded_ips:\n{}",
620            sqls[1]
621        );
622        assert!(
623            sqls[1].contains("DEFAULT nextval('seq_excluded_ips_id')"),
624            "Missing nextval default:\n{}",
625            sqls[1]
626        );
627        assert!(
628            sqls[1].contains("\"cidr\" VARCHAR(255) NOT NULL UNIQUE"),
629            "Missing cidr column:\n{}",
630            sqls[1]
631        );
632    }
633}
oxide_sql_core/migrations/dialect/duckdb.rs

oxide_sql_core/migrations/dialect/
duckdb.rs
