This Guide

This guide will walk you through the basic functionality and concepts of bit.io and will point you toward more detailed documentation on specific topics. After reading this guide, you will have a good understanding of the core features of bit.io, knowledge of what bit.io is and what differentiates it from other database tools, and ideas for where to find more detailed and specific guidance for particular use cases.

What is bit.io?

The Fastest Way to Get a Database

In short, bit.io is the fastest and easiest way to set up a PostgreSQL database. You can load data to a PostgreSQL database by dragging and dropping files, entering a data file's URL, sending data from R or Python applications or analyses, or using just about any other Postgres or HTTP client. You can then work with the data via the in-browser SQL editor or any of your favorite data analysis tools: SQL clients, R, Python, Jupyter notebooks, the command line, and more.

A Secure, Versatile, Convenient Home for Your Data

Once you've signed up and uploaded your data to a bit.io database, you have your own secure, private PostgreSQL database where you can upload more data, query and join tables, create schemas and views, and add documentation. Want to share your data? You can make the database public or share with any other user.

📘

What happened to repositories?

All of your tables exist in your own PostgreSQL databases. This is a significant difference from the earlier version of bit.io, where all of your tables existed in repositories, which were conceptually equivalent to PostgreSQL schemas. Having your own database means better performance and better resources isolation.

Why Use bit.io?

bit.io offers a full-featured PostgreSQL database that can be used in seconds with practically no configuration required and integrates with an ever-growing number of popular data tools. Any tool that works with Postgres will work with bit.io.

Using bit.io

The following sections of this guide will walk you through the core functionality of bit.io.

Create an Account

While you don't need an account to upload and query your first data file, having an account unlocks additional features, such as higher storage and query limits; data that persists longer than 72 hours; and the ability to access data via the API or some of bit.io's many integrations.

To create an account, select "sign up" on the upper right-hand side of the bit.io home page. Signing up is free!

Create a Database

Now it's time to create your first database. Click the "Create Database" button that appears after you first log in.

From here, you can name your database. Free databases on bit.io are limited to 3GB in size.

Click the "Create" button and you will be greeted with your database page, complete with an in-browser SQL editor. Now it's time to load some data!

Upload Your First Data File

If you have a CSV file on your computer, you can use it. Otherwise, we recommend trying it out with the U.S. Federal Housing Finance Association state-level House Price Index data. The url is: https://www.fhfa.gov/DataTools/Downloads/Documents/HPI/HPI_AT_state.csv.

To upload, simply click "Upload Data" and drag and drop the CSV. At this point, you can also name the new table and choose which schema to place it in (if you've just created your database, you only have a "public" schema).

You could also upload by pasting the URL of a data file in the "paste a link" box, in which case you don't have to download the file on your own computer.

📘

Public Schema

Your "public" schema isn't actually public (unless you've set your database to public). Other bit.io users can't access the contents of your public schema. The public schema represents the default schema referenced in queries. That is, if you attempt to query a table without specifying the schema, the query will attempt to access that table in the public schema and will fail if the table is actually in a different schema. To access tables in other schemas, you need to explicitly state the schema. For example, if I have table mytable in schema myschema, I can execute the query SELECT * FROM myschema.mytable but not SELECT * FROM mytable. The latter will work if mytable is in the public schema.

📘

Table Headers

When uploading data, you can select whether the presence of a table header should be auto-detected, the first row should be used as a table header, or there is no table header. In this case, auto-detection correctly concluded that there was no table header and assigned generic column names.

This results in the creation of a new table, which we called mytable. With that done, we can start querying the data right away!

Query the Data

Let's start simple and use the in-browser SQL query editor to get a list of all of the unique states included in the dataset. We'll use the following SQL query:

SELECT count(DISTINCT column_1)
FROM mytable;

Enter this into the query editor and click "Run Query." Here's what that looks like in the bit.io editor:

More Advanced Queries

Just about anything you can do with PostgreSQL is possible in bit.io, including more sophisticated queries. You're certainly not limited to counting rows!

Perhaps we want a column with a unique primary key. We can add this with the following query:

ALTER TABLE mytable
   ADD COLUMN id SERIAL
   PRIMARY KEY;

