Before proceeding
Ensure you install the CLI, log in to your Appwrite account, and initialize your Appwrite project.
Create and manage your tables using the CLI commands. The Appwrite CLI also helps you push your project's databases and tables schema from one project to another.
Initialize table
Create a new table using the following command:
appwrite init tables
Pull table
You can also pull your existing Appwrite tables and databases from the Appwrite Console using the pull command in the folder containing your appwrite.config.json file.
appwrite pull tables
appwrite.config.json
After initializing your Appwrite project and pulling your existing tables, your appwrite.config.json file should look similar to the following:
{
"projectId": "<PROJECT_ID>",
"endpoint": "https://<REGION>.cloud.appwrite.io/v1",
"tablesDB": [
{
"$id": "<DATABASE_ID>",
"name": "songs",
"$createdAt": "2023-07-01T18:35:27.802+00:00",
"$updatedAt": "2023-08-01T21:41:41.663+00:00",
"enabled": true
}
],
"tables": [
{
"$id": "<TABLE_ID>",
"$permissions": [
"create(\"any\")",
"read(\"any\")",
"update(\"any\")",
"delete(\"any\")"
],
"databaseId": "<DATABASE_ID>",
"name": "music",
"enabled": true,
"rowSecurity": false,
"columns": [
{
"key": "userID",
"type": "varchar",
"status": "available",
"error": "",
"required": false,
"array": false,
"size": 100,
"default": null
},
{
"key": "name",
"type": "varchar",
"status": "available",
"error": "",
"required": false,
"array": false,
"size": 100,
"default": null
},
{
"key": "cloudinaryId",
"type": "varchar",
"status": "available",
"error": "",
"required": false,
"array": false,
"size": 100,
"default": null
},
{
"key": "user",
"type": "varchar",
"status": "available",
"error": "",
"required": false,
"array": false,
"size": 100,
"default": null
},
{
"key": "audio",
"type": "varchar",
"status": "available",
"error": "",
"required": false,
"array": false,
"size": 200,
"default": null
},
{
"key": "genre",
"type": "varchar",
"status": "available",
"error": "",
"required": false,
"array": false,
"size": 500,
"default": null
},
{
"key": "artist",
"type": "varchar",
"status": "available",
"error": "",
"required": false,
"array": false,
"size": 500,
"default": null
}
],
"indexes": []
}
]
}
You can also move the tablesDB and tables arrays into separate JSON files with the includes field.
Learn more about multi-file configuration
Push table
Use the push command in the folder containing your appwrite.config.json file to push the changes you made.
appwrite push tables
Update columns
When you change a column that already exists on the server, the CLI updates it in place instead of deleting and recreating it, so the rows in that column keep their data. Only a subset of fields can change this way. Every other change still needs a delete and recreate, which clears the data in that column, and push warns you before it does that.
| Change | Behavior |
required and default on any column except relationship columns | Updated in place |
size on varchar columns and on string columns without a format | Updated in place |
min and max on integer, bigint, and double columns | Updated in place |
elements on enum columns | Updated in place |
onDelete on relationship columns | Updated in place |
type, array, encrypt, and format | Recreated, data in the column is lost |
relatedTable, relationType, twoWay, and twoWayKey on relationship columns | Recreated, data in the column is lost |
Any change to an index | Recreated, indexes hold no row data |
If an in-place update fails, for example when a new size is too small for the values already stored, push stops with an error before it deletes anything, so a failed update cannot leave your table half-migrated.
Rename a column
Renaming a column in appwrite.config.json looks like a delete and an add to the CLI, which would drop the column's data. To rename without losing data, set the column's key to the new name and add a previousKey field pointing at the name that still exists on the server.
You always add previousKey yourself. The CLI never writes it for you, and appwrite pull tables removes it from your config because pull replaces the columns array with the server's version.
{
"tables": [
{
"$id": "posts",
"databaseId": "blog",
"name": "Posts",
"columns": [
{
"key": "headline",
"previousKey": "title",
"type": "varchar",
"size": 255,
"required": true
}
],
"indexes": [
{
"key": "by_headline",
"type": "key",
"columns": ["headline"]
}
]
}
]
}
Then push the table:
appwrite push tables
Push reports the change as a rename instead of a deletion followed by an addition, and the rows carry over to the new name.
Indexes that reference the column follow the rename, so they are not recreated. Keep the index columns array in sync with the new name, as in the example above. If an index still points at the old name, the CLI treats it as a changed index and recreates it.
You can rename a column and change its updatable fields in the same push. The CLI applies the rename first, then the field update.
How the CLI resolves previousKey
The hint describes the state you want, so pushing the same config twice is safe. Push looks at what the table already has and decides from there:
- If the old name is still on the server and the new one isn't, push renames the column.
- If the new name is already there, the rename has happened. Push does nothing and ignores the leftover hint.
- If neither name is on the server, push ignores the hint and creates the column as a new one.
- If both names are on the server, push cannot rename without a collision. It warns, ignores the hint, and deletes the old column if it is absent from your config.
Commands
The tables-db command allows you to create structured tables of rows, queries, and filter lists of rows. Appwrite TablesDB CLI commands generally follow the following syntax:
appwrite tables-db [COMMAND] [OPTIONS]
| Command | Description |
list-tables [options] | Get a list of all tables that belong to the provided databaseId. You can use the search parameter to filter your results. |
create-table [options] | Create a new Table. Before using this route, you should create a new database resource using either a server integration API or directly from your database console. |
get-table [options] | Get a table by its unique ID. This endpoint response returns a JSON object with the table metadata. |
update-table [options] | Update a table by its unique ID. |
delete-table [options] | Delete a table by its unique ID. Only users with write permissions have access to delete this resource. |
list-columns [options] | List columns in the table. |
create-boolean-column [options] | Create a boolean column. |
update-boolean-column [options] | Update a boolean column. Changing the 'default' value will not update already existing rows. |
create-datetime-column [options] | Create a date time column according to the ISO 8601 standard. |
update-datetime-column [options] | Update a date time column. Changing the 'default' value will not update already existing rows. |
create-email-column [options] | Create an email column. |
update-email-column [options] | Update an email column. Changing the 'default' value will not update already existing rows. |
create-enum-column [options] | Create an enumeration column. The 'elements' param acts as a white-list of accepted values for this column. |
update-enum-column [options] | Update an enum column. Changing the 'default' value will not update already existing rows. |
create-float-column [options] | Create a float column. Optionally, minimum and maximum values can be provided. |
update-float-column [options] | Update a float column. Changing the 'default' value will not update already existing rows. |
create-integer-column [options] | Create an integer column. Optionally, minimum and maximum values can be provided. |
update-integer-column [options] | Update an integer column. Changing the 'default' value will not update already existing rows. |
create-ip-column [options] | Create IP address column. |
update-ip-column [options] | Update an ip column. Changing the 'default' value will not update already existing rows. |
create-relationship-column [options] | Create relationship column. Learn more about relationship columns. |
create-string-column [options] | Create a string column. |
update-string-column [options] | Update a string column. Changing the 'default' value will not update already existing rows. |
create-text-column [options] | Create a text column. |
update-text-column [options] | Update a text column. Changing the 'default' value will not update already existing rows. |
create-url-column [options] | Create a URL column. |
update-url-column [options] | Update an url column. Changing the 'default' value will not update already existing rows. |
get-column [options] | Get column by ID. |
delete-column [options] | Deletes an column. |
update-relationship-column [options] | Update relationship column. Learn more about relationship columns. |
list-indexes [options] | List indexes in the table. |
create-index [options] | Creates an index on the columns listed. Your index should include all the columns you will query in a single request. Columns can be 'key', 'fulltext', and 'unique'. |
get-index [options] | Get index by ID. |
delete-index [options] | Delete an index. |
list-table-logs [options] | Get the table activity logs list by its unique ID. |