[Nuxt3] using API, SQLite, and SQL. (not ORM)

Introduction

In this post, I'll show you the simple example that how to use Nuxt3's "server api" and connect to SQLite.
I didn't use ORM(Object Relational Mapper), because I wouldn't like to focus on the usage of ORM.

I wrote about these topics below.

1. trying to use "server api" with Nuxt DevTools as client
2. preparing SQLite's table
3. trying to connect to SQLite
4. using env file to set "SQLite's Database file name"

[Environment]
node: v20.15.1
nuxt: 3.12.3
SQLite: 3.46.0
os: windows11

1. trying to use "server api" with Nuxt DevTools as client

Let's create "api" folder in "server" directory.
And put hello.ts into the "api" directory.

server\api\hello.ts

export default defineEventHandler(() => {
  return {
    hello: 'world'
  }
})

Now you can see the response by Browser.

http://localhost:3000/api/hello

You can get this response.

{
  "hello": "world"
}

Then, let's create "api_post.ts" in "api" directory.

server\api\api_post.ts

export default defineEventHandler(async (event) => {
    const body = await readBody(event); // using readBody method
    return body
});

You can send "POST request" by Nuxt DevTools.
You will get the same data that you send as the response.
like this.

2. preparing SQLite's table

I downloaded these two zip-files from SQLite homepage.

• sqlite-dll-win-x64-3460000.zip
  64-bit DLL (x64) for SQLite version 3.46.0.

• sqlite-tools-win-x64-3460000.zip
  A bundle of command-line tools for managing SQLite database files, including the command-line shell program, the sqldiff.exe program, and the sqlite3_analyzer.exe program. 64-bit.

Then I extracted these files and got five files.

• sqlite-tools-win-x64-3460000/sqldiff.exe
• sqlite-tools-win-x64-3460000/sqlite3.exe
• sqlite-tools-win-x64-3460000/sqlite3_analyzer.exe
• sqlite-dll-win-x64-3460000/sqlite3.def
• sqlite-dll-win-x64-3460000/sqlite3.dll

Then put these files your favorite directory.
like this.

C:\YOUR_FAVORITE_DIR\SQLite

I decided to create "db" directory for database.

C:\YOUR_FAVORITE_DIR\SQLite\db

And I created DB into the db directory

Use Command Prompt on Windows.
And execute sqlite3 command.

> cd C:\YOUR_FAVORITE_DIR\SQLite
> sqlite3 db/test.db

check db name on "sqlite3 prompt".

sqlite> .databases
main: C:\YOUR_FAVORITE_DIR\SQLite\db\test.db r/w

Then Let's make create_table.sql

db/create_table.sql

CREATE TABLE user_tb (
  id integer primary key,
  name varchar(10)
);

Then create a table on "sqlite3 prompt".

sqlite> .read db\create_table.sql
sqlite> .tables
user_tb

Then insert TestData into DB.

sqlite> insert into user_tb (id, name) values( 1, 'aaa' );
sqlite> insert into user_tb (id, name) values( 2, 'bbb' );
sqlite> insert into user_tb (id, name) values( 3, 'ccc' );

sqlite> select * from user_tb;
1|aaa
2|bbb
3|ccc

3. trying to connect to SQLite

Let's install SQLite modules into your project.

> npm install sqlite3
> npm i @types/sqlite3 -D

Firstly, let's try to read SQLite's database-file directly in our code.
let's create "hello_db.ts" in "api" directory.

note: When you write Path of Windows in a javascript file, You need to use slash "/" instead of backslash "\" to write Path.

server\api\hello_db.ts

import sqlite3 from 'sqlite3'

export default defineEventHandler(() => {
  const db = new sqlite3.Database('C:/YOUR_FAVORITE_DIR/SQLite/db/test.db')

  return new Promise((resolve, reject) => {
    db.all('SELECT * FROM user_tb', (error, rows) => {
      if (error) {
        console.log('error:');
        console.log(error);
        reject(error);
      } else {
        console.log('rows:');
        console.log(rows);
        resolve(rows);
      }
    })
  })
})

You will be able to access this URL with browser,
http://localhost:3000/api/hello_db

And you will get this response.

[
  {
    "id": 1,
    "name": "aaa"
  },
  {
    "id": 2,
    "name": "bbb"
  },
  {
    "id": 3,
    "name": "ccc"
  }
]

4. using env file to set "SQLite's Database file name"

Let's set "DB file name" to env file as "environment variables".
Firstly, create ".env.local" file.

.env.local

# server side setting
SQLITE3_DATABASE = 'C:/YOUR_FAVORITE_DIR/SQLite/db/test.db'

Modify "package.json".
When we run our server as dev-mode, we need to make the server read ".env.local".
So add the parameter "--dotenv .env.local" into "nuxt dev" command of "scripts" section in "package.json".

package.json

    ...
    "dev": "nuxt dev --dotenv .env.local",
    ...

Modify "DB file name" to "environment variables" in "hello_db.ts".
This time, I made a new file "hello_db2.ts".

server\api\hello_db2.ts

import sqlite3 from 'sqlite3'

export default defineEventHandler(() => {
  const dbfile = process.env?.SQLITE3_DATABASE
  if (!dbfile) return {error: '... error messages'}
  const db = new sqlite3.Database(dbfile)

  return new Promise((resolve, reject) => {
    db.all('SELECT * FROM user_tb', (error, rows) => {
      if (error) {
        console.log('error:');
        console.log(error);
        reject(error);
      } else {
        console.log('rows:');
        console.log(rows);
        resolve(rows);
      }
    })
  })
})

You can access this URL with browser,
http://localhost:3000/api/hello_db2

And you will also get the same response as "hello_db".