SQLite Database Tutorial

With SQLite Database support in Fermyon Cloud, you can persist relational data generated by your Spin application in between application invocations without worrying about database management. Fermyon Cloud will provision and manage the database on your behalf:, no operations are required. Fermyon Cloud will do the heavy lifting for you. To learn more about the Spin SQLite SDK, please visit the API guide.

Please check that you have Spin CLI v1.4 or greater, and cloud plugin v0.3.1 or greater installed.

Service Limitations For the Database in Fermyon Cloud

  • To run a SQL statement (for migration and schema creation), use the spin cloud sqlite list and spin cloud sqlite execute commands. The equivalent of spin up --sqlite for spin deploy does currently not exist in Cloud. See this section for more info.

Creating a New Spin Application

If you already have a Spin application, you can skip this step. If you do not have a Spin application locally you will need to create one.

Grant SQLite Permission

To tell Spin that we want to use SQLite storage, we only need to grant SQLite permission to a component in the application’s manifest (the spin.toml file) by supplying a label. For example:

[component.example]
sqlite_databases = ["default"]

The new database file (sqlite_db.db) is created in your application’s .spin folder when you run this Spin application. The database persists across Spin application invocations and updates. The database is empty and can optionally have tables and data added using the following step.

Managing Your Local Spin Database Schema

See preparing an SQLite Database for more information on using the spin up subcommand’s --sqlite option to create and populate tables locally. No Cloud database is created or changed as a result of using this this optional spin up --sqlite example. Populating this local database can be useful for testing (the relationship between your application’s logic and your schema) before deploying your application to Fermyon Cloud.

When developing locally, you may also want to test queries, check tables were created properly, or inspect the data in your local SQLite database. sqlite3 is a command line program that allows users to evaluate queries interactively. You can point the sqlite3 CLI to the .spin/sqlite_db.db file, located in your application directory, to view or modify your database directly. Note: .spin/sqlite_db.db is created on spin up.

$ sqlite3 .spin/sqlite_db.db
SQLite version 3.43.2 2023-10-10 13:08:14
Enter ".help" for usage hints.
sqlite> .tables
Users
sqlite> SELECT * FROM Users;

Tables and Data in Fermyon Cloud

You have two options to create your database (both are equally valid):

  • Option A: Implicitly create a database (with an automatically assigned arbitrary database name) via spin cloud deploy. For example inspirational-pig or similarly named database will be created.
  • Option B : Explicitly provide the name of the database to create via the spin cloud sqlite create command. For example, spin cloud sqlite create finance-database creates a database called finance-database inside your Fermyon Cloud account.

Please run whatever step is relevant to your needs.

Implicitly Create A Database Via spin cloud deploy

When you first spin cloud deploy your application (with the sqlite_databases configuration, as shown above), Fermyon Cloud will create a new database for your Cloud application.

The new database is initially empty, so you will need to create tables (and potentially initial data) before it is useful. To do this:

  1. Find the name of your Cloud database. To do this, run the following command:
$ spin cloud sqlite list
Databases (1)
inspirational-pig (default)
  1. Run spin cloud sqlite execute, passing the name of the database and the SQL statement(s) needed to create your Cloud database’s tables and initial data.

See the SQLite documentation for information about the SQLite dialect of SQL.

$ spin cloud sqlite execute -d inspirational-pig "CREATE TABLE IF NOT EXISTS todos (id INTEGER PRIMARY KEY AUTOINCREMENT,description TEXT NOT NULL,due_date DATE,starred BOOLEAN DEFAULT 0,is_completed BOOLEAN DEFAULT 0)"

If you prefer to pass a file that contains the SQL you want to execute, you can do so by prefixing the file name with an @ like so:

$ spin cloud sqlite execute -d inspirational-pig @mysql.sql

Note: The Cloud database is completely unrelated to the local database, and must be prepared separately.

Explicitly Create A Database Via spin cloud sqlite create

  1. You can create your SQLite Database using the following command:

``

$ spin cloud sqlite create mydb
  1. Run spin cloud sqlite execute, passing the name of the database and the SQL statement(s) needed to create your Cloud database’s tables and initial data.

See the SQLite documentation for information about the SQLite dialect of SQL.

$ spin cloud sqlite execute mydb "CREATE TABLE IF NOT EXISTS todos (id INTEGER PRIMARY KEY AUTOINCREMENT,description TEXT NOT NULL,due_date DATE,starred BOOLEAN DEFAULT 0,is_completed BOOLEAN DEFAULT 0)"

Note: The Cloud database is completely unrelated to the local database, and must be prepared separately.

Now you can deploy your application and select mydb as the resource you would like to link your application to: 

```bash
spin deploy
Uploading todo-app version 0.1.0-r234fe5a4 to Fermyon Cloud...
Deploying...
App "todo-app" accesses a database labeled "default"
Would you like to link an existing database or create a new database?:
> Use an existing database and link app to it
  Create a new database and link the app to it

Since we chose to link to an existing database, spin deploy asks us which database we would like to link this app’s “data” label to:

Which database would you like to link to todo-app using the label "data":
> mydb
  finance
  projects
  inspirational-pig

Deleting the Cloud Database

Warning: using the delete subcommand is permanent.

You can delete your default database with the following command:

$ spin cloud sqlite delete inspirational-pig
The action is irreversible.
Please type "inspirational-pig" for confirmation: inspirational-pig
Deleting database ...
Database "inspirational-pig" deleted

Next Steps

  • For support or feature feedback, please join our Discord channel #no-ops-db-beta. This is the place where you can provide feedback and request assistance from the Fermyon Cloud team regarding all things SQLite Database.
  • Visit FAQ for frequently asked questions.