TogetherDB

https://togetherdb.com/docs
Admin
TogetherDB is a collaborative online database management tool and cloud hub that allows developers...
Tokens:6,383
Snippets:87
Trust Score:4.9
Update:1 week ago
Show doc for...
Tokens:
Raw
### Create MySQL user for TogetherDB (placeholder)

Source: https://togetherdb.com/docs/quick-start-guide

Placeholder SQL for creating a MySQL user with limited privileges. The statements are commented out and marked as TODO; they are not yet implemented.

```sql
-- TODO: Determine from https://dev.mysql.com/doc/refman/8.0/en/grant.html#grant-table-privileges
-- TODO: Test

-- grant select, insert, update, delete on ru_users* to. 'togetherdb'@'35.157.231.179';
-- create user 'togetherdb'@'35.157.231.179' identified by 'secret';
```

--------------------------------

### SQL: Fetch database structure (information_schema queries)

Source: https://togetherdb.com/docs/mysql-setup

Executed when navigating to a database or hard-reloading a table view. Issues 4 queries to information_schema: lists databases, table names, references, and columns.

```sql
-- lists accessible database names for this connection
SHOW DATABASES;

-- table names
SELECT TABLE_NAME
FROM information_schema.TABLES
WHERE TABLE_SCHEMA='<database-name>' ORDER BY TABLE_NAME ASC;

-- references
SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, REFERENCED_TABLE_SCHEMA, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME
FROM information_schema.KEY_COLUMN_USAGE
WHERE NOT REFERENCED_TABLE_SCHEMA IS NULL;

-- columns
SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE, COLUMN_TYPE, IS_NULLABLE, COLUMN_KEY, COLUMN_DEFAULT, EXTRA, PRIVILEGES, CHARACTER_MAXIMUM_LENGTH
FROM information_schema.columns
WHERE TABLE_SCHEMA!='information_schema' AND TABLE_SCHEMA!='performance_schema' AND TABLE_SCHEMA!='mysql'
ORDER BY ORDINAL_POSITION ASC
```

--------------------------------

### List MySQL users

Source: https://togetherdb.com/docs/mysql-setup

Lists all MySQL users and their hosts. Use this if you don't remember the exact user specification for the TogetherDB user.

```sql
SELECT Host, User FROM mysql.user;
```

--------------------------------

### SQL: View table rows and approximate row counts

Source: https://togetherdb.com/docs/mysql-setup

Executed when viewing a table. Fetches actual rows, referenced rows, and approximate row counts from information_schema. Exact row count is only determined when explicitly requested or when approximation is less than 10,000 rows.

```sql
-- get actual rows and its references
SELECT * FROM table_name LIMIT 50;
SELECT * FROM referenced_table1_name WHERE `id` IN (<referenced ids>);

-- get approximate row counts from cached statistics. we avoid exact counting here for performance reasons
SELECT TABLE_SCHEMA, TABLE_NAME,
    CASE
        WHEN avg_row_length=0 THEN 0
        ELSE ROUND(data_length / avg_row_length)
    END AS approx_count_1,
    table_rows AS approx_count_2
FROM information_schema.TABLES
WHERE TABLE_SCHEMA!='sys' AND TABLE_SCHEMA!='mysql' AND TABLE_SCHEMA!='information_schema' AND TABLE_SCHEMA!='performance_schema';
```

--------------------------------

### Create PostgreSQL user for TogetherDB

Source: https://togetherdb.com/docs/quick-start-guide

Creates a login role named 'togetherdb' with a password. Use this as the first step when setting up a restricted user for TogetherDB in PostgreSQL.

```sql
CREATE ROLE togetherdb WITH LOGIN PASSWORD 'secret123';
```

--------------------------------

### SQL: Load more rows when scrolling

Source: https://togetherdb.com/docs/mysql-setup

Executed when scrolling down in the table to load more rows. Uses LIMIT with OFFSET to fetch the next batch of rows and their references.

```sql
SELECT * FROM table_name LIMIT 50 OFFSET 100
SELECT * FROM referenced_table1_name WHERE `id` IN (<newly referenced ids>);
```

--------------------------------

### Create MySQL user for TogetherDB

Source: https://togetherdb.com/docs/mysql-setup

Creates a new MySQL user 'togetherdb' that can only connect from the TogetherDB worker server IP 159.69.45.33. The user has no permissions by default.

```sql
CREATE USER 'togetherdb'@'159.69.45.33' IDENTIFIED BY 'secret';
```

--------------------------------

### SQL: Update a value and refresh rows

Source: https://togetherdb.com/docs/mysql-setup

Executed when updating a value. Updates by primary key column (always indexed) and then fetches the updated row and its references.

```sql
-- updates by primary key column which are always indexed
UPDATE table_name SET column_name=<value> WHERE `id`=row_id LIMIT 1;

-- get row values again
SELECT * FROM table_name WHERE `id`=row_id LIMIT 1;
SELECT * FROM referenced_table1_name WHERE `id` IN (<newly referenced ids>);
```

--------------------------------

### Grant access to specific databases

Source: https://togetherdb.com/docs/mysql-setup

Grants SELECT on mysql.* for database/table info and SELECT, INSERT, UPDATE, DELETE on specific databases (mydatabase1, mydatabase2). Use this to limit access to selected databases.

```sql
-- to access database/table infos (recommended)
GRANT SELECT ON mysql.* TO 'togetherdb'@'159.69.45.33';

-- grant access to your databases
GRANT SELECT, INSERT, UPDATE, DELETE ON mydatabase1.* TO 'togetherdb'@'159.69.45.33';
GRANT SELECT, INSERT, UPDATE, DELETE ON mydatabase2.* TO 'togetherdb'@'159.69.45.33';
...
```

--------------------------------

### Grant full access to all databases

Source: https://togetherdb.com/docs/mysql-setup

Grants SELECT, INSERT, UPDATE, DELETE on all databases to the TogetherDB user. Recommended for viewing and editing data in all databases.

```sql
GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO 'togetherdb'@'159.69.45.33';
```

--------------------------------

### Grant PostgreSQL privileges to TogetherDB user

Source: https://togetherdb.com/docs/quick-start-guide

Grants the 'togetherdb' role permissions to read and write data on all tables, sequences, and functions in the public schema, and sets default privileges for future objects. Run these statements for each database you want to manage with TogetherDB.

```sql
GRANT CONNECT ON DATABASE <dbname> TO togetherdb;

GRANT SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER ON ALL TABLES IN SCHEMA public TO togetherdb;
GRANT SELECT, UPDATE ON ALL SEQUENCES IN SCHEMA public TO togetherdb;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO togetherdb;

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER ON TABLES TO togetherdb;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, UPDATE ON SEQUENCES TO togetherdb;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT EXECUTE ON FUNCTIONS TO togetherdb;

-- TODO: Test
```

--------------------------------

### Grant read-only access

Source: https://togetherdb.com/docs/mysql-setup

Grants SELECT only on all databases, enabling read-only mode for TogetherDB.

```sql
GRANT SELECT ON *.* TO 'togetherdb'@'159.69.45.33';
```

--------------------------------

### Delete TogetherDB user

Source: https://togetherdb.com/docs/mysql-setup

Deletes the TogetherDB user from MySQL. Use this to leave TogetherDB.

```sql
DROP USER 'togetherdb'@'159.69.45.33';
```
Tokens:
Raw