# Get Table Meta API

> Read the columns of a table or the fields of a collection in one common format with the get table meta system API of API Maker - names, types, counts - on every database, for migrations and dynamic code.

Source: https://docs.apimaker.dev/v4/docs/apis-all/system-apis/system-generated-get-table-meta-api.html

Describes a table : the name and the type of every column (or of every field found in a MongoDB collection), in the same format for every database. Migration scripts use it to check whether a column exists before adding it.

| | |
|---|---|
| Method | POST |
| URL | `/api/system-api/admin/get-table-meta` : `admin` is the user path of your account |
| Body | `{ instance, database, collection }` or an array of them |
| Answer | `data` : an array of fields, or an array of arrays |
| From code | [`g.sys.system.getTableMeta`](https://docs.apimaker.dev/v4/docs/apis-all/system-apis/system-generated-get-table-meta-api.html#from-code) |

## The call

**Body**

```json
{ "instance": "mysql8", "database": "inventory", "collection": "customers" }
```

**Answer**

```json
{
    "success": true,
    "statusCode": 200,
    "data": [
        { "name": "customer_id", "path": "customer_id", "count": 24, "total_count": 24, "type": "Number", "has_duplicates": false, "probability": 1 },
        { "name": "first_name", "path": "first_name", "count": 24, "total_count": 24, "type": [ "String", "Null" ], "has_duplicates": true, "probability": 1 }
    ]
}
```

| Key | Meaning |
|---|---|
| `name`, `path` | The column, with its path for nested MongoDB fields. |
| `type` | The type, or the list of types seen (MongoDB). |
| `count`, `total_count`, `probability` | For MongoDB, in how many of the sampled documents the field appears. |
| `has_duplicates` | Whether the same value appears twice in the sample. |

- `table` works in place of `collection`. An array of tables answers an array of arrays, in the same order.
- A table of a tenant : `"instance": "crm::acme"`, or the header `x-am-tenant-username`.

## From code

**TypeScript**

**A migration which adds a column once**

```typescript
const meta = await g.sys.system.getTableMeta({ instance: 'mysql8', database: 'inventory', table: 'order_transactions' });
if (!meta.find(c => c.name === 'description')) {
    await g.sys.system.executeQuery({ instance: 'mysql8', query: 'ALTER TABLE `inventory`.`order_transactions` ADD COLUMN `description` varchar(255) NULL' });
}
```

**Python**

**A migration which adds a column once**

```python
meta = await g.sys.system.get_table_meta(instance='mysql8', database='inventory', table='order_transactions')
if not any(column.name == 'description' for column in meta):
    await g.sys.system.execute_query('mysql8', 'ALTER TABLE `inventory`.`order_transactions` ADD COLUMN `description` varchar(255) NULL')
```

**PHP**

**A migration which adds a column once**

```php
$meta = $g->sys->system->getTableMeta(instance: 'mysql8', database: 'inventory', table: 'order_transactions');
if (!array_filter($meta, fn ($column) => $column->name === 'description')) {
    $g->sys->system->executeQuery('mysql8', 'ALTER TABLE `inventory`.`order_transactions` ADD COLUMN `description` varchar(255) NULL');
}
```

**C#**

**A migration which adds a column once**

```csharp
var meta = await g.Sys.System.GetTableMeta(new GetTableMeta { Instance = "mysql8", Database = "inventory", Table = "order_transactions" });
if (!meta.Any(column => column.Name == "description"))
{
    await g.Sys.System.ExecuteQuery(new ExecuteQuery("mysql8", "ALTER TABLE `inventory`.`order_transactions` ADD COLUMN `description` varchar(255) NULL"));
}
```

**Go**

**A migration which adds a column once**

```go
meta, err := g.Sys.System.GetTableMeta(am.GetTableMeta{Instance: "mysql8", Database: "inventory", Table: "order_transactions"})
if err != nil {
	return nil, err
}
found := false
for _, column := range meta {
	found = found || column.Name == "description"
}
if !found {
	if _, err := g.Sys.System.ExecuteQuery(am.ExecuteQuery{Instance: "mysql8", Query: "ALTER TABLE `inventory`.`order_transactions` ADD COLUMN `description` varchar(255) NULL"}); err != nil {
		return nil, err
	}
}
```

**Rust**

**A migration which adds a column once**

```rust
let meta = g.sys.system.get_table_meta(GetTableMeta::default().instance("mysql8").database("inventory").table("order_transactions"))?;
if !meta.iter().any(|column| column.name == "description") {
    g.sys.system.execute_query(ExecuteQuery::new("mysql8", "ALTER TABLE `inventory`.`order_transactions` ADD COLUMN `description` varchar(255) NULL"))?;
}
```

**Java**

**A migration which adds a column once**

```java
List<TableMetaField> meta = g.sys.system.getTableMeta(new GetTableMeta().instance("mysql8").database("inventory").table("order_transactions"));
if (meta.stream().noneMatch(column -> "description".equals(column.getName()))) {
    g.sys.system.executeQuery(new ExecuteQuery("mysql8", "ALTER TABLE `inventory`.`order_transactions` ADD COLUMN `description` varchar(255) NULL"));
}
```

**Kotlin**

**A migration which adds a column once**

```kotlin
val meta = g.sys.system.getTableMeta(GetTableMeta().instance("mysql8").database("inventory").table("order_transactions"))
if (meta.none { it.name == "description" }) {
    g.sys.system.executeQuery(ExecuteQuery("mysql8", "ALTER TABLE `inventory`.`order_transactions` ADD COLUMN `description` varchar(255) NULL"))
}
```

### A table of a tenant
- `instance: "crm::acme"` reads the table in the database of the tenant `acme` of the [multi-tenant](https://docs.apimaker.dev/v4/docs/features/multi-tenant.html) instance `crm`. In a custom API called for a tenant, `instance: "crm"` reads it in the database of that tenant.

**TypeScript**

```typescript
await g.sys.system.getTableMeta({
    instance: 'crm::acme',
    database: 'crm',
    collection: 'customers'
});
```

**Python**

```python
await g.sys.system.get_table_meta(
    'crm::acme',
    'crm',
    'customers',
)
```

**PHP**

```php
$g->sys->system->getTableMeta(
    'crm::acme',
    'crm',
    'customers',
);
```

**C#**

```csharp
await g.Sys.System.GetTableMeta(new GetTableMeta("crm::acme", "crm", "customers"));
```

**Go**

```go
if _, err := g.Sys.System.GetTableMeta(am.GetTableMeta{
	Instance:   "crm::acme",
	Database:   "crm",
	Collection: "customers",
}); err != nil {
	return nil, err
}
```

**Rust**

```rust
g.sys.system.get_table_meta(GetTableMeta::new("crm::acme", "crm", "customers"))?;
```

**Java**

```java
g.sys.system.getTableMeta(new GetTableMeta("crm::acme", "crm", "customers"));
```

**Kotlin**

```kotlin
g.sys.system.getTableMeta(GetTableMeta("crm::acme", "crm", "customers"))
```

## Access and settings

- Over HTTP, a system API answers once its [settings](https://docs.apimaker.dev/v4/docs/settings/systemApiSettings.html) give it `apiAccessType: TOKEN_ACCESS` (the token of an API user whose [group](https://docs.apimaker.dev/v4/docs/apis-security/api-group-permission.html) grants this system API, in `x-am-authorization`) or `IS_PUBLIC`. Without settings it is `NO_ACCESS` : your code calls it through `g.sys`, the admin panel tests it, and an HTTP call is refused.
- The settings can also cache the answer or require person tokens (`authProviders`) ; [pre and post hooks](https://docs.apimaker.dev/v4/docs/apis-all/hooks/preHook-api.html) run around it like around any API.
- The [request headers](https://docs.apimaker.dev/v4/docs/apis-all/header/requestHeader.html) apply : `x-am-response-case`, `x-am-content-type-response`, `x-am-internationalization`, `x-am-tenant-username`…

## Errors

| Code | When |
|---|---|
| `400` | The body is wrong : the message names the missing or invalid key. |
| `401` | No valid API user token, or the API is `NO_ACCESS` : `You are not authorized to access this API.` |
| `403` | No group grants this system API. |

## Related

- [All APIs at a glance](https://docs.apimaker.dev/v4/docs/apis-all/overview.html) · [Response format](https://docs.apimaker.dev/v4/docs/apis-all/response-format.html) · [System API settings](https://docs.apimaker.dev/v4/docs/settings/systemApiSettings.html) · [System APIs from code](https://docs.apimaker.dev/v4/examples/sys/system/system.html)