SECRET OF CSS

How To Use SQLite in Bun, Deno, and Node | by Jennifer Fu | Aug, 2022


Exploring the advantages and disadvantages of each

0*z1j6Y2DgzJyp45 Q
Photo by JOHN TOWNER on Unsplash

SQLite is a software library that provides a relational database management system (RDBMS). The lite in SQLite means lightweight in terms of setup, database administration, and required resources.

SQLite is the most widely deployed SQL database engine, which has the following features:

  • Self-contained: It requires minimal support from the operating system or external library. Therefore, it is easy to be embedded in iPhones, Android phones, game consoles, handheld media players, etc.
  • Serverless: It is integrated with the application that accesses the database. The applications interact with the SQLite database, and read and write directly from the database files stored on disk. It is different from a normal RDBMS, such as MySQL, which requires a separate server process to operate. SQLite does not require a server to run.
  • Zero-configuration: There is no package to be installed, no server process to be configured, started, and stopped. And SQLite does not use any configuration files.
  • Transactional: All transactions are fully ACID-compliant, i.e., Atomic, Consistent, Isolated, and Durable.

D. Richard Hipp designed SQLite in the spring of 2000, and the database engine is written in the C programming language. SQLite 1.0 was released on August 17, 2000. SQLite 3.0.0 was released on June 18, 2004. The latest version is 3.39.2. Version 3 has been maintained for 18 years, and it continues on.

From macOS X 10.4 and above, SQLite has been installed on macOS by default. It is not the latest version, though. Here’s how to do this:

Run brew upgrade sqlite3 to upgrade it.

However, it does not update the default SQLite. Instead, it updates /usr/local/opt/sqlite/bin/sqlite3.

Homebrew does not override the default SQLite because it is macOS’s default one, and overriding it may break the system in some unexpected ways.

SQLite can also be installed from the download page. sqlite-tools-osx-x86–3390200.zip is precompiled binaries for Mac OS X (x86) — a bundle of command-line tools for managing SQLite database files, including sqldiff, sqlite3, and sqlite3_analyzer.

sqlite3 is SQLite’s command-line utility, which allows the user to execute SQL statements manually against an SQLite database.

In this article, we are going to show the following ways to use SQLite:

  • Use SQLite in the command-line shell
  • Use SQLite in the Bun script
  • Use SQLite in the Deno Script
  • Use SQLite in the Node Script

We will compare Bun, Deno, and Node on how to connect and use SQLite.

We can use macOS default SQLite, or the homebrew-installed SQLite, or the downloaded SQLite to run the command.

In the following examples, we use homebrew-installed SQLite. Type sqlite3 with the full path to start the command-line shell:

If ...> is shown, instead of the execution result, it is usually caused by missing terminating ; or missing matching '.

  • At line 1, the SELECT statement is used to perform a simple calculation.
  • At line 2, it shows the result, 2.
  • Line 3 is not terminated.
  • Line 4 is terminated with ;.
  • At line 5, it shows the result, 2.
  • Line 6 is not terminated.
  • Line 7 is terminated with '.
  • Line 8 is terminated with ;.
  • At line 9, it shows the result, 2.

Besides SQL statements, the command-line shell also supports dot-commands, which begin with . at the left margin with no preceding whitespace.

The SQLite database is a file stored on disk. We can use sqlite3 to open a specific database file. If the file does not exist, a new file is created.

  • At line 1, a new database file is created in the current directory.
  • At line 4, .databases command is executed.
  • At line 5, it lists the names and files of attached databases.

We create the students table, which has been used in a MySQL article, using the following code:

  • At line 1, a table, named students, is created. This table has columns of id, name, age, and grade. Besides name is a variable-length string with the maximal length of 255. Other columns are integers.
  • At line 2, .tables command is executed.
  • At line 3, it lists table names.
  • At line 4, a new record is inserted into students.
  • At line 5, it selects data from students.
  • At line 6, the newly added record is displayed.

The database file is stored on disk:

All SQL commands in SQLite are the same as MySQL. But, SQLite is much more lightweight — self-contained, serverless, zero-configuration, and transactional.

Bun is a fast, modern, all-in-one JavaScript runtime with a native bundler, transpiler, task runner, and npm client built-in. Bun is zero-configuration, and it supports TypeScript out of the box.

bun:sqlite is a high-performance SQLite3 module, which can be used to execute SQLite statements. bun:sqlite is built-in to bun.js, and there is nothing to install except Bun itself. In a previous article, we have provided detailed steps on how to install Bun.