Now we have a column with an auto-incrementing unique key.

Now that we think about it, though, the unique key isn't very useful in this context. We're not stuck with it! Let's delete it.

ALTER TABLE mytable
DROP COLUMN id;

And now we're back where we started.

❗️

Deletions are Permanent

Be careful! Deleting a repo, table, or column is permanent. Make sure you really want to delete it and that you have a backup if necessary.

Another idea: let's add some table headers! We can change column_0 to state with:

ALTER TABLE mytable RENAME column_0 TO state;

Add some documentation

You can add table documentation in the "Documentation" pane—just click the pencil by your table name to start adding documentation. Use markdown syntax to style your documentation.

Download Query Results

You can click the download button to download the results of a query as a CSV or XLS file.

Connecting to a SQL Client

Thus far, we've only accessed our data through the browser interface. But we can do so much more. Do you have a preferred SQL client? You can connect it to bit.io in the same way as you'd connect to any other PostgreSQL database. The necessary credentials are in the "Connect" menu on your database page.

📘

PostgreSQL Connection Details

On any repository page, when you're signed in, you can click on the "connect" tab on the upper right to access API keys and PostgreSQL connection details.

For example, here's how the bit.io credentials map onto the PostgresSQL connection in Beekeeper Studio.

You can use the same credentials to connect to just about any SQL client, or to connect with programming languages such as R and Python that have packages for interfacing with PostgreSQL databases.

🚧

Database Names

By default, in the connect menu, your database name is formatted as username/dbname. Some integrations, especially those that use the PostgreSQL JDBC driver, will fail to connect because of the / in the database name. As such, you can use different separators: .,~,|,>, / are all valid. So if you're trying to connect to bit.io with a third-party service and encountering a connection failure, trying different separators in the database name (e.g. trying username.dbname) may fix the issues.

Using the Query API

One way to programmatically work with your data on bit.io is via the Query API. Using your API key (the "Password" from the connect menu), you can use a POST request to query any database you have access to. We can get the first ten rows of a table as follows, for example.

API_KEY=<your-api-key>
curl --request POST \
     --url https://api.bit.io/v2beta/query \
     --header "Authorization: Bearer ${API_KEY}" \
     --header "Accept: application/json" \
     --header "Content-Type: application/json" \
     --data '{"query_string":"SELECT * FROM mytable LIMIT 10;", "database_name":"GettingStarted/MyDatabase"}'

Note that, unlike in the prior version of bit.io, you must pass the database name as part of the request.

Share your Database

Chances are, you aren't the only one who will see or work with your data. We have a few options for sharing your data, whether it's with the whole world or with a few trusted colleagues.

Make your Database Public

Your databases are private by default. That means only you can view and edit them. You can make your databases publicly accessible and viewable by selecting "Public" from the dropdown menu next to your database name. This will let others view and query (but not modify) your databases.

Share your Database with Specific Users

If you want to enable specific people to access your database, you can invite them from the "Share" tab in the upper right side of your database page. You can choose the level of access each person has to your database:

• Reader: the user can read and query, but not modify, the contents of the database.
• Writer: the user can read and write data (including adding/removing tables, views, schemas, etc.)
• Admin: the user can do everything the writer can do, but can also add and remove collaborators (i.e. admins have access to the functionality of the Share tab).

Only the database owner can change public/private visibility or delete the database.

What's next?

After reading this guide, you can upload data to bit.io via the browser and query data using the in-browser query editor or your favorite SQL client. While this is enough to get started, there's much more you can do with bit.io. You can use a bit.io repository as in your data analyses with R or Python.

Scale your Database

bit.io offers a generous free tier, including three public or private databases (up to 3 GB each) and 1M rows queried. But some workflows require more. We want to allow you to scale your databases seamlessly. Sign up for bit.io pro. We'll make sure your databases are always fast and ready. You pay only $5 per 10M rows queried. We'll even store your active data (data you're actually querying a certain amount each month) for free.

Getting Help

bit.io pro subscribers can always contact support directly from the user menu, inside the app.

Other great resources include our User Forum for community-driven support and our Discord server.