# How to open or create a SQLite database

> Open/create a .db file with try-with-resources, use an in-memory database, and create tables.

Source: https://crossfly.dev/docs/cookbook/data/abrir-banco — CrossFly documentation. Full docs as one file: https://crossfly.dev/llms-full.txt

# How to open or create a SQLite database

**Purpose.** Open (or create, if it doesn't exist) a SQLite database and create its tables. In CrossFly the entry point is `SqliteDb`, an `AutoCloseable` handle — always use `try (...)` to guarantee the native connection is closed.

## APIs used
- `SqliteDb.open(String filename)` — opens/creates an **unencrypted** database in read/write (`OPEN_DEFAULT = OPEN_READWRITE | OPEN_CREATE`). Returns a `SqliteDb`.
- `SqliteDb.openMemory()` — opens an **in-memory** database (`:memory:`), volatile: it vanishes on close. Ideal for tests and temporary caches.
- `SqliteDb.exec(String sql)` — runs one or more statements **without a result** (DDL, PRAGMA, batch DML). Use it for `CREATE TABLE`, `CREATE INDEX`, `PRAGMA ...`. Throws `SqliteException` on error.
- `SqliteDb.close()` — closes the native connection. **Idempotent** (calling it twice is safe). `try-with-resources` calls this automatically.
- `SqliteDb.version()` — returns the SQLite library's version string (useful for diagnostics).

> **File path.** Pass a writable file path (per platform). Do not use `Gdx.graphics` for anything here; SQLite is independent of the UI. For the per-platform file location, see the storage guide.

## Full example (copy, paste and run)

```java
package com.exemplo;

import dev.crossfly.ui.*;                     // Window
import dev.crossfly.ui.layout.*;             // VerticalLayout
import dev.crossfly.sqlite.*;                // SqliteDb, SqliteException

/** Runnable main screen. Point the launcher (classname) and run: gradlew :lwjgl3:run */
public class MainWindow extends Window
{
   @Override
   public void onShow() throws Exception
   {
      super.onShow();
      setLayout(new VerticalLayout());

      // 1) File-backed database (created if it doesn't exist)
      try (SqliteDb db = SqliteDb.open("app.db"))
      {
         db.exec(
            "CREATE TABLE IF NOT EXISTS person(" +
            "  id   INTEGER PRIMARY KEY," +
            "  name TEXT NOT NULL," +
            "  age  INTEGER)");
         // ... use the database here ...
      }
      // connection already closed on exiting the try

      // 2) In-memory (volatile) database — good for tests
      try (SqliteDb mem = SqliteDb.openMemory())
      {
         mem.exec("CREATE TABLE t(id INTEGER PRIMARY KEY, v TEXT)");
         // ... lives only while 'mem' is open ...
      }
   }
}
```

> The snippet below shows **only the core**. To run it, place it inside the `MainWindow` skeleton above.

## Minimal snippet
```java
// verified in the source: SqliteDb.open / openMemory / exec
import dev.crossfly.sqlite.*;

// 1) File-backed database (created if it doesn't exist)
try (SqliteDb db = SqliteDb.open("app.db"))
{
   db.exec(
      "CREATE TABLE IF NOT EXISTS person(" +
      "  id   INTEGER PRIMARY KEY," +
      "  name TEXT NOT NULL," +
      "  age  INTEGER)");
   // ... use the database here ...
}
// connection already closed on exiting the try

// 2) In-memory (volatile) database — good for tests
try (SqliteDb mem = SqliteDb.openMemory())
{
   mem.exec("CREATE TABLE t(id INTEGER PRIMARY KEY, v TEXT)");
   // ... lives only while 'mem' is open ...
}
```

## Dig deeper
- [SQLite storage guide](/docs/data-and-services/storage-sqlite) · Development guide
- Sibling recipes: [Parameterized CRUD](/docs/cookbook/data/crud) · [Encrypted database](/docs/cookbook/data/banco-criptografado) · [Use outside the paint thread](/docs/cookbook/data/banco-fora-da-paint-thread)