Repository: piccolo-orm/piccolo
Issue: syntax error when adding index in migration

I am trying to add an index to a Varchar column on a table. Auto creating migrations work perfectly fine, but when I try to run the migration it fails with a syntax error ('syntax error at or near' + name of column). This is the only change in the migration and the migration file itself does not contain errors. Any sense for what could be causing the issue?

Thanks

Changed files:
tests/table/test_indexes.py, piccolo/query/methods/create_index.py, piccolo/table.py

diff --git a/piccolo/query/methods/create_index.py b/piccolo/query/methods/create_index.py
index 4a1822cc..594fd63b 100644
--- a/piccolo/query/methods/create_index.py
+++ b/piccolo/query/methods/create_index.py
@@ -44,7 +44,7 @@ class CreateIndex(DDL):
         index_name = self.table._get_index_name(column_names)
         tablename = self.table._meta.tablename
         method_name = self.method.value
-        column_names_str = ", ".join(column_names)
+        column_names_str = ", ".join([f'"{i}"' for i in self.column_names])
         return [
             (
                 f"{self.prefix} {index_name} ON {tablename} USING "
@@ -62,7 +62,7 @@ class CreateIndex(DDL):
         if method_name != "btree":
             raise ValueError("SQLite only support btree indexes.")
 
-        column_names_str = ", ".join(column_names)
+        column_names_str = ", ".join([f'"{i}"' for i in self.column_names])
         return [
             (
                 f"{self.prefix} {index_name} ON {tablename} "
diff --git a/piccolo/table.py b/piccolo/table.py
index 5bbfc088..dcc0efd4 100644
--- a/piccolo/table.py
+++ b/piccolo/table.py
@@ -404,8 +404,8 @@ class Table(metaclass=TableMetaclass):
 
         .. code-block:: python
 
-            band = await Band.objects().first().run()
-            manager = await band.get_related(Band.manager).run()
+            band = await Band.objects().first()
+            manager = await band.get_related(Band.manager)
             >>> print(manager.name)
             'Guido'
 
@@ -534,7 +534,7 @@ class Table(metaclass=TableMetaclass):
 
             instance = await Manager.objects().get(
                 Manager.name == 'Guido'
-            ).run()
+            )
 
             >>> instance.to_dict()
             {'id': 1, 'name': 'Guido'}
@@ -677,7 +677,7 @@ class Table(metaclass=TableMetaclass):
 
             concert = await Concert.objects(
                 Concert.all_related()
-            ).run()
+            )
 
             >>> concert.band_1
             <Band: 1>
@@ -695,7 +695,7 @@ class Table(metaclass=TableMetaclass):
                 Concert.venue,
                 Concert.band_1,
                 Concert.band_2
-            ).run()
+            )
 
         :param exclude:
             You can request all columns, except these.
@@ -726,7 +726,7 @@ class Table(metaclass=TableMetaclass):
             await Band.select(
                 Band.all_columns(),
                 Band.manager.all_columns()
-            ).run()
+            )
 
         This is mostly useful when the table has a lot of columns, and typing
         them out by hand would be tedious.
