# CoCalc API Tutorial - Database Queries ## 1. The CoCalc schema file CoCalc keeps information about accounts, projects, and other data in a database. The API provides limited access to this database. To see which files and fields are accessible, see CoCalc source files in [src/smc-util/db-schema](https://github.com/sagemathinc/cocalc/tree/master/src/smc-util/db-schema). There is an explanation of how the schema is setup in the comments at the top of the [types.ts](https://github.com/sagemathinc/cocalc/blob/master/src/smc-util/db-schema/types.ts) file, just after the licensing boilerplate. Take a moment to read that part of the file now. Here's an example, with excerpts from the [projects.ts](https://github.com/sagemathinc/cocalc/blob/master/src/smc-util/db-schema/projects.ts) file. Information about projects is kept in the `projects` table. Here's the beginning of the `projects` table definition: ``` Table({ name: "projects", rules: { primary_key: "project_id", ``` Further down, you find the list of fields which an API user can read (_get_) and write (_set_): showing that you can both get and set the project title and description: ``` user_query: { get: { ... fields: { project_id: null, title: "", description: "", ... set: { fields: { ... title: true, description: true, ``` There is additional information in the comments, including other access limitations, explanations of how a field is used, and default values. The `query` API call can be used to _get_ or _set_ values in the database. **If any attribute in the query object is `null`, the query is a _get_; if all values specified for fields are not `null`, the query is a _set_**. ## 2. Getting values from the database To get values from a table using the API, you provide table name, key to the record you're interested in, and fields you would like to retrieve. Think of the query payload as a template for the record you'd like to retrieve, including only the fields you're interested in. **In a _get_ query, at least one of the fields in the query record must be null.** Fields passed in as null will be filled in with data in the response. Open example notebook [PYTHON/03-get-query.ipynb](PYTHON/03-get-query.ipynb). Note that a value of `null` in the JSON query object is indicated by `None` in the Python code. **Get one record.** The first two cells set up imports and credentials. Make sure the account you are using has at least one project and run the cell marked 'Query A'. It gets title and description for one project. This is not particularly useful when you have more than one project, because it doesn't specify which project has its details returned. **Get multiple records.** To return more than one record to be returned, use brackets around the record object in the query. Before running the next cell, make sure the account you are using has two or more projects. Run the cell marked 'Query B' to get a listing of all projects. Do you see the difference in query payloads? **Filter multiple records.** To filter results, that is, to select only records with fields that match certain values, you can specify field names and values you want matched. In the example file, edit the payload in cell marked Query C to select just one of your projects, based on one or more of the fields `project_id`, `title`, or `description` - making sure at least one field is still `null` (`None`), and run the cell. Note: CoCalc `query` is similar to Facebook's [GraphQL](https://code.facebook.com/posts/1691455094417024/graphql-a-data-query-language/). ## 3. Setting values in the database To set values for a record in the database, provide a unique key and specify the settings for all fields you want to modify. Open example notebook [PYTHON/04-set-query.ipynb](PYTHON/04-set-query.ipynb). This notebook includes two examples of updating a database table. **Set the value of a field.** Run the first two cells of the notebook, then look at Query A. The first call to the API sets the new project description. The second call displays the result. Enter the project_id for one of your projects and a new description you would like to set for that project and run the cell. Note that the response to a successful _set_ query has an empty message object. **Set one value in a map field.** Some fields in the CoCalc database have values that are complex objects. Here's an example from the 'accounts' table of the 'editor_settings' field: ``` editor_settings: { strip_trailing_whitespace: false, show_trailing_whitespace: true, line_wrapping: true, line_numbers: true, ... ``` These fields have a 'type' attribute of 'map'. Check the type of the 'editor_settings' field in the [accounts.ts](https://github.com/sagemathinc/cocalc/blob/0f8847c50b292772d1404d31d5017c413d520818/src/smc-util/db-schema/accounts.ts) file now. The CoCalc API makes it easy to set just one or (a few) selected attributes of a map field. Settings specified in the _set_ query are merged into the rest of the map. Run Query B and verify that you can set just one attribute in 'editor_settings' and the change is merged into the rest of the settings. ## 4. More practice Try the following queries to test your understanding. You'll find answers, coded as `curl` commands, in the [API documentation for query](https://doc.cocalc.com/api/query.html): ### get - Get project id, given title and description. - Get users (i.e. owner and all collaborators), given the project id. - Show project upgrades. ### set - Make a path public (publish a file). - Add an upgrade to a project. [Back](FirstSteps.md) First Steps [Next](Specialized.md) Specialized Calls