Introducing SQLite file uploads

Splitgraph already had excellent support for importing CSV files, but now we can use the same interface to upload SQLite files as well. Try it out:

Why use Splitgraph for SQLite data?

SQLite is an incredibly versatile embedded database, used by apps on mobile devices, client code running in web browsers (via WebAssembly), and native applications. The main difference compared to more traditional relational databases such as PostgreSQL is that an SQLite database is just a file, there's no need for a dedicated database server process. This is an enormous advantage in resource-constrained environments and makes it possible to build cost-effective data-intensive applications using Litestream for example. At the same time, SQLite's database-as-a-file approach makes concurrent multi user access -and thus collaboration- difficult.

In addition to collaboration, Splitgraph offers countless features beyond what SQLite provides, for example:

• column-based data storage for efficient aggregation queries
• immutable data images
• built-in support for dbt transformations
• joining tables from other databases using foreign data wrappers

What about Datasette?

Datasette is a versatile open source tool for data analysis and publishing built on SQLite. It's a popular solution for making SQLite data available to the public, but doesn't have built-in support for controlling access to published data. For those Datasette users who need fine grained access control, Splitgraph offers a solution.

Similar to Datasette, Splitgraph also makes it possible to directly query database tables from the browser. Unlike Datasette, authenticated Splitgraph-backed web apps can even write to database tables.

Datasette documentation includes two demo databases, global-power-plants.db and legislators.db, both of which can be loaded into Splitgraph. You can also try the SQLite version of the Chinook reference database. Try uploading them here!

Key differences and limitations

SQLite inherited most of it's syntax from PostgreSQL, so the majority of SQLite queries should work on Splitgraph out of the box. A major difference is that by default, SQLite allows any type of data to be written to any column, regardless of the column's declared type. In fact, column type declarations are entirely optional in SQLite!

PostgreSQL is more strict when it comes to enforcing data types. Importing SQLite databases which store different types of data in a column than what was declared in the table schema may fail. Tables containing columns without a declared type are ignored by the current implementation of SQLite import.

We'd love to hear from you! If you're excited about this new feature or if you're missing something from the current SQLite import functionality, let us know!