@@ -785,7 +785,7 @@ class Table(metaclass=TableMetaclass):
 
             await Band.insert(
                 Band(name="Pythonistas", popularity=500, manager=1)
-            ).run()
+            )
 
         """
         query = Insert(table=cls)
@@ -800,7 +800,7 @@ class Table(metaclass=TableMetaclass):
 
         .. code-block:: python
 
-            await Band.raw('select * from band').run()
+            await Band.raw('select * from band')
 
         Or passing in parameters:
 
@@ -839,9 +839,9 @@ class Table(metaclass=TableMetaclass):
 
         .. code-block:: python
 
-            await Band.select().columns(Band.name).run()
-            await Band.select(Band.name).run()
-            await Band.select('name').run()
+            await Band.select().columns(Band.name)
+            await Band.select(Band.name)
+            await Band.select('name')
 
         :param exclude_secrets:
             If ``True``, any password fields are omitted from the response.
@@ -861,7 +861,7 @@ class Table(metaclass=TableMetaclass):
 
         .. code-block:: python
 
-            await Band.delete().where(Band.name == 'Pythonistas').run()
+            await Band.delete().where(Band.name == 'Pythonistas')
 
         :param force:
             Unless set to ``True``, deletions aren't allowed without a
@@ -879,7 +879,7 @@ class Table(metaclass=TableMetaclass):
 
         .. code-block:: python
 
-            await Band.create_table().run()
+            await Band.create_table()
 
         """
         return Create(
@@ -895,7 +895,7 @@ class Table(metaclass=TableMetaclass):
 
         .. code-block:: python
 
-            await Band.alter().rename_column(Band.popularity, 'rating').run()
+            await Band.alter().rename_column(Band.popularity, 'rating')
 
         """
         return Alter(table=cls)
@@ -912,11 +912,11 @@ class Table(metaclass=TableMetaclass):
 
             pythonistas = await Band.objects().where(
                 Band.name == 'Pythonistas'
-            ).first().run()
+            ).first()
 
             pythonistas.name = 'Pythonistas Reborn'
 
-            await pythonistas.save().run()
+            await pythonistas.save()
 
             # Or to remove it from the database:
             await pythonistas.remove()
@@ -928,12 +928,12 @@ class Table(metaclass=TableMetaclass):
             .. code-block:: python
 
                 # Without nested
-                band = await Band.objects().first().run()
+                band = await Band.objects().first()
                 >>> band.manager
                 1
 
                 # With nested
-                band = await Band.objects(Band.manager).first().run()
+                band = await Band.objects(Band.manager).first()
                 >>> band.manager
                 <Band 1>
 
@@ -947,7 +947,7 @@ class Table(metaclass=TableMetaclass):
 
         .. code-block:: python
 
-            await Band.count().where(Band.popularity > 1000).run()
+            await Band.count().where(Band.popularity > 1000)
 
         """
         return Count(table=cls)
@@ -959,7 +959,7 @@ class Table(metaclass=TableMetaclass):
 
         .. code-block:: python
 
-            await Band.exists().where(Band.name == 'Pythonistas').run()
+            await Band.exists().where(Band.name == 'Pythonistas')
 
         """
         return Exists(table=cls)
@@ -971,7 +971,7 @@ class Table(metaclass=TableMetaclass):
 
         .. code-block:: python
 
-            await Band.table_exists().run()
+            await Band.table_exists()
 
         """
         return TableExists(table=cls)
@@ -994,19 +994,19 @@ class Table(metaclass=TableMetaclass):
                 {Band.name: "Spamalot"}
             ).where(
                 Band.name == "Pythonistas"
-            ).run()
+            )
 
             await Band.update(
                 {"name": "Spamalot"}
             ).where(
                 Band.name == "Pythonistas"
-            ).run()
+            )
 
             await Band.update(
                 name="Spamalot"
             ).where(
                 Band.name == "Pythonistas"
-            ).run()
+            )
 
         :param force:
             Unless set to ``True``, updates aren't allowed without a
@@ -1023,7 +1023,7 @@ class Table(metaclass=TableMetaclass):
 
         .. code-block:: python
 
-            await Band.indexes().run()
+            await Band.indexes()
 
         """
         return Indexes(table=cls)
@@ -1041,7 +1041,7 @@ class Table(metaclass=TableMetaclass):
 
         .. code-block:: python
 
-            await Band.create_index([Band.name]).run()
+            await Band.create_index([Band.name])
 
         """
         return CreateIndex(
@@ -1061,7 +1061,7 @@ class Table(metaclass=TableMetaclass):
 
         .. code-block:: python
 
-            await Band.drop_index([Band.name]).run()
+            await Band.drop_index([Band.name])
 
         """
         return DropIndex(table=cls, columns=columns, if_exists=if_exists)
diff --git a/tests/table/test_indexes.py b/tests/table/test_indexes.py
index 95275a4f..383dc06f 100644
--- a/tests/table/test_indexes.py
+++ b/tests/table/test_indexes.py
@@ -1,10 +1,17 @@
 from unittest import TestCase
 
-from tests.base import DBTestCase
+from piccolo.columns.column_types import Integer
+from piccolo.table import Table
 from tests.example_apps.music.tables import Manager
 
 
-class TestIndexes(DBTestCase):
+class TestIndexes(TestCase):
+    def setUp(self):
+        Manager.create_table().run_sync()
+
+    def tearDown(self):
+        Manager.alter().drop_table().run_sync()
+
     def test_create_index(self):
         """
         Test single column and multi column indexes.
@@ -27,6 +34,34 @@ class TestIndexes(DBTestCase):
             self.assertTrue(index_name not in index_names)
 
 
+class Concert(Table):
+    order = Integer()
+
+
+class TestProblematicColumnName(TestCase):
+    def setUp(self):
+        Concert.create_table().run_sync()
+
+    def tearDown(self):
+        Concert.alter().drop_table().run_sync()
+
+    def test_problematic_name(self):
+        """
+        Make sure we can add an index to a column with a problematic name
+        (which clashes with a SQL keyword).
+        """
+        columns = [Concert.order]
+        Concert.create_index(columns=columns).run_sync()
+        index_name = Concert._get_index_name([i._meta.name for i in columns])
+
+        index_names = Concert.indexes().run_sync()
+        self.assertTrue(index_name in index_names)
+
+        Concert.drop_index(columns).run_sync()
+        index_names = Concert.indexes().run_sync()
+        self.assertTrue(index_name not in index_names)
+
+
 class TestIndexName(TestCase):
     def test_index_name(self):
         self.assertEqual(