Here is a Bun script to create the students table.

  • At line 1, Database is imported from bun:sqlite.
  • At line 3, a database connection is created from the file, mybun.db. If the file does not exist, a new file will be generated.
  • At lines 4–6, a table, named students, is created. This table has columns of id, name, age, and grade. Besides name is a variable-length string with the maximal length of 255. Other columns are integers.
  • At line 7, a new record is inserted into students.
  • At line 8, it selects data from students, and displays the result on the console.

Execute this TypeScript, and we see the resulting array of one student record (line 3). Since Bun is proud of its speed, it always displays the time to execute the command (line 2).

After the execution, the database file is stored on disk with the following code:

For our small code snippet, the performance does not matter much. However, Bun does claim that it is faster than Node and Deno.

Performance comparison among bun:sqlite3, better-sqlite3, and deno.land/x/sqlite
Image from https://github.com/oven-sh/bun#bunsqlite-benchmark

Deno is a runtime for JavaScript, TypeScript, and WebAssembly based on the V8 JavaScript engine and the Rust programming language. It has secure defaults and a great developer experience. The name is an anagram of node, and it is also an acronym of DEstroy NOde.

On macOS, we can use homebrew to install Deno with this command:

% brew install deno

Unlike node supporting commands, node, npm, and npx, Deno only has a single executable, deno. For more details on Deno, please check out this article.

Deno provides a hosting service, https://deno.land/x, for Deno scripts. It caches releases of open source modules stored on GitHub and serves them in one easy-to-remember domain. https://deno.land/x/sqlite/mod.ts is a Deno SQLite module, which is downloaded to local cache when it is used. Deno is also zero-configuration.

Here is a Deno script to create the students table:

  • At line 1, DB is imported from https://deno.land/x/sqlite/mod.ts.
  • At line 3, a new database connection is created from the file, mydeno.db. If the file does not exist, a new file will be generated.
  • At lines 4–6, a table, named students, is created. This table has columns of id, name, age, and grade. Besides name is a variable-length string with the maximal length of 255. Other columns are integers.
  • At line 7, a new record is inserted into students.
  • At line 8, it selects data from students, and displays the result on the console.

Execute this TypeScript. Since Deno has secure defaults, we have to answer many questions to allow read on mydeno.db (lines 2–3), to allow us to write on mydeno.db (lines 4–5), to allow read on mydeno.db-journal (lines 6–7), to allow us to write on mydeno.db-journal (lines 8–9). And eventually, we see the resulting array of 1 student record (line 10).

After the execution, the database file is stored on disk:

The Deno script works similarly to the Bun script.

Node is an open-source, cross-platform, back-end JavaScript runtime environment that runs on a JavaScript Engine and executes JavaScript code outside a web browser, which was designed to build scalable network applications.

Node can be installed by NVM. Here is an example of installing Node 18.0.0:

better-sqlite3 is a node package for SQLite3. Since Node is not zero-configuration, the package must be installed first.

We reuse Create React App as a working environment:

% npx create-react-app my-app
% cd my-app

Set up better-sqlite3:

% npm install better-sqlite3

better-sqlite3 becomes part of dependencies in package.json:

Since Node does not support TypeScript out of the box, we will write a JavaScript file to simplify the setup.

Also, Node does not support ES modules out of the box. One solution is to end the script name with .mjs extension to instruct it to use ES2015 module notations.

Here is a Node script to create the students table:

  • At line 1, Database is imported from better-sqlite3.
  • At line 3, a new database connection is created from the file, mynode.db. If the file does not exist, a new file will be generated.
  • At lines 4–6, a table, named students, is created. This table has columns of id, name, age, and grade. Besides name is a variable-length string with the maximal length of 255. Other columns are integers.
  • At line 7, a new record is inserted into students.
  • At line 8, it selects data from students, and displays the result on the console.

Execute this JavaScript. better-sqlite3 echos SQL statements along the way (line2, line 3, and line 4). In the end, we see the resulting array of one student record (line 5).

After the execution, the database file is stored on disk. Here’s the code:

The Node script needs more setup compared to the Bun or Deno script.

We have seen SQLite as a lightweight relational database management system. It is self-contained, serverless, zero-configuration, and transactional.

We have compared Bun, Deno, and Node on how to connect and use SQLite. Apparently, Bun and Deno are more advanced as they support TypeScript out of the box and are zero-configuration.

Is it time to move out of Node? Is Bun or Deno ready for Primetime?

Thanks for reading.

Want to Connect?If you are interested, check out my directory of web development articles.



News Credit

%d bloggers like this: