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

oxide_sql_core/migrations/dialect/
duckdb.rs
