Create your first database

Create a local SQLite database from an Acore schema, then make one versioned schema change and see how rollback works. You need no sqlite3 command and no database server.

Diagram source

flowchart LR
  A[Version 1.0.0: tasks table] -->|axiom database migrate| B[Version 1.0.1: adds note]
  B -->|axiom database rollback| C[New compensating version: removes note]

Prerequisites

• Axiom CLI 0.149.1. This walkthrough is checked on macOS arm64. Local SQLite and PostgreSQL contracts are Experimental; see the support matrix.
• An empty directory named database-demo. Run every command from it.

1. Define a database

type = "database"
targets = ["server"]

project(id: "docs.database", version: "1.0.0")
module docs.database

database tasks(engine: DatabaseEngine.sqlite) {
  manage(schemas: [Schemas.main], mode: ManagementMode.ownedObjects)
  schema main {
    table tasks(strict: true) {
      id: Sqlite.Text()
      title: Sqlite.Text()
      primaryKey(columns: [.id], sqlName: "tasks_pkey")
    }
  }
}

The project version is the schema version. manage tells the CLI it owns the objects in schema main.

2. Create the database and a record

unset DATABASE_URL
axiom check schema.acore
axiom run schema.acore --no-shell --execute "INSERT INTO tasks(id,title) VALUES ('1','Learn Acore'); SELECT * FROM tasks;"
axiom database status schema.acore
axiom database history schema.acore

What you should see:

• axiom check prints Valid database contract: schema.acore.
• The first axiom run validates and applies version 1.0.0, then the query returns the row 1 | Learn Acore.
• status reports Drift: none, and history lists version 1.0.0.

Running the insert again fails with a primary-key conflict, so leave it out of later runs. Unsetting DATABASE_URL keeps the CLI from connecting to another database from your shell.

For an interactive SQL console, run axiom run schema.acore. Use \dt to list tables, ordinary SQL to read rows and \q to exit. The CLI prints the SQLite file path for external viewers. To choose the file location, pass --database-file ./tasks.sqlite on every command.

3. Make a schema change

Change the project version to 1.0.1 and add a nullable note column after title:

project(id: "docs.database", version: "1.0.1") 
// Inside table tasks:
note: Sqlite.Text(nullable: true) 

This is an excerpt; keep the rest of the schema.

axiom database migrate schema.acore
axiom database diff schema.acore --from 1.0.0 --to 1.0.1
axiom database history schema.acore

The migration adds note, and the existing task remains. diff shows the added column, and history lists both versions. Published schema versions are immutable: if you change a version that was already applied, the CLI asks for a new version instead of replacing its history.

4. Understand rollback

axiom database rollback schema.acore --steps 1

Read the prompt before you accept: removing note deletes its values. Rollback publishes a new compensating version, keeps the earlier history and leaves your source file unchanged. It does not restore deleted data. For flags used in automation, see Database contracts.

Troubleshooting

Cleanup

All data from this guide lives in the database-demo directory. Keep it to continue experimenting, or delete it when you no longer need its SQLite data and migration history.

Next steps

• Run your first app to connect a schema to an API and UI
• Database contracts for PostgreSQL and the full schema syntax
• Existing database to adopt a schema you already have