# SQLite

SQLite is a compact, embedded relational database. It does not require a separate server to operate, which makes it 
Ideal for lightweight applications in development, testing and production. SQLite is used in cases where 
when complex transaction processing or large parallel load is not required.

SQLite provides support for core SQL features, including transactions, indexes, triggers, and most standard data types. 
SQLite database files typically have a `.db`, `.sqlite` or `.sqlite3` extension.

## Use in Amverum
A separate application/container is not required to use SQLite in Amverum. It is enough to specify the storage path in the code 
files of this database to permanent storage. If the library for your language requires manual creation of a database file or you need to initialize the database
existing file, then it must be uploaded through the interface by selecting the Data folder of the "Repository" tab.

![data_folder](../img/data_folder.png)
```{eval-rst}
.. admonition:: Important
   :class: warning

   Save data to the permanent storage folder specified in the configuration (by default `/data`).
   
   The `data` folder at the root of the repository and `/data` are different directories.
```
```{eval-rst}
.. admonition:: Attention
   :class: attention

   DB files saved in the Artifacts folder (where the code is run from) can be overwritten and lost when rebuilding the project.
```

To check where the data is saved, the Data folder in the "Repository" section. If the folder does not contain the required file, 
Probably, the data is saved in the Artifacts folder (you can see it the same way).

The correct path (assuming the default mount folder is `/data`) looks like this (don't forget about the / before data):

`/data/sqlite_database.db`

```{eval-rst}
.. admonition:: Clue
   :class: hint

   For ease of local testing and checking paths, you can create a /data directory on your local computer. 
   This will allow you to test locally using the correct paths when pushing the application to the cloud.
```

## Connection examples
The examples above assume that the persistent storage folder is mounted in the default `/data` folder.
### Python

In Python, the built-in sqlite3 library is often used to work with SQLite.

```python
import sqlite3

# Connect to a database (or create one if it doesn't exist)
conn = sqlite3.connect('/data/example.db')

# Create a cursor object to run SQL queries
cursor = conn.cursor()

# Execute the request
# cursor.execute('''CREATE TABLE stocks
# (date text, trans text, symbol text, qty real, price real)''')

# Closing the connection
conn.close()
```

### Java

In Java, you can use a JDBC driver to connect to SQLite, for example, SQLite JDBC from xerial.

```java
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class Main {
    public static void main(String[] args) {
        Connection connection = null;
        try {
            // Connecting to the database
            String url = "jdbc:sqlite:/data/database.db";
            connection = DriverManager.getConnection(url);
            
            System.out.println("Connection to SQLite successful");
            
            // There may be requests and data processing here
            
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        } finally {
            try {
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException ex) {
                System.out.println(ex.getMessage());
            }
        }
    }
}
```

### JavaScript (Node.js)

In Node.js, SQLite can be worked with through the sqlite3 package, which must be installed via npm.

```javascript
const sqlite3 = require('sqlite3').verbose();

// Connect to the database
let db = new sqlite3.Database('/data/example.db', sqlite3.OPEN_READWRITE | sqlite3.OPEN_CREATE, (err) => {
    if (err) {
        return console.error(err.message);
    }
    console.log('Connection to the SQLite database was successful.');
});

//Close connection
db.close((err) => {
    if (err) {
        return console.error(err.message);
    }
    console.log('Database connection closed.');
});
```